Archive

Posts Tagged ‘MDX’

MDX – Putting Visual Totals on top or at the bottom

March 31, 2016 Leave a comment

Dynamically totaling child members

Putting the dynamic totals on top or at the bottom can be a trivial or a big decision. Very often, visual tools reply on MDX queries to get the aggregation correct in the MDX query so that the need to aggregate in the visual tool is eliminated.

Suppose you need to display the sales for just Accessories and Clothing, and on the top you just want to show the aggregated sales value for Accessories and Clothing, as shown in the following example.

Internet Sales Amount
Total – All Products $1,040,532.57
Accessories $700,759.96
Clothing $339,772.61

VisualTotals() function can be used to get visual totals

VisualTotals() function is a good choice to display the aggregated values for the result set (“visual” here implies that the aggregation will be only based on the visually seen results, not based on the entire cube space). The VisualTotals () function will allow you to show the totals on top or at the bottom, depending on where you put the All member.

This query using the VisualTotals () function will display the visual total on top. This is because the All Product member was the first in the named set.

WITH SET [Visual Total] AS
VisualTotals(
{ [Product].[Category].[All Products],
[Product].[Category].[Accessories],
[Product].[Category].[Clothing] }
,
'Total - *'
)

SELECT
{ [Measures].[Internet Sales Amount] }  ON COLUMNS,
[Visual Total]  ON ROWS
FROM
[MDXBook]

Let’s put the All member as the last member in the named set as the following:

WITH SET [Visual Total] AS
VisualTotals(
{ [Product].[Category].[Accessories],
[Product].[Category].[Clothing],
[Product].[Category].[All Products] }
,
'Total - *'
)

Now the visual total is shown at the bottom.

Internet Sales Amount
Accessories $700,759.96
Clothing $339,772.61
All Products $1,040,532.57

Now the visual total is shown at the bottom.

As a matter of fact, you can display the totals anywhere. Try putting the All member in the middle, you will get the totals in the middle of the result set.

WITH SET [Visual Total] AS
VisualTotals(
{ [Product].[Category].[Clothing],
[Product].[Category].[All Products],
[Product].[Category].[Accessories] }
,
'Total - *'
)

Hierarchize doesn’t seem to be necessary if there is only one level

I’ve seen MDX queries that used the Hierarchize() function for the only purpose to put the visual totals at the bottom, using the POST options.

Hierarchize (
[Visual Total],
POST
) ON ROWS

When using the POST option, the Hierarchize function sorts the members in a level using a post-natural order. In other words, child members precede their parents. Therefore the dynamic totals are at the bottom. If there is only one level in the result set, I don’t see the need to use the Hierarchize function since we can position the dynamic totals at the bottom by simply rearranging the set.

Check out this MSDN page to see the description of the VisualTotals() function:

VisualTotals (MDX)

 

When a calculated measure is added NON EMPTY stopped working

March 24, 2016 Leave a comment

We use NON EMPTY key word on the rows axis to remove rows with NULL values. It works only when all the measures are NULL. If any one of the measures has value, NON EMPTY key word will not be able to remove rows.

NON EMPTY on rows axis worked as expected

A co-worker recently worked on getting 30+ measures from multiple measure groups out from our production cube. The NON EMPTY key word on the rows axis worked as expected.

SELECT
{ [Measures].[measure 1],
....
{[Measures].[measure 30] } ON COLUMNS,
NON EMPTY
[Employee].[Employee Name].[Employee Name].MEMBERS
ON ROWS
FROM
[REPORTING_CUBE]
WHERE
( [Date].[Date View].[Month].&[201601])

The month on the WHERE clause is hardcoded in this blog, but this query is supposed to be executed in an OPENQUERTY with a dynamic month value. In order to attach a period key value to the month, my co-worker decided to also get the month period key in the same query. (Let’s put aside why the OPENQUERTY is used and whether the month period key is needed or not and let’s focus on the NON EMPTY.)

NON EMPTY stopped working when a calculated Period Key is added

In order to get the hidden month period key, he used the WITH MEMBER to create a query-scope measure:

WITH MEMBER [Measures].[Period Key] AS LEFT([Date].[Date View].CurrentMember.Member_Key, 6)

This [Measures].[Period Key] is then added to the columns axis.

SELECT
{ [Measures].[Period Key],
[Measures].[measure 1],
....
{[Measures].[measure 30] } ON COLUMNS,

The query at the point returns rows that more than doubled what it returned before with many of them showing all measures NULL, except the Period Key.

The NON EMPTY is obviously not working at this point. When you think about it, this actually makes sense. In the context of the query, the Period Key has value and should be the same for all the employees whether those measures are NULL or not. This explains why NON EMPTY cannot remove the rows even when all the measures are NULL, since [Measures].[Period Key] has value.

Below is a made-up example. It shows that we get rows back even when all measures are NULL.

 

Period Key Measure 1 Measure 2 Measure 3
Austin, D 201601 (null) (null) (null)
Avalos, K 201601 (null) (null) (null)
Baker, B 201601 (null) 1,158.78 (null)
Brooks, W 201601 85.55% 291.38 1
Brown, L 201601 (null) (null) (null)
Cruz, P 201601 (null) (null) (null)
Cruz, S 201601 (null) (null) (null)

NONEMPTY() function is a lot more flexible

The NONEMPTY() function is a lot more flexible and should work in this case. But the usual form of it, where one measure is given as the second parameter, would not work in this case.

NONEMPTY(
[Employee].[Employee Name].[Employee Name].MEMBERS,
[Measures].[measure 1]
)

Use a named set as the second parameter for the NONEMPTY() function

We need to check for all the 30+ measures. The solution here is to use a named set as the second parameter.

NONEMPTY(
[Employee].[Employee Name].[Employee Name].MEMBERS,
[The Set]
)

We will define the named set as the following to combine all 30+ measure together into a set:

WITH
SET [The Set] AS
{ [Measures].[measure 1],
...
[Measures].[measure 30] }

Putting all together, here is the final query that will only return the employees who have values in those 30+ measures.

WITH
SET [The Set] AS
{ [Measures].[measure 1],
...
[Measures].[measure 30] }
SELECT
{ [Measures].[Period Key],
[The Set] } ON COLUMNS,
NONEMPTY(
[Employee].[Employee Name].[Employee Name].MEMBERS,
[The Set]
) ON ROWS
FROM
[REPORTING_CUBE]
WHERE
( [Date].[Date View].[Month].&[201601])

In my made-up example, only the two rows that are in red and bold will be returned, Baker, B and Brooks, W.

Always provide set_expression2 in NonEmpty() function

On MSDN the NonEmpty() Function page shows the syntax as this:

NONEMPTY(set_expression1 [,set_expression2])

It implies that the second parameter is optional. Check out my old blog to understand why we should always provide set_expression2 in the NonEmpty() function.

https://bisherryli.com/2012/01/20/mdx-1-why-should-we-always-provide-set_expression2-in-nonempty-function/

Categories: MDX Challenges Tags: , ,

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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: ,
%d bloggers like this: