Archive for Analysis Services

Analysis Services : How to add subfolders to your Measures Groups (Display folders tips and tricks)?

I’ve already blogged three times on measure groups matters and never thought I would have enough material for a fourth blog post. Fact is I stumble across an old post from Mosha Pasumansky on his sqlblog blog on the topic and rediscovered something I almost forgot with the years.

First you can find the three first posts here, although you’re not required to read them first to get the point of this post :

Analysis Services: What are the best practices for grouping measures ?

Analysis Services Feature Suggestion : Managing Measure Group visibility

Analysis Services : How to break distinct count measures into separate measure groups ?

To sum up Mosha’s blog post, you can add some extra flavour to your measures organization into folders with the following techniques :

– You already know that you can organize your measures into folders. But it is less known that you can have also sub folders. For doing this, as the UI in BIDS does not seem to allow you to do it, you jus have to enter the folders located on the path to the indicator separated by a “\” or “/” sign depending on your client tool. (ie : “folder 1\folder 2\…\folder x”)

– Even better, you can specify multiple folders to host the same measure by separating them with a “;” sign. (ie : “folder 1;folder 2;…;folder x”). In this case the measure will appear as much as the number of folders but it will act as one and single measure as selecting or de-selecting one of the clone will automatically impact the other instances.

Note : All those behaviors are client tool dependant as the information are only metadata attached to each measure and as nothing requires the client tool to make use of it. So depending on which tool you’re using, you may have to test its behavior before proposing those mechanism to you end users. I have tested them with success in Excel 2010.


Leave a Comment

Analysis Services : Beware of unused dimensions “out of sight, out of mind”

OLAP Cubes in general and Analysis Services in particular are very handy, easy to learn and powerful tools to find quick answers to huge and unexpected questions.

Once that’s said, one of the first thing I teach the end users I have a chance to meet during workshops is that they must never trust what they see without questioning the exposed data in the context of their functional specifications. Easy to learn, but yet hard to master …

Along the years I have gathered a list of common misconceptions about cubes and OLAP databases that I usually put in a good place in my workshops’ agenda. Just to be sure they won’t get disappointed several weeks/months later when they will get their hands on the solution we have built for them…

Here I want to discuss the mechanism by which dimensions that are not used in a query still have an impact on the result. In my list of misconceptions this one is called “Beware of unused dimensions : out of sight, out of mind”.

Indeed, that’s not because a dimension is not used in a query that it does not have an impact on the result and it is an extensive source of questioning/errors when you’re not accustomed to your cubes business rules.

In every cases the usage of those features must be thoroughly documented in the user documentation.

1) Left aside dimensions : impact of overridden default members

The most common source of problem  are the dimensions’ default members. By default, it’s the ‘All’ member, but it can as well be something else if you decide it so.

For example, when querying a stock indicator the default member of the time dimension will most probably be something like the last date for which we know the stock level as aggregating stocks over time at the ‘all’ level of the dimension has no meaning (There is other ways to achieve this including using SCOPE statements or LAST NON EMPTY aggregations, but that’s just for the purpose of my demonstration).

In case the dimensions left aside your query or pivot table do have their default members defined as their respective ‘All’ member, then no problem, everything goes as if they weren’t there at all. And as it is the most common scenario you may be mislead to think that this is always the case, but it’s not.

If I take back my stock example, you realize that if you leave your “snapshot time” dimension aside, your query gives you only the last known figures and not all known stocks from the beginning of times. That’s exactly because the left aside dimension has one of his member defined as default member. You can achieve the same result by explicitly filtering the query by the dimension custom default member meaning that it has indeed a filter action on the query context.

As a corollary to this, every dimension, used or not in a query, has a current member in the context of any query.

2) Left aside dimension’s hierarchies : how do behave unused hierarchies ?

A more subtile behavior occur when you are using hierarchies in your queries.

Take for example a standard “Time” dimensions with two hierarchies defined as H1 : “Year -> Week -> Day” and H2 : “Year -> Quarter -> Month -> Day”. The default member of the dimension is the ‘All’ member of the ‘All’ level.

When left aside, the current member of the dimension is ‘All’ and by extension, every hierarchy in the dimension inherit the same behavior.

Now here’s the questions :

Q1 : If I filter my query by using a member of the quarter attribute of the dimension, what is the current member of the left aside members ?

A1 : You’re right, it’s the ‘All’ member.

Q2 : And what is the current member of the left aside H1 hierarchy ?

A2 : Yep again, the ‘All’ member.

Q3 : And now, what is the current member of the left aside H2 hierarchy ?

A3 : If you answered the same member of the quarter level at the respective quarter level, then you guessed it right. That’s because hierarchies are really some kind of views over dimension attributes, so filtering attributes do have an impact on the hierarchies and vice-versa.

Sometime, when coding measures in MDX, you end up testing toward which hierarchy the formula should be evaluated as you have to explicitly quote the hierarchy in the formula when there are two of them or more. In this case testing if you are not on the default member doesn’t guaranty that the hierarchy is actually used as it can be left aside but filtered by the usage of another hierarchy or attribute as exposed earlier.

All in all, though it may seem straightforward to query cubes, particular attention should be accorded to the behavior of cubes when they grow in complexity.

Leave a Comment

Analysis Services : How to break distinct count measures into separate measure groups ?

Having blogged last week here about the advantages of the new storage file format found in the Denali’s CTP3 for those kind of measures when they are of the string data type, remember me that maybe this little feature may need a post. This is no rocket science, just a little tip that every one would solve after spending 5 minutes on it. I even think that most of you may already know about it….

Read the rest of this entry »

Comments (2)

[SQLServer 2012] Analysis Services New Feature : Scalable String Storage

As you may know, both dimension’s attributes and distinct count measures can be of string data type. During processing, Analysis Services reads data and put them into individual stores organized by attributes in case of dimensions or by measures groups/partitions for cubes.

The file format of those stores differ to adapt to the type of the data located inside. By default, until now, string stores couldn’t exceed the 4GB file size limit.

Starting with the CTP3, a new feature names “Scalable String Storage” allow to go beyond this limit. A new property named “StringStoresCompatibilityLevel” is available at different places to activate it.

I’ll review in this post how to set up this new feature in your SSAS solution and what we can expect from it…

Read the rest of this entry »

Comments (5)

Analysis Services Feature Suggestion : Managing Measure Group visibility

The most commonly used reporting tool by the users to query Analysis Services may surely be Excel. As professionals, while testing SSAS solutions, we also add BIDS and SSMS to the list.

One of the great but little known feature of those SSAS reporting tool, is their ability to hide the multiple measure groups complexity by selecting one of them and only displaying the measures and dimensions that belongs to it.

Just in case you don’t know what I’m talking about, here’s a screenshot :

Read the rest of this entry »

Comments (1)

Analysis Services: What are the best practices for grouping measures ?

You’ll find in the following post some general considerations to have in mind when designing cubes about how to dispatch indicators into groups to suite the end-user needs and the consequences of these choices.

Lately, a colleague of mine at Avanade was embarrassed because his customer wanted to visually group and mix heterogeneous kind of indicators into same folders so that he’ll later find them easily into his pivot table into Excel.

As a good practices follower he designed his data warehouse and cube by respecting multidimensional modeling, commonly approved, best practices and putted all the measures that share the same grain into a single fact table and into the same measure group…. which, as he discovered it later, is not necessarily the organization that the end-user have in mind for his data when it comes to build analysis reports.

My colleague first thought was to rely on the “display folder” and “associated measure group” properties to fill the end-user need. The drawback is that what is defined in a measure group stays in the measure group and a fact table cannot lead to more than one measure group.

So as it comes that he was unable to easily answer the requirements, he asked me for advises on how to handle the situation. Here is what we can say…

Read the rest of this entry »

Comments (2)