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

 

Handle wrong results in SSAS for unrelated dimension and measure groups


By:   |   Last Updated: 2010-12-15   |   Comments (1)   |   Related Tips: > Analysis Services Dimensions

Problem

While browsing a cube, if you are using a dimension and fact that are not related, the result can be quite confusing as the results appear as repeating values across all columns and/or rows depending upon the query. The repeating values are sliced or non-sliced aggregated values of the measure. No result or a NULL result would provide a more prompt hint that the dimension and measure group might be unrelated, rather than repeating the same values. In this tip we look at how to deal with this problem.

Solution

To recreate the problem, we need a dimension and measure group on a cube that are not related.

Before we can simulate the problem, deploy the AdventureWorks SSAS sample project that ships with SQL Server, so that we have the AdventureWorks cube. After you have deployed the cube, follow the below steps to simulate the problem.

1) To find out whether a dimension and measure group are related, we can use the "Dimension Usage" tab of the cube designer. Double-click the cube and select "Dimension Usage" tab. You will see a lot of measure groups and dimensions. Use the filters on dimensions to select "Reseller" and "Product" dimension and use filters on Measure Groups to select "Internet Sales" and "Reseller Sales" measure group. After you apply the filters your screen should look as shown below.

use the "Dimension Usage" tab of the cube designer

2) We can clearly see that the "Reseller" dimension is not related to "Internet Sales" measure group. Now let's try to browse these dimension and measure groups. Switch to the "Browser" tab, drag-and-drop "Business Type" attribute from "Reseller" dimension on the rows axis, "Category" attribute from "Product" dimension on columns axis. Now we can see the results where measure groups are related as well as non-related.

3) Clear the details area. Drag-and-drop "Reseller Sales Amount" measure from "Reseller Sales" measure group which is a related measure group to both the dimensions, on the details area. The result should look as shown in the below screenshot.

we can see the results where measure groups are related as well as non-related.

4) Drag-and-drop "Internet Sales Amount" measure from "Internet Sales" measure group which is a non-related measure group to both the dimensions, on the details area. The result should look as shown in the below screenshot. Even if you remove "Business Type" attribute from the rows axis, the values would remain the same and the only change would be that there would be a single row.

the values would remain the same and the only change would be that there would be a single row.

5) Only when you investigate values, or check the pattern of repeating values across various columns, you can predict that there's something wrong and the probable reason can be that one of the dimension and measure groups are unrelated. If the query would be narrow enough to result in a single cell, one cannot predict whether the result is undesirable due to unrelated dimension and measure groups. If this value is returned as NULL, this would clearly indicate at least some issue with the result. To achieve this result, there is a measure group property named "IgnoreUnrelatedDimensions". Select the "Internet Sales" measure group, and change the value of this property to "False" from its default value which is "True". Deploy this change on the server.

there is a measure group property named "IgnoreUnrelatedDimensions"

6) Repeat step 4 and you should get the result as shown in the below screenshot.

this small property solves our purpose of achieving blank values instead of incorrect values

This small property solves our purpose of achieving blank values, instead of incorrect values, which is mostly the aggregated value of the measure.

Next Steps
  • Query the cube which involves unrelated dimension and measure group, where the result is a tuple i.e. a single scalar value.
  • Test whether you are able to confidently predict if the result is correct, if this value was blank or with an incorrect value.


Last Updated: 2010-12-15


get scripts

next tip button



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

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.



    



Monday, March 23, 2015 - 8:46:09 AM - bbeeharry Back To Top

I have got an issue whereby although the dimension is related to the fact, it produces repeating values.

Can you please advise what is the issue?


Learn more about SQL Server tools