Kasper de Jonge PowerPivot Blog

yes, the word that any BI or IT pro dreads: Kerberos .. Imagine you want to run Power View in a SharePoint farm on top of a SSAS database running in Tabular mode. You probably want to use security so you can secure the data by the user that is actually running the report using the AS security features.

But most likely you won’t run your AS instance on a machine that is inside of the SharePoint farm. When all of your machines are inside the same SharePoint farm, SharePoint will take care of passing the credentials between the machines. But as soon as one of the machines lives outside of the farm you have to setup Kerberos to make sure the security is correctly passed on between machines. Configuring Kerberos is a lot of (hard) work if Kerberos is not already set up correctly in your organisation.

But there is some good news on this with SQL server 2012. In SQL Server 2012 we introduce the BISM file that allows us to start a Power View report based on the connection information in this BISM file. Whenever a connection is made from Power View to the Tabular database it tries to connect using the credentials of the user that is executing the Power View report. If Kerberos is not configured this connection will fail regardless of him having access.

But there is a fall back scenario, when using the BISM connection to connect to a tabular database ADOMD will give it another try using the execution account the Reporting Services app server is running under and then switch to the actual user who initiated the connection. All you have to do is add the execution account the Reporting Services app server is running under to the administrators of the AS instance and this connection will succeed even without Kerberos is set up. Admin connections are always allowed cross machines, under the covers ADODM uses the effectiveusername connection string parameter to switch to the actual user initiating the connection after connection is being made.

This post is dedicated to Marc Valk my esteemed former colleague with whom we battle the Kerberos beast several times at my previous job .

One of the question that regularly comes up is: “He I have created a tabular model in BIDS and deployed it to my test server, now I have changed a measure or a calculated column I deploy those changes and all of my tables are again loaded. I don’t want that ! I just want to deploy some metadata”.

Well actually that is exactly what we do, but the UI is a little deceiving.

When you deploy for the first time and any time after you get the same UI:

But under the covers what we do is:

• Update the metadata of the database on the server
• Send a Process Default command to the database

Got an interesting question today. The question was about creating sparklines for all the country’s that shows a running total of sales for the last 12 months (Rolling 12 months) based on the year that I select from a slicer. I use the trick I blogged about before to work with sparklines and PowerPivot, I would recommend reading it for some background.

First I want to start by creating the sheet that I want to use as a report. On this sheet I start by creating the slicer that I want to use, In this case Calendaryear:

Next I create a second sheet where I want to collect my data for the sparklines.

Because we want to show the running 12 months per year I would like to use only the months of one year that is the baseline of the running month. So we hook up the slicer from our report to the pivottable in the other sheet. Select the slicer, go to options and click on pivottable connections, here I can select the pivottable from the other sheet to be filtered as well:

Next we want to create a measure that creates our running total:

=if(COUNTROWS(values(DimDate[CalendarYear])) = 1,
			CALCULATE(SUM(FactSales[SalesAmount]),
				DATESBETWEEN(DimDate[Datekey],
					DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH)
					, LASTDATE(DimDate[Datekey])))
			,blank())

What this DAX measure does is first of all check if only one year is selected using:

COUNTROWS(values(DimDate[CalendarYear])) = 1,

If more than one year is selected we return blank(). If only one year is selected we calculate the running total by doing a sum of FactSales[SalesAmount] by creating a date range using DatesBetween.

The start date of the range is determined by getting the first day of the current month (we know this because of the current row context) and then go back 11 months before that:

DATEADD(FIRSTDATE(DimDate[Datekey]),-11, MONTH)

to the last date of the current month:

LASTDATE(DimDate[Datekey])

Using this in combination of calculate the sum will now calculate over the entire date range.

Now we have the data set up we can create the sparklines. First we need to copy the rows and paste them to the report and create the sparklines on the report page, check the previous blog post on how to create the sparklines.

I have uploaded the workbook for download here.

I wanted to share this cool program that will start in the next month: The SSAS Maestro program. There are more and more enterprises are using SSAS for large implementation, like Yahoo’s 12 TB SSAS cube as you could have seen on SQLPass. That means also that these large servers and cubes have to be maintained and developed as well. This asks for a more in depth knowledge only a few people in the world currently posses.

The new SSAS Maestro Program is a three-day, deep-dive course on Analysis Services 2008 R2 gives architects and consultants the education and hands-on experience needed to deliver highest scalable OLAP solutions.

Prepared and presented by top industry experts and the SQL Server Analysis Server team, this intensive course gives top SSAS professionals the education and hands-on experience needed to deliver highly complex and highly scalable OLAP solutions using Analysis Services 2008 R2. Today, there is increasing need for expertise to architect deliver and maintain mission critical OLAP deployments and the primary objective of this course is equip you with the latest SSAS best practices and case studies.

SSAS Maestro Program Benefits

Upon successful completion of this course, assessment and case study evaluation by industry experts, attendees will:

• Be a part of the elite group of SSAS Maestros
• Help Microsoft accounts teams deploy highly complex/highly large SSAS projects
• Have access to webcasts and Q&A sessions on the learnings some of the most complex SSAS implementations directly from SQLCAT and the Analysis Services team
• Be showcased on a special section within the Microsoft SQL Server Web page for the SSAS Maestros

As you can see this is not for the faint of hearts You have to live and breathe SSAS for a few years to be able to pass this. So most candidates are known by Microsoft (local subsidiaries, SQLCat or the product team) and invited directly. This will help us spread the in depth knowledge of SSAS to our top partners who can share it further along.

Isn’t this very cool…

At a customer we wanted to get an overview of all the roles and members of all his cubes at specific server. I decided to create a console application in C# that query’s the server using the SSAS management objects. Here you can find the code and download the program.

What it does is create a connection to the SSAS server and loop through all the databases, inside the database loop through the roles and all the members belonging to this role.

Make sure you reference the Analysis management objects to your project when you write your own code.

You can find the code here:

static void Main(string[] args)
{
    // Test if input arguments were supplied:
    if (args.Length < 1)
    {
        Console.WriteLine("Please enter a connectionstring and filename in the argument.");
        Console.WriteLine("Press any key to continue");
        Console.ReadLine();
        return;
    }
    string filename = args[1];

    try
    {
        using (System.IO.StreamWriter file = new System.IO.StreamWriter(filename, false))
        {
            string filerowheader = string.Format("\"{0}\",\"{1}\",\"{2}\"", "Database", "Role", "Member");
            file.WriteLine(filerowheader);
            using (Microsoft.AnalysisServices.Server Server = new Microsoft.AnalysisServices.Server())
            {
                string servername = args[0];
                Server.Connect(servername);
                Console.WriteLine(string.Format("{0} {1}", "Connected to :", servername));
                foreach (Microsoft.AnalysisServices.Database ssasdb in Server.Databases)
                {
                    string Databasename = ssasdb.Name;
                    Console.WriteLine(string.Format("Reading roles from {0}",Databasename));
                    foreach (Microsoft.AnalysisServices.Role CubeDbRole in ssasdb.Roles)
                    {
                        string Rolename = CubeDbRole.Name;
                        foreach (Microsoft.AnalysisServices.RoleMember CubeRoleMember in CubeDbRole.Members)
                        {
                            string RoleMember = CubeRoleMember.Name;
                            string filerow = string.Format("\"{0}\",\"{1}\",\"{2}\"", Databasename, Rolename, RoleMember);
                            file.WriteLine(filerow);
                        }
                    }
                }
            }
        }
        Console.WriteLine("Finished reading the SSAS database");
    }
    catch (Exception ex)
    {

        if (ex is Microsoft.AnalysisServices.AmoException)
        {
            throw new ApplicationException("Error on reading the SSAS server", ex);
        }

        if (ex is System.IO.IOException)
        {
            throw new ApplicationException("Error opening or writing the file", ex);
        }
        throw new Exception("Unknown exception", ex);

    }
    Console.WriteLine("Press any key to continue");
    Console.ReadLine();
}
}

Or download the application from my skydrive.

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.