Problem

Today, I received a special requirement from my client that they wanted to build a report for counting distinct Brand Name of Digital Media Type. For example, a data source as below

And the client expected the output as below

As you saw two above image, the client only wanted to count unique Brand Name of Digital. Other Media Types are excluded (Brand Count is NULL). The number of distinct values for (2017-October, Digital ) is 2 because there are two Brand Name and so on.

Solution

There are some approaches for this kind of requirement. We definitely create a calculated field and when we query data for the report, we combine IIF statement to check in case Media Type is Digital or not.

However, I would like to share with you another approach that we use SCOPE statement.

Syntax

SCOPE(Subcube_Expression)
[ MDX_Statement ]
END SCOPE

Assuming that I developed a OLAP cube with SSAS and I created a calculated field that named as Brand Distinct Count. The script to create this calculated field, we only count unique BrandName in case Media Type is Digital

CALCULATE;
CREATE MEMBER CURRENTCUBE.[Measures].[Brand Distinct Count]
AS NULL, /*DISTINCTCOUNT([Brand Name].[Brand Name].[Brand Name]),*/
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Fact Brand’ ;
/*Scope for Digital */
SCOPE
(
[Media Type].[Media Type].&[Digital]
);
Measures.[Brand Distinct Count] = DISTINCTCOUNT([Brand Name].[Brand Name].[Brand Name]);
END SCOPE;

Now, let’s see how we query data with MDX to build the report

SELECT ([Measures].[Brand Distinct Count]) ON COLUMNS,
NON EMPTY([Year Month].[Year Month].[Year Month],
[Media Type].[Media Type].[Media Type],
[Media Sub Type].[Media Sub Type].[Media Sub Type]) ON ROWS
FROM [Brand]

Conclusion

SCOPE is used for many situations and it is quite useful  if you want to work on a sub-cube, that you can not execute with IIF or CASE…WHEN statement because they require input MDX expression. For example, a retail company needs to see how much profit will be impacted if they increase price of some products in some their stores, other stores will still keep current price. In this case, SCOPE is a suitable approach.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.