Archive

Archive for the ‘MDX Challenges’ Category

MDX #41–Remove employees with less than $100,000 sales

May 30, 2014 Leave a comment

Table of contents

Need to remove employees who have zero sales amount
Create a calculated measure with the IIF() function
The NON EMPTY keyword will take care of the removal
Ordering results with both numeric and string expression
IIF() statement VS. Scope() statement in MDX Cookbook 2012

Need to remove employees who have zero sales amount

I recently helped a co-worker with a simple MDX query. All she wanted to do is to remove employees from the return set who has no sales or who has zero $ sales. She was using the NON EMPTY keyword on both the X and Y axis. That removed employees who have no sales, but the employees who have zero sales amount are still showing in the data set.

A quick and simple way to remove those employees with zero sales amount is to create a calculated measure in the query. This new calculated measure will simply use the IIF function to turn the sales amount into a NULL value, when the sales amount is zero. The NON EMPTY keyword on the Y axis will automatically take care of the removal of those employees with zero sales amount.

Create a calculated measure with the IIF() function

Suppose that this is our initial MDX query, where all employees (at leaf level of the Employees hierarchy) who have sales are returned.

 

image

Now you would like to only see employees who have more than $100,000 sales. In the following modified MDX query, I added this calculated measure. 

[Measures].[Reseller Sales Amount > 100,000]

This measure is simply identical to [Measures].[Reseller Sales Amount], except that it is set by the IIF function to be NULL, when it is less than 100,000.

The NON EMPTY keyword will take care of the removal

The NON EMPTY keyword on the Y axis will automatically take care of the removal of those employees with sales less than 100,000.

 

image

Ordering results with both numeric and string expression

To be sure that we get only the employee who have more than 100,00 sales amount, we can sort the results by the sales amount using the order() function with this numeric expression.

[Measures].[Reseller Sales Amount > 100,000]

 

image

 

If you insist to sort the results by the employees’ name, use the employee names as a string expression in the order() function.

[Measures].[Employee name]

 

image

IIF() statement VS. Scope() statement in MDX Cookbook 2012

In the book MDX Cookbook 2012, we have quite extensive examples on how to use the query-based IIF() statement, as well as the the cube-based Scope() statement.

You can check out the recipe Detecting a particular member of a hierarchy while you are using the book.

Categories: MDX Challenges Tags: , ,

SSRS #74 – 2008 Cascading bug when you have a middle parameter

May 9, 2014 2 comments

Some SSRS reports have a large number of parameters

I recently had a chance to help a co-worker to modify an existing SSRS+Cube report. The first thing that caught my eye is the large number of parameters in the report.

Although I never really developed any reports that have more than, let’s say, 6 parameters, I can understand why developers sometimes need to use, let’s say more than 20 parameters. This happened most likely because, the report developer is lack of authority to re-design the cube, or because the report has unnecessarily complex design. Another possibility is that the developer had made conscious decision to avoid writing MDX queries and shifting the coding to the Reporting Services.

A bug in SSRS 2008

Here is a scenario where developers sometimes might need to resolve to using a hidden parameter, that sits in the middle of two parameters. In the diagram below, the middle parameter will take the user input from parameter 1, and have some IIF statement to transform the input, and then the dynamically set value will be the input for the dataset 3 which feeds parameters 3.

clip_image002

I’ve tested this type of "cascading" parameters (with a middle parameter that has a dynamically set default value). The short conclusion is this: in SSRS 2008, the parameter 3 failed to reflect user’s section in parameters 1; in SSRS 2012, all is good.

So I had to say that this is a bug in SSRS 2008.

Remove the middle parameter to work around the bug

To work around the bug, fortunately, we have a pretty simple solution. Simply remove the middle parameters, AND then code the IIF logic in the query parameter for dataset 3 (which feeds parameter 3).

clip_image004

The only drawback I can see in this workaround is that if the transformation logic needs to be used for another dataset, then you have no choice by repeating it.

Longer version of the solution

The following is the longer version of the solution.

My Original Suggestion
  • Use a middle parameter to do the transformation, with nested IIF statement in both Available Values and Default Values.
  • Then pass the middle parameter to the cascading dataset; the middle parameter is passed directly to the dataset without the IIF statement.
  • The solution failed in SSRS 2008: the cascading dataset DataSet_Employees would never get refreshed even when the middle parameter is refreshed correctly when ever a new Department is selected by the user.
  • Reason for the failure: it might have to do with how the value for the middle parameter is set programmatically with the nested IIF statement, rather than by user selection.
  • Good news: this solution worked in SSRS 2012 in my test.

Here are two screen shots from our failed solution.

clip_image006

clip_image008

New Solution That Worked
  • Remove the middle parameter entirely.
  • Pass the parameter Departments to the cascading dataset, DataSet_Employees, instead of the middle parameter.
  • The Departments parameter needs to be modified to perform the transformation; use the same nested IIF statement we used in the middle parameter.
  • Voila. It works.
  • Reason for the success: by removing the middle parameter and coding the nested IIF in the parameter that is passed to the cascading dataset, Reporting Services made no mistake, but had to refresh the cascading dataset based on the user selection.

Here are a few screen shots from our successful solution.

clip_image010

The nested IIF statement is now coded in the parameter expression.

clip_image012

MDX # 40 – CROSS JOIN does not return Cartesian product of two hierarchies from the same dimension

April 15, 2014 2 comments

Correction

4/25/2014

Thanks to Chris Webb’s comment (see the comment section). I am making a correction on this blog.

The title of this blog contains a few important words, “from the same dimension”. You might have guessed the opposite side of it is hierarchies “from different dimensions”.

But my correction is not about this. In trying to explain why CROSS JOIN in MDX does not return Cartesian product of two hierarchies from the same dimension, I originally stated:

With the CROSSJOIN operation in MDX, we get only the combinations that actually have fact data. We now know that out of the 10 colors of Bib-Shorts only the Multi color Bib-Shorts has sales data (fact data).

Unlike the SQL CROSS JOIN, where we had to join one fact table and two dimension tables to find out which color of bib-shorts have sales, in MDX, the CROSSJOIN function automatically took into the consideration of the fact table. I have to say that the twists and turns are for the best. 

I highlighted “fact table” above because that is what made my statement incorrect in the context of two hierarchies from the same dimension. When we crossjoin two sets of members from hierarchies from the same dimension, the fact table(s) does not come into play, rather, it’s the validate combinations from the dimension table(s) that will determine the resulting tuples.  In this case, the Multi color is the only color for the Bib-Shorts. This can be verified by joining the two dimension tables, DimProduct and DimProductSubcategory. Here is the SQL query and the result.

Multi-Bib-shorts-correction

 

 

 

 

 

Here is the original post.

Cartesian product can be illustrated by a Cartesian square

Most SQL developers are familiar with the concept of CROSS JOIN and Cartesian product. Cartesian product is the result that is produced from a CROSS JOIN.

In this Wikipedia article, Cartesian product, a Cartesian product is illustrated by a Cartesian square as shown below.

Cartesian Square

 

 

 

 

 

 

 

In SQL Server, CROSS JOIN returns the Cartesian product of rows from tables in the join

Both of the following articles claimed that the SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

The above definition of the SQL CROSS JOIN can be illustrated with the following SQL script using the AdventureWorksDW database.

use AdventureWorksDW
go

with color as (
select      distinct Color
from  dbo.DimProduct
),

sub as (
select      distinct EnglishProductSubcategoryName
from  dbo.DimProductSubcategory
whereEnglishProductSubcategoryName = ‘Bib-Shorts’
)

select      *
from  sub CROSS JOIN color
order by
EnglishProductSubcategoryName, Color
–10

To simplify the result set, I am using only one product subcateogry ‘Bib-Shorts’. The above SQL script generates the results shown in the screenshot below. With 1 product category and 10 product colors, the result set is the every possible combination of these two lists, which is 10 (1 * 10) rows.

Multi-Bib-shorts

 

 

 

 

 

 

 

 

 

 

Keep in mind that the above 10 combinations of the colors and the subcategory do not necessarily have any sales. In order to find witch combinations have sales, we will need to do an INNER JOIN on these 3 tables:

dbo.FactResellerSales or dbo.FactInternetSales
dbo.DimProductSubcategory
dbo.DimProduct

The following 2 SQL queries can be used. It turned out that there is no internet sales for multi color bib-shorts, but there are reseller sales for them.

– There is no Internet Sales for Multi color Bib-Shorts
select      *
from  dbo.FactInternetSales fact join dbo.DimProduct prod
on          fact.ProductKey = prod.ProductKey
join  dbo.DimProductSubcategory sub
on          prod.ProductSubcategoryKey = sub.ProductSubcategoryKey
wheresub.EnglishProductSubcategoryName = ‘Bib-Shorts’
and         prod.Color = ‘Multi’
–0

– There is 756 Reseller Sales for Multi color Bib-Shorts
select      *
from  dbo.FactResellerSales fact join dbo.DimProduct prod
on          fact.ProductKey = prod.ProductKey
join  dbo.DimProductSubcategory sub
on          prod.ProductSubcategoryKey = sub.ProductSubcategoryKey
wheresub.EnglishProductSubcategoryName = ‘Bib-Shorts’
and         prod.Color = ‘Multi’
–756

In MDX, CROSS JOIN does not return the Cartesian product members from hierarchies

Enter the world of MDX. CROSS JOIN is an important operation in MDX, with some twists and turns.

The MDX query below is the equivalent of the SQL CROSS JOIN in the previous section. However, the result set contains only one row.

select
{ } on 0,
{ CROSSJOIN(
[Product].[Subcategory].[Subcategory].[Bib-Shorts],
[Product].[Color].[Color].members
)
} on 1
from
[Adventure Works]

Multi-Bib-shorts Crossjoin

 

 

 

 

 

With the CROSSJOIN operation in MDX, we get only the combinations that actually have fact data. We now know that out of the 10 colors of Bib-Shorts only the Multi color Bib-Shorts has sales data (fact data).

Unlike the SQL CROSS JOIN, where we had to join one fact table and two dimension tables to find out which color of bib-shorts have sales, in MDX, the CROSSJOIN function automatically took into the consideration of the fact table. I have to say that the twists and turns are for the best.

Please refer to the section ‘Finding related members in the same dimension“ in Chapter 5 of our book, MDX with SSAS 2012, to gain more knowledge on this topic and how it is related to the concept of auto-exists and the EXISTS and EXISTING function.

Three different ways to do crossjoining

In MDX, you can cross join different hierarchies using three different ways. Chris Webb has a blog here, The rather-too-many ways to crossjoin in MDX, which is better than anything I can write about CROSS JOIN.

 

MDX # 39 – Find Default Measure using MDX Query

April 15, 2014 Leave a comment

In Chapter 1 of our book, MDX with SSAS 2012, we’ve devoted a section “Setting a default member of a hierarchy in the MDX script” to show three places where you can set up Default members for hierarchies.

For more information about how to define a default member for hierarchies and whether  you should have a default member or not for hierarchies, please also refer to the following Microsoft Developer Network article:

Define a Default Member

MDX script is an easy place to maintain the default member definition

As the MDX book pointed out that the MDX script is an easy place to maintain the default member definition.

Below is an example of how to define a default member for the Destination Currency hierarchy by issuing an UPDATE DIMENSION command.

/*– Set default member for the Destination Currency cube dimension —-*/

Alter Cube
CurrentCube
Update Dimension [Destination Currency].[Destination Currency],
Default_Member = [Destination Currency].[Destination Currency].[US Dollar];

MDX query is also an easy place to find what the default members are for each hierarchy

