Boyan Penev on Microsoft BI

Obtaining Microsoft BI Certification

2010-02-24T13:20:00.001+11:00

I have been a little busy recently with getting certified with Microsoft. I am quite happy to announce I have passed both 70-448 and 70-452 exams in the last couple of weeks without reading a single page of preparation material. Now I am MCP, MCTS and MCITP in SQL Server 2008 BI.

I have always wondered how important and relevant these certificates are. The common opinion around the industry professionals seems to be that experience is what really counts and certifications are for people without experience who are trying to get into the profession. I did it the other way and I am not sorry the least bit for it. An overview of my experience in regards to the two certifications:

MCTS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance
Exam: 70-448 Microsoft SQL Server 2008, Business Intelligence Development and Maintenance

I passed this one with 857 (out of 1000). Since it was my first attempt at certification I did go through a few sample questions from a prep book based on MeasureUp, and a friend also flicked my way some really poor quality Braindumps, which contained some quite poor terminology and a fair bit of nonsense. On the MeasureUp tests I scored between 65-85%, so I decided to get a Free Second Shot voucher from Prometric and just go with no further preparation. As expected, my Data Mining skills did not quite cover the expectations and I had a few glitches around SSIS maintenance, but as expected I got more than 90% on all the development components and around 80% on all the administration/maintenance ones. After all I am a developer and I am not that experienced with administering BI solutions. So in general, my impression was that the test can be passed without much preparation from fairly inexperienced developers, who read some preparation materials.

MCITP: Business Intelligence Developer 2008
Exam: 70-452 PRO Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008

This one was harder, but I passed it with 92% with doing one sample test of 30ish questions before the attempt. I had another Prometric Free Second Shot voucher, so I was not stressing if I would pass or fail. The test had double the amount of questions than the MCTS one and it took me a fir while to go through all of them. Again - Data Mining was my weakest part (less than 50% right), while I managed to score 100% on SSRS, SSAS and "Designing the BI Architecture" part. SSIS was almost perfect too. Now, if a fairly inexpreienced developer passes MCTS, this test will definitely cause a lot more headaches. The questions are much more practical and much higher degree of relevant experience would be required to pass (or much more reading).

In general, both of the tests are not easy and I do believe that if one can pass them without preparation (from experience), he is quite prepared to tackle the design, implementation and administration of Microsoft BI solutions. The Free Second Shot vouchers are also great for eliminating stress to some degree. However, on the not-so-good side, the tests can be passed with no experience and because the questions do follow a certain pattern and do not allow for much freedom in choosing the correct answer (short answer ones could be better), I think that there is a moderate chance of inexperienced and not that knowledgeable people to study, pass and then immediately forget the subject matter.

Still, I have no idea how much my career will benefit from these certifications, however I did get a few PDF certificates signed by Steven A. Ballmer to wave at sutiable occasions :)

To PowerPivot or Not

2010-02-13T17:18:00.001+11:00

Just last week I attended a Microsoft presentation with a subject: “SQL Server 2008 R2 for Developers”. The presenter went through most of the new features in R2 and in SQL Server 2008 but the largest chunk of the presentation was dedicated to PowerPivot. Unsurprisingly, the 50+ developers in the room had not heard anything about it and did not seem very interested in what the presenter had so say and show. I was not surprised not because the feature is insignificant, but mostly because .NET developers are naturally not the best audience for database presentations, let alone for new BI functionality which is tightly coupled with Excel.

However, my eyes and ears were wide open for what the Technology Specialist had to say and he did alright – showed us how we can use Web Slices from SSRS 2008 R2 as a data source for PowerPivot, how we can publish reports through Excel Services, sorting and filtering 101 million rows in less than a second, etc. It was quite impressive.

After the presentation a senior developer approached me (since I am the BI go-to guy) and asked me, among other easy questions, the following: “Who will be using all this?” and “How will users upload 101 million rows spreadsheet to SharePoint?”

Since I had no straight answer for him, I thought it might be a good idea to ask the Microsoft national technology specialist. The replies (not literal quotes) we got were:

“How will users upload 101 million rows spreadsheet to SharePoint?”

Because PowerPivot uses column compression, 101 million rows actually take between 10 and 15 Mb, so uploading them over a network connection to SharePoint is not a problem.

“Who will be using all this?”

I understand that the potential use scenarios for PowerPivot are quite limited, but we (Microsoft) believe that there will be some cases where power-users will need and want this sort of functionality.

Fair enough. However, both of the answers were not convincing. The problems with them are:

1. What happens when we have a large number (say 1-2 million) dimension values, and a large number of facts as a source for PowerPivot? Obviously, column compression will not work that well in this case, which could be quite common.

2. If PowerPivot is mainly for power-users, how powerful do they need to be to take advantage of it? It seems like only a tiny subset of power-users with some expert Excel knowledge will be able to operate this new Excel functionality. I will be quite surprised to see users managing live feeds, analysis services sources, DAX, etc.

3. If BI developers first have to build a PowerPivot environment for the end-users, this does not seem like self-service BI at all, and I would be reluctant to do that if I can achieve the same and more though Analysis Services.

What I liked and I think would be quite useful are the numerous improvements to Excel 2010 as a data analysis and report generation tool. The product seems quite improved and I believe that it will get adopted quickly by heavy Excel users. However, I am somewhat perplexed by the buzz around self-service BI and PowerPivot in particular. Especially since we (BI developers) still need to get some extra functionality implemented to make our and our users’ everyday experience smoother and easier.

Please feel free to let me know if you have better answers to my questions. For the time being I consider myself instead of an opponent, an unconvinced and cautious observer of the recent developments in the BI space, and from this point of view self-service BI seems a bit overhyped. Hopefully it’s me – not the product, what needs refinement.

All Member Properties - Name, Key and Level

2010-02-05T12:25:00.002+11:00

I just tried to find some more information about the All Member in SSAS dimension hierarchies and since it was not readily available, I had to experiment a bit, so I thought I may as well share my findings. For some these may be obvious, but for some they could as well be interesting.

So in brief, I will explore the Name, Key and Level of an All member in a dimension hierarchy. The one of choice was the Customer dimension and Customer Geography hierarchy in Adventure Works. There is an All member, called All Customers. As expected, .PROPERTIES("MEMBER_NAME") gives us "All Customers":

WITH
MEMBER [Measures].[test] AS
[Customer].[Customer Geography].CurrentMember.PROPERTIES("MEMBER_NAME")
SELECT
{
[Measures].[test]
} ON 0,
{
[Customer].[Customer Geography].Members
} ON 1
FROM [Adventure Works]

The first row shows us: All Customers.

Now, let's see what its key is:

WITH
MEMBER [Measures].[test] AS
[Customer].[Customer Geography].CurrentMember.PROPERTIES("KEY")
SELECT
{
[Measures].[test]
} ON 0,
{
[Customer].[Customer Geography].Members
} ON 1
FROM [Adventure Works]

This gives us 0.

And its level:

WITH
MEMBER [Measures].[test] AS
[Customer].[Customer Geography].CurrentMember.Level.Name
SELECT
{
[Measures].[test]
} ON 0,
{
[Customer].[Customer Geography].Members
} ON 1
FROM [Adventure Works]

The result this time is: (All).

So far so good. Now let's try using these to get only the All member:

SELECT
{
[Customer].[Customer Geography].[All Customers]
} ON 0
FROM [Adventure Works]

This works. Now if we try the Key:

SELECT
{
[Customer].[Customer Geography].&[0]
} ON 0
FROM [Adventure Works]

Interestingly, since the All member is a calculated member and has no physical key, if we try to use the one that SSAS gave us does not actually work - we get nothing on Axis 0.

Using the level works:

SELECT
{
[Customer].[Customer Geography].[(All)].Item(0)
} ON 0
FROM [Adventure Works]

Also, after experimenting a bit further:

SELECT
{
[Customer].[Customer Geography].[All]
} ON 0
FROM [Adventure Works]

This query also works even though the All member name is [All Customers], not just [All]. However, Analysis Services does recognise [All].

In summary, the most robust options for referencing the All member in a dimension hierarchy that I have found are:

1. [Dimension].[Hierarchy].[(All)].Item(0)
2. [Dimension].[Hierarchy].[All]

These will always work - regardless of the dimension and hierarchy names.

Another option is using [Dimension].[Hierarchy].[] - e.g. [Customer].[Customer Hierarchy].[All Customers]

And, one that does not work - referencing through its alleged key: [Customer].[Customer Hierarchy].&[0]

Please let me know if there are any better alternatives, or why it would give me a key of 0 for the All member and would not work when actually using this key.

An Open-Source Approach to Business Intelligence

2010-02-02T11:29:00.000+11:00

Since Microsoft discontinued PerfromacePoint Planning, it is slightly unclear what is their strategy in this field. I have made a few suggestions in the past, some of which:

• Stored-Procedure write-back from Excel
• SSAS write-back from Excel
• Better support for dimensional and measure group write-back

Whether, when and how these may get implemented is an open question. I firmly believe that we should continue learning from any possible source – including our competitors. This is why I am quite interested in any possibility to extend my knowledge in the BI space regardless of who is presenting the ideas and solutions.

Being lucky as I am, I received an open invite to the PALO Australia Roadshow 2010, presented by a company I recently posted about – Naked Data. In a brief, what you can expect from PALO and the presentation is:

• In-memory MOLAP
• Rule-based forecasting and planning
• Some sci-fi GPU parallel processing
• Integration with Excel (PALO formulas and Pivot tables)
• .NET API
• Free breakfast

I will be there trying to get some ideas for my future forecasting and planning implementations. Seats are limited, so make sure you register soon!

Average Aggregation in Analysis Services

2010-01-20T17:30:00.005+11:00

In SSAS we do not have a measure Average aggregation type. We do have AverageOfChildren (or Average over time), however it is semi-additive and works only along a Time dimension. Fortunately, we have Sum and Count, and since Average = Sum / Count, we can build our own Average aggregation when we need one.

To do that:

1. Create a measure using the Sum aggregation type (which is also the default). In our example, let's call it Amount.
2. Create a Count of Non-Empty Values (or Count of Rows) measure. In example - [Measure Count].
3. Create the actual calculation - [Measures].[Amount]/[Measures].[Measure Count]

We can either create a calculated measure, which performs the MDX calculation above:

CREATE MEMBER CURRENTCUBE.[Measures].[Average Amount]
AS
[Measures].[Amount]/[Measures].[Measure Count]
,NON_EMPTY_BEHAVIOR = {[Measures].[Measure Count]}
,VISIBLE=1;

, or if we really do not need the Sum base measure, we can set it to be replaced by the calculation with a SCOPE statement:

SCOPE([Measures].[Amount]);
This = [Measures].[Amount]/[Measures].[Measure Count];
NON_EMPTY_BEHAVIOR(This) = [Measures].[Measure Count];
END SCOPE;

Voila! We have created a measure simulating an Average aggregation type. Then, we can hide the Count helper measure and from user point of view there is no evidence of our effort.

Since the count will never be 0, we do not have to say "If not 0, divide, else - do not" and the NON_EMPTY_BEHAVIOR query hint may in fact improve performance, since the calculation will not be performed when the Count measure is NULL (instead of resulting in NULL/NULL=NULL).

Mosha has previously blogged about NON_EMPTY_BEHAVIOR and division by zero and I strongly recommend reading his post.

Another important consideration, which depends on the business scenario is the type of the Count aggregate. It could be Count of rows (Row Bound) or Count of non-empty values (Column Bound). The difference is whether we want to include or exclude the empty values from our aggregate. Either way, the described technique will work equally well.

I realise that this is a well-known approach, but since it is hard to find the solution online I thought it may be interesting for some less-experienced developers.

Reporting Services 2008 Layout Properties Glitch

2010-01-11T09:01:00.008+11:00

Today while working with a basic sample dashboard in SSRS I noticed that for some reason one of my subreports disappeared:

While it got rendered on its own in Report Manager, when I placed it within a subreport (with no other containers around it) it just refused to show up. One of the properties I tried was the Layout menu item on the subreport:

To my surprise, selecting the "Bring To Front" option solved my problem:

This one was also hard to understand since it rendered correctly in BIDS, leading me to think that there is something wrong with my browser or report server setup. It seems like report items can be sent behind the report background, which at a first glance seems like a confusing and redundant bit of functionality. I also unsucessfully tried to reproduce this in SQL 2005, so it must be a new "feature". Also, it happened without any action on my part - I did not edit any layout options prior to its disappearance.

EDIT (15/01/2010)
The issue appears only with a subreport containing a Map. Interestingly, the Map actions - I have a Go To URL on the states still work, but the actual image gets lost somehow.

EDIT (21/01/2010)
Microsoft replied with the following:

Posted by Microsoft on 20/01/2010 at 11:25 AM
Thank you for reporting this issue. It only happens if the Map is the first report item on the Subreport and there are no Data Sets specified. You can work around this problem by placing the Map into a Rectangle report item.

OLAP Browser Feature on Connect

2010-01-05T12:03:00.000+11:00

As there has been a considerable stir around a few blogs (including mine) lately in regards to the lack of a good OLAP browser/client in the Microsoft BI space, I just created a feature suggestion on Microsoft Connect. Please support me with some more ideas about how Microsoft can fill this apparent gap in the current BI stack. The link is here:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=523128

Other posts about this issue:

http://richardlees.blogspot.com/2009/09/which-cube-browser-for-microsoft-olap.html
http://richardlees.blogspot.com/2009/10/whats-preventing-excel-from-being.html
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!5100.entry
http://sqlblog.com/blogs/marco_russo/archive/2010/01/05/microsoft-doesn-t-play-the-traditional-bi-client-game.aspx
http://www.bp-msbi.com/2009/09/on-search-for-perfect-olap-browser.html

Merry Christmas and Happy New Year

2009-12-21T19:34:00.004+11:00

I now have 40 articles on this blog, around 183 subscribers and approximately 1500-1600 visits a month. It’s been a very pleasant and rewarding casual occupation to write about the world of Microsoft Business Intelligence.

As my professional commitment for the new year I can promise that I will continue writing about new and interesting things I find while playing with SQL Server and the other great products which Microsoft puts on the market.

Also, recently I have started visiting the MSDN SQL Server forums and I will continue doing so in the next year. I would encourage everyone who reads this blog to start sharing their knowledge and helping new developers there – the technologies Microsoft offers are only as good as the people who implement them – us. Working together builds us as professionals and in the end makes our lives easier.

Many thanks to Nick Barclay for encouraging me to start this blog at the first place. Also, a big THANK YOU to Paul Hales, who even though hard to convince to start writing himself, has taught me quite a few things over the last couple of years and has always been the fiercest Microsoft advocate imaginable.

To Paul and Nick, to everyone who I have had the pleasure to work closely with (Tamzyn Bielecka, Mark Dasco, Maxim Yefremov, Sue Hourigan, Chris Mentor, Edmund Harvey and many others) and to all bloggers and readers – have a really MERRY Christmas and a very HAPPY New Year!!!

Blog Maintenance

2009-12-13T22:27:00.000+11:00

This is just a note that I have moved this blog to a custom domain - http://www.bp-msbi.com/. Also, I changed the Subscribe To link on the blog to point to my feedburner feed.

Based on some research, this change should not affect any existing subscribers and links to the previous address, as blogspot will redirect the older version of the blog URL and its older feed. However, I would really urge you to change your feed settings to point to the feedburner address just in case.

And to justify this little amount of trouble I am causing for eveyone - I quite like some other alternatives, like WordPress, and the longer I keep my blog as a subdomain on blogspot, the harder it becomes to move it out. Plus, I am a Microsoft advocate, especially when it comes to SQL Server, so I find it slightly more suitable to have a non-blogspot (read Google) URL for $10/yr extra.

7 Ways to Process Analysis Services Objects

2009-12-10T00:25:00.003+11:00

Being asked a bit too often how we can process Analysis Services databases (or cubes and dimensions) here is a list of 7 different methods:

1. Through the GUI

This one is obvious. We can do it through both SSMS and BIDS.

2. XMLA Script

To generate the script we can use the hefty Script button in SSMS. Simply configuring the processing settings and then instead of clicking the all too usual OK, we can as well click on the little button in the top left corner of the Process window:

Then, we can just execute the generated query.

3. SSIS Analysis Services Processing Task

This Control Flow task allows us to configure any settings and then add it to our ETL process. Quite handy.

4. SQL Server Agent Job

This one is really an automation of Method #2 – XMLA Script. We can encapsulate it into a job of SQL Server Analysis Services Command type:

5. .NET Code

This allows us to process cubes as a part of an application. Nice if we want to let our users process our cubes on-demand. Of course, better left to application developers, but still a good trick to know. Especially if we want to seem all-knowing when it comes to databases of any type. To achieve this objective, we use AMO (Analysis Management Objects). An API can be found here:

http://technet.microsoft.com/en-us/library/microsoft.analysisservices(SQL.90).aspx

6. Command Line - ascmd

The command line utility can do a lot – including processing SSAS objects. For a full readme you can go here:

http://msdn.microsoft.com/en-us/library/ms365187.aspx

7. Command Line - PowerShell

This PowerShell script will perform a Full Process of Adventure Works DW 2008 on localhost:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices")
$servername=New-Object Microsoft.AnalysisServices.Server
$servername.connect("localhost")
$databasename=New-Object Microsoft.AnalysisServices.Database
$databasename=$servername.Databases.GetByName("Adventure Works DW 2008")
$databasename.Process("ProcessFull")

Using AMO we can do any maintenance tasks through PowerShell, including an object process.

Probably not a fully exhaustive list, but I hope it helps with giving developers some options when it comes to this trivial and crucial part of the development and deployment process.

SQL Server DBMS Top 1 Wish List

2009-12-08T14:23:00.004+11:00

As an addition to Teo Lachev's Top 10 Wishlists (SSAS and SSRS), I would like to contribute only 1 item to a possible SQL Server DBMS wishlist:

1. Source Control.

Not SourceSafe source control, but rather an automated version out-of-the-box, not relying on developers to check in/out. Rather, it should track the changes to the code as they are made, and a full version history should be available directly in the DBMS. It should not be too hard. After all, there is a nice database available, which can store code with its version numbers just like anything else.

This would make a lot of developers' lives a bit less frustrating.

OK, a SQL code "beautifier" would also be nice, but it is not all that important...

Vote on Connect

Filtering measures by indirectly related dimensions in MDX

2009-11-26T16:21:00.005+11:00

I have lately started visiting the SQL Server MSDN Forums and trying to answer some questions about Analysis Services and Reporting Services. One of the questions about MDX queries seems to get repeated quite often and I will try to address it in this post, so hopefully more people will get to read this rather than ask about it on MSDN.

The actual question takes the form of:
"I have Dimension A and Dimension B, related to Measure 1. Dimension B is also related to Measure 2. How can I (is it possible to) get the values for Measure 1 filtered/sliced by Dimension A. I know it is easy to achieve with a join in SQL, but I do not know how to do it with MDX.

This suggest the following dimension model:

One solution would be creating a many-to-many relationship between Dimension A and Measure Group 2. However, we may want to avoid that for some reason and answer the problem with a query.

We can achieve the desired result in a number of ways but I will discuss the one using NONEMPTY. Others would be using FILTER and EXISTS.

A sample script is:

SELECT
{
[Measures].[Measure 2]
} ON 0,
NON EMPTY
{
NONEMPTY( [Dimension B].[Dimension B Hierarchy].Members,
([Measures].[Measure 1], [Dimension A].[Dimension A Hierarchy].&[Member_Key]))
} ON 1
FROM [Cube]

What this script does:

1. Gets all Dimension B members, which have associated cells for Measure 1 and the specific Dimension A member (which we are filtering/slicing by)
2. Gets the Measure 2 cells for the set of members retrieved in Step 1
3. Removes members from Step 1, for which cells from Step 2 are empty

An AdventureWorks example is:

SELECT
{
[Measures].[Internet Order Count]
} ON 0,
NON EMPTY
{
NONEMPTY( [Product].[Product].Members,
([Measures].[Reseller Order Count], [Reseller].[Reseller].&[238]))
} ON 1
FROM [Adventure Works]

Problems with FORMAT_STRING, VBA functions and NON_EMPTY_BEHAVIOR

2009-10-27T13:28:00.006+11:00

Consider the following requirement: "We want our measure to be truncated to 4 decimal places without any trailing 0s after the decimal point and a comma as a thousands separator."

First, let's focus on the truncation part. If we want that to happen for a measure, we can do it through the following formula, as I have described previously:

SCOPE([Measures].[Our Measure]);
This = Fix([Measures].[Our Measure]*10^4)/10^4;
END SCOPE;

This takes care of our truncation. So far so good.

Now let's have a look at the formatting. If we want to apply custom formatting through FORMAT_STRING for a number such as 12345.1234, which states: "#,0.####", in order to obtain 12,345.1234 we run into a problem. The same FORMAT_STRING expression applied to 12345 gives us 12,345. - including a trailing decimal point (in our case a trailing period). There is no way to get rid of it through FORMAT_STRING. Even in the specifications for FORMAT_STRING it is pointed out that:

If the format expression contains only number sign (#) characters to the left of the period (.), numbers smaller than 1 start with a decimal separator.

This holds true for #s to the right of the period, as well.

What we can do in this case is either conditionally format the number, with some sort of a rule, which checks if the number is whole or not (I will aviod that), or we can use the VBA Format function like this:

SCOPE([Measures].[Our Measure]);
Format([Measures].[Our Measure], "#,0.####");
END SCOPE;

This yields the correct result, so in the end, we can do:

SCOPE([Measures].[Our Measure]);
This = Format(Fix([Measures].[Our Measure]*10^4)/10^4, "#,0.####");
END SCOPE;

That may be achieveing the result, but let's look at perfromance of a calculated measure utilising this approach. The first thing I noticed when I implemented this is the huge increase in query processing time and the large number of 0 valued cells. It turned out that the VBA functions in MDX do not skip empty (NULL) cells. If you try Fix(NULL), you'll get 0. So, after Fix-ing our measure, we get 0s for every empty cell in our cube. The same is valid for Format.

Next step was trying to find a way to skip these empties. I tried:

SCOPE([Measures].[Our Measure]);
This = Format(Fix([Measures].[Our Measure]*10^4)/10^4, "#,0.####");
NON_EMPTY_BEHAVIOR(This) = [Measures].[Our Measure];
END SCOPE;

but it did not work. I still got the 0s in my result set. I suppose that it got ignored by SSAS. Because of this issue I decided to write an IIF statement like this:

SCOPE([Measures].[Our Measure]);
This = IIF([Measures].[Our Measure] = 0, NULL, Format(Fix([Measures].[Our Measure]*10^4)/10^4, "#,0.####"));
END SCOPE;

This also worked. However, now we have an IIF statement, which serves no purpose other than filtering our empty cells, because of the VBA functions' behavior. It would be interesting if there is any other way of implementing this, avoiding the problem. It would be nice if:

1. VBA functions can skip empty cells
2. FORMAT_STRING behaves just like Format
3. NON_EMPTY_BEHAVIOR actually works with SCOPE (in queries)

Please comment if you have a solution for the problems above and let me know if you would like to see the above issues fixed (I am considering raising a feedback/recommendation issue on Connect).

Data Mystification Techniques

2009-10-15T13:53:00.024+11:00

I am a fan of studies in Data Visualisation. It is a creative and dynamic field with a lot of room for experiment. I am considering report and dashboard design, and within this frame Data Visualisation, as a form of practical art. Well designed and built reports are critical for solution adoption and usability. However, in this post I will concentrate on exactly the opposite topic - intentionally mystifying reports, obscuring the data and making it hard, for the report consumers to reach informed conclusions. I will also show how we can make the data visualisations as misleading as possible. This post is not as abstract as one may think, as it draws its examples from a very real project, for which I had to build a report under heavy pressure.

Initially, I was asked to build a report based on a small data set (~450 rows) stored in an Excel workbook. The data was perfectly suitable for building a Pivot Table on top of it, so I did so and then I decided to use the pivot table as a source for my report. The users have one measure - Spending Amount and a few dimensions - Category and Focus for that spending. The request came in the form: "We want to see the Amount by Category and Focus in both graphical and numeric form". So, I sat down and produced this prototype (with their colour theme):

As you can see from the screenshot, the report is fairly simple - the bar graphs on the top clearly show how the Amount is distributed per Category and Focus. Also, because of an explicit request, I build the bar graph on the second row to show category and focus amounts combined, and in order to clarify the whole picture, I added the table at the bottom of the report. The report works for colour-blind people too, as the details for the Focus per Category Expenditure bar graph are shown directly below in the data table.

I sent the report prototype for review, and the response was:

1. Remove the table.
2. Change the bar graphs to make them more "flat". Meaning: there is too much difference between the bars.

The reasoning - it is "too obvious" that the data is unevenly distributed. As the report is supposed to be presented to higher level management, discrepancies in the amount allocation would "look bad" on the people who requested me to create the report at the first place.

Adding more fake data got rejected, so I was advised to prepare a new prototype report with the new requirements. It follows:

Now, Stephen Few would spew if presented with such a report. I did everything I could to obscure the report - added 3D effects, presented the amount in 100% stacked graphs when absolutely not necessary and, of course, I had to add a Pie Chart. A 3D Pie Chart. The whole report is totally useless. It is impossible to deduct the actual numbers of the various category/focus amounts. Furthermore, the Pie Chart combines the focus and category and uses virtually indistinguishable colours for the different slices. The 3D effect distorts the proportions of these slices and if printed in Black and White, or if viewed by a colour-blind person, the report looks like this:

Since my goal of total mystification of the report was achieved, I sent the second prototype back.
The response was: "It looks much better, and we really like the top right bar graph and the Pie Chart. Would it be possible to leave just those two and change the Pie Chart to show data broken down just by Category?”

So, the users did not like my combined series. A point for them. Then I decided to remove the 3D cone graph, to remove all 3D effects, to make it more readable and to create the following 3rd prototype:

Here, we can see that the pie has the actual percentages displayed, and each category amount can be calculated from there. The stacked bar graph is again almost useless.

The users liked it, but still thought that it was too revealing, and were particularly concerned with the fact that there are a couple of "invisible" categories (the ones with small percentages) on the Pie Chart. They had a new suggestion - change the pie chart back to a bar graph and play with the format, so that even the smallest amount is visible. I offered an option, made another prototype and it finally got approved. The exact words were: "The graphs are exactly what we want". There it is:

The Y Axis in the first graph is interesting. Not only that there is a "scale break", but in fact the scale is totally useless, as it is manually adjusted, because of the big gaps between the amounts. I needed two scale breaks, which for a series with 6 values is a bit too much. You can compare the normal linear scale of the first Prototype I created and this one. However, it hit the goal - my users were satisfied.

I consider this effort to be contrary to be an exercise in "Data Mystification". It is very easy to draw the wrong conclusions from the report, and it achieves the opposite to the goals of Business Intelligence, as instead of empowering users, it could actually confuse them and lead them to making wrong decisions.

A new BI company

2009-09-22T13:09:00.010+10:00

Just a quick one - recently I learned that three former colleagues of mine had formed a new BI company in Australia - Naked Data. What makes it slightly different is that it is based on an open-source platform; and what makes this venture interesting for me is the people who are involved. All of them are exceptional professionals and I believe that if anyone can make it it is them.

I am curious!

On the search for the perfect OLAP browser

2009-09-22T11:54:00.012+10:00

Browsing Analysis Services cubes is typically done by power users in SQL Server Management Studio or Excel. However, because of a requirement stating that intranet and non-intranet users need to be able to access and browse an OLAP cube I had to go out there and try to find the best OLAP browser on the market. The client had been using Dundas OLAP Services with mixed success, and had purchased PerformancePoint with ProClarity but never used it because of infrastructure issues. That is why they used Dundas as an interim solution. Dundas’s OLAP Services is a user-friendly tool, but tis performance is not ideal when it comes to large grids, so my client wanted something combining Dundas’s usability with Excel’s performance, which could be used both internally and externally.

I contacted Microsoft with a question: “What tools can I use?” They came back to me with the following suggestions:

IntelliMax Solutions OLAP Browser (an Australian partner)
BI Companion
Panorama
Dundas
ProClarity

I added PerformancePoint to the list, as well as Radar-Soft’s OLAP Browser, so my final list was:

IntelliMax
BI Companion
Panorama
Dundas
ProClarity
PerformancePoint
Radar-Soft

IntelliMax Solutions

A sales guy told me that they will organise an online demo for me, and then if I am interested, they will organise an evaluation installation of their product on our servers. That just happened half an hour ago and it immediately became apparent that the product lack one critical piece of functionality - it does not support attribute hierarchies. So, I cannot use anything but user-defined custom hierarchies, because according to the support person, including attribute hierarchies make the grids and reports "too big". I, however, definitely need these big grids/reports. Otherwise, the tool is nice and simple with very obvious support for Custom Data - you can define this property from the UI, which makes it easier to work on non-Kerberos enabled environments. It also should integrate with PerformancePoint and Reporting Services, but I did not test those parts, because of the aforementioned problem with attribute hierarchies.

BI Companion

They were very friendly and responsive. I got an evaluation install, tried it out and it worked fine, apart from a small bug with non-aggregatable dimension hierarchies, which they fixed immediately. I was quite impressed with the product. It seemed to perform slightly better than Dundas, but it also was slightly less user-friendly. The interface imitates Cube Browser and is very feature-rich. Unfortunately, the HTML version did not work with Firefox, so they suggested using a SilverLight version, which was not supported by my client. As one of my requirements was cross-browser operability (IE and Firefox at least), BI Companion became a no-go-to for me.

Panorama

Panorama’s NovaView product competed with PerformancePoint as a dashboard creation tool and I have no idea why Microsoft recommended a competitor. I contacted their reseller in Australia and the sales person organised an online demo. I could play with their grid analytics components and it seemed OK. NovaView comes in two versions – a legacy Java version, as well as a new Flash version still in development. The Flash version is quite nice, but still harder to use than BI Companion or Dundas. As a big miss in the current version, Panorama NovaView does not let the user to add/remove dimension hierarchies from its grid columns. It can be done on rows, but not on columns, which is frustrating and ultimately a critical lack of functionality in my case. The Panorama support person told me that “they did not want to release all features yet” and that they have it going in their lab. He also advised me to use the Java version until the Flash version gets updated. I did and found out that the Java version is indeed fast, but not as fast as Excel and ProClarity. Furthermore, Panorama’s product is just too big for our needs and all of its administrative functionality, which includes user management, security and what-not, is just not necessary. Otherwise it looks nice.

Dundas

The Dundas OLAP Services OLAP grid is very usable – all the users need to do is drag and drop dimension hierarchies to browse the cubes they have access to. Unfortunately, it is a bit buggy and very slow when the cells, which need to be displayed, hit the > 10 000 range. After performance testing, we found out that about 50-80% of the processing time takes place on the client machines, where the browser renders a huge HTML table. It turns out that it is not only Dundas which has the same issues. Any HTML OLAP tool I tested suffers from exactly the same problem. This includes Dundas, BI Companion and Radar-Soft. Nothing we can do. Dundas supports paging, so the results can be split in a number of pages. It performs better if that feature is turned on, but requires hacking when exporting to Excel, because in order to export the whole grid, by default the user needs to export all the pages individually and then manually concatenate them in Excel. Since the HTML table problem cannot be rectified by Dundas or us, Dundas’s OLAP Services remain what they were intended to be – an interim solution.

ProClarity

Dated, but FAST. ProClarity still beats all other solutions with its performance. It was as fast as Excel and much, much faster than anything else. Although it lacks the apparent user-friendliness of some of the other solutions, it does allow users to conveniently browse OLAP cubes. Furthermore, it integrates well with PerformancePoint. Unfortunately, 6.2 is its last ever version. Whether its functionality gets implemented as a part of SharePoint 2010 is still to be announced by Microsoft. By the current way things look, ProClarity is still the best solution for ad-hoc OLAP browsing. It is stable and even though its installation is not quite smooth (e.g. requires IIS to be running gin 32bit mode), it does offer what most users would feel comfortable with. Its drawbacks are: tabbed interface, no drag-drop, and outdated graphics. Not perfect, but good enough. Oh, and it works with Firefox.

PerformancePoint

The Microsoft dashboarding tool offers an Analytics Grid part, which can be used for ad-hoc analysis. Unfortunately, it does not allow the users to add more dimension hierarchies on rows or columns. What is does well is drilling up or down the hierarchies and slicing by other hierarchies. Unfortunately this is hardly enough when it comes to full-featured ad-hoc reporting.

Radar-Soft

The small OLAP browser Radar-Soft is offering seems fast at a first glance. It would have been a nice small tool if it did not have one major problem – paging. It pages everything. It even creates a number of pages within cells. This improves performance but makes it impossible to use if we want to export a whole grid to Excel. I guess that some of the paging can be disabled, but I am quite sure that, since it is HTML, it will have the aforementioned issues with performance when it comes to displaying a relatively large HTML table.

Conclusion

The three HTML solutions - Radar-Soft, Dundas and BI Companion all had the same issue with performance. My guess is that there is no HTML solution which is faster, because in the end it comes to browser performance rather than MDX or .NET implementation.

Panorama with its Java and Flash versions is maybe a good solution for a full-featured dashboarding and reporting, but is a massive overkill for our circumstances. Also, it is not very user-friendly and not very fast.

PerformancePoint and ProClarity are my favourite and I believe that Microsoft’s offering provides the best capabilities for our users at least until something better comes from the Microsoft workshop. These integrate nicely with SharePoint and perform very well. While PerformancePoint is very user friendly, I would not say that for ProClarity. Hopefully in the future we will see nicer OLAP browsing by Microsoft based on these two tools.

A word about SilverLight and Excel

While SilverLight OLAP browsers are available by various software houses (most notable BI Companion has one in Beta), it is a relatively new technology and was not an option for me as it was not in my client’s SOE. Also, from my testing it does not seem like it is much faster than HTML, but definitely looks nicer.

Excel is, in my opinion, the best tool for browsing OLAP cubes inside the organisation. It is fast, and provides a rich set of analytics capabilities. There are a lot of discussions about Excel as a report authoring tool on various blogs, but I believe that for ad-hoc reporting there is nothing out there which beats Excel. Despite this, I could not recommend it to my client, as the solution is exposed to external users and letting them connect to our cubes directly though Excel is just impossible considering our security infrastructure. Also, Excel Services does not provide enough ad-hoc functionality, as it does not allow users to modify the Pivot Tables.

Wishes

I wish and hope that Microsoft releases a full-featured, user-friendly and performant OLAP browser as part of Visual Studio and .NET or as a part of SharePoint 2010. Such a tool is just a must and a serious miss in the Microsoft BI stack. The combination of Excel+PerformancePoint+ProClarity does the trick for now, but as ProClarity is getting discontinued, there must be something in the future toolset, which takes its place.

A few other bloggers wrote posts closely related to this topic:

Richard Lees - Which cube browser for Microsoft OLAP
Richard Lees - What's preventing Excel from being the ubiquitous cube browser
Chris Webb - Proclarity Migration Roadmap (or lack thereof)

Inferred Members Implementation Best Practise

2009-08-30T10:07:00.004+10:00

Just last week a colleague of mine and I did a very thorough research on the best implementation practises in regards to Inferred Members. We went through a few web sites and we also had a look at previous implementations through SSIS. There are quite a few resources on the subject and the best two methods we found were based on Project REAL and a SQL CAT recommendation. So, in the end we agreed that we can recommend three approaches, which have their advantages and disadvantages and could be all successfully used when we need to have Inferred Member (early arriving dimensions = late arriving facts) support in a solution.

Our findings can be summarised in the following table:

Method	Pros	Cons
Stored procedures	No need to use SSIS	Slow Complicated development Need to link source and target data server instances.
Script Component (SSIS)	Fast High reusability	.NET skills required
Double Lookup (SSIS)	Fast High reusability	Performance issues with multiple dimensions

I will go through each of these methods and provide some more details about them.

1. Stored Procedures

If SSIS data flow tasks are not an option for our ETL we can implement inferred member support through SQL code. The basic idea behind this method is:

Find new dimension members in the fact data and add them to the dimension
Move the fact data from source to target (i.e. staging to datamart) with surrogate keys from the dimension table

If we have our source and target data on separate instances of SQL Server it becomes quite inconvenient to utilise this approach. We need to link the two instances to use tables from both tables in the same stored procedure. This is a major issue and it is easily avoided by using SSIS.

2. SQL Server Integration Services

A general design of SSIS solutions can be represented with the following diagram:

We can implement this in the following ways:

2.1. Script Component (Project REAL)

The Data Flow task for this approach is:

After looking up missing states, we pass them to a Script Component, which hashes the misses and hits the database only when a genuinely new miss occurs. We have a stored procedure in place which simply adds the new dim member in the dimension table in the database and returns the new surrogate key, which then gets sent to the fact table.

Using .NET we can efficiently hash all values in a object collection and we can also handle both character and numerical data.

Because we are using a fully cached lookup, this data flow item is case-sensitive, therefore we should make sure we equalise the case in both fact and reference table data before we compare the two. We should also make sure that in this case the Script Component is case-insensitive, because if it is we will end up with multiple different rows in our dimension table for each case variation of our inferred members.

Additionally, the Script Component task should be built either accepting a parameter for the dimension name, or it can read its name (in our implementation) and find the first word in it to determine the dimension it is used for. In the above diagram, the Script Component task is handling the State dimension, therefore its name starts with State – . This makes the implementation of multiple Script Components for multiple dimensions very easy – all we need to do is change its name and it just works. There can also be some .NET code for auditing purposes. This is also fairly easy to implement and an entry level of .NET should be sufficient for development and maintenance. A sample of this approach can be found in Project REAL and it is thoroughly discussed in the SSIS paper produced with it.

2.2. Double Lookup (SQL CAT)

Here, we are doing the same as before – matching fact rows against a State dimension. If a mismatch is found in the first (fully cached) lookup, we pass the row to the second one. The New StateSK is a partially cached lookup. Its purpose is similar to a hash table – it caches new rows, and when there is a brand new mismatch it adds it to the database by executing a stored procedure. Then we Union All our inferred members with the rest of the fact data.

Because the second lookup task is utilising partial lookup, it is case-insensitive and case variations of the same character term will not lead to multiple dimension rows for the same member. However the first fully-cached lookup is case-sensitive, so we should make sure that both source and reference data is in the same case because that would be more efficient. Furthermore, In SQL Server 2005 partial caching must have a specified memory limit and if we have a large number of early arriving dimension members, we may run out of memory for them. In that case SQL Server 2005 will start discarding the least used values from its cache, which may have performance implications. The latter problem is overcome in SQL Server 2008.

Another problem with this approach is the use of Union All tasks. These are semi-blocking and may impact performance when used multiple times in our ETL package.

For much more detailed description, including samples you can go to:

SQL CAT - Assigning surrogate keys to early arriving facts using Integration Services

From our testing and research we reached the conclusion that using Script Component is the best approach, closely followed by Double Lookups. The stored procedure approach is slow, hard to maintain and may be impossible to implement in a production environment. Using SSIS with .NET proves to be efficient, convenient and fast.If avoiding .NET is preferable, handling inferred members in SSIS is a very good alternative.

Trouble with Employment Agencies

2009-08-24T12:03:00.005+10:00

I would normally not write about anything not Microsoft BI related, but in this case I will break my own rule because of an issue which concerns all developers who work on a contract for their employer.

Dealing with employment agents is something everyone dislikes because of their usually quite arrogant attitude. Since their job is to find us jobs and when we are independent contractors in some cases process our pay, I do not see a reason for their arrogance. We are purchasing services through them (paying them quite a lot for trivial tasks) and they should treat us with utmost respect.

In a recent case, which made me write this, a colleague and a friend of mine, who is well respected in the Microsoft BI community was treated in a disrespectful manner by Aurec. The case had to do with a clause in his contract stating that he cannot do work for his employer for 12 months after finishing his contract, unless he signs again through them. It is an usual BS clause, which would not pass through any court of justice in Australia because it limits our rights of free trade (or at least that's how I understand it). After his refusal to sign a restrictive contract, they refused to let him work for his employer. To make the matters worse, he had brought to them the contract at the first place and they had already strong-armed him (by threatening to increase their rates) into bringing his payroll to them too.

Since his employer had already given him the position through Aurec, it would be unfair if he goes through another agent for the same position (as far as the employer is concerned), so they are in a position to dictate their own terms, regardless of the fact that he is the one who found the contract, got approved by the employer, and most importantly - would do the job.
This whole matter enrages me as it prevents a really good expert from working for his chosen employer due to a profiteering agent, who should just shut up and do their insignificant bit in the whole matter.

In conclusion, I know that there are other agents like this out there, and please speak up if you have had similar experiences because the community needs to weed out the bad seeds. And one more thing - based on the described situation, my personal advice to you is never to sign anything through Aurec.

P.S. In case Aurec are interested in accusing me of defamation, I can provide full evidence (emails) and a witness, proving that the above article truthfully represents their actions and the case I am describing.

Custom Rounding and Truncation of Numbers in MDX

2009-08-02T15:53:00.006+10:00

Article published in SQL Server Central on 2009/03/26

In some scenarios we need to be able to round or truncate decimals to achieve correct calculation results. In SQL we have ROUND, which can do either of these. It rounds like we are used to - 0.5 rounds up to 1, can round up or down and we rarely get a project where as a part of the requirements we are implementing our own rounding or truncation algorithm.

However, in MDX we have Round() which performs a "strange" operation - bankers' rounding, which our business users have usually not been exposed to, and if we decide to truncate to an integer number through casting with Int or cInt, we also get some strange results. To illustrate the problem with MDX please consider the value of these expressions:

Round(2.15, 1) = 2.2
Round(2.25, 1) = 2.2
Round(2.35, 1) = 2.4
Round(2.45, 1) = 2.4

Int(1.9) = 1
Int(-1.9) = -2
cInt(1.9) = 1
cInt(-1.9)= -2

These are usually considered wrong, because they are not the obvious results. Even though they are mathematically well founded, if we round 2.25 to 2.2, our users will come back at us with wrong numbers on their reports. Same goes for "trimming" -1.9 to -2.

To resolve the first problem with rounding, we can use our own math formula:

Fix([Measures].[???] * Factor + 0.5 * Sgn([Measures].[???])) / Factor

Where Factor is the rounding factor - 1 for 0 decimal places, 10 for 1 and so on (defined by 1/Factor). Of course, Factor of 0 will give us Div by 0 error. (Reference: http://support.microsoft.com/kb/196652)

If we have the Excel function libraries intalled on our server, we can also simply use Excel!Round() as Chris Webb advises.

As for the Int and cInt in MDX, we can use the Fix() VBA function to remove decimal places:

Fix(1.9) = 1
Fix(-1.9) = -1

Also, for truncation of values to a certain decimal point in MDX, we can use the following formula:

Fix(<value>*10^1)/10^1

All we need to adjust in order to change the decimal places is to replace 10^1 with another power of 10 - in example, to truncate 3.156 to 3.15 we can use: Fix(3.156*10^2)/10^2. To make things simpler, in all our formulas the power of 10 is what determines how many decimal paces we need to round to; negative powers will give us rounding to tens, thousands and so on. If we use ^0 we will round to whole numbers.

Using these we can avoid bankers' rounding and some strange results with converting to integers in MDX.

Developing reports for colour-blind people

2009-07-02T13:58:00.003+10:00

According to Wikipedia 8% of all males and 0.4% of all females in Australia are colour-blind to some degree. The percentages are slightly different in the USA – 7% for males and 0.4% for females. It is estimated that these would be similar to other countries in the world, which means that a very high percentage of people may have difficulties distinguishing colours. Therefore, a significantly large part of our potential report users may not be able to interpret some of our graphs and charts correctly (There would be around 400 colour-blind males and 20 colour-blind females in an organisation which employs 10000 people).

In example, the following chart is quite nice and simple but can be useless to colour-blind people:

versus:

Similarly, this table is somewhat confusing if we remove the colours:

versus:

We have to be very careful with design of KPIs and dashboards in general:

versus:

As we can clearly see from the above examples, not being able to clearly distinguish colours makes our poorly designed charts and tables confusing.

We should always keep in mind the following considerations when designing and implementing our most-common report items to ensure that they can be used by everyone in our client organisation:

KPI indicators must be different in shape rather than just colour
Line charts should utilise markers with different shapes
Bar graphs should include a marker on the top of each bar
Avoid colour-coded pie-charts – they can be extremely difficult to read for a person with even the slightest colour-blindness condition
Avoid colour-coding tables – either backgrounds or text colours are usually unacceptable

Other more general suggestions:

Shapes are much more important than colours
Greyscale and shades of the same colour are acceptable, as a colour-blind person can distinguish between those

Of course, even after all our efforts to create reports readable by everyone, we may miss some detail. The best way to ensure that we have done a good job is to test. There are two good and easy ways to do that:

Printing – print the report in black and white and see if all information is well presented
Changing display to greyscale – Windows lets us choose how many colours we want to display on our screen. Choosing greyscale and then playing with our report is possibly the best way to ensure that colour-blind people can use our reports.

It is fairly simple and easy to always apply these design principles when creating reports. I have found that most organisations are quite happy to include minor tweaks to their dashboards and reports when they understand how important they could be for some of their employees. Furthermore, it helps to promote accessibility to technology regardless of minor disabilities and gender.

Reporting Services Styles in a Database Table

2009-05-19T12:16:00.000+10:00

Article published in SQL Server Central on 2009/03/26

As it was previously discussed by Adam Aspin in his series Add Styles to Your Reportng Services Reports on SQLServerCentral, it is very useful to be able to change various report attributes, such as colours and borders properties, in a centralised way just like by using CSS files or Skins. Currently, Reporitng Services does not allow us to use a similar approach, so Adam showed us how to apply a "style" to a report by using custom code in Reporting Services - either by embedding the code for all attriibutes directly in the report, or by referencing an external source. However, there is another way to handle such attributes and that is by storing them in a database table. There are certain advantages in doing so, but there could be some disadvantages, as well.

I will proceed to show how to build a report utilising this apporach and then I will discuss its strengths and weaknesses.

Database Table

I would suggest to keep your database tables stroing values such as report attributes, Reporting Services environment settings (for using dynamically generated javascript) and other dynamic report properties separated by naming them in a unified way. I have used the CTL (for control) prefix in the past. For illustrative purposes I will provide a simple definition of a database table called CTLReportColours:

CTLReportColours (
HeaderColour nvarchar(10),
FooterColour nvarchar(10),
BodyTextColour nvarchar(10)
)

As actual values we can store either the Reporting Services colour name (WhiteSmoke, SteelBlue, etc), or the colour codes (#00AA00, #CCBB66, etc). I am mentioning this because I have had issues with printers in the past, which recognise Red as Pink and changing to hex colour codes fixed the problem. This should be the topic for another article, so I will drop the subject here.

Stored Procedure

We can either retrieve the values from the database table in a sotred procedure we are already using on our report, or we can create a new one, specifically built for getting report attributes. We can use the first approach if our report uses one data set only. Then we can assign all report items to that data set and simply use the Fields collection items in our expressions. However, if we have multiple data sets assigned to different report items, such as tables(ixes), we may find that we need to code the retrieval of our report attributes in many many stored procedures. To overcome this limitation, we can ceate a spearate stored procedure, which returns the set of values stored in the database table. A simple example is code like:

CREATE usp_CTL_Get_Report_Colours
AS
BEGIN
SELECT HeaderColour
, FooterColour
, BodyTextColour
FROM CTLReportColours
END

Data Set

In Reporting Services we can use usp_CTL_Get_Report_Colours to construct a data set, which will have three fields named like the column names.

Usage

To use a separate data set means that we need to be able to use its fields in some tables which already have a data set assigned to them. Since we can have only one data set per report element, we need to create separate parameters for each of the fileds in the attributes data set we have created. In our case, we need to create three parameters: HeaderColour, FooterColour and BodyTextColour. All of these should be hidden and should be populated by our data set in the outermost report only - if we have subreports, they should get their values from the parent report, so we avoid multiple execution of the usp_CTL_Get_Report_Colours stored procedure.

Conclusion

The described method for handling report attributes can be preferable because:

· We do not need to know anything about coding to create dynamic reports

· Reports are dynamic with no need to re-deploy them

· Report attributes are in a database table, which makes their management easy

· Various management screens can be built on top of the database table in .NET with relative ease

· Retireving the values through a stored procedures allows us to pass back parameters, which then can drive what colours get displayed - making the retrieval flexible if we need to display different colours for different departments or people

· Centralised and secured storage to all report attributes - environment settings, styles, etc.

The drawback is one as I see it: having to create multiple report parameters in all our reports. In my opinion it is a much smaller issue than having to manually change report properties with every change in the requirements and overall it is well worth the effort.

Enhanced Security and Integration of Microsoft BI Solutions with Kerberos

2009-04-04T15:51:00.015+11:00

The following is an overview of an article prepared by Mark Dasco and myself. The full article is around 4800 words and is attached to the end of this post. Basically, the article is a quite thorough description of how to implement Kerberos and the benefits Kerberos provides when implementing Microsoft BI solutions.

Table of Contents

1 Overview
2 The Double Hop
3 The NTLM Protocol
4 The Kerberos Protocol
5 Business Intelligence Case
6 Implementation
6.1 Considerations
6.2 Implementation
6.2.1 Getting Started
6.2.2 Configure Clients for Kerberos Authentication
6.2.3 Defining SPNs
6.2.4 Using Negotiation
6.2.5 Enable Impersonation and Delegation
6.3 Checking that it all works
7 Conclusion

Overview

When developing Microsoft Business Intelligence solutions we frequently need to rely on tight security integration between various tools. The NTLM protocol provides enough features for simple implementations, but when we need to provide enterprise-class solutions we invariably feel constrained by it. With Windows 2000 and later versions Microsoft provides an alternative security protocol - Kerberos, which addresses certain limitations of NTLM, provides improved security and better performance. The implementation of Kerberos could be fairly simple or very complex depending on the requirements. Configuring a few default server instances with no constraints on their services secured through Kerberos could be almost trivial, while for some more specific cases it could be a major cause of frustration.

As a base for this study we will examine a specific BI case – a digital dashboard, which involves all layers of the Microsoft BI stack:

SQL Server 2005
SQL Server Analysis Services
SQL Server Reporting Services
PerformancePoint 2007 Monitoring and Analytics for building a dashboard
SharePoint Server as a organisational portal hosting the dashboard

Furthermore, each of the servers exists on two environments – Development and UAT. Also, we will show how we can implement Kerberos only between the services utilised by the servers, not affecting the rest of the domain and effectively isolating the implementation.

Typically, solutions not configured for Kerberos authentication and delegation fall back to the default NTLM authentication protocol. Whilst NTLM is completely transparent and very easy to use on a Windows domain, it falls short when we need to pass user credentials across a few server layers. This is commonly known as a double hop issue. If we depend solely on NTLM for user authentication, passing user names to servers on lower levels of our server topology involves including them in our connection strings or passing them programmatically, which is hardly the right choice for an enterprise-grade security framework.

On the other hand solutions which correctly implement Kerberos are advantaged with cross-server delegation and authentication, thus allowing the use of Integrated Windows Authentication throughout the whole solution. The ability to capture user credentials on any server is essential if we want to be able to secure and control access to each server independently and minimise the damage resulting from a potential security breach.

Download Full Article by Mark Dasco and Boyan Penev

Attribute Relationships in Analysis Services - RIGHT vs WRONG

2009-02-28T15:13:00.003+11:00

Just a quick one - I just saw again that someone has built his attribute relationships wrong. And that is in SSAS 2008 even after there is a nice graphical interface to it. I believe it is wrong by design. To illustrate:

The dimension:

Current version (WRONG):

Desirable version (RIGHT):

It is not hard - you have:

One-to-Many Chart Names for a Version,
One-to-Many Ledger Names for a Chart Name, and
One-to-Many Finance Structures per Ledger Name.

They form a hierarchy, which has levels.

Each Finance Structure relates to a Ledger Name,
each Ledger Name relates to a Chart Name, and
each Chart Name relates to a Version.

Having the correct set-up allows Analysis Services to create aggregations correctly, so the cube performance is optimised and MDX queries run quicker. Also, if the aggregations are wrong we can sometimes get wrong results for our queries...

Combining Slowly Changing Dimensions and Current Dimension Versions

2009-02-24T12:49:00.015+11:00

When we need to see historical changes of a dimension in our OLAP cube the common practice is to implement it as a SCD - or a Slowly Changing Dimension. There are a few ways to do this and a really good definition of the different types of SCDs can be found in Wikipedia: Slowly Changing Dimension. Also, there are quite a few articles on Implementing SCD ETLs in SSIS, two of which are:

SCD Wizard Demo - SSIS Junkie blog example of a package using the Slowly Changing Dimension transformation in SSIS
MSDN Article on the Slowly Changing Dimension transformation in SSIS

Since SQL Server Integration Services 2005 and 2008 include a SCD transformation it is not too hard to implement such dimensions.

Here I am discussing a typical requirement - to be able to have a SCD and a Current version of the dimension.

First, it is important to notice that a SCD should have two dimension keys: a unique surrogate key identifying every version of the dimension members and a non-unique code, which is common for all versions for a dimension member. This is also very important if we want to be able to determine the current version of a dimension member. An example of a very simple dimension table utilising this design is:

Here we have two distinct dimension members with Code of 1 and 2. Member1 has two versions and Member2 has three. The SKeys (surrogate keys) for these versions are unique but the codes stay the same for each member. Also, notice the From and To dates which allow us to distinguish the periods for the member versions. We can have an IsActive or IsCurrent bit column, which shows us the latest version of a node, but we can also just filter on dates which are 9999-12-31, which will give us the same result.

Assuming the described design I will move on to discuss the ways to build a dimension in SSAS.

First, the standard way to link the dimension table to our fact table is through the surrogate key. We can have a regular relationship between the two tables. As the fact data is usually also linked to a Time dimension, fact records linked against the periods between the From and To dates of our SCD will be linked to that versions SKey. An example of a fact table with a few rows, which can be linked to the dimension table above is:

The row with a FactKey of 1 will be linked against Member1Ver1, while FactKey 2 will go against Member1Ver2. Therefore, when we slice our cube by Time and our dimension we will see:

This is the standard way to implement our SCD and these are the results we would expect. Now, we get a new requirement. We want to be able to see both this and an aggregation against the current version of our dimension. We have a few ways to implement it. One obvious way is to create another dimension containing only the current dimension members. This can be easily achieved if we add a Named Query in our DSV, which shows only the current dimension members:

SELECT SKey
, Code
, Description
FROM DimTable
WHERE ToDate = '9999-12-31'

The result will be:

Then we need to replace our fact table with a Named Query, which shows the DimSKeys for current version dimension members:

SELECT ft.FactSkey
, dt_current.DimSKey
, ft.TimeKey
, ft.Amount
FROM FactTable ft
INNER JOIN DimTable dt
ON ft.DimSKey = dt.SKey
INNER JOIN DimTable dt_current
ON dt.Code = dt_current.Code
WHERE dt_current.ToDate = '9999-12-31'

This will give us the following result:

When we slice our cube, all records for Member1 will be against the latest version:

Implementing this, we can have two dimensions in our cube, so our users can use the one that makes more sense for their needs:

Dimension and
Dimension (Historical), and the Historical designation stands for, in technical terms, a SCD

However, we can also implement this in a different way, which allows us to avoid building such logic in a view or our DSV. The trade-off is some space on our disks and one more column in our fact table. Instead of adding a new column through writing SQL, we can simply add the dimension Code in the fact table. Then, we can build our dimension again by getting the latest versions, but instead of having the SKey as a dimension key, we can use the Code. It is of course unique across all dimension members, as long as we filter our the non-current versions. The query for doing this is exactly the same as the one we used before. However, we need to change our fact table design and add a DimCode column:

Then, we create two dimensions again, but we link the Historical dimension with the DimSKey column and the Current one with the DimCode column. The result of slicing the cube by the current version is exactly the same as before. The trade-off is space vs. processing time and CPU usage. It is up to the developer to choose the more appropriate way to build the solution.

So far I discussed two ways of having our SCD and Current Version dimension in different dimensions in our cubes. There is, however a way to combine both in the same dimension. To do this, we need to have two levels in the dimension: a parent level, which contains the current version of the dimension members, and a child level, which contains the historical versions. In example:

Member1Ver2
Member1Ver1
Member1Ver2
Member2Ver3
Member2Ver1
Member2Ver2
Member2Ver3

This way the historical versions aggregate up to the current version and we can use either level, depending on what we want to achieve. To build this, we can use our current dimension table and add a parent level through SQL. This way, we do not need to update all records when a new version comes:

SELECT dt.SKey
, dt.Code
, dt.Description
, dt_p.SKey AS ParentSKey
FROM DimTable dt
INNER JOIN DimTable dt_p
ON dt.Code = dt_p.Code
WHERE dt_p.ToDate = '9999-12-31'

The result is:

Then, we can build our Parent-Child dimension and we can use the Parent level is we want to have current versions and the Child level for the historical ones.

This approach allows us to combine the two dimensions into one. It is also possible to implement it in a non-parent child fashion because the hierarchy is not ragged.

It is always advisable to make sure we actually need a SCD and avoid it whenever possible because it is not always intuitive for users to use one. Splitting our fact data on multiple rows can be surprising for users and understanding how the historical dimension works and the multiple nodes it consists of can be a problem. However, it lets us satisfy a common requirement and therefore it is quite important to know how to build.

Improving performance and maintainability by keeping logic out of our reports

2009-02-14T23:00:00.002+11:00

As Reporting Services allows us to implement some quite complex logic, report authors are often tempted to build a lot of it in their reports. Having some simple calculations there does not generally cause harm, as simple + and – operations specific to a report should probably be built in it. However, I have seen aggregations, complex queries and code hacks, which would normally be performed fairly easily in Analysis Services or SQL Server built in Reporting Services.

There are a few distinct benefits we can get from keeping logic out of our reports.

Firstly, by having our MDX scripts and SQL Queries (stored procedures) in the back-end allows us to use them in different reports with no need to maintain them by painfully synchronizing them with every change. Also, this allows business MDX-ignorant users to use complex measures, increasing the value of the BI solution for them.
Then, normally, our database server is usually maintained by people who have skills in SQL or MDX, and can analyse and optimise the scripts there instead of relying on report authors to do so. Even if we have BI professionals maintaining the whole solutions, it is far more convenient to have most of the logic stored on the same server. Whether it is a performance bottleneck, or a bug, it is far easier to fix it by tracing either cube calculations or stored procedures than logic stored in a report.
Sending a large amount of data between the back-end server and SSRS is increasing the stress on the network, which can be critical when multiple requests with millions of rows in results are passed to it.
Caching is important when different reports use the same stored procedures or MDX calculations.
And a last point: a database/OLAP server is just faster in calculations that SSRS.

In general, using stored procedures as SQL Server data sources instead of building queries in SSRS; or alternatively, keeping MDX in calculations in the OLAP cubes is the first step towards avoiding excessive code accumulation in SSRS. Then, avoiding anything more complex than simple sums and groupings in the reports and moving them to the stored procedures or MDX calculations is another desirable way to structure our solutions. Finally, even simple things like Actual – Budget variance measure should be built in MDX or SQL rather than in the reports. Following these simple rules and keeping ourselves disciplined when building reporting solutions improves their performance, maintainability and even usability.