Miky Schreiber's Blog - BI|MDX

Non Empty Behavior in Panorama

mikypuff — Wed, 03 Mar 2010 17:01:11 GMT

Non Empty Behavior is a very good optimization method you can use with your calculated measures. It allows you to define a list of measures that their emptiness defines the emptiness of your calculated measure. To understand this, look at the picture (taken from the MSDN):

This is the calculation expressions pane from the Calculations tab in the cube designer. From MSDN:

"The measures you specify in the Non-empty behavior list are used to resolve NON EMPTY queries in MDX. When you specify one or more measures in the Non-empty behavior list, Analysis Services treats the calculated member as empty if all the specified measures are empty. If the Non-empty behavior property is blank, Analysis Services must evaluate the calculated member itself to determine whether the member is empty."

This is not new. NEB is quite old feature in SSAS. The new thing (at least for me) is that NEB can also be used in Panorama formulas! For example, M1+M2 /*NONEMPTY_BEHAVIOR={M1,M2}*/ will apply M1 & M2 as NEB. You can see more Panorama optimization methods here.

History Browsing in OLAP and LastNonEmptyLeaf

mikypuff — Wed, 03 Mar 2010 12:34:48 GMT

One of the basic requirements of a modern BI system or Data Warehouse is to be non-volatile, meaning that the DWH will save the history. In this post I'll explain how to implement it in the DWH while assuming that the history can be changed and (the more interesting part) how to implement it in Analysis Services.

