Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

How do you create a cross tab report using a SSAS OLAP cube


By:   |   Last Updated: 2011-07-25   |   Comments (3)   |   Related Tips: > Analysis Services Development

Problem

I have a need use a few different rating methods to classify my customers as Great, Very Good, Good, etc.  We are using SQL Server Analysis Services and I have heard you can create a cross tab report between two members on a same dimension of a SSAS cube?  Do you have any suggestions on how to implement this functionality?  Check out this tip to learn more.

Solution

Cross tab reports are useful to find data transition/associations between two members. Real world examples include: using cross tabs to find swap sets, credit rating transition among different scoring models, etc. For this tip, let's consider a scenario where we score/rate our customers using different rating methods and classify them as "Great", "Very Good", "Good", "Not Good" or "Bad". Now we want to compare the data between different scoring methods to find swap sets. We might find that some customers who were classified as "Great" with one scoring method might not be classified as "Great" with a different scoring method.  In this circumstance, we want to apply different business strategies.  So let's jump right in.

Below is the schema we will use for this example:


SSAS Cube Implementation

In order to create cross tab reports between members of the same dimension we need to design a many to many relationship in the SSAS cube. Below is the relational schema of the model.  nq_Fact_CustomerRating_CT is a named query on "Select * from Fact_CustomerRating", this will be a factless fact table in the model.

Below is the Dimension Usage of the model, it includes two new dimensions Classification_CT and Rating_CT which will be used for slicing and dicing on the factless fact table.

Let's look at the data using the cube browser, we have 9 customers and 2 rating methods which classify the customers into 5 categories.

Now by adding the Rating_CT and Classification_CT on the column axis we can generate a cross tab report as shown in the figure below.  The data is filtered to show Algorithm1 on Rows and Algorrithm2 on Columns. This cross tab report is very straight forward to find swap sets; for example it shows that there is 1 customer who is rated as "Not Good" with Algorithm1 where as the same customer is rated as "Great" with Algorithm2. By identifying these swap sets and depending on how far apart they are business analysts can deploy different strategies to these customers.


Next Steps


Last Updated: 2011-07-25


get scripts

next tip button



About the author
MSSQLTips author Vamsi Bogullu Vamsi Bogullu is a Database Architect administering database servers, developing applications and designing enterprise BI solutions.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, March 13, 2015 - 4:32:30 PM - Prasad Back To Top

Hi Siddharth / Vamsi,

Did you find any better option to implement cross tabulation in same dimension in SSAS multidimensional cube? Option given by Vamsi is working perfectly fine with small data but for large data it is giving me performance issue. In my case there is one more issue is I will have to use distinct count measure.

 

Regards,
Prasad.


Monday, July 25, 2011 - 3:43:06 PM - Vamsi Back To Top

Hi Siddharth,

The main purpose of the tip is to show how to generate the data that can be used for cross tab reports across the members of the same dimension. 

Analysts have multiple options to get to the data, either using excel or other reporting tools that are available for reporting. Excel was used as en example in the tip, the same data can be generated with a MDX query and can be used in reports.

Fact table Fact_CustomerRating in the demo doesnt  have to be a factless fact table, it was just for simplicity reasons used as a factless fact table in the demo since I was using only the counts in the demo. Where as nq_Fact_CustomerRating_CT should be a factless fact table.

As stated above the emphasis was to show the data generation for doing swap set analysis/cross tabs across multiple members of the same dimension. For a simple cross tab report a many to many relationship design is not required.

Thanks,

Vamsi. 


Monday, July 25, 2011 - 2:26:09 PM - Siddharth Mehta Back To Top

How do you consider the view in OWC Browser as a cross-tab report ?

Do you expect the analysts to open up SSAS project, browse Cube data using this browser, and consider the same as the report ?

From the schema you have demonstrated, the fact table looks like a factless fact. Why such design ?

You have mentioned that to create a cross tab report, you have to design many to many relationships. Do you mean to say that schema design is driven by report requirements ? Wouldn't that make the design too complex ? What if the report requirement changes to pivot table kind of repoting ?

In my opinion, OWC is just a way of browsing the data, and calling it a cross tab report is way too misguiding. If you have created SSRS Cross tab report using data from OLAP Cube, it makes sense. OWC Browser is not meant for reporting and you cannot create any reports out of it.

 

Regards,

Siddharth Mehta


Learn more about SQL Server tools