Modeling and Architect for Currency Conversions 1

[sunnyphoenixier]

I am in the design phase of one project that requires currency conversions for multiple facts by using a currency exchange table. The difficulties I am having is that it feels like requires calculations across two fact tables. Here are the table structures:

Fact_Table
-------------------
Product_key
Department_key
Manufacture_Cost
Manufacture_cost_Currency_Code
Shipping_Cost
Shipping_Cost_Currency_Code
Storage_Cost
Storage_Cost_Currency_Code
Date


Currency_Exchange_Table
------------------
From_Currency_Code
To_Currency_Code
Rate_Effective_Start_Date
Rate_Effective_End_Date
Exchange_Rate

Notes:
1. As you can see, the key in Currency_Exchange_Table is the first 4 columns.
2. All conversions for all cost use the same Date column in the Fact_Table. The Date needs to fall inbetween of Rate_Effective_Start_Date and Rate_Effective_End_Date.
3. The To_Currency_Code is alwasys "USD".
4. The multiple currency codes in the Fact_Table are supposed to join to the From_Currency_Code in the Currency_Exchange_Table
5. The ideal SQL I am trying to get is

Select Product_Key,
Department_Key,
max(Manufacture_Cost*Exchange_Rate),
max(Shipping_Cost*Exchange_Rate),
max(Storage_Cost* Exchange_Rate)
from Fact_Table a11,
Currency_Exchange_Table a12,
Currency_Exchange_Table a13,
Currency_Exchange_Table a14
where a11.Manufacture_cost_Currency_Code = a12.From_Currency_Code
and a11.Shipping_Cost_Currency_Code = a13.From_Currency_Code
and a11.Storage_Cost_Currency_Code = a14.From_Currency_Code
and a12.Rate_Effective_End_Date <= a11.Date <= a12.Rate_Effective_Start_Date
and a13.Rate_Effective_End_Date <= a11.Date <= a13.Rate_Effective_Start_Date
and a14.Rate_Effective_End_Date <= a11.Date <= a14.Rate_Effective_Start_Date
Group by Product_Key,
Department_key

I am sure many of you have been through this situation. My questions to you are:
1. Is this a good model to attack the currency conversion problem?
2. If it is the model you use, what do you suggest for MSI Architect? What relationships do you create to ensure joins are correct?
3. Do you have a better approach to suggest?

Any suggestions are greatly appreciated!

Phoenixier

 

[entaroadun]

First of all, you will never get MSTR to generate any join other than an equijoin in an attribute relationship. You might be able to jury rig a transformation with an ApplySimple, but why give yourself the headache?

I would encapsulate the logic in a view. Create a view called USD_Fact_Table that joins Fact_Table to Currency_Exchange_Table three times with the relationships you have described. Model this view into MSTR.

If you are using a decent commercial RDBMS (even SQL Server will work), it will be smart enough to know that it doesn't have to materialize every join in the view definition unless you are specifically asking for the fields. For instance, if you aren't accessing Storage_Cost, the RDBMS should know that it doesn't have to process that particular currency conversion join.

If the query optimizer isn't working right, usually defining a foreign key constraint will make the RDBMS smarten up. If this doesn't work, materialize the view.

It's worth the extra space and ETL time. Trust me. There's no reason to play architecture god if you don't have to. Even if you get MSTR to play along, you will be facing down performance headaches. To quote the infomercial, "Just set it and forget it.

 

[sunnyphoenixier]

entarodun,

That is a wonderful response! I totally agree with you on the principal. There are many things OLAP tools can do great jobs on, and there are also times that we need to leave the work to ETL and RDBMS. This is probably one of those cases.

On the other hand, unequal join is something I am facing in multiple places. I am afraid that I have to manipulate the data relationships in MSI a little bit. Any suggestions on that?

Thanks,

Phoenixier

 

[entaroadun]

You won't need to manipulate anything. Assuming your conversion tables are clean, i.e. there are no overlapping time periods, you should be fine. Just model the view into MSTR with the fact fields as US currency. MSTR doesn't have to know that the underlying data is in foreign currency and that you are joining to convert it.

My only question would be: Are the GROUP BY and MAX necessary? I would think that if the conversion tables are clean, the view wouldn't increase the # of records in the fact table.

 

[sunnyphoenixier]

Entaroadun,

Using a view is probably the best approach of doing this. For my project here, however, after dealing with push-backs too much from all "political parties", I had to make this work through MSI without a view. I did get the SQL I want and here is the relationships I specified in MSI.

1. In the conversion table, set Convert_From_Currency as the Key (the child to everything else in this table such as To_Currency_Code, Rate_Effective_Start_Date, Rate_Effective_End_Date). The other form of this attribute in the fact table is, for example, Manufacture_cost_Currency_Code. Name this attribute: Manufacture Cost Currency. (The Exchange Rate has a Join Child of all other columns in conversion table.)

2. I have to repeat this process to create multiple attributes, for this example, including Shipping Cost Currency, Storage Cost Currency. In terms of Conversion table, I have to creat Table Aliases in MSI for each attribute.

3. Date is the one I need to do unequal joins on. Here comes the questionable part, I set Date as a Child of those Currency attribute such as Manufacture Cost Currency (M:1).

4. Create Filters qualify on [Effective Start Date]@ID<+[Date]@ID<=[Effective End Date]@ID.

5.The SQL appears to be correct: a11.Manufacture_cost_Currency_Code = a12.From_Currency_Code and a12.Rate_Effective_End_Date <= a11.Date <= a12.Rate_Effective_Start_Date

I know the relationship I specified here are nature relationships But it seems like there wouldn't be a case that it cause incorrect SQL in the future.

Like I said, the view is probably the best way of doing this, but if anyone needs to do it through MSI, this process works.

I really appreciate your excellent response!

Phoenixier

 

[entaroadun]

This is absolutely the correct way to implement the joins you need. Excellent work!

It's amazing how much politics insert themselves into stupid views on a database. In my experience, there is much resentment on the part of DBAs at MSTR and other ROLAP applications. They don't cooperate, opting instead to highlight the "deficiencies" of the tool. I don't understand why so many programmers insist on SDKing everything. Stuff comes out of the box for a reason. There are economies of scale to consider.