Before starting with history saving in the DWH, I need to say that this has nothing to do with Slowly Changing Dimensions. You can work with Type 1, 2 or 3. All you need to take care of is that the fact table will be consistent with the dimension tables. Assuming that the history can change (this is the scenario in my organization), we need to extract all the fact data every time. If you work with staging level/environment/layer you can use it to calculate which records has been changed or inserted and only upsert them to the DWH itself (it's more secure). In our case, we'll work without such staging level so we need to load all the fact data in every load to the DWH.

Loading the fact in the ETL is very simple - we'll save the datetime of the execution start, round it and put it in every row in the fact table. We can call this field CreateTime. You probably ask yourself why we need to round it. The answer is that we don't want the time dimension to have every possible value of datetime. It's huge and the users don't need that kind of accuracy. That's why I round it in my projects to half-hour, meaning that 14:27 becomes 14:00 and 14:50 becomes 14:30. Let's look at an example of such fact table:

*RowNum*	*Dimension1*	*Dimension2*	*Measure1* P>	*Measure2* P>	*CreateTime*
1	X	A	4	20	03/03/2010 15:00
2	X	B	56	19	03/03/2010 15:00
3	Y	B	2	3	03/03/2010 15:00
4	Y	B	1	10	03/03/2010 15:00
5	Y	C	7	5	03/03/2010 15:00
6	X	A	4	20	03/03/2010 15:30
7	X	B	56	40	03/03/2010 15:30
8	Y	B	2	3	03/03/2010 15:30
9	Y	B	10	10	03/03/2010 15:30
10	Y	C	7	5	03/03/2010 15:30

Here we can see two executions of the ETL: The first between 15:00 and 15:00, the second between 15:31 and 15:59. Note the differences in rows 2 vs. 7 and 4 vs. 9. This means that the source table(s) has changed between the executions.

In the DWH, we create foreign keys from Dimension1 and Dimension2 to their dimension tables. We'll also create FK between CreateTime and the time dimension. I won't get into the time dimension design here, you can read about it a past post. I assume that the time dimension has the hierarchy Year -> Month -> Day -> Hour -> Half-Hour. It seems that now we have a good fact table for use in Reporting Services (or any other reporting tool). Create a parameter called Extraction Time/ETL Time and assign it values from distinct on CreateTime. The user can pick a date and the report will filter on this date. Perfect.

The problematic part starts in Analysis Services. Only one extraction time can be the current member. There is no sense in summing or aggregating more than one extraction time. There are two ways to deal with it:

The wrong way - I'm writing this down so you'll learn from this design mistake. We created a calculated member which takes the last extraction time:

Create Member CurrentCube.[Extraction Time]. [Calendar].[All].[Last Data Time]
AS
Tail ([Extraction Time].[Calendar].[HALF HOUR].Members,1).Item(0),
Visible = 1;

In every Panorama view, we sliced the Extraction time on the [Last Data Time] member, which will show the user the data of the last ETL run. Our basic assumption was that the user wants to see the current data and when he'll want to see the historical data he will pick another member from the Extraction time dimension. This is the point we missed and it soon enough bumped into our face.

We forgot that every system, even BI system which is built for analysts should be intuitive and idiot-proof. The users picked members from the day level in the Extraction time dimension. When we asked them why they did it they said that "we wanted to see the data from 03/03/2010 and suddenly the numbers got bigger!". That happened, of course, because the member 03/03/2010 is the sum of 03/03/2010 15:00 and 03/03/2010 15:30. The solution is that no matter which member the user picks in the Extraction time dimension, he should see only one leaf (half-hour level) member at a time. This takes us to

The right way - We set the [Last Data Time] member to be visible = 0 (I kept it for debug purposes) and I written MDX script which I call NonLastEmptyLeaf. This script makes sure that no matter which member in the Extraction time hierarchy will be picked, the user will be sliced only on the last non empty leaf. For example, if the user picks 03/03/2010 he will see the data of 03/03/2010 15:30. This is what will happen also when he'll slice on 03/2010, 2010 and even the [All] member. This is the MDX script:

Freeze([Extraction time].[Calendar].[All]);
Scope(
Descendants (
   [Extraction time].[Calendar]. [All],
   0,
   SELF_AND_AFTER
)
);
this = Tail (
   Filter(
    Descendants(
     [Extraction time]. [Calendar].CurrentMember,
     [Extraction time].[Calendar].[HALF HOUR],
     LEAVES
    ),
    [Measures].CurrentMember > 0
   )
).Item(0);
End Scope;

Now, there is no meaning of [Last Data Time] member because slicing on the [All] member will show the current data, meaning that no slicing at all will show the current data because [All] is the default member.

To conclude, history browsing can be dangerous if we let the users the option to slice on more than one data time. The open question that I have is how can I prevent the user from picking more than one member in this hierarchy in Panorama views? Should I even try to do that or should I count on his minimal common sense?

Text in OLAP Measures

mikypuff — Sun, 14 Feb 2010 12:49:00 GMT

I knew for a long time that measures can have text and not only numbers, but last week I had a scenario where it was the perfect solution for my problem.

Let's say that I work in number of jobs. In every job I got some tasks that I need to do. The fact table looks like this:

*Job*	*Task*	*Tasks To Do*	*Tasks Completed*
Developer	Develop BI System	1	1
Developer	Help Friends	1	0
Manager	Manage	1	1
Therapist		0	0

Summarizing the "Tasks To Do" column will get the total tasks that I need to do. Summarizing the "Tasks Completed" column will get the tasks that I did. Note that in the Therapist job I didn't get any tasks.
In the OLAP cube, I created a calculated measure which is the percent of the completed tasks (for every job or for any other dimension). The problem is: What is the answer in case that there are no any tasks to do? Some will say 0% and some will say 100%. That's why a text measure such as "There are no tasks" can be a perfect solution for this kind of problem. This is the calculation of the calculated measure:

Create Member CurrentCube.[Measures].[Completed Percentage] as
IIF(IsEmpty([Measures].[Tasks To Do]),
  null,
  IIF([Measures].[Tasks To Do] = 0),
    'There are no tasks',
    [Measures].[Completed Tasks] / [Measures].[Tasks To Do]
  )
)
This scenario can happen in many cubes. Tell me if you have any other solution for this kind of problem.

Update: Read the important comments below.

The MdxInjection Program - continued

mikypuff — Mon, 17 Nov 2008 21:54:17 GMT

After announcing the MdxInjection program I got several requests for additional details and for the ability to run it without using Visual Studio. So, here are some important points:

When I published it I had developers in mind because I'm sure than anyone will want to do his little modification before using it for his own needs. That's why I published it as a solution and not as executable.
I written it down using VS2008 but only with the .Net 2 framework. Those of you who uses VS2005 won't be able to open the solution.
The program has only one public method - InjectMdx, who takes two arguments: The location of the CommonMdx file and the location of the xml configuration file.
The CommonMdx.mdx file contains the common MDX script. The relevant part has to start with /* Common MDX */ and then the common mdx script. Anything written before it won't be treated. That gives you the ability to save some data or comments for yourself in this file.
Example of the configuration xml file can be found in the Test libary inside the solution. Basically, it enable you to define in which servers, databases and cubes you want to inject the common script. Pay attention that you have to write the connection strings in this file.
Note that the program will detect cube dimensions with their name changed and will know how to replace them. That means that if you mention the Time dimension in the common script and inject it to AdventureWorks cube, the script will replace the string "Time" with the "ShipmentDate" string, for example.

For those of you who want simple execution file, I added a windows console project in the solution.

Link to only executable program
Link to the solution with the added windows application project
Link to the solution without the windows application project

The MdxInjection Program

mikypuff — Sun, 16 Nov 2008 23:29:02 GMT

In the previous post I talked about the DRY principle in the BI Development. I mentioned that one of the major problems in the principle's implementation is in the common MDX code. Chris Commented:

"I'd like to be able to have a global MDX Script and be able to do something like a #include to bring calculations into specific cubes. One to add to my wishlist for the next version..."

And as I said there that I have a temporary good solution until we'll have it in the next SQL Server release (if someone from Microsoft is reading...).

The MdxInjection program takes your common MDX Script and a very simple xml file that defines where to inject this script. It injects the script into your desired cubes and even replaces the dimensions' names where necessary (it is relevant where you put dimension in a cube with a different name to thr dimension or when you use Role Playing Dimensions). I couldn't hold myself from writing some test code so it's also included in the project. The project is written in C# 2 using much AMO code. All the technical little details are inside.

Enjoy.

Download Link

Error Wrap in MDX

mikypuff — Sun, 03 Aug 2008 19:28:26 GMT

update: Chris Webb and Mosha commented and made it clear that the reason for this error wrap is the NonEmptyCrossJoin function and nothing else. I also checked and I did not find any other function that wraps underlying errors.

This is something you need to be aware of when you're writing MDX. I don't know whether it's a bug or by-design. I'll be happy
to know (please comment if you know something that I don't).
Consider the following MDX:

SELECT
  NonEmptyCrossJoin
  (
    [Customer].[Customer Geography].[State-Province].&[NSW]&[AU].Children
   ,[Employee].[Employee Department].[Department].&[Sales]
  ) ON 0
FROM [Adventure Works];

The query will return with this error: The Set_Count argument of the NonEmptyCrossJoin function is either negative or larger than the number of sets provided. This is quite reasonable because I written the second argument as a member, where (NonEmpty)CrossJoin expects only sets. So, let's upgrade this member to a set:

SELECT
  NonEmptyCrossJoin
  (
    [Customer].[Customer Geography].[State-Province].&[NSW]&[AU].Children
   ,{[Employee].[Employee Department].[Department].&[Sales]}
  ) ON 0
FROM [Adventure Works];

All I did is wrapping the second argument with {} and we have a set. The query will return 19 columns.
Now, for the interesting part. Let's count the members of this CrossJoin before the we fix it:

WITH
  MEMBER [a] AS
    NonEmptyCrossJoin
    (
      [Customer].[Customer Geography].[State-Province].&[NSW]&[AU].Children
     ,[Employee].[Employee Department].[Department].&[Sales]
    ).Count
SELECT
  [a] ON 0
FROM [Adventure Works];

This return... 0.
Where is the error?
My guess is that the Count function wraps the error. The NonEmptyCrossJoin returns null and the count of members in null is zero. The meaning of this is that if you'll ever forget to wrap the member with {} you'll always get zero and not an error. This can be very dangerous. Just for the check, running this query after the fix:

WITH
  MEMBER [a] AS
    NonEmptyCrossJoin
    (
      [Customer].[Customer Geography].[State-Province].&[NSW]&[AU].Children
     ,{[Employee].[Employee Department].[Department].&[Sales]}
    ).Count
SELECT
  [a] ON 0
FROM [Adventure Works];

will return 19. This been tested with both SSAS 2005 and 2008 (RC0). The examples here are from RC0.

Be careful with your MDX.

Very Customed Aggregation

mikypuff — Tue, 13 May 2008 20:28:38 GMT

I started a long conversation about this subject in the MDSN SSAS forum. I think that it's a question and a principal that every advanced MDX programmer should be familiar with.

It all started with a customer that needed a standard deviation aggregation. I thought that it would be simple because there's a StdDev function in MDX, but it turned out that my customer had major plans for me: He wanted this aggregation to act for every dimension he puts on his axis. This means that the aggregation is not defined over a specific dimension (such as date), but the std-dev is defined over the current dimension in the axis.

The solution for this problem consists of a principle and an answer.

The Principle
Aggregation or a measure that is based on the current user's query is bad. This can and will result two users to see different results using the same measure. This will cause confusion and disinformation. The sacred principle of One Truth will be desecrated. Taken from the thread, in Chris Webb's words:

"I quite often see people wanting to write calculations that behave differently depending on the query that's being run, and I always tell them not to do it. You can hack something but it's almost impossible to get it work properly for every single possible query - MDX just doesn't work like that"

In the end I explained that to the user and he agreed. One more reason for his approval is that std-dev often doesn't really says something about the data. In other words, it isn't informative. "The standard deviation is 0.432. That means that... ???"

The Answer
If you (or the customer) still insists on that crazy measure, the following MDX will work.

With
Member [Measures].[RowSTDOrders] as
iif(Count(NonEmpty(StrToSet("Axis(1)").Item(0).Hierarchy.Children,
{[Measures].[Order Quantity]}) as ChildSet) < 2,
Null,
StDev(ChildSet, [Measures].[Order Quantity]))

select
[Date].[Calendar Year].[Calendar Year] on 0,
Non Empty [Product].[Product Categories].Members on 1
from [Adventure Works]
where [Measures].[RowSTDOrders]

Thanks for Deepak Puri for this code. Notice that the StrToSet function will cause performance degrade, but this is the only way that the code will also work in MDX script and not only in queries.

P.S
It doesn't matter if you write StDev or StdDev.

Getting the Current Day in MDX

mikypuff — Tue, 08 Jan 2008 20:39:58 GMT

I feel like I don't have the right to write about it after so many bits of information were moving in the web about this subject but I would like to add my point of view (or in fact - my point of code). First of all, I must mention some of those who written about this subject before me. Mosha Pasumansky wrote a long post about it in last may. This post contains some ideas of how to come along with this problem, but none of them is perfect. In fact (as always) - there is no perfect solution for this problem. Another important source of knowledge can be found here in the MSDN forums, where Chris Webb, Thomas Pagel and others discussed it. Now, I would like to add my solution. Take it or leave it - your choice.

First of all, create a column in the time dimension that will be the current day indicator. Thanks to the Data Source View or the UDM approach of SSAS 2005 you don't have to change the relational table itself. Just add a named calculation in the DSV with expression that will be 1 for the row of the current day and null or zero for the others. The expression syntax itself depends on the underlying DB so I won't write it here, but it's very simple. Add this column as an attribute (let's call it CurrentDayInd) in the dimension structure and select its AttributeVisible propertiey to false. That's because we don't need such Attribute Hierarchy in our time dimension. After that, create a new User Hierarchy (you can call it Current Day), where the first level in it will be CurrentDayInd and after it place the day (key) attribute.

Now, what we have here? We have a hierarchy (Current Day) with two members - 1 and zero. The 1 member has only one child which is the current day. Link that member to your regular hierarchy (it's called YSQMD at my place) and here you have it. For example, you can use it that way in the MDX script:

Create Set [Last 30 Days] as
LinkMember([Time].[Current Day].[CurrentDayInd].&[1].Children.Item(0),[Time].[YSQMD])
:
LinkMember([Time].[Current Day].[CurrentDayInd].&[1].Children.Item(0),[Time].[YSQMD]).Lag(30);

I think that this solution is good and elegant. First of all, you don't have to use external functions such as Now(). The second pro is that we use the native OLAP mechanism which saves time and makes the queries run faster. Believe me, you'll feel the difference with big cubes. The last thing is that this solution is easy to understand (at least I think so) and it is easy for maintenance. The big con is that you have to process this dimension (and related cubes) every day. I don't think that it's so bad because most of the organizations do make process every day.

Excel in the SSAS 2005/8 Server

mikypuff — Mon, 31 Dec 2007 08:06:00 GMT

As many of you already know, installation on Microsoft Excel on the SSAS 2005 server is needed in order to use Excel functions in MDX. That's very helpful because MDX is lack of many important functions such as Round (!). Many organizations don't like it at all, but here's something that may help. In the SSAS 2005 server you don't need to install the whole program, only the .Net programmability support. In the installation, choose to manually pick up which components you wish to install and then choose the .Net programmability Support as seen in the picture:

Notice that this issue will not be fixed in SSAS 2008, so this tip will be relevant for a long time.

Showing multiple values in Panorama Dashboards

mikypuff — Wed, 19 Dec 2007 19:18:35 GMT

My team master Yaron asked me to check some things in the Panorama Dashboards:

1. Can have two hands in one gauge.
2. Can I show two values in the text of every gauge.

Here are the answers. I think that the second answer is a beautiful one. In fact, I really enjoyed while I thought how to do this.

1. This is simple: Just use the Goal hand as the second hand. In the KPI Wizard go to the Define Goal step and choose Custom formula. Enter the measure you want to see in the second hand.

2. This is beautiful: In the KPI Wizard, go to the Finish step and to the Title part. Click on the little blue arrow and click on "Edit MDX...". Then, write this MDX:

[My Dimension].[My Hierarchy].CurrentMember.Name + '\n' +
[Measures].[First Measure].Name + ': ' +
Generate({[My Dimension].[My Hierarchy].CurrentMember},[Measures].[First Measure]) + '\n' +
[Measures].[Second Measure].Name + ': ' +
Generate({[My Dimension].[My Hierarchy].CurrentMember},[Measures].[Second Measure])

Note that:

This solution may apply to other BI applications, not only to Panorama.
This way you can show many values and data, not only two values.
What the Generate function doing there? The '+' operator needs to have two strings in both sides, so writing only the [Measures].[First Measure] or [Measures].[First Measure].Value will return a numeric value which will cause error. The Generate function used this way will return a string. It generates for the set (which contains only our member) the value of the measure (in the second argument of the formula) and as mentioned, returns it as string.
'\n' will jump to the next line

Sort your KPI

mikypuff — Wed, 21 Nov 2007 21:24:44 GMT

I never thought of it until one of my users said it. Sorting the KPI can be a very good idea. Instead of always having the same indicators (gauges, traffic lights, etc.) in the same position of the screen, sorting it can make the viewer expect that the most relevant indicator will be placed in the top-left corner of the screen, the second most-relevant will be placed after it, etc.

Sorting the KPI is a very easy thing. Every end user, even with no clue in MDX can do it by following this:

In the Define KPI wizard, go to the "Select Set" step. Copy the current set. For the example, let's say that the current set is [Products].Members and you want to sort it according to the Sales measure in descending order. Click on the advanced button on the right of the set (The button with the "..." on it) and enter the following MDX statement:

Order([Products].Members, [Measures].[Sales], DESC)

If you want to order in ascending order you can replace the DESC with ASC or not to mention it at all.

Data Inheritence in MDX

mikypuff — Wed, 21 Nov 2007 20:12:21 GMT

I had a little challenge back in work and I solved it (in a couple of hours). Here is the description of the problem and its solution:

Let's say that I have a Products dimension, time dimension and a fact table that describes all the faults which occurred in these products. The new requirement is that: Given a product-tree that describes all the parts of every product, I want to know how many faults occurred to every part. The problem is that the fact table points only to the products and the customer wants to know how many faults happened to the parts.

The two solutions I thought about are:

1. The trivial solution: Build a view above the fact table that takes every row and adds a row for each of its product's parts. That way, the fact will contain every fault that happened to every part. The problem with this solution is that the view is very long to compute.

2. The good solution: Build a parent-child dimension out of the parts table which will describe all the parts of every product. Notice that this dimension is not ragged, meaning that one member can have 2 children while another member in the same level can have 10 children. The next step is to add this MDX Script:

Calculate;

Scope([Measures].[Faults],
         Descendants([Products].[Products].[All],
                           1,
                           After));

This = Ancestor([Products].[Products].CurrentMember,
[Products].[Products].[Level 02]);

End Scope;

Scope([Measures].[Faults],
[Products].[Products].[Level 02].Members);

   This = ([Products].[Products].CurrentMember)
             -
             Sum([Products].[Products].CurrentMember.Children);

End Scope;

Explanation: The first level of the dimension is the [All] member. The second level is the products and the other levels contain the parts. The first block takes all the parts and inherits their amount of faults from their product ancestor. The second block solves the aggregation problem: Let's say that The bike product has 3 children. The bike had 4 faults in our slice of time, so according to the first block of the script every child has 4 faults. Now, the cube makes its aggregation and now the bikes has 16 faults - 4 of its own and more 4 for every of its parts. The second block decreases the sum of the product's children from the products and the result is that the products have their original number of faults.

Showing Member Properties in Panorama

mikypuff — Sun, 30 Sep 2007 18:47:12 GMT

This post is about Panorama because it is the UI tool I'm working with, but this can be made with every BI UI tool.

My customer wanted to get the effect shown by Analysis Services 2005 when browsing a dimension (see the picture below). He wanted to see some properties of the members shown in the rows, along with the usual measures. Unfortunately, Panorama (and I'm sure that also other tools) does not have this option in the GIU. The solution is this code:

Create Member CurrentCube.[Measures].[MyProperty] as
iif(IsLeaf([MyDimension].[MyHierarchy].CurrentMember),
[MyDimension].[MyHierarchy].CurrentMember.Properties("MyProperty"),
Null)

Note that declaring only the third row will cause that every member that is not a leaf will cause an error, which is something we don't want the viewer to see. If the dimension has properties for members in other levels too, you can adjust this decleration. This member can be declared either in the DataBase's Script (after the CALCULATE expression) or inside the session/query (not recommended in Panorama). Now, all you have to do is to show the dimension's members in the rows and this new measure in the columns (after or before the regular measures), and you'll get what you want.

"Exception handling" in MDX

mikypuff — Sun, 09 Sep 2007 19:54:32 GMT

I won't cover here the topic of Exception handling in MDX, but show you a funny thing that I have never seen in any computer language. Consider this MDX code:

iif (1.0e+40 * 1.0e+40 = (1/0), "Overflowed", "Didn't Overflow")*

On some processors, this code will output "Overflowed". That's because this multiplication will overflow and (1/0) also overflows, so what we have here is two "overflow values" that are equal.

Where on earth have you seen something like this???

* Taken from the book "MDX Solutions" second edition, p. 136

MDX notes

mikypuff — Wed, 01 Aug 2007 17:04:47 GMT

While reading the first chapter of the book "MDX Solutions With MS SQL Server Analysis Services 2005 And Hyperion Essbase", I wrote down some important notes, especially for the MDX beginners. Even if you're experienced user, check this out. You may find something useful.

If you were a code programmer in your past, you can relax: MDX don't care about capitalization.
Don't even try to skip an axis: It's impossible and it is meaningless. Use the predefined names for the axis, such as: columns, rows, pages, etc.
You're new to MDX and the whole OLAP gives you a headache? Try to imagine this as a hypercube. It can help you a lot.
When writing large queries, pay attention to the "readability" of your MDX. Use the Monospace fonts whenever possible.
Do NOT think of SQL when learning or working with MDX. Although the syntaxes may look alike, these languages are totally different when you get to know them.
.Members will give you all regular members. .AllMembers will also include calculated members.
An expression like [Time].Members won't work if the Time dimension has multiple hierarchies.
The asterisk (*) can replace the CrossJoin function. It may improve readability of the code.
When using Order() function, you can specify a sorting criteria which is not shown in the result grid.

MDX is like chess

mikypuff — Sat, 02 Jun 2007 19:43:35 GMT

knowing the rules doesn't mean you know how to play.
A great post in the Panorama blog with a riddle in MDX. No knowledge in MDX is required for this riddle, because they teach you what you need to know to solve this riddle.
Have fun. Believe me - you will.

Update: Look at Mosha Pasumansky's blog for another review at this MDX riddle. He claims that the answer in Panorama's Blog is not complete. While I'm new to MDX, I understand that their answer is not 100% complete, but I think it's enought in order to make their point.