I've had an 8.4 cube running for a client for several years with some facts in Canadian dollars and a conversion table to convert CAD to USD. There is one rate per month in the conversion table, and facts are all dated on the first of the month.
Things were running fine until the client decided that facts of type "B" should use th Budget conversion rate and facts of type "F" and "A" should use a different rate. So now I will need two rates for each month for one country. Anyone have any ideas on how I could structure a new currency table? And yes, the client will expect to see budget, forecast, and actual figures in the same view.
Do you have separate fact tables for each of the different facts with the currency table aliased and joined to each or do you have multiple metrics in a single fact table with the currency table joined to it?
Single fact table with multiple metrics (types F,A,B) with the currency joined to it.
Oh geez...long day. I wasn't focusing on the Transformer aspect of this! The measures are currently coming in with conversion to USD already applied? Or do you have both CAD and USD measures available?
I'm thinking you'd expand your currency table to include the additional rates since they pertain to the same time frame and join in the same way to your fact table. Then you could just do the math for the appropriate measure/FX rate combination.
Yes, the measure (only one measure) comes in all USD. But in the cube the client will sometimes want to see CAD.
Here's what's bothering me about this. Before I loaded the currency table from a data source, and all that had to happen was that the fact joined to the currency by the date. But now I can't do that anymore. I can certainly add a rate type (FAB) to the currency table. The fact table already has the type. But it looks to me like I'm limited to four columns in the currency table...is that true? If I'm not limited, I think I can see how to do this. What do you think?
Looks ugly. I was thinking you'd feed in the measures already converted to USD by expanding the currency table in your data source. Then maybe feed in another set of metrics in CAD. Like I said, not pretty.
Maybe others with better experience dealing with currency conversion challenges will chime in.
Well, it's a thought, maybe instead of using currency conversion in the cube, this becomes another dimension - one set of data for USD, one set for Canada, select the currency as a filter from another dimension. Not pretty but it might work. We'll see if someone comes up with another solution. Thanks, Lynn.
Here's what the client and I decided on. We will add a new measure "Amount CAD" to the fact table and to the cube. I can calculate the Amount CAD in the database using the preferred conversion rate and display it in the cube as a second measure. When the user selects this measure, all will be displayed in CAD.
The downside of having no display below the grid "Canadian Dollars" was judged to be acceptable since "Amount CAD" appears in the upper left part of the grid.
Thanks to those who answered!