Category Archives: SSAS
It has been a while since I wrote my last article in here. Not because I was uninterested but because I have been the busiest I have ever been these past few years. These past few years, I have been tasked with the most complex/complete mandates I have ever been on. I have also been tasked with mentoring clients, coworkers as well as becoming a manager. I have learned A LOT to say the least and now that it is all winding down, I plan on releasing quite a few blog posts on the many challenges that I faced and how I overcame them. Stay tuned, this blog is about to get a bit more crowded 🙂
I recently encountered a harmless, but annoying nonetheless, little issue presenting itself when we were processing a SSAS cube or a SSAS database. In the process progress window, we were seeing the processing of measure groups of the cube inside/under the processing of a dimension. Normally, measure groups and dimensions processing are cleanly separated in the visual treeview of the processing progress window.
I was 100% confident that this was not causing other issues because the values while browsing the cube were accurate and all dimensions were properly slicing and dicing.
Although it was harmless, if you are like me – curious and like for thing to get done right, this seems peculiar enough to spend time understanding what exactly is going on.
It turns out that the person that originally created the cube had named both a dimension and the cube itself with the same name. Once created, the given name becomes the “ID” property of a given object. In this case, both a dimension and the cube were named “Reservation”.
It turns out that when processing a cube, the treeview of the process progress window uses this “ID” property to group progress and display them under parent objects (dimensions or Cube). Given that 2 objects had the same “ID”, and the first being processed was, naturally, a dimension, when the measure groups of the cube started processing, the treeview simply grouped the feedback/progress of the measure groups under the object of the same “ID”: under the dimension.
To correct this, just make sure that you give unique names across dimensions, cubes and measure groups. Then everything falls into place as it’s supposed to be.
If, like me, the cube is already created (which is likely the case – You processed the cube to experience this behavior. It is thus, already created!) you can just open a given object by right-clicking and selecting view code and then searching and replacing as needed.
Warning: Make sure to have a backup of your solution before proceeding with the search/replace inside the xml code approach!
Let me know if you experienced this behavior or any other similar ones!
Typically, measures cannot be aggregated along a currency dimension and so we set the IsAggregateable property to False. However, doing so has some side effects because you need to set a default member for the attribute (if you don’t, the first member of the attribute will be used). The default member will then automatically act as a default filter if the user does not explicitly use the currency dimension.
Using all dimensions, there is no issue:
But remove the Currency dimension and this is what you get:
The data behind this very simple pivot table actually contains 3 transactions in 3 different currencies (CAD, EUR and USD) but because of the currency dimension’s behaviour of not aggregating, displaying transaction amounts by any dimension other than currency will, in fact, only show transactions for the currency that was used as the default and thus, transactions in all other currencies will be hidden from the user.
To me, this is a big risk because we cannot ask of users to remember to ALWAYS use the currency dimension. It’s just not a good design from a usability point of view. What is needed is some way to show to the user something that can tell him that there are amounts for what he is currently looking at but that we cannot show it because that amount would be summing up multiple currencies. After thinking for quite a while and trying various approaches, I have finally come up with what seems to me like the best solution.
If you have a fact table containing amounts in various currencies, then without a doubt you have a foreign key pointing to the currency for that amount.
The trick here is to use that foreign key (let’s call it CurrencyID) as a measure in your measure group. Actually, you’re going to have to use it twice. Set one of those two measure’s AggregateFunction to Min and the other to Max naming both as MaxCurrencyID and MinCurrencyID respectively.
What will happen when you browse your cube is that for any given position, these two measures will represent the minimum and maximum values of your foreign key, CurrencyID, used for all transactions used in the sum for any amount. If these two measures have the same value, you can confidently display the amount knowing that only one currency was used. Conversely, if they have a different value, then it means that more than one currency is used in summing the displayed amount. In this case, all you need to do is display some text like “Multiple Currencies” instead of the actual amount.
Of course, you cannot show that 24$ total since all detail amounts are in different currencies so you will need to scope your measure with some very simple MDX scripting as follows:
[Measures].[Amount] = iif([Measures].[MinCurrencyID] = [Measures].[MaxCurrencyID], [Measures].[Amount], “Multiple Currencies”);
This scope statement will then produce something the following output:
Of course, MinCurrencyID and MaxCurrencyID aren’t the kind of measures you want your users to be using, so you’ll need to set the Visible property for those measures to False.
Using this technique, browsing the cube suddenly becomes much more informative to end users and nothing ever gets hidden to your users.
Be warned that this technique will work well for regular measures but implementing this on calculated measures is a bit trickier and will be the subject of my next post.
Recently, I was having a discussion on whether a given cube implementation should have, or not, been implemented as multiple cubes instead of just one. This blog post from Chris Webb pretty much sums up my thinking. I tend to go the “1 cube route” by default for the ease of use. But I do like the arguments for both cases.
One of the questions discussed in the book that Marco, Alberto and I wrote last year, “Expert Cube Development with SSAS 2008” (available in all good bookshops, folks!) was whether, if you have multiple fact tables, you should create one big cube with multiple measure groups or multiple cubes each with a single measure group. While I still stand by what we wrote then, I recently took part in an interesting debate on this subject in the MSDN Forum with Akshai Mirchandani from the dev team about the pros and cons of each approach where some interesting new details came to light:
Here are the main points you need to consider when deciding whether to use the single cube approach or the multiple cube approach. In favour of the multiple cube approach:
- Having multiple, smaller cubes may result in faster query performance than one large cube in…
View original post 1,027 more words
To be used to drag a value across the time dimension (simulates a LastNonEmpty) measure
CREATE MEMBER CURRENTCUBE.[Measures].[Potential Investment Equity Value] AS IIf(
[Measures].[Potential Investment Equity Snapshot],
[Calendar].[By Calendar Year].CurrentMember
[Measures].[Potential Investment Equity Value],
[Calendar].[By Calendar Year].CurrentMember.PrevMember
,[Measures].[Potential Investment Equity Snapshot]
), FORMAT_STRING = "$#,##0.00;($#,##0.00)", VISIBLE = 1, ASSOCIATED_MEASURE_GROUP = 'Acquisition';