PASS Workshop “Creating a BI solution from A to Z” - Interview

Davide Mauri — Thu, 16 Sep 2010 22:16:27 GMT

If you’re still unsure if the workshop I’ll deliver at PASS Summit the next November - “Creating a BI solution from A to Z” - is a good choice for you or not, you can get some more details reading the brief interview here:

PASS Summit Post-Con Preview - Davide Mauri

Where you’ll find answers to the following questions:

Is there an audience that would benefit especially from this session?
After having attended your seminar, what are two or three things that an attendee will be able to take back to the office and put to use right away?
What background should attendees ideally have to be fully prepared for your seminar?
What experience are you, as a speaker, bringing to this session?

That may help you to take your final decision. More than 20 people already decided to come: you’ll be in good company! :)

Reporting Services, MDX and the (unwanted) cascading parameters

Davide Mauri — Tue, 10 Aug 2010 19:21:21 GMT

Reporting Services can be used in conjunction with Analysis Services, creating powerful reporting solution that can satisfy almost any customer needs for “standard” reporting. For “standard” I mean solutions that has not be created using Excel and a Cube to give the user complete freedom (and responsibility) over data.

In one of our current project a customer has the need to allow all subsidiaries around Italy to access a report through their web-based extranet. As a consequence the report must be accessible via web and needs to be exported in Excel and PDF (plus several other constraints that are not interesting for the discussion so I’ll omit them).

Reporting Services is surely the ideal solution for these requests. The data is stored in a Analysis Service 2008 Cube. Easy. The Report will have a little bit more that 10 parameters. Again, easy.

Well…it SHOULD be easy. And it will be, at least ‘till the point where you start to struggle with MDX and parameters. It’s a common knowledge that Reporting Services can work quite well, though with several limitations, with Analysis Services: Report Parameters can take their value from an MDX query, and the values can be passed back to another MDX query in order to get the correct data for the report.

Unfortunately, by default (tested on SQL Server 2008) all Report Parameters that uses MDX and support Single & Multi-Value selection, will act as a “cascading” parameters, which means when you run the report, all parameters except first will be disabled:

You have to select the value for the first parameter and than the report will load the value for the second. Then you select the value for the second and the third will get enabled…and so on.

Of course, with several parameters this can be quite frustrating. This situation also make harder the development of a custom web page that has to purpose to make more user-friendly the selection of all the values when you have a high number of parameters.

So, how to solve this problem? First, it’s imperative to understand why parameters become “cascaded”. If you have created the MDX query using the Query Editor, you also have specified what Dimension Attributes (or Hierarchies) has to be turned into parameters, simply flagging the related checkbox:

Behind the scenes this will create hidden dataset used be the automatically created Report Parameters

All the created dataset, with the exception of the one used by the first Report Parameter, have an explicit reference to others parameters. In particular the MDX slicer look like the following:

WITH

[…]

SELECT

[…]

FROM
(
    SELECT
        ( STRTOSET(@ProductProductCategories, CONSTRAINED) ) ON COLUMNS
    FROM
    (
        SELECT
            ( STRTOSET(@CustomerCustomerGeography, CONSTRAINED) ) ON COLUMNS
        FROM
            [Adventure Works]
    )
)

This can lead to think that simply removing these references (which, in my experience, are not useful anyway), you can solve your problem. If you do so, just remember also to remove the associated Dataset Parameter. Anyway – and unfortunately – it’s not so easy.

In fact, the culprit of the problem is in the indentation of the parameter caption:

The hierarchical look is generated by a calculated field in the parameter’s dataset:

As one can guess, the field ParameterCaptionIndented is the one that is used in the Parameter, to make it look like a hierarchy.

The Expression of that field is very simple:

=Space(3*Fields!ParameterLevel.Value) + Fields!ParameterCaption.Value

but this is enough to make the parameter “cascaded”.

If you remove this, you won’t have any look-like hierarchy parameter, but you’ll also get rid of the “cascade” behavior in the Report Parameters.

Now the we have solved the problem, we have to figure out how to have the hierarchy again, which is very comfortable from a user perspective. We can’t do it Reporting Services, so we have to move on Analysis Services. MDX doesn’t offer any kind of advanced string manipulation function, so we have to use .NET to create a MDX User Defined Function that can be called within our MDX Query so that the field ParameterCaptionIndentation will be generated correctly directly by Analysis Services, without requiring us to create a Calculated Field anymore.

Said and done:

using System;
using Microsoft.AnalysisServices.AdomdServer;

namespace SolidQ.AnalysisServices.Utils.Functions
{
    public class StringHelper
    {
        public string IndentString(string source, int indentationLevel, int indentationSize)
        {
            string indentation = new string(' ', indentationLevel * indentationSize);
            return indentation + source;
        }

        public string IndentString(string source, int indentationLevel)
        {
            return IndentString(source, indentationLevel, 4);
        }
    }
}

Very simple but very useful! After having compiled the code and loaded the generated Assembly into Analysis Services

the function can be used directly in the MDX query. The following code is the one used by Customer Geography Report Parameter:

WITH
MEMBER [Measures].[ParameterCaption] AS
    [Customer].[Customer Geography].CURRENTMEMBER.MEMBER_CAPTION
MEMBER [Measures].[ParameterCaptionIndented] AS
   SolidQUtils.IndentString(
        [Customer].[Customer Geography].CURRENTMEMBER.MEMBER_CAPTION,
        [Customer].[Customer Geography].CURRENTMEMBER.LEVEL.ORDINAL,
        3)
MEMBER [Measures].[ParameterValue] AS
    [Customer].[Customer Geography].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterLevel] AS
    [Customer].[Customer Geography].CURRENTMEMBER.LEVEL.ORDINAL
SELECT
    {
        [Measures].[ParameterCaption],
        [Measures].[ParameterCaptionIndented],
        [Measures].[ParameterValue],
        [Measures].[ParameterLevel]
    } ON COLUMNS,
     [Customer].[Customer Geography].ALLMEMBERS ON ROWS
FROM
    [Adventure Works]

The highlighted section shows that the ParameterCaptionIndeted member simply call the IndentString function that takes 3 parameters:

String to be indented
Indent Level
Indent Size (Optional. If omitted indentation size is 4 spaces)

Using the same approach for all the Report Parameters, we can finally solve the “cascaded” problem!

With many thanks from users and developers!

PASS Summit 2010 – BI Workshop

Davide Mauri — Wed, 30 Jun 2010 12:01:10 GMT

PASS Summit 2010 Pre & Post conference are out! This year I’ll deliver the “Creating BI Solution from A to Z” seminar in which, as the title implies, attendees will see how to create a BI solution starting from scratch. Going through the dimensional modeling and the creation of the Datawarehouse, the implementation of the ETL process with SSIS, the creation of cube with Analysis Services and reports with Reporting Services with, if time permits, also a glance at PowerPivot, attendees will get a solid ground on the whole process that drives the creation of a BI solution.

The workshop will also show all the best practices and the best methodological approach matured in more than 5 years of working in the BI field.

If you’ll be working on BI or you already have a created an initial BI solution and you want to be sure you’re following the right path, this is workshop is for you!

Don’t miss it!

An OLAP client!

Davide Mauri — Sun, 28 Mar 2010 18:30:55 GMT

While surfing CodePlex I’ve come across a very interesting tool for all BI Developers who misses a decent OLAP client where to write, run & test MDX queries

http://ranetuilibraryolap.codeplex.com/

I’ve not tested it yet, but I’ll surely do this week and I’ll post my impressions ASAP.

The first impression, just looking the CodePlex page, is that tool Rocks!!!!!

Davide Mauri : MDX

PASS Workshop “Creating a BI solution from A to Z” - Interview

Reporting Services, MDX and the (unwanted) cascading parameters

PASS Summit 2010 – BI Workshop

An OLAP client!