Sunday, July 24, 2005 - Posts
Did you ever try to setup international reporting with SSRS? It will be a very hard job or better, it doesn’t work. I hope that this will change (perhaps with SP1) but I don’t know for sure.
So why isn’t multilingual reporting easy? Microsoft introduced the Unified Dimensional Model (UDM) as a common data source for reporting. This is a cool thing, I like it very much. The UDM also has “translations” for each member/dimension/level name and you can define different sources (fields) for each language you define.
So what? This seams to be perfect, what am I complaining about? From the standpoint of the UDM everything is fine. But now we try to use these features in a report.
Which language to use?
So first you have to know which language you want to display in the report. How do you tell the UDM which language you’re currently working with? That happens with a parameter in the connection string. IDW15 has a bug in it, so this will not work perfectly. Brian has a blog about that… http://blogs.msdn.com/bwelcker/archive/2005/07/03/435130.aspx
Translating Names
OK, so you dropped your tables in the report, you have column headers and footers, everything seams to be fine. And now you expect that these header/footer are translated automatically!? No, this is not possible (out of the box). The only way I know is by creating a new cube (or a separate UDM) where you store all the report/dimension/level/attribute names and issue a query (well, you need one query per name you want to have!) and exchange each multilingual text with a link to the query result… That’s a bunch of work and it’s not easy to manage.
Perhaps now you say: Why a new UDM, the names are all already translated in the UDM so why setup a new database/UDM to store them? The answer is easy: Maybe you simply need other labels in the report than you have already in the UDM. I.e. “Transactions per Second” might be a good measure name but on the report you only want “TPS” because it’s shorter… Where do you want to store this information? Perhaps in a different language “TPS” is not the correct abbreviation? So you need a different table to store this information because the UDM simply doesn’t have any place to store it!
Translating Report Manager
Microsoft did a good job to translate the Report Manager. It switches automatically the language depending on the system’s language settings. That’s great. But the report/folder names don’t switch! So your report “Sales Report” appears with the same name for German users, too. That’s not good… The only workaround we found out is that you create folders per language and place each report in with localized names in the corresponding folders. Much work, hard to support…
Translating Parameters
If you work with parameters you want different parameter captions for each language. Guess what you can do? Correct: Nothing. It’s not possible; at least we didn’t find any workaround for that but to create a report per language and well, that’s not what we/you want…?!
Free-Text Parameters
Well, a little off-topic… But did you ever try to pass free-text parameters to an UDM? Did you ever try to tell your users that they have to enter “[Time].[Year].[2005]” instead of “2005” when they want only 2005’s data? Then you have to deal with strtomember() functions and so you loose the functionality of the query designer… Or do you see any alternative for that? I don’t…
So thanks for my colleagues at Software4You (http://www.software4you.com) for providing input for this post. We are all waiting for some feedback (from Microsoft?) about what is planned in the future to address these issues…
Thomas
Microsoft tried to include best practices and other common tasks you had to implement on your own in Analysis Services 2000 in the new 2005 release by wizards. What do I have to say – THANKS for that! However there are some problems arising…
One is the currency conversion… Microsoft added a nice wizard for that. It handles the currency table and all the exchange rates. The result is a calculation added automatically in the cube. Here’s an example:
// <Currency conversion>
// Currency conversion wizard generated script.
// Currency conversion generated on: Freitag, 24. Juni 2005 11:44:51
// by user: cthiell
// Currency conversion type: OneToMany
// Selected members to be converted: Kosten des Einkaufs
// Please be aware that any changes that you decide to make to it may be overridden the next time you run the Currency Conversion wizard again.
// All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension
Scope ( { Measures.[Price]} );
Scope( Leaves([Date]) ,
Except([Reporting Currency].[Currency].Members, [Reporting Currency].[Currency].[Currency].[EUR]));
// This section overrides the Pivot Currency values with the Converted value for each selected measures/account members/account type members needing to be converted with Measure rate FAKT KURS - FAKT KURS KURS
// LinkMember is used to reference the currency from the source currency dimension in the rate cube.
Scope( { Measures.[Price]} );
This = (Measures.[Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) * [Reporting Currency].[Currency].[EUR];
End Scope;
End Scope;
So what does this script do? It simply exchanges the calculation of the measure “Price” for all currencies when the currency selected is not the currency the measure is stored in. So if you use “USD” instead of “EUR” each day’s value is multiplied by each day’s conversion rate (which is 100% correct) and then aggregated.
OK, so what’s the problem? Well, I see two problems:
-
If you have any calculated measures based on measures working with currency conversions, the calculated measures don’t work anymore. This might be a problem of my limited MDX knowledge but at the moment this is a fact for me…
-
If you have a complex cube you will notice significant performance degradation. Why? Well, that’s easy… Instead of retrieving the year’s sales (that’s already pre-aggregated) each day sales is queried, multiplied by the conversion rate and then aggregated... This is really a significant overhead…
I didn’t try to use a different approach… This would be that I change the Cube’s underlying Data Source view to do a cross join of the fact table and the currency conversion table. So I would have a fact record per currency with the correct conversion rate. This would end up in a much bigger cube but performance should be better (since everything can be aggregated) and calculated measures should be no problem anymore.
Any comments are very much appreciated…
Thomas
One of the very nice enhancements of SQL Server 2005 Integration Services (SSIS) compared to DTS is the introduction of the wizard for slowly changing dimensions (SCD). It makes it easy to create dimension tables storing changing data and tracking these changes. There are some blogs about that, i.e. Jamie has a nice example for a package using the wizard… http://blogs.conchango.com/jamiethomson/archive/2005/06/06/1543.aspx
However I didn’t find anything about how to use a slowly changing dimension table in the fact load process. The problem is that you have to find the right record in the dimension table which fits to the fact record. This might be an exact match but more likely you have to deal with a “best fit”.
A “classical” example would be: You have a table tracking which sales representative is responsible for a customer. The table has the following fields:
Customer_ID
Salesperson_ID
Start_Date
End_Date
So the customer with the ID 1 can be related to salesperson ID 1 from 1/1/2002 to 12/31/2003 and to salesperson ID 2 from 1/1/2004 to 12/31/2004. This would end up in two records in this table.
The SCD wizard does a great job maintaining this table (I’ll probably blog about some problems with the wizard later…). But how do you find out which salesperson was responsible for customer ID 1 when you try to process an invoice issued on 5/5/2002? You can’t use a Lookup transform for that because this needs an exact match in the dimension table and what should be the key for that match?
I see two approaches for this problem, one I already tried, one I like to evaluate later.
OLE DB Command
The first way to work with SCDs is to use an OLE DB command to do the lookup. I did that using a small stored procedure which finds out the right ID corresponding salesperson for the fact record. It receives the Customer ID and the date of the fact record. The procedure executes a SQL command finding out the “best match” for the fact record and returns the correct Salesperson_ID. Something like a
CREATE PROCEDURE dbo.P_Salesperson
@Customer_ID [int],
@date [smalldatetime],
@Salesperson_ID [int] OUTPUT
WITH EXECUTE AS CALLER
AS
select @Salesperson_ID=Salesperson_ID
from Salesperson_SCD
where isnull(Start_Date,'1900-01-01')<= @date
and isnull(End_Date,'2078-12-31')>= @date
In the OLE DB command transform you issue a
exec P_Salesperson ?,?,? output
Certainly you have to take care about the correct parameter mapping. But that’s all. It works, however it works slowly. The reason for that is that the OLE DB transform executes a SQL command per record flowing through the SSIS pipeline. This makes it very slow…
Lookup & Cross Join
I didn’t try the second approach, yet. But this one might be quite fast, however it will be a solution only for some scenarios.
The Lookup transform is very fast, much faster than executing one command per record. But how do you create a lookup table in a scenario like I described before?
The trick would be to do a cross join for the salesperson table with the date table (I assume that you have one…). So the result would be a table where you have one record per customer per day. The SQL for the lookup would be something like a
Select customer_id, date (select salesperson_id from salesperson_SCD where salesperson_SCD.customer_id = customer.customer_id and start_date<=date and end_date>=date) from customer, date
This might be quite a resultset and when it’s completely loaded into memory it might be a problem. But if that works, this approach seams to me like a very good solution.
There is another problem with this solution. You need a date table to do this cross join and the granularity of the table is the same as the referenced data. What happens when you track the SCD not by day but by second? Would you really setup a date table with records for each second? That would be a huge resultset of the cross join…
Conclusion
So what do we learn out of that? The best thing I can imagine is that Microsoft (or someone else?) provides a more flexible Lookup transform. This would accept not only exact match but also ranges (like the start_date and the end_date), “less than”, “greater than”, …. All other solutions I can imagine have their cons. Or perhaps do YOU have a better approach? I would be happy to see some feedback on this post…
Thomas