0

Dimension for Currency Rates

Malcolm Johnson

September 07, 2018 11:21

None

Follow

 Hi All

I have created Finance and PR & AR cubes but the customer has now asked for me to show a conversion rate / factor in use at a given date.  so far I have tried to create a shell dimension based on a Rates table that I have brought into the DW

The Dimension I have created has three levels:

1. Currency

2. Start Date

3. Factor

 

the problem is that I can't get it to resolve to show all the dates when I group it under currency

 

There should be about 10 dates under each currency

 

Any thoughts on what I am doing wrong

 

Thanks

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  David Zebrowitz September 07, 2018 13:05

  First thought is that the key field is set to currency and SSAS doesn't like duplicates. Try making it a composite key with Currency and Date, or use a Surrogate Key instead.

• 0
  
  Malcolm Johnson September 07, 2018 13:11

  Thanks David

  How do I create a composite or Surrogate Key

   

• 0
  
  David Zebrowitz September 07, 2018 13:23

  Here's a KB article for creating a composite key in a Cube dimension:  https://jetsupport.jetreports.com/hc/en-us/articles/219401407-How-to-create-a-composite-primary-key-relation-in-the-Cubes

   

  Surrogate Keys are slightly more involved in setting up and are used for slowly changing dimensions.  However, it is really a Data Warehousing best practice to use them when joining any fact table to any dimension table.  If you're unsure of how to implement them, I'd stick with the composite key above for now.  If you're interested in learning more, the Jet Enterprise User Guide has more details on History Tables and Slowly Changing Dimensions.

  Another alternative you may want to consider is actually adding the currency conversion to your fact table.  For example, if the client wants to have all Balances converted to USD, you can bring in the conversion factor from your table as a lookup and then use a custom field with a transformation to do the actual conversion.  That field could then be added to the cube as "Balance USD".

• 0
  
  Malcolm Johnson September 07, 2018 13:38

  David

  Thanks again for your thoughts,  I originally worked on the assumption that I could do the conversion based on the posting date of the transaction, and for the income statement this is what I am doing.  However the customer has asked for the Balance Sheet values to be valued at an As-At date so I need to bring the LCY values into the report can calculate the reporting currency rate in the report.  As a result the Income Statement values are calculated from the Posting Date conversion rate and are held in the cube measures, the Balance Sheet in converted at the As-At rate,  as a result I am trying the bring the rates as a value that I can calculate on the Currency Code and the Start Date within the report