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 1 comment

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: ,

SSRS #75 – Use RunningValue() Function and VB Script to capture discrete dates

November 11, 2014 Leave a comment

Drill-down report is a common design

In SSRS, it’s a common design to allow users to drill down to more granular data in a same report.

In the following snapshot, users will see a summary line that shows the Call Handled 130. The two days, 11/4 and 11/5 will not be immediately visible. Only when the user clicks the + sign in the All Dates field, the two detail lines will become visible. All this is happening in the same report.

pic1

 

 

 

 

 

 

 

Result set can contain only discrete dates

In this particular report, I had two date filters, as shown below.

pic2

 

The date filters ask for data in a continuous date range, starting from 11/1 to 11/10. However, the report is only showing some discrete dates per employee. This is because other filters suppress certain dates.

The point here is that although the date filters ask for data in a continuous date range, the result set contains only discrete dates within the date range.

Now the users ask you to create a hyperlink to go to a separate report, to show data for that employee, and for those discrete dates only.

The challenge becomes how do we capture those discrete dates within a group (The report is grouped by employee).

Capture discrete dates in SSRS

You might attempt to create a stored procedure to run the same query for the current report again so you can capture those discrete dates in your code, and then pass them to your second query to get the new report.

This will not be my first choice though, because it requires a lot of coding, not mention that the query performance might suffer because I will need to jam too much and redundant logics into one query.

Instead, I want to accomplish it entirely in the SSRS report.

Before I create the hyperlink on the summary line, I created a new column “Running Values” to just test my idea of capturing those discrete dates per employee.

pic3

 

 

 

 

 

 

 

 

I named this column “Running Values”, because I know that the RunningValue() VB function is what I need to use. A while ago I blogged about this wonderful aggregate function.

SSRS #72 – Use RunningValue() Function to Concatenate Row Values

As a matter of fact, all the aggregate functions in the following screenshot allow you to do aggregation within a group in SSRS.

pic4

 

 

 

 

 

 

 

RunningValue() function needs an expression, an aggregate function and a group name

To use the RunningValue() function, I’ll need three parameters.

  • An expression, which normally is a field name, such as Yearly Income.
  • An aggregate function, such as MAX, SUM.
  • An group name, which is called GROUP_EMP in my report.

It is quite obvious that using the RunningValue() function out-of-box will not work for me, because I am not doing any direct aggregation on dates per employee.

It’s time to go back to my old blog, SSRS #72 – Use RunningValue() Function to Concatenate Row Values.

A VB script that simply concatenate the dates per GROUP_EMP

What I need is a VB script that allows me to simply concatenate the dates per GROUP_EMP. Every time the script is called, it keeps the prior dates, and adds the new date to the concatenated string.

This function will do exactly that. In Report –> Report Properties window, write the following code, making sure that the two strings aStr and tempStr are defined outside the Public Function/End Function block. This will make sure that both aStr and tempStr are saved per GROUP_EMP.

 

pic5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Here is the code in text.

Dim aStr as String = “”
Dim tempStr as String = “”

Public Function ConcatenateString_Dates (ByVal inEmployee_ID As String, ByVal inDays As String) As String

If inEmployee_ID = aStr Then
tempStr = tempStr + “,” + inDays
Else
aStr = inEmployee_ID
tempStr = inDays
End If

Return tempStr

End Function

 

Calling the RunningValue() function from the test column

Now it’s time to use the RunningValue() function.

In this test column RunningValues, I need to call the RunningValue() function.

 

pic6

 

 

 

 

 

 

 

 

 

Here is the actual code I used.

=RunningValue(Code.ConcatenateString_Dates(Fields!EMPLOYEE_ID.Value, Fields!CAL_DAY_LOCAL.Value), Max, “Group_Banker”)

The trick is in the first parameter, the expression.

Code.ConcatenateString_Dates(Fields!EMPLOYEE_ID.Value, Fields!CAL_DAY_LOCAL.Value)

This expression is not a simple field, it is the VB script I just created to concatenate the dates per employee.

The aggregation function I used is MAX. This allows me to get the last concatenated string, which is all the dates within one GROUP_EMP.

URL encoding allows more robust drill-through design

Now I am ready to just pass the following as a dates parameter to my next report.

RunningValue(Code.ConcatenateString_Dates(Fields!EMPLOYEE_ID.Value, Fields!CAL_DAY_LOCAL.Value), Max, “Group_Banker”)

Calling another SSRS report, or creating a drill-through report is normally pretty straightforward. SSRS has an out-of-box feature called Action for textboxes that can do that perfectly. But to make your reports more robust, and lasting long after the developer has moved on, there is something called URL encoding that VB scripts are good at. That can be another blog topic.

Book Review: Why You Should Read Expert Cube Development With SSAS 2012 Cover to Cover

June 15, 2014 Leave a comment

Expert Cube Development with SSAS 2012 Multidimensional Models was published earlier this year by Packt Publishing. It’s the second edition of the very successful book on SSAS cube development by three well-known industry leaders, Chris Webb, Alberto Ferrari and Marco Russo.

This book is not a tutorial book on using SSAS as a tool. It is more of a guided tour through the lifecycle of building an Analysis Services solution with an informed commentary telling you what to do, what not to do, and what to watch out for.

Reading this book cover to cover

If you are a SSAS cube developer, you would want to read this book cover to cover, no matter what level you are, with the exception of absolute beginners who do not understand basic Analysis Services concepts yet, such as what a cube and a dimension is.

I bought the first edition a few years ago, but didn’t read it cover to cover because at the time I didn’t find some of the topics relevant to my work. Earlier this year I bought the second edition and I found myself unable to put the book down. By the time I knew it, I had already read it cover to cover once, with pages of notes in Microsoft OneNote. Knowing that my cube development skills could have progressed much faster, I wish I had read the book a few years ago cover to cover.

So don’t repeat the same mistake I made. Whether you already have the first edition or just bought the new 2012 edition, go ahead and start reading it now.

What I enjoyed about the book

I don’t wish to spoil your fun with the book, so I’ll just gloss over a few key points about the book.

  • Beginner developers might think that cube development is all about how to use SSAS as yet another tool. This book will change your mind. The big chunk of Chapter 1 focused on the data modeling for Analysis Services. Then the book moved on to Chapter 2 to show you how to build basic dimensions and cubes. More complex dimension modeling is covered in Chapter 3. Data modeling for measures and measure groups is covered in Chapter 4. What I enjoyed the most is how the book presented the challenges we all encountered in our day-to-day work and provided the best practices in terms of data modeling in Analysis Services multidimensional model.
  • Microsoft Analysis Services is not a standalone technology, it’s part of a family of technologies and disciplines that all work together to make it possible for end-users to do interactive data analysis, reporting, and visualization. From a developer’s point of view, these technologies include the SQL Server engine, the Reporting Services, the Analysis Services, with the Integration Services in the middle as the glue. The disciplines include, but are not limited to, data warehouse data modeling, multidimensional modeling, and designing and implementation for performance and good user experience. I personally find that being able to fit all these techniques and disciplines together in the lifecycle of building an Analysis Services solution is not an easy task. Throughout the book the authors did a fantastic job of showing how each technique and discipline can fit seamlessly to build high performance cubes.
  • As a tool, Analysis Services is very easy to use; some might say too easy. Dimensions and cubes are built with various wizards with properties already being filled with default values. You can have a cube up and running in a matter of minutes. Some properties are for cube’s client tools to consume, but many of the properties are cube’s metadata and will end up having some impact on the cube processing performance, query performance, and/or storage engine performance. Assuming that your cube has started its life with a good design, then a good portion of a cube developer’s job is to understand what those impacts are and to make informed trade-off decisions. This book is a life-saving book that tells you what those properties mean, what to do with them, what not to do, and what to watch out for.
  • Bad cube query performance can be detrimental for your Analysis Services projects. The book has devoted an entire Chapter 8 to query performance tuning. The concept of query performance tuning is very familiar to SQL Server developers, but cube query performance tuning methodology has its own twist and turns, such as the Formula Engine vs. the Storage Engine, the partitions and aggregations, and tuning an algorithm in MDX. The book explains in detail what to do with each methodology and even the right tools and scripts to use to get the job done correctly.
  • I also like the many links in the book to other very detailed white papers, such as “The Analysis Services 2008 R2 Performance Guide”, and “The Many-to-Many Revolution”. Many blog posts are also included in the book, such as the blog posts from Mosha Pasumansky who was considered the most influential person in MDX.

No covering of SSAS Tabular models

As you may know, as of SQL Server 2012, there are two versions of Analysis Services: Multidimensional and Tabular. Although both of them are called Analysis Services and can be used for much the same purposes, the development experience for the two is completely different.

I have bought the first edition a few years ago. Although this is basically the same book as the first edition, I still went ahead and bought it because the 2012 edition has a new section that talks about the DAX query support in SSAS 2012 multidimensional model. Don’t get me wrong, this book only covers SSAS Multidimensional models. But it’s nice to have a new section on how SSAS 2012 multidimensional model supports not only MDX queries, but also DAX queries.

No substantial changes in this second edition

Since there are no substantial changes in this second edition, it’s probably not worth buying a copy of the second edition if you already have a copy of the first edition. What is covered in the first edition should work perfectly fine in SSAS 2008 and 2012, and even in 2014. This is because Microsoft has not added anything that is substantially new to SSAS Multidimensional models since the 2008 version. But if you don’t have the 2008 edition, I’d recommend you to buy this new 2012 edition, even if you are still working on cubes in SSAS 2008.

Not a book for absolute beginners

If you still need to understand basic Analysis Services concepts, such as what a cube and a dimension is, then this book is not book for you. This book does not take the form of a basic tutorial either.

Authors’ personal experience and thoughts are invaluable

Chris Webb, Alberto Ferrari, and Marco Russo are well-known in the SSAS and MDX community. This is an invaluable book because it contains their personal experience and thoughts. I myself visit Microsoft books online (BOL) very often. But if a book is solely derived from BOL then it is not too useful for me, as I can read it in the BOL myself. I am putting this review on my blog, and also planning to put it out on Amazon and Barnes and Nobel, hoping that all cube developers will read the book cove to cover.

Packt Publishing

Packt Publishing is one of my favorite tech book publishers. Their books focus on practicality, recognizing that readers are ultimately concerned with getting the job done. They also offer a subscription service, which I personally also use. Good job for putting out “Expert Cube Development with SSAS 2012″!

 

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: , ,
Follow

Get every new post delivered to your Inbox.

Join 173 other followers

%d bloggers like this: