SSAS: Currency Conversion (Many-to-Many)

Original Link: SSAS: Currency Conversion (Many-to-Many)

This article is a complement of the book "SSAS Step by Step 2005". In this book, Reed Jacobson and Stacia Misner gave us some guidelines and generic directions about how to apply currency conversion based on many-to-many relationships, but they did not provide a step-by-step case. In order to make it complete, I will try to describe how I work on it, and I hope you can join in the discussion.

First of all, let's suppose that you have finished the exercises described before the part of "Supporting Currency Conversion". So that we can do the work based on a qualified "SSAS" cube.

1. Go to DSV, right click DSV pane, click ADD/Remove tables, add FactCurrencyRate and DimCurrency into DSV and click "OK"

2. Right-click "Diagram Organizer" to create a new diagram, and add DimCurrency, FactCurrencyRate, FactInternetSales and DimTime into it.

3. Go to "Cube Structure", right-click "Measures" to add a new Measure Group "Fact Currency Rate"

4. After we add the measure group "Fact Currency Rate", change the property "Type" of it to "ExchangeRate", expend it then
4.1 Right-click the measure, "Average Rate", to show its properties, change AggregateFuction to "AverageOfChildren"
4.2 Right-click the measure, "End of Day-Date", to show its properties, and change AggregateFuction to "LastNonEmpty". Save the project.

5. In Solution Explorer, right-click the folder "Dimensions" to create a new dimension using Dimension Wizard.

6. Make sure "Build the dimension using a data source" is chosen and the check box of Auto Build is clear, click Next

7.Available data source view should be "SSAS Step by Step DW" and click Next.
8.Dimension Type should be "Standard dimension"

9.Choose dbo.DimCurrency in the step of "Select the Main Dimension Table", key column should be "CurrentKey", member name can be "CurrencyName", click Next.

10. At the step "Select Dimension Attributes", make the setting like following, and click Next.

11. At the step "Specify Dimension Type", set Currency ISO Code to Currency Alternate Key, set Currency Source to Dim Currency, which is in fact CurrencyKey. Click Next.

For Currency ISO Code, please refer to:
http://www.iso.org/iso/support/currency_codes_list-1.htm

12. At the step "Define Parent-Child Relationship", click Next.

13. At the step "Completing the Wizard", name the dimension as "Currency". Click Finish.

14. Double click "Currency.dim" in Solution Explorer, rename the attribute "Dim Currency" to "Currency" and "Currency Alternative Key" to "Currency ISO Code"

15. Go to Cube Structure, and add Currency in the dimensions, then deploy the project. Save the project.

16. Right-click SSAS.cube to run Business Intelligence Wizard. Choose "Define currency conversion" and click Next (before we start to use BI Wizard, we should deploy the project first).

17. At the step "Set Currency Conversion Options", choose "Fact Currency Rate" and set other options like the following, then click Next.

18. Since we just simply use currency conversion to apply an exchange rate to measures, at the step "Select Members", we will only check Reseller Sales Amount and Internet Sales Amount. Click Next.

19. Select "Many-to-Many" as Conversion Type, then Next.

20. The step "Define Local Currency Reference" will be like the following, usually we do not need to change anything, so we just click Next.




http://msdn.microsoft.com/en-us/library/ms175660.aspx

Local currency:

The currency used to store transactions on which measures to be converted are based.
The local currency can be identified by either:
A currency identifier in the fact table stored with the transaction, as is commonly the case with banking applications where the transaction itself identifies the currency used for that transaction.

A currency identifier associated with an attribute in a dimension table that is then associated with a transaction in the fact table, as is commonly the case in financial applications where a location or other identifier, such as a subsidiary, identifies the currency used for an associated transaction.

21. At the step "Specify Reporting Currencies", select all reporting currencies, click Next.

22. At the step "Completing the Wizard", notice that BI Wizard will generate script about currency conversion in the script of the cube. This part gives us an idea, that is if we find there is something wrong with the result generated by BI Wizard, in order to roll back the state before we run BI Wizard, we can go the script of the cube, find the script of currency conversion and delete it, then deploy the project to roll back.
Click Finish and save the project.

23. Go to DSV, we can find a named query "Reporting Currency", and set CurrencyKey as Logical Primary Key. Relate "Reporting Currency" to "FactInternetSales" and "FactResellerSales" by dragging CurrencyKey to them.

24. Go to Dimension Usage. Click the intersection of Reporting Currency and Fact Currency Rate, set a regular relationship between them as follows.

25. Click the intersection of Reporting Currency and Internet Sales, set a many-to-many relationship between them as follows, then do the same thing to Reseller Sales. Save the project.

26. Deploy the project. We may run into the following issue. It tells us that there is something wrong with the named query, Reporting Currency.
-----------------------------------------------------------------------------------------
Error 1 Dimension 'Reporting Currency' > Attribute 'Currency' : The 'Integer' data type is not allowed for the 'NameColumn' property; 'WChar' should be used. 0 0
-----------------------------------------------------------------------------------------

27. Double click "Reporting Currency.dim" in Solution Explorer, and check the properties of the attribute "Currency" of Reporting Currency. We can find BI Wizard incorrectly set the data type of NameColumn to Integer. We need to reset it to WChar. Then deploy it again.

27. The deployment run into another issue which is saying "Conversion failed when converting the varchar value 'Afghani' to data type int " as follows.

28. In order to solve it, go to DSV, right click Reporting Currency to edit the named query

29. We can notice that the query tries to union 2147483647 which is an integer and CurrencyName which is varchar together. This might be a reason to explain the error.
So how about we update the second part of the union as follows:
---------------------------------------------------------------------
SELECT DISTINCT NULL AS [Local 0], Null AS Local, NULL AS [Local 2]
FROM DimCurrency
---------------------------------------------------------------------

When we deploy it again, it runs into another issue. It seems there should be a member called [Local] in Reporting Currency.dim

So let's edit the name query again and this time we will update the second part of the union as follows and deploy the cube.:
---------------------------------------------------------------------
SELECT DISTINCT NULL AS [Local 0], 'Local' AS Local, NULL AS [Local 2]
FROM DimCurrency
---------------------------------------------------------------------
Deployment Completed Successfully!!!

30. Drag Currency from Reporting Currency, Average Rate from Fact Currency Rate and Reseller Sales Amount and Internet Sales Amount in Cube Browser, we can get the final result!

31. Properties of the attribute of Reporting Currency/Currency (To be continued)
Because the attributes, "Currency" and "Currency ISO Code" are not groupable attributes, we should set the property "AttributeHierarchyEnabled" to False, but we need to set "IsAggregatable" to True. I will also talk about this later

Obama about Big Data

OBAMA talks about Big Data



* Is not the real President Obama

How Much Data is Created Every Minute?

Posted by Neil Spencer

The Internet has become a place where massive amounts of information and data are being generated every day. Big data isn’t just some abstract concept created by the IT crowd, but a continually growing stream of digital activity pulsating through cables and airwaves across the world. This data never sleeps: every minute giant amounts of it are being generated from every phone, website and application across the Internet. The question: how much is created, and where does it all come from?
To put things into perspective, this infographic by DOMO breaks down the amount of data generated on the Internet every minute. YouTube users upload 48 hours of video, Facebook users share 684,478 pieces of content, Instagram users share 3,600 new photos, and Tumblr sees 27,778 new posts published. These are sites many people around the world use on a regular basis, and will continue to use in the future. The global Internet population now represents 2.1 billion people, and with every website browsed, status shared, and photo uploaded, we leave a digital trail that continually grows the hulking mass of big data. See where else big data is coming from in the infographic below

IT Training By Experts

At Technitrain we believe the best way to learn a technology is to learn from an expert. Our courses are taught by top consultants who have a wealth of hands-on experience to share and who can answer all of your difficult questions. You'll acquire the practical skills you need to do your job as well as learn the tips and tricks that only the experts know.

Here is the link to more information.

Our trainers are the best in their field: Microsoft MVPs, authors and well-known bloggers such as Chris Webb, Gavin Payne, Christian Bolton.

The Big Data Fairy Tale

By Roel Castelein

Fairy tales usually start with ‘Once upon a time ...' and end with ‘... And they lived long and happily ever after'. But nobody explains ‘how' the heroes live long and happily ever after. Big data (analytics) promise to transform your business, but just as in fairy tale endings, big data will not explain ‘how' to transform your organization. In my view, big data might spark some behavioral change or open people's minds, but it will not transform organizations. At best, big data evolves organizations. Let's look at the concept and a concrete example to draw conclusions.


What big data analytics does is take a bunch of data, analyze and visualize it, and then derive insights that potentially can improve your organization or business. Based on these insights the actual transformation can begin, but it requires more than just big data. Let's have a look at a classic example of data analytics; the reduction of crime in New York under Mayor Giuliani with the help of CompStat.

CompStat is a data system that maps crime geographically and in terms of emerging criminal patterns, as well as charting officer performance by quantifying criminal apprehensions. The key to success was not the data or analysis, but that the organizational management that used the data and analysis was effective. Processes, structures and accountability were setup to drive the transformation. In weekly meetings, NYPD executives met with local precinct commanders from the eight boroughs in New York to discuss the problems. They devised strategies and tactics to solve problems, reduce crime, and ultimately improve quality of life in their assigned area. CompStat tracked the results of these strategies and tactics, and whether they were successful or not. Precinct commanders were held accountable for the results.

Drawing upon my own experience, I know how difficult an organizational transformation is. Even if you have the data and the analysis that shows things need to change, it requires much more than data analysis. Let's assume that the data uncovers opportunities for improvement, either in reducing cost or in increasing revenue. The next step is to design the changes in processes, in people's roles, in org charts and in the systems. This usually entails a two pronged approach; communicate the change in org charts, processes and roles, and engrain these changes in the systems to track the change results. This tracking creates a feedback loop, necessary to manage the transformation.

Another challenge in the big data transformation message is finding the right people. Ideally the team leading the transformation needs to understand an organization's data, enriched with outside data, then know how to do data analysis, and once the results are there, strategically communicate the change to get everybody on board. Next, the transformation team needs to set up a tracking and feedback process that holds participants accountable for the transformation results. And when participants do not play along, have an escalation process in place, with the possibility for punitive measures.

In the same way that Giuliani fired one of the precinct commanders when he showed up drunk at the first CompStat meeting, big data systems require a complementary management philosophy to ensure whatever transformational insights are derived get implemented and controlled.

So, when the advertisements claim that big data will transform your business, remember that big data brings the potential for transformation, not the actual transformation. That still requires commitment and hard work, just like ‘living long and happily ever after'. That's why they are called fairy tales.

How Big Data Brings BI and Predictive Analytics Together

Big data is breathing new life into business intelligence by putting the power of prediction into the hands of everyday decision-makers.


For as long as anyone can remember, the world of predictive analytics has been the exclusive realm of ivory-tower statisticians and data scientists who sit far away from the everyday line of business decision maker. Big data is about to change that.
As more data streams come online and are integrated into existing BI, CRM, ERP and other mission-critical business systems, the ever-elusive (and oh so profitable) single view of the customer may finally come into focus. While most customer service and field sales representatives have yet to feel the impact, companies such as IBM and MicroStrategy are working to see that they do soon.

Big Data Moves Analytics Beyond Pencil-Pushers
Imagine a world in which a CSR sitting at her console can make an independent decision on whether a problem customer is worth keeping or upgrading. Imagine, too, that a field salesman can change a retailer's wine rack on the fly based on the preferences that partiers attending the jazz festival next weekend have contributed on Facebook and Twitter.
Big data is pushing a tool more commonly used for cohort and regression analysis into the hands of line-level managers, who can then use non-transactional data to make strategic, long-term business decisions about, for example, what to put on store shelves and when to put it there.
However, big data is not about to supplant traditional BI tools, says Rita Sallam, Gartner's BI analyst. If anything, big data will make BI more valuable and useful to the business. "We're always going to need to look at the past…and when you have big data, you are going to need to do that even more. BI doesn't go away. It gets enhanced by big data."
How else you will know if what you are seeing in the initial phases of discovery will indeed bear out over time. For example, do red purses really sell better than blue ones in the Midwest? An initial pass through the data may suggest so—more red purses sold last quarter than ever before, therefore, red purses sell better.
But this is a correlation, not a cause. If you look more closely, using historical transaction data gleaned from your BI tools, you may find, say, that it is actually your latest merchandise-positioning-campaign that's paying dividends because the retailers are now putting red purses at eye level.
That's why IBM's Director of Emerging Technologies, David Barnes, is actually more inclined to refer to the resulting output from big data technologies such as Hadoop, map/reduce and R as "insights." You wouldn't want to make mission-critical business decisions based on sentiment analysis of a Twitter stream, for example.

Infographic explains Lifetime Value of a Customer

++ Click Image to Enlarge ++



Source: How To Calculate Lifetime Value