Kasper de Jonge BI Blog

Last week i made a blog post about the power of the “Add business intelligence” wizard to add the current year to date. I decided to extend the script to add 2 new values with Previous YTD and current period last year.

All I did was copy and paste the automaticly created script and change the PeriodsToDate to get 12 months before [Date].[Dates].CurrentMember (last year) using Parallelperiod and give the measure a new name. This results in the following script:

Create Member
  CurrentCube.[Date].[Date Calculations].[Previous YTD]
  As "NA" ;
 
Scope(
       {
         [Measures].[Sales Amount]
       }
) ;
 
// Year to Date
  (
    [Date].[Date Berekening].[Previous YTD] ,
    [Date].[Calendar Year].[Calendar Year].Members,
	[Date].[Calendar Month].Members
  )
  =
  Aggregate(
             { [Date].[Date Berekening].[Current Periode] }
             *
            PeriodsToDate(
                            [Date].[Dates].[Calendar Year],
                             Parallelperiod(
                                            [Date].[Dates].[Calendar Month],
                                             12,
                                             [Date].[Dates].CurrentMember
                                            )
             )
 
  ) ;
 
End Scope ;

As second member i created a current period last year:

Create Member
  CurrentCube.[Date].[Date Calculations].[Current period last year]
  As "NA" ;
 
Scope(
       {
         [Measures].[Sales Amount]
       }
) ;
 
// Year to Date
  (
    [Date].[Date Berekening].[Current period last year] ,
    [Date].[Calendar Year].[Calendar Year].Members,
	[Date].[Calendar Month].Members
  )
  =
  Aggregate(
             { [Date].[Date Berekening].[Current Periode] }
             *
            Parallelperiod(
                               [Date].[Dates].[Calendar Month],
                               12,
                               [Date].[Dates].CurrentMember
                               )
  ) ;
 
End Scope ;

As you can see here we have removed the periodstodate and kept only Parallelperiod to get 12 months before [Date].[Dates].CurrentMember (last year).

This way you can add your own time intelligent functions very easy.

Having played with Data mining in combination with PowerPivot in a previous blog post, i wanted to do data mining at the company i’m currently consulting at, they use Excel 2007 in combination with SSAS. In excel 2007 we do not have the power of the flattened pivot table.  So i had to think of a way to convert the pivot table to a regular excel table. And i found a way, since i have converted all my excel version to 2010 i had to find an older image with Excel 2007 to make this post. I have used the dutch Excel version in the screenshots, i hope you can follow it.

Let’s say i want to data mine on the Adventure works cube. First step is creating a regular pivottable containing all the values you want:

Next we want to put all rows into a regular column, right mouse click on the pivot table, and select Pivot table options, Go to the display tab and select classical pivot table layout. Press ok.

This will result in all rows being put in a column:

Next we remove all totals since we don’t need this in this data mining example, go to design, subtotals, do not show subtotals.

Ok we have made our data ready to be converted to a regular table, we can do this in 2 ways. Copy all cells and paste them in a new sheet or convert to formula’s and use that.

I copied and pasted the cell to a new worksheet, used the keep only values as paste option. This results in the following table:

the problem here is the blank values in the excel sheet, to fix this i found a solution on the web at Mr Excel.

Start by selecting all the columns we want to fill the blanks of, go to the home tab and select “find and select”, and click “Go To special”. Here we can select blanks and click ok.

This will result in all the blanks being selected.  Type =, press the up arrow and press Ctrl+Enter (that’s hold down Ctrl and press the Enter key). All the empty cells will be filled with the value of one row above. Exactly what we wanted.

Now convert it into a table (Ctrl – L) and you are a ready to unleash the data mining beast:

I’ve been using SSAS in combination with Reporting Services for a few years now but i have always found it very cumbersome to deal with YTD and other time intelligence functions in combination with SSRS. A few days ago i read this whitepaper: Translating Cognos PowerPlay Transformer Relative Date Functionality into SQL Server Analysis Services. In this paper they use the “Add business intelligence” wizard to add time intelligent members to the time dimension, I’ve seen the button but never tested it out.

This whitepaper made me do some testing. To do so i’ve created a cube based on the adventureworks database. I’ve created 2 dimension, and used the sales amount in the fact table.

In the date dimension I’ve created a hierarchy:

Now for the magic bit:

We run the Business Intelligence wizard to add a time intelligence functions

We can pick multiple calculations to be created

You have to make sure you pick the date hierarchy so the date calculation can determine the relationship between the different levels.

Last step is picking the measure we want to use the functions with

Now we can click finish, the time intelligent functions will be implemented. This is done by adding a calculated member to the time dimension as we can see here:

This will add a attribute with 2 members to my date dimension, one which shows the measure value for the current month selected and member that shows the year to date to the month selected:

we now can use this member in a query, in for example a SSRS report:

As you can see we now have the sales amount of the selected month from the selected month and the sales amount of the current month year to date. Putting this in a tablix makes it into the following report:

This is great stuff, makes YTD very easy to implement !

I don’t know how i could have missed this great wizard for all this time. I’ll use it for sure very soon at the next project.

With the release of PowerPivot CPT3 you can connect to a SSAS cube, a feature i was really looking forward to and i think will be used a lot. The best mashup possible between traditional BI and ad hoc questions. Think about a salesof the current year compared to the forecast of this year. Where the Current year is available and the forecast is determined by an analyst in Excel.

First thing we notice when we open the PowerPivot window is the addition of Analysis services on the from database tab.

We can connect to a analysis server using the regular data connection.

Next we can type an MDX query (like any analyst is going to do that) or choose for design:

Of course we go for design, this opens our familiar analysis query designer, here we can drag and drop desired data from the cube.

When we press OK, the data will be loaded to PowerPivot:

One big issue i see here is that the data coming  from SSAS needs to be “joinable”. You usually want to join on code instead of description but what you see is that most cubes consist of solely description or code – description. Think of the Month member, usually has the monthname as value. The Adventureworks cube had Monthname + year as value so this makes joining easier. One major design essential for you SSAS cube, try to add a code member to all your dimension and a date member.

The datatypes from SSAS are not recognized themselves so you have to set it yourselves on the measures row and I like to rename to columns to somewhat nicer description.

Ok next up is loading the forecast data, our analyst has created a sheet that with the same month column, subcategory’s and sales forecast and puts it into our worksheet. To make it easier we used the same name as our ssas columns.

You can load the data into PowerPivot bij creating a linked table with one press on the button. You have to create a table first (CTRL-L inside the table will create it very easy)

Now we have the data in our PowerPivot environment we can start analyzing. So we create a PivotTable in our Excel sheet. I drag the salesamount and sales forecast into the values and drag the month on the row.

PowerPivot CTP3 automatically sees we need a relationship to join our two tables, a warning shows up on the top right hand:

i click create but no relationship is detected. To discover what is wrong we have to go back to the powerpivot window and try to create the relationship ourselves.

hmm it appears you have to a master data table for your dimension. Hmm not too user friendly …

Ok back to the table import tool, we have to load data from the time dimension into our worksheet, we open SSAS cube and select Year and Month. Since this is a dimension table we don’t include any measures and click include empty cells. This gives us a unique row of data to use as dimension table. But when loading the data into PowerPivot we get 0 Rows imported, oblivious PowerPivot doesn’t handle MDX query without measures well. Too bad we can’t use this option since this is the preferred option that because when the data refreshes i want to have the latest values from the cube. Update: This has been fixed in the next version and is a known bug.

On to our second option we do it ourselves in Excel. I copied the columns from the facttable into excel and use the “remove duplicates” feature to create unique columns. Use Create linked table to load the 2 tables into powerpivot. we now have 4 tables available in PowerPivot, 2 measure tables and 2 dimension tables.

When we return to Excel we see a warning that our model is modified and we can refresh the Pivottable with one press of the button, great new function.

I now drag and drop the months on the y-ax with sales amount and sales forecast in the pivottable, PowerPivot automatically detects relationship when the columns are called the same and lets the user create them, resulting in the following:

I now want to slice on category and subcategory, when i drag these values in the slicer PowerPivot again automatically detects the relationship.

One more cool thing we would like to add is to see the difference between sales amount and sales forecast so lets add a new measure that subtracts the 2 measures, i added a new measure in the Inetnet sales amount table with the following formula:

=’Internet Sales’[Sum of Sales Amount] – ‘Table3′[Sum of Sales forecast]

Resulting in this sheet eventually:

After this I cleaned it up a bit to hide some of the dimension fields from the fact table, you can download the excel sheet yourself and play with the result: date.xslx. I try to create a movie about this post this week to walk it step by step because in understand some parts go a little fast in this blog post.

Conclusion: PowerPivot is great but there are still a lot of things you need to know when you want to create a Ad hoc BI without technical knowledge, i had meant to create this blog post in a few hours but eventually took me a lot more and two bugs in the process I hope MS isn’t quiet celebrating, although i love the product to let end users play with it they still have to make it more userfriendly.