As it turned out that MDX query is also an easy place to find what the default members are for each hierarchy. For every report developer, knowing how to find the default measure is also crucial. You can obviously check the dimension and measure group structures in SQL Server Data Tools, or the cube’s MDX script. But the easiest way to find default members for hierarchies and the cube’s default measure is to write your own MDX query using the DefaultMember function. Please refer to the following MSDN article:

DefaultMember (MDX)

The general syntax is:

Hierarchy_Expression.DefaultMember

Three Sample MDX queries to find default members for hierarchies and cube default measure

The following are three examples to find default members for hierarchies and the cube’s default measure.

  •  Find Default Member of Destination Currency hierarchy

WITH MEMBER Measures.[Default Member] AS
[Destination Currency].[Destination Currency].DefaultMember.Name

SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
— Default member for Destination Currency hierarchy: US Dollar

  • Find Default Member of Geography hierarchy

WITH MEMBER Measures.[Default Member] AS
[Geography].[Geography].DefaultMember.Name

SELECT
Measures.[Default Member] ON 0
FROM
[Adventure Works]
— Default member for Geography hierarchy: All Geographies

  • Find Default Measure of the Adventure Works cube

WITH MEMBER Measures.[Default Measure] AS
[Measures].DefaultMember.Name

SELECT
Measures.[Default Measure] ON 0
FROM
[Adventure Works]
— Default cube measure: Reseller Sales Amount

Showing default measure and the value in one query

By placing the default measure directly on the X axis, you can get the default measure name and the value in one query.

SELECT
–Measures.[Default Measure] ON 0
[Measures].DefaultMember on 0
FROM
[Adventure Works]

Reseller Sales Amount
$80,450,596.98

 

 

MDX+SSAS #37–Ignore unrelated dimension or not

February 7, 2014 1 comment

In Adventure Works cube 2008 or 2012, in the Dimension Usage tab, you will see that many intercepts of measure groups and dimensions are blank. To a specific measure group, the dimensions that have blank intercepts are un-related to the measure group. As a matter of fact you will see blank intercepts more often than not.

As the term un-related dimension suggests, these measure groups have no relationship to these dimensions. SSAS, however, gives us a measure group property IgnoreUnrelatedDimensions (true or false) to control how we want these measures to show for those un-related dimension members.

IgnoreUnrelatedDimensions is a boolean property, so how hard it can be. It turned out that this property is not as straightforward as it seems, and it might not be as effective as you would think either.

When IgnoreUnrelatedDimensions = True, the measure repeats for all members and all levels on the un-related dimensions

IgnoreUnrelatedDimensions = True is a default setting for measure groups. The measure behavior is not the best (actually very confusing to users), but the behavior is consistent and easy to remember.

When IgnoreUnrelatedDimensions = True,

  • the measure will repeat with the same value for all member for all level for any unrelated dimension,
  • regardless if the dimension has default member or not, root [All] member or not
  • this behavior is the same as VALIDMEASURE() function which also repeats the measure with the same value for all member for all level for any unrelated dimension
  • Example 1:

  • Exchange Rates measure group is not related to the Promotion dimension
  • Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
  • Promotion has no default member

image

    Example 2:
  • Exchange Rates measure group is not related to the Scenario dimension
  • Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
  • Scenario has a default member of Actual

image

    Example 3:
  • Exchange Rates measure group is not related to the Sales Channel dimension
  • Exchange Rates measure group’s IgnoreUnrelatedDimensions property is set as default True
  • Sales Channel IsAggregatable is set to False; no root [All] member

image

When IgnoreUnrelatedDimensions = False, the measures behave differently depending on if  the un-related dimension ….

…has default member or not, has root level [All] member or not.

Example 4:

  • Sales Target measure group is related to the Date dimension but only at Calendar Quarter level
  • (I’ve disable the calculation in the MDX Script "Sales Quota Allocation" which allocates values down to the Month level)
  • The measure shows at the Quarter level and rolls up to all the ancestor levels.
  • VALIDMEASURE() function overwrites the above behavior and force it to repeat the the same value for all months and days 
  • image

Example 5:

  • Sales Target measure group is not related to the Promotion dimension
  • Promotion dimension has a [All Promotions] root level member
  • The measure shows for the [All Promotions] member only.
  • VALIDMEASURE() function overwrites the above behavior and force it to repeat the the same value for all members

image

Both Example 4 and 5 produce an easy to remember result, that is, the setting forces the measure to the top root member and the member that the measure is linked to and al the ancestor members from that point on.

However, Example 6 and 7 is a bit confusing, because the setting of IgnoreUnrelatedDimensions = False is totally ignored, just because the un-related dimension

  • has a default member, or
  • has no root [All] member

Example 6:

  • Sales Target measure group is not related to the Scenario dimension
  • Scenario dimension has a default member Actual
  • The setting IgnoreUnrelatedDimensions = False is totally ignored as if IgnoreUnrelatedDimensions = True.
  • VALIDMEASURE() function behaviors the same as always and repeats the value for all members.

image

Example 7:

  • Sales Target measure group is not related to the Sales Channel dimension
  • Sales Channel IsAggregatable is set to False; no root [All] member
  • The setting IgnoreUnrelatedDimensions = False is totally ignored as if IgnoreUnrelatedDimensions = True.
  • VALIDMEASURE() function behaviors the same as always and repeats the value for all members.

image

Reporting Driven Decision – relate or un-relate

I recently worked on a request to pull some measures on a dimension that is unrelated to the measure group.

Although the original cube design didn’t link the measure to the dimension, I can see that the design might have missed the reporting requirement. I had a few choices to meet the new reporting requirement:

  1. IgnoreUnrelatedDimensions = False
  2. Creating a MDX calculation to show the measure for the appropriate members on the un-related dimension.
  3. Linking the measure to the dimension in the relational data warehouse, not to the leaf level members but to a middle level, similar to how the example 4 where the Sales Target measure group is related to the Date dimension but only at Calendar Quarter level.
    After some experiment, I’ve abandoned both option 1 and 2, opted for option 3.
    This blog explains why I didn’t choose option 1. The IgnoreUnrelatedDimensions setting for measure groups is not a very reliable way to meet my reporting requirement with so many moving parts.

Option 2 seemed very attractive to me at first. The reason that I didn’t go for it will be topic for another blog.

When SSAS is not enough

After running the above queries with different IgnoreUnrelatedDimensions setting for different scenarios, I recalled a Chapter in our book MDX Cookbook “When MDX Is Not Enough”. Here I’d like to say that sometimes settings in SSAS can seem to provide simple solutions but are actually not enough.

MDX #36 – Many thanks to the reviewers

January 1, 2014 Leave a comment

In this blog, I’d like to give special thanks to the people below who have taken time to write reviews for my book MDX with SSAS 2012 Cookbook.

“It helped me a lot in my projects and helped me advanced my MDX skill in a very short time.”

hxy0135 NJ

“What I like about the cookbook style is the scenario and solution design. ”

Dan English

“The structure of the books is very similar and is again very easy to follow.”

Gerhard Brueckl

"This book is a must have. I have struggled trying to find a book with good illustrations and easy to follow samples UNTIL this book!"

Desert Fun

“I recommend this book to anyone regardless of MDX exposure, really great to have a reference like this book when you are trying to work through tough requirements."

richard mintz

"MDX with SSAS 2012 Cookbook" has a good chapter and topic organization which starts simply, but not too simply, and builds in complexity. "Elementary MDX" sets the stage for the expected level of knowledge with the other chapters building from that knowledge base. I particularly liked the chapters on "Working with Time", and "Business Analytics" because the regression and non-allocated expenses are tough problems for MDX newbies to solve."

PaulG

In the blog below, I’ve given my thanks to Vincent Rainardi for his quick reviewing.

MDX #32 – Vincent Rainardi’s blog

Follow

Get every new post delivered to your Inbox.

Join 169 other followers

%d bloggers like this: