November 9, 2011 at 11:46 am
I started my journey into SSAS 2 days ago ... so if this post sounds naiive, it probably is.
First task was to build a simple cube, and then facilitate currency convertions.
As per the few examples found on Google:
> Fact Transaction table (with each row possessing it's own local currency.)
> Currency table (Key | Name)
> Exchange Rate Table ( CurrFrom | CurrTo | Date | Rate)
> Date Table
> Reporting Currency table (To any currency)
All regular relations except Fact_Transaction table to ReportingCurrecy which is Many-to-many with intermediate being ExchangeRate.
Then I tried the built-in BI wizard ... and then examine the results.
Whilst I could see that the exchange rates for each combination of local & reporting currencies was correctly displayed, my sales measure to be converted, was not shown at all.
Investigating, I saw in the Wizard's script, that the generated code includes a reference to "[Reporting Currency].[Local]"
On viewing the value of [Reporting Currency].[Local], I found it to be null.
I am currently assuming that this refers to some values that the BI Wizard generates when you set your Pivot Currency ?
So when I selected USD as my Pivot Currency, the wizard converts all my Fact table's sales figures into USD and stores them somewhere with a currency description of 'Local' ??
If this is correct, then where does it store them, and why is my [Reporting Currency].[Local] Null ??
In the end, I scrapped the Wizard Code and simply entered a MeasureExpression of [NETCUSTOMERREVENUE]*[RATE]
... and now my cube returns my sales data converted into my reporting currency.
Is there anything wrong with using the 'simple' solution I ended up with ?
Am I 'cheating' or missing some vital consideration that I do not (yet) know about ?
P.S.:
If anyone wants to reccommend a good book to purchase as an intro into SSAS & MDX, feel free to do so.
Particularly interested in reading material that explains 'why' as well as 'what' to do.
Any maybe one that doesn't think the whole world works exaclty the same way as Adventureworks does ?
Many Thanks
Confused Newbie ....
November 11, 2011 at 8:27 pm
I like the step-by-step books. Also a big fan of the new mdx cookbook.
November 14, 2011 at 1:57 am
step by step is good.
Wizard is ok for some basic stuff but you really cant beat a good 'human' design.
My choice would be to populate the transaction table with multiple transaction values so you get one sale that has a value of £1, 1.35Euro, $1.30 etc. Then all you have to do in the cube is selct the correct currency you want to see. I would say this gives you a lot better speed of processing the data into the cube and an easier cube to maintain.
Mark
November 14, 2011 at 2:22 am
Thanks for the book reccommends folks, I'll take a look at both.
I'm with the theory of storing all currencies, but I'm going to have +/- 100 measures that will need converting ... and maybe a dozen currency options.
You didn't know that of course 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply