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

Nordic Choice Business Intelligence

Here is a video where shows the job that me and BI collegues from Nordic Choice Hotels have done last 2 years.
Also we collaborated with Platon (www.platon.net) in UX and Dashboard Designing of our Business Intelligence Solution.



A Visionary Choice - Nordic Choice Hotels Business Intelligence vision from Platon on Vimeo.

SSAS 2005: Cube Performance Tuning Lessons

Intro.

A recent project has forced me (which is a good thing) to learn both the internals of SSAS 2005 as well as various performance tuning techniques to get maximum performance out of the OLAP server. It goes without saying that the grain of both your underlying data warehouse's Dimensions & Facts will drive how large your cubes are (total cube space). It also should be a given that Processing Time & Query (MDX) Execution Time usually compete with one another. Given the same grain of a model, the more Grain Data, Indexing, and Aggreggations you process upfront should generally result in a more performant end-user experience (but not always). And while ETL & Cube Processing time is of importance, in the real-world it is the end-user experience (capability and performance) which drives the DW/BI solution's adoption!

Throw-out unused Attributes/Optimizing Attributes/Leverage Member Properties

The more dimensional attributes you create infers a larger cubespace, which also means more potential aggregations and indexes. Take the time to review with your clients the proposed set of attributes and be sure they all provide value as a 1st class Dimension Attribute. Also, if you find attributes are used primarily for informative purposes only consider leveraging Member Properties instead of creating an entire Dimension Attribute. Also, the surrogate key for your dimensions almost never add business value, delete those attributes and assign the keyColumns of your dimension's grain member (ie it's lowest level) attribute to the surrogate key. If an attribute participates in a natural hierarchy but is not useful as a stand-alone attribute hierarchy you should disable it's hierarchy via the AttributeHierarchyEnabled setting. Finally, be aware that if you have a 'deep' dimension (ie like 19 million members) at its lowest grain, any additional attributes you add will incur much overhead as they have a much higher degree of cardinality.

Set Partition Slices

The question of whether or not you must explicitly set a partition's SLICE property is clearly documented incorrectly in SQL Server 2005 Books Online (BOL). For all but the simplest partition designs you should consider setting the SLICE property to match the source property (ie the dataset definitions should match across both properties). For those who do not know, a partition's SLICE is useful for query execution purposes, the SLICE tells the formula|storage engine which partition(s) hold the data that is being requested of it. Please see resources section below for more information on this.

Optimizing Attribute Relationships

Attribute relationships are the developer's mechanism to inform the OLAP server of the relation between attributes. Just like Fact Tables (measure groups) relate to dimension in various ways (Regular, Referenced, Fact, Many-to-Many), dimension attributes can relate to one another in various forms (One-to-One or One-to-Many).Also, you can set the RelationshipType to Flexible or Rigid. If you know your member's change over time (ie reclass), make sure to leave this setting as Flexible, otherwise set it to Rigid. Take the time to thoroughly review your attribute relationships and ensure that both represent their natural hierarchy and are optimized!

Scalable Measure Group Partitioning & Aggregation Strategies

This is one of the better known techniques but it is still of utmost importance. Make sure to design your measure group's partitions to optimize their performance (both processing and query execution). If your system has a 'rolling window' requirement also account for this in your ETL design/framework. You should almost always partition your measure groups by the DATE_KEY and match the underlying relational data warehouse (RDW) partitioning scheme. The basics of this is your 'hot' (the current period) partition should be optimized for query-execution time via setting a different Aggregation Design as opposed to the 'colder' (older) partitions. Also, if your main storage device (ie SAN usually) cannot hold all of your required data, consider leveraging Remote Partitions to offload the extreme 'cold' partitions to slower storage.

Continuously Tune Aggregations Based On Usage

Most documentation in the community clearly states the order of creating effective aggregations is to first leverage the Aggregation Design Wizard, enable the Query Log, and then re-tune the aggregations using the Usage-Based Tuning Wizard. What is not mentioned (near enough anyway) is to continuously retune your aggregations using a refreshed Query Log using the Usage-Based Tuning Wizard. By doing so you are ensuring your aggregations are periodically revised based up recent, real-world usage of your cubes.

Warming the Cache

Another well known technique...by issuing real-world MDX queries onStartUp of the MSOLAP service your cube's cache will be pre-optimized.

Be Mindful of Many-to-Many Dimensions Performance Implications

While M:M dimensions are a powerful feature of SSAS 2005, that power comes at the cost of query-execution time (performance). There are a few modeling scenarios where you almost have to leverage them but be aware that if you are dealing with large amounts of data this could be a huge performance implication at query-time.

Control of the Client Application (MDX): That is the Question

A lesser discussed matter yet still very important is how much control you have over the MDX issued to your cubes. For example, Excel Pivot Tables and other analytical tools allow the user to explore your cubes with freedom pending the security (no Perspectives are not a security measure). If you can write (or control) the MDX being issued by the end-user then obviously you have more control to ensure that actual MDX is optimized.

Got 64-Bit? Multi-Cores?

For enterprise-class Microsoft DW/BI engagements forget about x86/single-core, period. Analysis Services can chew through (process) more data, in higher-degrees of parallelization with x64 multi-core CPUs. Storage|Formula engine cache rely on memory...long-story short, Analysis Services has been designed to perform at higher levels of scalability when running on multi-core/x64 CPUs. Also, be sure to set Analysis Service's Min/Max Thread settings properly for both Query & Processing.

Conclusion

I am dedicated to life-long learning. I cannot take full credit for my content above as much of this knowledge was the work of others as well as my own testing. The resources section listed below gives credit where it is due accordingly. Take the time to learn and implement highly-performant SSAS 2005 cubes to ensure your project's stakeholder’s first query is a performant one!

Resources

Mosha Pasumansky's Blog (MDX 'Father'): http://sqlblog.com/blogs/mosha/default.aspx

Microsoft SQL Server 2005 Analysis Services (best SSAS 2005 OLAP internals book out!) by SAMS Publishing: http://safari.samspublishing.com/0672327821

SQL Server Analysis Services 2005 Performance Tuning Whitepaper (a great tuning document): download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc

HP Solutions with Microsoft SQL Server: http://h71028.www7.hp.com/enterprise/cache/3887-0-0-0-121.html

My Friends & Colleagues at Scalability Experts

Analyzing data

As you can imagine, the amount of data contained in a modern business is
enormous. If the data were very small, you could simply use Microsoft Excel
and perform all of the ad-hoc analysis you need with a Pivot Table. However,
when the rows of data reach into the billions, Excel is not capable of handling
the analysis on its own. For these massive databases, a concept called OnLine
Analytical Process (OLAP) is required. Microsoft’s implementation of OLAP is
called SQL Server Analysis Services (SSAS), which I cover in detail in Chapter 8.
If you’ve used Excel Pivot Tables before, think of OLAP as essentially a massive
Pivot Table with hundreds of possible pivot points and billions of rows
of data. A Pivot Table allows you to re-order and sum your data based on different
criteria. For example, you may want to see your sales broken down by
region, product, and sales rep one minute and then quickly re-order the groupings
to include product category, state, and store.
In Excel 2010 there is a new featured called PowerPivot that brings OLAP to
your desktop. PowerPivot allows you to pull in millions of rows of data and
work with it just like you would a smaller set of data. After you get your Excel
sheet how you want it, you can upload it to a SharePoint 2010 site and share
it with the rest of your organization.
With PowerPivot you are building your own Cubes right on your desktop using
Excel. If you use PowerPivot, you can brag to your friends and family that you
are an OLAP developer. Just don’t tell them you are simply using Excel and
Microsoft did some magic under the covers.
When you need a predefined and structured Cube that is already built for
you, then you turn to your IT department.