Random Thoughts on Technology: Data Refresh

Yet another interesting situation where we had to display an Report on Excel and put it on SharePoint Power pivot gallery. We selected the Cube database and dragged in a few dimensions and Measures along with a few slicers. The query that was auto generated was not very optimized.
The report was to show data only for last one week. One of the slicer "Week Starting Date" was to have dates for the last one month only.
We had to use some hidden filters to ensure that we show only part of the data. i.e. only last 4 weeks data. There are few ways (Slicer Settings) where we can visually indicate that Items that do not have data should be disabled (will be shown) and also there is a way to move those items to the end of the slicer. There is no way of removing that altogether.

In such situations we can write a custom query to only fetch data with Week Starting Dates that is within the last one month range. This also improves the performance because of the optimized query against the Cubes.
Excel do not provide a direct way to update the query. There are a few Adins/products that aid to this, like Vivid http://www.varigence.com/products/vivid/features/navigation that will come into handy.

Another approach could be to use an ODC File where you can embed the MDX query. I am attaching a sample ODC file. Pay close attention to the highlighted section.

<html xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Cube>
<meta name=SourceType content=OLEDB>
<meta name=Catalog content=CubeDB>
<meta name=Table content=CDW>
<title>sqlserver2008r2 CubeDB CDW</title>
<xml id=docprops><o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<o:Name>sqlserver2008r2 CubeDB CDW</o:Name>
</o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=MSOLAP.4;Password=password;Persist Security Info=True;Data Source=Server\sqlserver2008r2;Initial Catalog=CubeDB</odc:ConnectionString>
<odc:CommandType>MDX</odc:CommandType>
<odc:CommandText>SELECT
{ [Measures].[Sales Amount],
[Measures].[Tax Amount] } ON 0,
{ [Date].[Fiscal].[Fiscal Year].&[2002],
[Date].[Fiscal].[Fiscal Year].&[2003] } ON 1
FROM [Adventure Works]
WHERE ( [Sales Territory].[Southwest] )
</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
<style>

</style>
</head>
</html>

There are already tons of articles to help you out in setting up the PowerPivot, however each setup has its own set of challenges. In this article I am going to highlight the issues I faced and I am sure it can save someone that 1 week of time that I spent on it.

At the end of the article I will also publish a few references that helped me in my setup.

Claims based authentication is the only way to create your web application if you want to support Forms Authentication. i.e. any Authentication provider that is not tied to Active Directory (in which case you could chose your web application to be Classic mode)

If you are reading this article thinking that you can setup Power Pivot on your Claims based Web application, you would be sorry to hear that it is NOT supported. I learnt it the hard way.

You can still view the Power Pivot reports published without any trouble but, you will not be able to take the advantage of the Data Refresh (On Demand/Scheduled) capabilities of Power Pivot.

However, I was able to get it working on my laptop (DC, SP Server, DB & Analysis Server). But, with a limitation that I needed to be a Domain Admin & Farm Admin. It worked only through a unattended account which was tied to a Secure Store Service Application ID configured with the Domain Account.

The same did not work on a multi box environment. Not able to understand why? I was working with a Microsoft Tech support to resolve this though. Which I think now is futile.

For the installation problems, you can refer to my previous article:

http://prashanthkn.blogspot.com/2010/10/troubleshoot-on-setup-of-powerpivot-for.html

Either you are setting up the PowerPivot on Claims/Classic based Web applicaiton, you will definitely want to take care of the following:

1. Ensure that you have deployed the PowerPivotFarm.wsp globally & PowerPivotWebApp.wsp for the Web Application you are enabling Power Pivot capabilities.

See Central Administration > System Settings > Farm Management > Manage Farm Solutions >

2. Ensure that Secure Store Service, SQL Server Analysis Services, SQL Server PowerPivot System Service, Timer Service are started.

See Central Administration > System Settings > Servers > Manage Services on Server

3. Few Features have to be activated in the Site collection.

PowerPivot Feature Integration for Site Collections

SharePoint Server Publishing Infrastructure

SharePoint Server Publishing

See, Site Settings > Site Collection Administration > Site Collection Features >

4. If you have changed the master page then you may end up failing to load the power pivot gallery. Add the following in the VirtualDirectory\{app port}\web.config

5. For data refresh ensure that the application pool account of the Power Pivot Service Application has enough privileges.

6. You may need to setup Kerberos (or enable for Delegation in Active Directory) when you have multiple box setup. This one I am not sure yet.:)

Some settings that you need to be aware of for the Power Pivot.

1. Creating Secure Store Service Application ID

2. Linking that in the Power Pivot Excel Workbook while publishing it. I may write another article very soon.

3. Central Administration > PowerPivot > Configure Service Application Settings

4. Central Administration > Monitoring > Timer Jobs > Review Job Definitions > PowerPivot Data Refresh Timer Job

References:

http://technet.microsoft.com/en-us/library/ff955761.aspx

http://powerpivotgeek.com/2010/04/22/why-powerpivot-requires-classic-mode-web-applications/

http://www.powerpivotblog.nl/add-a-new-user-to-your-single-box-demo-machine-with-ad

http://powerpivotgeek.com/2010/09/08/a-peek-inside-getting-the-most-from-data-refresh/