SSAS process progress window showing measures processing inside of dimension processing

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.

20160406-1

 

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.

20160406-2

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!

Transparent is not a valid background color

I don’t know about you but I hate leave build warnings when I work on something. I am usually not satisfied until Visual Studio’s Error List window is empty, free of any errors (of course) and of any warnings. Lately I was developing some reports for a client where I implemented alternate row coloring to ease the reading of a list-type report.

With the expression in this next screenshot, I get a light grey (WhiteSmoke actually) every odd row. On my even rows, I don’t want to set the background to white. I want it to be transparent so that any color of a control behind would show through. The problem is that when you use the constants in SSRS’s expression builder, selecting “No Color” gives you “Transparent” to use in your expression.

Oddly, using “Transparent” raises warnings in SSRS and doing so fills up the Warning list as such:

What you actually have to use is the Nothing key word and all warnings relating to “Transparent” not being a valid background color will go away. Nothing translates back to a transparent background at run-time so the final output is still the same.

I would have thought that the expression builder would have given me the correct constant to use though! I guess I’ll have to take the habit of writing “Nothing” instead of clicking on the “No Color” selection.

The importance of well formatted SQL

Everyone knows the importance of well formatted code in whatever language and I don’t think I’ll have to convince anyone on that point.  But today I have a situation where there are SQL statements saved in a field inside a database table.  These statements are not just simple SELECT statements but are more like what would constitute a small stored procedure.  Much to my disappointment, these statements are stored as single lines of text making any modifications to it become an endless repetition of  TAB, TAB, ENTER, TAB, TAB, ‘TAB, ENTER, TAB, TAB, ‘TAB, ENTER, TAB, TAB, ‘TAB, ENTER, TAB, TAB, ‘TAB, ENTER.  You get the picture…

I know that there are some SQL Management studio add-ins that can take an SQL statement and format it automatically but my situation at this particular client is that I cannot install anything on the development PC and so I have to rely on web-based stuff a lot.

After a quick look around the Web, I stumbled on this web-based SQL formating tool that does the job very well so I thought I’d share!

http://sqlserverlearner.com/online-tools/tsql-code-formatter

Do you know of any other tools that can do the job? Preferably non-web based and no installation required. Please share with me in the comments below.

SSAS – Summing on different currencies. Don’t hide your data.

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.

One Cube vs Multiple Cubes

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.

Chris Webb's BI Blog

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:

http://social.technet.microsoft.com/Forums/en-US/sqlanalysisservices/thread/681e59bd-93ca-4a91-9f26-8ed96e825553

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

Reporting Services’s RepeatOnNewPage property – that elusive feature

For my first post (please be gentle with me), I’d like to write about something that’s been bugging me time and time again in SSRS: the RepeatOnNewPage property.

In all my list type reports, I want my users to be able to see the nice column headers on every page of the report and not just at the beginning of it (who would want that anyway?).

Here’s what you have to do:

  • Select the Advanced option from the arrow on the right of the groupings pane so to show every group;
  • From the new “Static” groups that appeared, one (or more) is your header row.  Essentially, it’s the row(s) containing the column headers (in my screenshot, I selected the first Static row group);
  • In the properties for that group, set the RepeatOnNewPage property to True;

Normally, one would think that this would be it.  The property is namedRepeatOnNewPage after all.  Well NO! At least, not always.  That’s the part I was struggling with for over a year.  Even if that property was set to true, the column headers would never repeat on the new page. Searching on the web never turned up anything that was of any help to me.

I later found out that it was so because I, as I usually do, had deleted the group header column. When you add a row grouping in SSRS, the default behavior is to add a column that spans over all the child groups. That group header column, had I kept it there, would have span over the entire child groups and so, the parent group would always have been visible (or should I say contextually current) and my column headers would therefore always have been visible.

After nearly two years of working around that issue by keeping the group header column but making it as small as possible because hiding it had an unwanted effect for design vs runtime layout, the answer finally came to me.  I was showing a report to a coworker who asked “What does the KeepWithGroup” property do?” And then it hit me.

If I “keep my parent group” with the child group using theAftersetting, the parent group would always be “contextually current” (or visible if you will).  I tried it and voila!  My column headers were repeating as I wanted them to be two years before.

So there you have it: repeating headers even if, like me, you don’t want to use the group header column that SSRS adds, by default, when you create a group.

Fresh Start

Well, after having used this blog for personal purposes and having posted a whopping 6 posts in about 5 years,  I decided to try this thing again.

This time around, I will be writting in english.  Why?  Because I willl be writting mostly about Business Intelligence (what I do for work) and since it’s a computer subject, it HAS to be in english 🙂  … and also the potential audience is much wider.

I must admit I’m far from being even a moderatly good writter but hopefully, this will help me practice.

I intend to write about stuff I discover at work and use this blog as my personal reminder.  If it helps anyone, I’ll be very happy, but to start with, I’ll just write for myself.  I’ll be writing about BI in general when applicable but mostly on the tools I use and specialize in: the SQL Server BI Suite.

So here we go, I’ve done my spring cleaning and deleted all my (very) old posts except one which is still pertinent.

So here goes… Let’s see where this takes me!

Recursive Calculated Member

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(
IsEmpty(
(
[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';
%d bloggers like this: