Archive

Archive for May, 2015

MDX #46–6 different ways to detect a particular member in MDX script

May 14, 2015 Leave a comment

There are many great tips in MDX with SSAS 2012 Cookbook

The book MDX with SSAS 2012 Cookbook has many great tips for MDX script writers.

Here are two that are from Chapter 5 Navigation:

  • detecting a particular member by comparing object with keyword IS is better than comparing name
  • using SCOPE() statement in MDX script is a more “permanent” solution than calculations in MDX query

MDX script writers frequently need to include or exclude a particular member in a calculation. The first step is to determine the member exists in a hierarchy.

The book provided great recipes on how this can be done in MDX queries. In this blog, I’ll focus on how this can be done in MDX scripts.

If you have questions about the following concepts, please refer to Chapter 5 Navigation:

  • Iteration on query axes
  • Currentmember function
  • IS keyword
  • SCOPE() statement
  • Member’s unique name
  • Why we should avoid comparing names
  • Why using SCOPE() statement is a better way in MDX script

6 different ways it can be done in MDX script

Suppose that we need to detect the NA member in the Color hierarchy of the Product dimension. The result should show us TRUE for color NA only.

Color Member is detected
Black
Blue
Grey
Multi
NA TRUE
Red
Silver
Silver/Black
White
Yellow
Assembly Components

 

Here are 6 different ways it can be done in MDX script (there are more ways if you insist; see the screen shot below).

  1. Member is detected 1 – Name: compare the Name property of the current member
  2. Member is detected 2 – INTERSECT-COUNTING: intersect with the current member and then use Count()
  3. Member is detected 3 – Uniquename: compare the Uniquename property of the current member
  4. Member is detected 4 – VALUE: compare the full value of the current member
  5. Member is detected 5 – IS: use IS to compare member object
  6. Member is detected 6 – SCOPE: use SCOPE() statement

The solutions are increasingly better from 1 to 6, with number 1 being the worst, and number 6 much better. Avoid number 1 and 2; Number 3 is similar to number 4 and 5; Number 6 is a better choice in MDX script.

Become familiar with the SCOPE statement

Check out this link:

http://sqlbits.com/Sessions/Event8/Fun_with_Scoped_Assignments_in_MDX

Chris Webb did a wonderful job in this “Fun with Scoped Assignments in MDX” video.

 

SCOPE

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

ETL #72–Your data can mysteriously disappear after a WHERE clause was added (2)

Why a seemingly innocent WHERE clause can cause large amount of data missing

In the previous post, ETL #71–Your data can mysteriously disappear after a WHERE clause was added (1), I talked about how I discovered a WHERE clause was added to a main procedure that processes the raw data prior to loading the data into the fact table.

ecda1.[CUSTOM_DATA_8] <> ‘null’

A quick check of the raw data shows that out of 1.3+ million rows, there is only one row that has string value of ‘null’ in ecda1.[CUSTOM_DATA_8], and also that about 20% of the rows has SQL NULL value in the field CUSTOM_DATA_8. It’s these 20% of the data that was also filtered out along with the one ‘bad’ row.

The missing 20% of the data has SQL NULL in the field

The field [CUSTOM_DATA_8] on table alias ecda1 has one row that has a string value of ‘null’. But about 20% of rows has a value of SQL NULL.

We all know a thing or two about the SQL NULL:

  1. SQL NULL is the term used to represent a missing value.
  2. A NULL value in a table is a value in a field that appears to be blank.
  3. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces.

IS NOT NULL is implied whenever we add a filter

But the most important thing about the SQL NULL is this: IS NOT NULL is implied in the WHERE clause whenever we add a filter of either equality or inequality.

So if we add a filter as:

ecda1.[CUSTOM_DATA_8] <> ‘aStringValue’

Then, we really mean this:

ecda1.[CUSTOM_DATA_8] <> ‘aStringValue’

AND

ecda1.[CUSTOM_DATA_8] IS NOT NULL

If we add a filter as:

ecda1.[CUSTOM_DATA_8] = ‘aStringValue’

Then, we really mean this:

ecda1.[CUSTOM_DATA_8] = ‘aStringValue’

AND

ecda1.[CUSTOM_DATA_8] IS NOT NULL

Now you can understand why those 20% of rows with SQL NULL values are missing.

Adding a WHERE clause during data processing in the production environment of ETL is almost never a good idea

You probably will also understand why it is not a good idea to add WHERE clauses to the data processing stage in ETL.

In production, WHERE clauses should not be added unless it’s a documented business requirement.

In data discovery stage, WHERE clauses are used only for data discovery purpose and should not be carried over into production.

Handling of “bad” or missing dimensional data

If these “bad” data are part of the data you will use to slice and dice your data, then there are many ways you can handle them. How to handle these “bad” or missing dimensional data is beyond the scope of this short blog.

%d bloggers like this: