Archive

Archive for the ‘MDX Challenges’ Category

5th Blogging Year

December 31, 2014 Leave a comment

It’s hard to believe that it’s been 5 years since my first ever post, in which I simply spelled Business Intelligence. Thanks to everyone for being with me since then!

I blogged around my daily experience as a Data Warehouse and Business Intelligence developer. Most of my posts are about how to use the tools, i.e. the Microsoft SQL Server Business Intelligence stacks with more productivity and efficiency, of cause, also about the two major data query languages, SQL and MDX. I enjoy working as a DWH and BI developer for the creativity I found in all I do. I mostly praise the tools and the query languages we use, but occasionally my posts complain about the the inefficiency and even bugs in the tools.

In 2013, I co-authored the book MDX with SSAS 2012 Cookbook. As much as I’d love to write again and blog more, in 2014 I felt that I I needed more time for myself to relax and recharge, and to spend more time with my family.

Looking forward to next year, I would like to think that being a DWH and BI developer will continue to give me professional satisfactions; I do not need to find a new career. I also hope that 2015 is a year that will give me inspiration for a new book for DWH and BI developers.

My posts cover a lot of grounds in SSIS, SSRS, SSAS, SQL Jobs, SSMS, Visual Studio’s SSDT, and SQL and MDX. But it’s interesting that the top 5 posts that received the most views are all in SSIS, according to the WordPress’s annual report. More interestingly, these top SSIS posts are all about how to use variables in SSIS. I will consider writing about those topics again.

Once again, thanks for following and commenting on my posts in 2014. I look forward to seeing you all online again in 2015! Happy New Year!

 

Sherry Li

MDX #45–Find all the Calculated Sets and Their Calculation Scripts

November 14, 2014 Leave a comment

Now we know how to find all the calculation scripts in a cube by querying the DMV $SYSTEM.MDSCHEMA_MEASURES.

MDX #43–Find a MDX Calculation Script

What about all those calculated sets in the cube?

In the Adventure Works cube, there are quite many calculated sets scattered around in the Sets folder in some dimensions, as shown in the following screenshot.

pic1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

How do we find out all of them and their calculation scripts using the DMVs?

Now this time we can use this DMV $SYSTEM.MDSCHEMA_SETS.

SELECT * FROM    $SYSTEM.MDSCHEMA_SETS

Here is the result. The EXPRESSION field shows the calculation scripts.

pic2

 

 

 

 

 

Categories: MDX Challenges Tags: ,

MDX #44–How to find all the calculated measures

November 13, 2014 Leave a comment

Calculated measures are visually identifiable

If you have access to a cube, it’s quite easy to find all the calculated measures.

The following is a screenshot from SSMS MDX query editor. The icon next to each measure can visually tell you if it’s a regular measure or a calculated measure. The icon with three uneven bars indicates a regular measure, and the one with a calculator and an orange bar (not sure what it represents) on top indicates a calculated measure.

pic1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

AllMembers function returns both the regular and calculated measures

I am always interested in knowing how to also accomplish things with MDX queries.

It turned out it’s pretty simple. The key is the AllMembers function.

By default, MDX excludes calculated members when it resolves set functions. In this blog, I am only examining the special dimension, Measures, which has only one single visible hierarchy; as a result, the hierarchy can be either referred to by the dimension name or by the hierarchy name. So both of the following are valid expressions, and both will return regular measures only.

[Measures].[Measures].Members
[Measures].Members

The AllMembers function is very straightforward. It will return what it is meant to do, both the regular measures and the calculated measures.

Here are 4 simple statements regarding how to use AllMembers function to get calculated measures.

  1. Members function only returns regular members, or regular measures on the Measures hierarchy.
  2. AllMembers function returns regular members AND calculated members, or calculated measures on the Measures hierarchy.
  3. (AllMembers – Members) gives us calculated measures only.
  4. AddCalculatedMembers() function is semantically similar to the AllMembers function.

The following are 4 MDX queries to demonstrate the 4 statements above.

pic2

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The following screenshot shows the results.

pic3

 

 

 

 

 

 

 

Here are the queries in text.

–1. Members function only returns regular members, or regular measures on the Measures hierarchy
SELECT
{[Measures].Members} ON COLUMNS
FROM
[Adventure Works]
go

–2. AllMembers function returns regular members AND calculated members, or calculated measures on the Measures hierarchy
SELECT
{[Measures].AllMembers} ON COLUMNS
FROM
[Adventure Works]
go

–3. AllMembers – Members gives us calculated measures only
SELECT
{ [Measures].AllMembers –
[Measures].Members
} ON COLUMNS
FROM
[Adventure Works]
go

–4. AddCalculatedMembers() function is the same as the AllMembers function
SELECT
AddCalculatedMembers([Measures].Members) –
[Measures].Members
ON COLUMNS
FROM [Adventure Works]

The AllMembers function and the AddCalculatedMembers() function can be also applied to other regular dimensions to get the calculated members.

MDX #43–Find a MDX Calculation Script

November 12, 2014 Leave a comment

Finding out MDX calculation scripts is a common task

A co-worker recently asked me what the calculation for a calculated measure is in our reporting cube.

If you have the Analysis Services project in Visual Studio locally, it is easy to find what the calculation script is from the Calculations tab in the cube designer.

But what if you don’t have the VS project handy, and you have access to the cube from SQL Server Management Studio?

EXPRESSION field in $SYSTEM.MDSCHEMA_MEASURES

Here is a simple script you can run to quickly get the calculation script.

This script queries the SSAS Dynamic Management View $SYSTEM.MDSCHEMA_MEASURES. The EXPRESSION field will return the actual calculation script. You will need to run the DMV queries in the MDX query editor, not the SQL query editor.

pic1

 

 

 

 

 

Here is the result.

pic2

 

 

 

Here is the query in text.

SELECT    CUBE_NAME
,        MEASURE_UNIQUE_NAME
,        EXPRESSION
,        MEASUREGROUP_NAME
from    $SYSTEM.MDSCHEMA_MEASURES
where    MEASURE_UNIQUE_NAME = ‘[Measures].[Ratio to Parent Product]’

Use $SYSTEM.DBSCHEMA_COLUMNS to find all the columns in a DMV

You might ask how do I know what are all the columns in this view. Here is a DMV view, $SYSTEM.DBSCHEMA_COLUMNS, you can query to find out all the columns in a DMV view.

pic3

 

 

 

Here is the result.

 

pic4

 

 

 

 

 

 

 

 

 

 

 

 

Here is the query in text.

SELECT *
FROM    $SYSTEM.DBSCHEMA_COLUMNS
WHERE    TABLE_SCHEMA = ‘$SYSTEM’
AND        TABLE_NAME = ‘MDSCHEMA_MEASURES’

$SYSTEM.DISCOVER_SCHEMA_ROWSETS is the only DMV name you need to remember

You might also ask how would I know to use the view $SYSTEM.MDSCHEMA_MEASURES to find out the calculation script.

The answer is to just remember one view, $SYSTEM.DISCOVER_SCHEMA_ROWSETS.

SELECT * FROM $SYSTEM.DISCOVER_SCHEMA_ROWSETS

pic5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The $SYSTEM.DISCOVER_SCHEMA_ROWSETS view will show you all the DMV views that you can use to get the metadata about your dimensions and cubes.

 

DMVs can be very useful for documenting SSAS databases, monitoring usage and activity. To know more about how to use these DMVs check out our book “MDX with SSAS 2012 Cookbook”.

Categories: MDX Challenges, SSAS Tags: , ,

MDX #42–IsEmpty? or = 0 ? or IS NULL?

November 12, 2014 2 comments

In SQL, NULL means undefined value

Any SQL programmer can tell you that zero is not NULL, NULL is not zero, and NULL is not even equal to NULL either, because NULL means undefined. One undefined value cannot be equal to another undefined value.

In MDX, NULL means an empty cell

What about in MDX, how do we detect zero and NULL? What does even NULL mean? Since we are leaving the one dimensional SQL world, and are stepping into the multi-dimensional cube, NULL has a slightly different meaning. To me, it really means an empty cell. When the Internet Sales is showing as (null) for 11/11/2014, it means that the cell of {[Internet Sales], [11/11/2014]} is an empty cell. If you query the fact table, SELECT Internet Sales FROM factSales WHERE Date = 11/11/2014, it should return no rows back.

What if the Internet Sales is showing as 0 (zero) for 11/11/2014? The cell is no longer empty. the fact table should have one row WHERE Date = 11/11/2014, only that the Internet Sales is zero.

Detecting zero and NULL in MDX

How do we detect when Internet Sales is zero or when the cell is totally empty in MDX?

I wrote the following demo MDX code to see the various ways and the results.

I first created two values, a zero value and a NULL value.

pic1

 

 

 

Then I created three new values to detect the zero value, using three different ways

  • 0 = 0?: is 0 = 0?. It should be true.
  • 0 Is NULL?: It should be false.
  • 0 IsEmpty?: It should be false.

 

pic2

 

 

 

 

 

 

 

 

 

I also created three new values to detect the NULL value, using three different ways

  • NULL = 0?: a big question mark here.
  • NULL Is NULL?: It should be false. Remember NULL is not equal to NULL in SQL.
  • NULL IsEmpty?: It should be true. Remember NULL means empty cell in MDX.

pic3

 

 

 

 

 

 

 

 

 

Then I wrote the following MDX query.

pic4

 

 

 

 

 

 

 

Here is the result.

pic5

 

 

Zero is zero, AND NULL is also zero

Do the four results in red surprise you?

  • Zero is zero, AND NULL is also zero.
  • Zero is certainly not empty.
  • NULL IS empty.

It did surprise me. I don’t have much explanation why MDX thinks NULL is zero. At this point, I am just taking in this fact.

Here is the code in text.

with
member [measures].[A Zero Value] as 0
member [measures].[A NULL Value] as null

member [measures].[0 = 0?] as
iif ( [measures].[A Zero Value] = 0,
“true”,
“false”
)
member [measures].[0 Is NULL?] as
iif ( [measures].[A Zero Value] is null,
“true”,
“false”
)
member [measures].[0 IsEmpty?] as
iif ( IsEmpty([Measures].[A Zero Value]) = True,
“true”,
“false”
)
member [measures].[NULL = 0?] as
iif ( [measures].[A NULL Value] = 0,
“true”,
“false”
)
member [measures].[NULL Is NULL?] as
iif ( [measures].[A NULL Value] is null,
“true”,
“false”
)
member [measures].[NULL IsEmpty?] as
iif ( IsEmpty([Measures].[A NULL Value]) = True,
“true”,
“false”
)

select { [Measures].[A Zero Value],
[measures].[0 = 0?],
[measures].[0 Is NULL?],
[measures].[0 IsEmpty?],
[Measures].[A NULL Value],
[measures].[NULL = 0?],
[measures].[NULL Is NULL?],
[measures].[NULL IsEmpty?]
} on 0
from    [RADM_REPORTING]

Empty cells, natural or user-defined can be a powerful way to optimize our MDX queries

Then you might think that since MDX thinks NULL is zero, then why many people set certain side of a MDX calculation to be NULL. Why cannot we all use zero, not mess with the NULL? Well, empty cells, natural or user-defined can be a powerful way to optimize our MDX queries, with the help of either the NONEMPTY() function or the Non EMPTY keyword that works only axes. Check out our book MDX with SSAS 2012 Cookbook on this topic.

Categories: MDX Challenges Tags: ,

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

Follow

Get every new post delivered to your Inbox.

Join 185 other followers

%d bloggers like this: