Last week I had to build some PPS Dashboards in an existing SharePoint 2010 environment.

One of the requirements for the dashboards was to support English, Dutch and French. Because the data was loaded from an Analysis Services Cube, I added the extra required translations to the Analysis Services Project: Dutch-Belgium and French-Belgium.

The SharePoint site was already configured to support the 3 display languages:

The Analysis Services Translations were working correctly in every other environment but for some strange reason not in the PPS Components (Analytic Grids, Charts, …)

A very smart guy at Microsoft (Thanks, Wesley ) gave me the tip to use the profiler to have a look at the MDX that PPS sends back to Analysis Services. In the details of the “Query Begin” event I found this MDX:

<PropertyList xmlns=”urn:schemas-microsoft-com:xml-analysis”>
<DbpropMsmdMDXCompatibility>2</DbpropMsmdMDXCompatibility>
<Catalog>Titanium</Catalog>
<LocaleIdentifier>1036</LocaleIdentifier>
<Format>Multidimensional</Format>
<AxisFormat>TupleFormat</AxisFormat>
<Content>SchemaData</Content>
<Timeout>300</Timeout>
</PropertyList>

Okay, it looks like PPS sends the requested locale identifier to Analysis Services. But 1036 is the language code for France and I am in Belgium:

• French – France: 1036
• French – Belgium: 2060
• (More language codes: http://msdn.microsoft.com/en-us/goglobal/bb964664)

But why is Performance Point using the wrong language code? The answer is actually very simple: Because of the installed language packs for SharePoint.

When you have a look at the available language packs for SharePoint (http://technet.microsoft.com/en-us/library/ff463597.aspx), you will see that there is only 1 French language pack, with language ID 1036.

So I solved the problem by adding yet another translation to the Analysis Services Project: French-France.

Enjoy the translations!

At a current project I needed to create a deploy script for the MDX calculations of a cube.

SQL Server Management Studio makes it very easy for you to create XMLA-scripts to create or alter cubes, dimensions, etc… But it’s not possible to use Management Studio to create an alter-script that only contains the MDX calculations. Because I needed to resolve a bug in the calculations but the rest of the cube was not ready to be deployed to production yet, I had to create an alter script just for the MDX.

Of course I could have started learning the XMLA-syntax to write a script for this, but I decided to go for the easy way: the BIDSHelper has an option to deploy the MDX to a cube without having to deploy the entire cube.

If you don’t know BIDSHelper, or don’t already have it installed, go to http://www.codeplex.com/bidshelper. This is a must have add-in for all SSAS and SSIS developers!

But as I was allowed to use Management Studio to makes changes to the cubes in the development environment only. So I needed to capture the XMLA script the BIDSHelper executes on the cube, to deploy the same script to the production environment.

That what the profiler is for, not? J

Open the SQL Server Profiler, create a new trace and connect to the analysis services instance. Use a blank template and switch to the tab “Events Selection”. The only event we want to capture here is “Command Begin”.

Click Run.

Now the profiler is ready to capture the XMLA-script, so switch back to Management Studio and use the BIDSHelper to deploy the MDX calculations. When the deploy is finished you can stop the trace.

The profiler should have captured 2 events. The event with subclass “Alter” is the one we need.

Copy the script from the profiler to a new XMLA script in Management Studio.

The last 3 lines (PropertyList) can be deleted.

In the node “ObjectDefinition” you will find your script.

Enjoy!

At a current project I needed to create a deploy script for the MDX calculations of a cube.

SQL Server Management Studio makes it very easy for you to create XMLA-scripts to create or alter cubes, dimensions, etc… But it’s not possible to use Management Studio to create an alter-script that only contains the MDX calculations. Because I needed to resolve a bug in the calculations but the rest of the cube was not ready to be deployed to production yet, I had to create an alter script just for the MDX.

Of course I could have started learning the XMLA-syntax to write a script for this, but I decided to go for the easy way: the BIDSHelper has an option to deploy the MDX to a cube without having to deploy the entire cube.

If you don’t know BIDSHelper, or don’t already have it installed, go to http://www.codeplex.com/bidshelper. This is a must have add-in for all SSAS and SSIS developers!

But as I was allowed to use Management Studio to makes changes to the cubes in the development environment only. So I needed to capture the XMLA script the BIDSHelper executes on the cube, to deploy the same script to the production environment.

That what the profiler is for, not? J

Open the SQL Server Profiler, create a new trace and connect to the analysis services instance. Use a blank template and switch to the tab “Events Selection”. The only event we want to capture here is “Command Begin”.

Click Run.

Now the profiler is ready to capture the XMLA-script, so switch back to Management Studio and use the BIDSHelper to deploy the MDX calculations. When the deploy is finished you can stop the trace.

The profiler should have captured 2 events. The event with subclass “Alter” is the one we need.

Copy the script from the profiler to a new XMLA script in Management Studio.

The last 3 lines (PropertyList) can be deleted.

In the node “ObjectDefinition” you will find your script.

Enjoy!

Reporting Services 2008 is a great reporting tool, but displaying Analysis Services data with Reporting Services doesn’t always display the data as you would expect.

A Parent-child hierarchy is one example of this: When you browse Parent-child hierarchy from an Analysis Services cube with Excel, Excel automatically displays it with nice drill-down functionality.

But when you add it to a Reporting Services report, you get something like this:

That’s not how we like to display a Parent-Child hierarchy to our users!

To display it with the expected drill-down you need to do some extra configuration:

The following example uses the AdventureWorks example cube available on CodePlex:

Create a new report and add a dataset with the following MDX query:

SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS,

NON EMPTY { (DESCENDANTS([Organization].[Organizations].[Organization Level 01].ALLMEMBERS) ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME,

LEVEL_NUMBER ON ROWS

FROM [Adventure Works]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,

FONT_NAME, FONT_SIZE, FONT_FLAGS

Add a Tablix to your report and drag the parent-child hierarchy “Organizations” en the fact “Amount” to the Detail Row of the Tablix.

To enable drill-down you need to edit the Group Properties of the Row Group:

Configure the group to iterate on the UniqueName of “Organizations”:

Go to the Advanced options and set the property “Recursive Parent” to the ParentUniqueName of “Organizations”.

Change the visibility of this Group to “Hide” and check the option “Display can be toggled by this report item”. The toggle item should be the Textbox containing the Parent-Child hierarchy.

Now optimize the layout by increasing the indent for the lower levels of the hierarchy:

In the Textbox properties: set the following expression for “Padding – Left”:

=Cstr(Fields!Organizations.LevelNumber * 10) & “pt”

Click Preview to test the report.

Look’s way better isn’t it?

Reporting Services 2008 is a great reporting tool, but displaying Analysis Services data with Reporting Services doesn’t always display the data as you would expect.

A Parent-child hierarchy is one example of this: When you browse Parent-child hierarchy from an Analysis Services cube with Excel, Excel automatically displays it with nice drill-down functionality.

But when you add it to a Reporting Services report, you get something like this:

That’s not how we like to display a Parent-Child hierarchy to our users!

To display it with the expected drill-down you need to do some extra configuration:

The following example uses the AdventureWorks example cube available on CodePlex:

Create a new report and add a dataset with the following MDX query:

SELECT NON EMPTY { [Measures].[Amount] } ON COLUMNS,

NON EMPTY { (DESCENDANTS([Organization].[Organizations].[Organization Level 01].ALLMEMBERS) ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME, PARENT_UNIQUE_NAME,

LEVEL_NUMBER ON ROWS

FROM [Adventure Works]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING,

FONT_NAME, FONT_SIZE, FONT_FLAGS

Add a Tablix to your report and drag the parent-child hierarchy “Organizations” en the fact “Amount” to the Detail Row of the Tablix.

To enable drill-down you need to edit the Group Properties of the Row Group:

Configure the group to iterate on the UniqueName of “Organizations”:

Go to the Advanced options and set the property “Recursive Parent” to the ParentUniqueName of “Organizations”.

Change the visibility of this Group to “Hide” and check the option “Display can be toggled by this report item”. The toggle item should be the Textbox containing the Parent-Child hierarchy.

Now optimize the layout by increasing the indent for the lower levels of the hierarchy:

In the Textbox properties: set the following expression for “Padding – Left”:

=Cstr(Fields!Organizations.LevelNumber * 10) & “pt”

Click Preview to test the report.

Look’s way better isn’t it?

I recently struggled with the following challenge in Analysis Services:
In one of the fact tables there was a field duration which actually was a duration of a certain status in seconds. I needed to display this in a readable format, like “2 days 22:15:59″.

bing brought me to the following great article, where Mosha explains how you can use MDX to create a custom format:
http://sqlblog.com/blogs/mosha/archive/2008/09/26/displaying-duration-values-mdx-expressions-in-format-string.aspx

When you apply this for a calculated member you get something like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDays]
     AS [DurationInDays],
    FORMAT_STRING =  
        IIF([Measures].[Duration] < 1
         ,’”0 days” hh:mm:ss’
         ,’”‘ + cstr(int([Measures].[Duration])) + ‘ days” hh:mm:ss’)
    ,
    VISIBLE = 1;

But this brings us to another problem:
This custom format will only display the correct result if [Measures].[Duration] contains decimal seconds.
For more information about the conversion from seconds to decimal seconds, read the following article on wikipedia: http://en.wikipedia.org/wiki/Decimal_time
In short, you need to divide the number of seconds by 86.400. The resulting number presents the number of days on the left side of the decimal separator and the remaining decimal seconds on the right side.

So the final result will be something like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDecimalSeconds]
     AS
        [Measures].[DurationInSeconds] / 86400,
    VISIBLE = 0;  

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDays]
     AS [Measures].[DurationInDecimalSeconds],
    FORMAT_STRING =  
        IIF([Measures].[DurationInDecimalSeconds] < 1
         ,’”0 days” hh:mm:ss’
         ,’”‘ + cstr(int([Measures].[DurationInDecimalSeconds])) + ‘ days” hh:mm:ss’)
    ,
    VISIBLE = 1;

If you want this custom format to be displayed in Excel, make sure the connection properties are set correctly to retrieve the number format from the server:

Go to Connection Properties, tab Usage, and check Number Format in the OLAP Server Formatting options.

There is one more thing I would like to tell you about these options: Normally all these options will always be checked by default, but I’ve noticed that when you start from an existing odc-file, this is not the case. So, when you’re used to deliver an odc-file to your users to start analyzing from Excel, you will have to replace this odc-file by an Excel template that connects to the cube, to make sure this option is always on.

There might be a better way to change this behavior for odc-files, but I couldn’t find any. If you know it, please let me know

Enjoy!

I recently struggled with the following challenge in Analysis Services:
In one of the fact tables there was a field duration which actually was a duration of a certain status in seconds. I needed to display this in a readable format, like “2 days 22:15:59″.

bing brought me to the following great article, where Mosha explains how you can use MDX to create a custom format:
http://sqlblog.com/blogs/mosha/archive/2008/09/26/displaying-duration-values-mdx-expressions-in-format-string.aspx

When you apply this for a calculated member you get something like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDays]
     AS [DurationInDays],
    FORMAT_STRING =  
        IIF([Measures].[Duration] < 1
         ,’”0 days” hh:mm:ss’
         ,’”‘ + cstr(int([Measures].[Duration])) + ‘ days” hh:mm:ss’)
    ,
    VISIBLE = 1;

But this brings us to another problem:
This custom format will only display the correct result if [Measures].[Duration] contains decimal seconds.
For more information about the conversion from seconds to decimal seconds, read the following article on wikipedia: http://en.wikipedia.org/wiki/Decimal_time
In short, you need to divide the number of seconds by 86.400. The resulting number presents the number of days on the left side of the decimal separator and the remaining decimal seconds on the right side.

So the final result will be something like this:

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDecimalSeconds]
     AS
        [Measures].[DurationInSeconds] / 86400,
    VISIBLE = 0;  

    CREATE MEMBER CURRENTCUBE.[Measures].[DurationInDays]
     AS [Measures].[DurationInDecimalSeconds],
    FORMAT_STRING =  
        IIF([Measures].[DurationInDecimalSeconds] < 1
         ,’”0 days” hh:mm:ss’
         ,’”‘ + cstr(int([Measures].[DurationInDecimalSeconds])) + ‘ days” hh:mm:ss’)
    ,
    VISIBLE = 1;

If you want this custom format to be displayed in Excel, make sure the connection properties are set correctly to retrieve the number format from the server:

Go to Connection Properties, tab Usage, and check Number Format in the OLAP Server Formatting options.

There is one more thing I would like to tell you about these options: Normally all these options will always be checked by default, but I’ve noticed that when you start from an existing odc-file, this is not the case. So, when you’re used to deliver an odc-file to your users to start analyzing from Excel, you will have to replace this odc-file by an Excel template that connects to the cube, to make sure this option is always on.

There might be a better way to change this behavior for odc-files, but I couldn’t find any. If you know it, please let me know

Enjoy!

Excel 2007 is great tool for browsing OLAP cubes. But 1 thing a lot of people don’t know about or at least don’t use enough, is Member Properties.

When they want to add a property of a specific attribute, they just check this item in the field list. By using it this way, you’re kind of building hierarchies between all the selected attributes, therefore all possible relationships must be checked. This of course has a huge performance impact.

There is an easier and better way that also makes loading the data a lot faster: Member Properties. The relationships between Members and their Properties is defined by specifying Attribute Relationships in Analysis Services.

Ever saw that Tooltip in Excel when you’re hovering your mouse over a field in the Pivot Table? This Tooltip shows all available Member Properties.

But you can also display them on columns:

Click right on the field where you would like to display one or more properties for. Under “Show Properties in Report”, select the properties you would like to show.

By using Member Properties the layout will be better and your report will load a lot faster!

Using Attributes:

Using Properties:

Excel 2007 is great tool for browsing OLAP cubes. But 1 thing a lot of people don’t know about or at least don’t use enough, is Member Properties.

When they want to add a property of a specific attribute, they just check this item in the field list. By using it this way, you’re kind of building hierarchies between all the selected attributes, therefore all possible relationships must be checked. This of course has a huge performance impact.

There is an easier and better way that also makes loading the data a lot faster: Member Properties. The relationships between Members and their Properties is defined by specifying Attribute Relationships in Analysis Services.

Ever saw that Tooltip in Excel when you’re hovering your mouse over a field in the Pivot Table? This Tooltip shows all available Member Properties.

But you can also display them on columns:

Click right on the field where you would like to display one or more properties for. Under “Show Properties in Report”, select the properties you would like to show.

By using Member Properties the layout will be better and your report will load a lot faster!

Using Attributes:

Using Properties:

Ever tried to change the URL of the Report Manager in IIS using host headers?

I did, but got the following error message when I tried to open the Report Manager:

“The request failed with HTTP status 400: Bad Request”

There is some extra configuration needed to make this work:

Edit the RSWebApplication.config file to update the report server URL, that Report Manager uses to connect to the report server:

• Delete the default entry in ReportServerVirtualDirectory
• Type the new URL using host headers in ReportServerURL

The configuration should look like this:

http://myhostheader/reportserver

Now, if you want to be able to open the Report Manager also on the machine hosting the Reporting Services website, you need to make some changes to the Registry, to add an exception to the loopback check security featurethat is designed to help prevent reflection attacks on your computer. If not, authentication will fail with the following error message:

“HTTP 401.1 – Unauthorized: Logon Failed”

To add the exception follow these steps:

1. Click Start, click Run, type regedit, and then click OK.
2. In Registry Editor, locate and then click the following registry key:

   HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0

3. Right-click MSV1_0, point to New, and then click Multi-String Value.
4. Type BackConnectionHostNames, and then press ENTER.
5. Right-click BackConnectionHostNames, and then click Modify.
6. In the Value data box, type the host name or the host names for the sites that are on the local computer, and then click OK.
7. Quit Registry Editor, and then restart the IISAdmin service.