Monthly Archives: June 2014

FOREX Conversion in OLAP Cube

I was conducting training recently where the participants asked for a solution on how to convert data from many different currencies into a target currency using an exchange rate table. For those building enterprise cubes this might be a familiar problem since consolidations of amounts across reports is usually a challenge. I remember seeing an example of this implementation in Adventure works database but figured I should do a cut down version focusing just on the above topic. So here is how I did it.

The first step was to create the required tables so here is a screenshot of my main tables and the data in them.

The first table is a date table to capture exchange rates for different days.

 

The next table is a forex table which contains rates for different weeks, you can easily extrapolate this for a daily rate.

The next table I needed to create was a join between the Forex table and the date table to get rates for each date. Ideally here I am creating a list of all possible rates for all possible dates. If you look closely at the data you will notice that I am converting between USD to GBP < INR < FRF and EUR and vice versa. Naturally you need to keep in mind the conversions you require and create the table accordingly. If the source and target currencies are the same then you will need to add a row for USD to USD conversion with a Exchange rate of 1 ( I haven’t included it in this example).

The last table I have is my sales table where the data is currently stored.

Now I move on to my cube Datasource view.

In my cube I have created appropriate dimensions and measures. And an calculated measure called conversion which is amount* value.

With the dimensions and measure created here is what my Mapping looks like

Notice that date and currency dimensions access the sales fact table via a factless fact table called Measuretable.

Deploy and process the cube and the result will look as below. Notice the parameter for Target Currency shows as INR. Essentially picking up only the exchange rates where the destination currency is INR , the source could be any Currency whose amount is then multiplied by the target exchange rate to give the converted value.

And that’s all it takes to convert currencies across different countries. Hope you found the blog useful , please feel free to comment if you have any queries.

5 Mary series, SSIS using TSQL in MSBI

An All new video on using TSQL task in SSIS to run a query and fetch data from the database and assign it to a table. All part of the Mary series of videos. We are following a new pattern with these videos, trying to keep the short and more focused around a single task. Hope you like the video and as always look forward to any feedback.

http://youtu.be/vAeRDOREv9s

Unable to establish server mode SSAS tabular mode

Ever encountered the above error when starting SQL server management studio and connecting to SSAS ? The error has a few different root causes one of which is detailed in the link below.

https://connect.microsoft.com/SQLServer/feedback/details/686801/ssms-raises-unable-to-establish-server-mode-error-if-ssas-encounters-errors-during-startup

However this is not the only cause, the error can even occur if the databases are not operational. E.g. in this case the database was corrupt it opened fine in SQL Server 2014 management studio but refused to open in SQL 2012 management studio.

Once the databases were dropped and I tried connecting to Analysis Services and it went thru fine.

Here is another blog that covers the issue as having the root cause as incorrect default database in connection sting.

http://cwebbbi.wordpress.com/2012/05/28/unable-to-establish-server-mode-error-in-ssms/

Not getting options when right clicking the report in report manager

I came across this issues today while doing a training today reporting services report manager doesn’t display the options when you right click the report . See the screenshot below.


I was getting no response when I should have been getting

The reason for this is due to the Report manager not being rendered properly in IE , in order to overcome this you need to start the window in Compatibility mode. How?

On IE open the Compatibility view settings as show below

 

If your do not see the above menu , simply press Alt +V

In the above option add the Report manager URL where it says Add this website and press Close and your good to go.

The world needs to watch this

 

We are not promoting this video as an ad but simply a social awareness program. It hits home on the fact that Simple is Better doesn’t apply to just Business but even Lifestyles. A few simple things we do every day could mean the difference.

https://www.youtube.com/channel/UCpfAIVLEdyTp-HoALoz-gRw

How would you get the most out of 10 Rs?

 

Today, we were chatting about efficiency and how it is different from utilization and we thought we would conduct a poll about it. So here is the background. Utilization simply means consumption e.g. you can burn up 40 ltr of petrol every week but how efficient are you i.e. How much distance did you travel?

Simply speaking I can write a blog of 1000 words and still not have any meaningful content in it or I can tweet a sentence and it could change a person’s life. The same thing goes for time; you utilize 8 hours a day in office, the question is how many hours are actually efficient (where work gets done)? This brought us to the next question. If we lived efficiently which would be most important thing to be efficient about?

  • Time
  • Money
  • Energy – Fuel, power, etc.
  • Relationships – confused? A million Facebook friends don’t count against the one person who you can depend on.

The human body is a perfect example of efficiency, we extract the most amount of energy for our biomass, our brain is the most compact and yet more powerful computing machine there is.

We figured money is the best bet for everybody since better use of money promises to give the best results to society as a whole. So what would you do if you were given 10 Rs, how would you make the most out of it? Why 10 Rs, because 1 is too little and 100 is too much for any creative answers. The best answer will win a free book on any technology topic they want as long as it doesn’t cost more than 500 Rs on Flipkart.

Here is a good link on the topic.

 

Part 3 of 4: Video on Difference between partitioning and just multiple FGs

 

Sorry it took so long but here is the third video in our 4 part series on MS SQL Server partitioning.

http://www.youtube.com/watch?v=GSNYGpNbidc

I cover the difference between simple Files and Filegroups, show a real time example of proportional filling and the advantages good design has on DR.