Dan English wrote a great blog post about Analysis Services (SSAS) Processing and Aggregations with SSIS:

So you created an SSAS database solution and have deployed it to a production environment.  Now the data has accumulated and you need to take a look at moving to a incremental processing approach because you are either loading the data more regularly or the volume of data and processing does not meet your maintenance window anymore.  So what do you do?

Read the solution Dan proposes at his blog: http://denglishbi.spaces.live.com/blog/cns!CD3E77E793DF6178!2101.entry Great in depth information !

I was working with a client where i had some difficult security questions, that i had to solve without having to resort to advanced MDX query’s. The client has to be able to create reports without MDX knowledge. Take the following example from the AdventureWorks cube:

I want to give users a report that show all sales by state province of the country they are in, e.g. you are a sales person belonging to postal code 91801 and you want a report showing all the state-province’s of the United States since that is the country you belong to.
As seen in the dimension below:

But by default users are forbidden to see the sales of another state province when their postal code isn’t in it. When you place security on a country by using a role, autoexists take care of the security for the rest of the levels resulting in only the parents of the selected postal codes will be shown to the user with the role defined.

This will result in not being able to show the parent levels your postal code does not belong to, as seen below while browsing the report using the role:

When you create a report based on this dimension you get the following report (don’t mind the formatting)

The key to solving this problem is using two dimensions with the same levels. One with the security enabled by using a role and one dimension (you can just copy the original dimension) without security, you could remove the postal code level in this dimension to make sure the security is intact.

When you create your report you use levels from both dimensions, and this is where autoexists is your friend. Since you want to show only city’s from a sales persons own country you use the the country level from the secured dimension (only his own country is returned by the secured dimension) and the state province level from the security-less-dimension, autoexists takes care of showing only those state provinces of the country the sales person belongs to.

Resulting in the following desired report:

All in all reasonably simple when you understand autoexists.

Teo Lachev blogged about a new product from Intelligencia.In short it is a a Silverlight-based Analysis Services browser. The Intelligencia for Silverlight control has scriptable interface and a filter control which allows management dashboards to be created by linking grids and filters, as the first link on the web page demonstrates. (the product is currently in Beta)

This had loads of potential, especially for developers, to create webbased ad hoc reports or dashboards.

Read more at :

http://prologika.com/CS/blogs/blog/archive/2009/10/18/intelligencia-for-silverlight.aspx

SQLCat has a great blog post about installing SSRS with Sharepoint integration:

This technical note describes how we designed and implemented a business intelligence solution that utilized a server farm containing Microsoft® Office SharePoint® Server 2007, Microsoft Office PerformancePoint® Server 2007 Monitoring Server, and Microsoft SQL Server® 2008 Reporting Services in SharePoint mode, all running on Windows Server® 2008 R2 and with all servers and applications configured for Kerberos authentication. In this technical note, we discuss the design requirements for this business intelligence solution, its logical architecture, the challenges we faced in architecting and implementing this solution, and our resolutions to these challenges.

wish we had this sooner, implementing this is NOT easy. See also my colleague Marc Valk excellent blog post about Sharepoint and SSRS integration over multiple servers (not the default all on one box) with the famous double hop issues and how to fix them. Read it here:

http://www.marcvalk.net/2009/04/sharepoint-and-ssrs-integration/

Some colleagues of mine upgraded their host system to Windows 7. Since that moment they couldn’t connect to our test SSAS server to test the new developed analysis cubes from Excel.

The following error occured:

The following system error occurred: The security database on the server does not have a computer account for this workstation trust relationship. .

My collegue Bas Kroes found the answer while searching the internet on the MS forums:

Did you by chance install Windows 7 on your workstation (the one you re-imaged)?  I understand that this issue arises in Windows 7 due to a component called “Windows Live Sign In Assistant”.  From my understanding, you basically have 3 options:

1. Configure Kerberos on your SSAS server http://support.microsoft.com/kb/917409
2. Uninstall Windows Live Sign In Assistant on each workstation
3. Use SSPI=NTLM in your connection string to force NTLM as the Security Support Provider Interface

#1 is probably the preferred method, as it addresses the problem server-side, whereas #2 and #3 are both client-side workarounds.

found at http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/b058635f-6d3e-4240-a555-1dbd945ae25a

Brian Knight has come up with a really nice, clean solution which leverages a helper assembly to do a drill through to the database, real easy to setup and use.

Download the assembly here:

http://blogs.pragmaticworks.com/brian_knight/2009/09/creating-a-ssas-rowset-action.html

Great work Brian!

Found at: performancepointblog.com