Kasper de Jonge BI Blog

For those who missed it, the launch date of SharePoint was announced. The launch will be done by President of the Microsoft Business Division Stephen Elop as he introduces Office and SharePoint 2010 on May 12, 2010 at 11 a.m. EST.

You can join him live at: http://sharepoint.microsoft.com/businessproductivity/proof/pages/2010-launch-events.aspx#fbid=JadoeNy2pBe

I just can’t wait to get PowerPivot to production !

I recently found a great PowerPivot feature to make the published PowerPivot sheet “view only” for a end user.

When you have write permissions for a PowerPivot sheet you can open the sheet in Excel and use all the measures and columns and use everything available in the sheet by using the “Open in Excel” button:

Now the great news, you can contain these permission to view only, documents can only be viewed sever side:

When you open the PowerPivot sheet in the browser you see the Open in Excel button is gone:

When we open the PowerPivot gallery as a default document library you will not be able to download the Excel sheet, it will render as html when you download it. I think this will be a very usefull permission !

This screencast will show you how we create a dashboard on a SSAS cube in PerformancePoint 2010 using a filter with time intelligent functions:

A few important points:

• use a Hierarchy to assign multiple Time Member Associations,the more levels you use the more functions are available. When you assign day you can use functions on day (like today), when you assign year you can use functions like nextyear.
• The Time intelligent functions use the time settings of the server to determine the current day and calculate the functions.
• You can map the time from your cube to any time you want, like in the demo you can map 2008 to 2010 to fool the functions
• Use the formula in the  time filter to connect controls where available

Pretty though demo to do 10 minutes, please don’t hesitate to ask me questions here.

Big thanks to these blog posts:

http://consultingblogs.emc.com/davidfrancis/archive/2007/12/02/PerformancePoint-Time-Intelligence-_2D00_-BI-for-the-masses-Part-1.aspx
http://blogs.msdn.com/performancepoint/archive/2009/12/14/configuring-data-source-time-settings-for-time-intelligence.aspx
http://blogs.msdn.com/performancepoint/archive/2010/01/21/time-intelligence-formula-quick-reference.aspx

And Dan English to point out to me i have to use the Hierarchy to add multiple Time Member Associations at the Ms forum:
http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/thread/5f7f3e9b-1648-48c7-8cdf-8e3fb211b0ae?prof=required&ppud=4

This screencast shows how you can import data from a reporting services report published to SharePoint into PowerPivot:

This blog post will describe how you I enabled PerformancePoint 2010 on my SharePoint 2010 with PowerPivot integraton where I used the VidasM installaton guide. This is by no way the best way to do this but it enables you to play with PerformancePoint.  I used (and took some quotes) the PerformancePoint team excellent blog post Deploying PerformancePoint 2010 Soup to Nuts as a base (this is the official way to do it).

 We start by creating a site collection:

Set a name, location and choose  Business intelligence center as template:

Site should be created ok:

Now we are going to turn on the PerformancePoint services:
Go to central admin , system settings, manage services on serve.  You need to start the PerformancePoint Services and the Secure Store Service:

Once the Business Intelligence Center has been created, the administrator must create a PerformancePoint service application. This creates the service application proxy that facilitates web service calls between the WFE and the app server, along the Windows Communication Framework.

Go to Application Management, Service Applications , Manage service applications, click new, select performancepoint service application:

Last step is configuring the service app, give it a name and add check the “Add this service application’s proxy to the farm’s default proxy list” and create a new app pool.

Read the entire PerformancePoint team blog post for the details on the why.

When you finished this last step you can start playing with PerformancePoint and publish the dashboards:

In my previous blog posts we made use of a published PowerPivot workbook to use a datasource. In this post we are going to discover what goes on under the hood  when you call a PowerPivot workbook on a SharePoint 2010 server. Thanks to Denny Lee from the PowerPivotTwins for reviewing this post, clearing some things up and providing excellent information.

When I make a connection with the browser to the workbook of a published xlsx by clicking the PowerPivot gallery the following things happen:

• The Excel web access service starts rendering the workbook
• It will connect to Excel Calculation Services which will extract the workbook from the SharePoint content dB
• The workbook is rendered as it was published, whatever slicers that were chosen at the point of publishing will be shown as is, including the data at the point of publishing.
• When you interact with the workbook the following happens:
  • Excel Web Access connects to Excel Calculation services which then connects to the OLE DB Provider for Analysis Services. Because it (MSOLAP) recognizes this as a published workbook, it will then redirect the request to the PowerPivot System Service.
  • PowerPivot System Service opens a request to the Analysis Services Engine Service to get the data to be rendered if the data has already been uploaded.
  • Otherwise, the PowerPivot System Service will obtain the workbook (remember Excel Services has already grabbed it), extract the database from it, find the right Analysis Services Engine Service (based on round robin or health status), and attach it to the Analysis Services Engine Service. You can see this latter action from the SSAS Profiler trace:
    

    <ImageLoad xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine” xmlns:ddl100=”http://schemas.microsoft.com/analysisservices/2008/engine/100” xmlns:ddl200_200=”http://schemas.microsoft.com/analysisservices/2010/engine/200/200” xmlns:ddl100_100=”http://schemas.microsoft.com/analysisservices/2008/engine/100/100“>
    <ddl200_200:ImagePath>C:\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\Backup\Sandboxes\DefaultGeminiServiceApp\ReadOnlyExclusive-006bc445-9ea7-419d-ab2d-886f33b89a03-BP.xlsx</ddl200_200:ImagePath>
    <ddl200_200:ImageUniqueID>213a3a72-eb2d-4646-aa6d-46d9baf5c070</ddl200_200:ImageUniqueID>
    <ddl200_200:ImageVersion>01/09/2010 20:01:13</ddl200_200:ImageVersion>
    <ddl200_200:ImageUrl>http://sp2010ppdemo/PowerPivot Gallery/BP.xlsx</ddl200_200:ImageUrl>
    <ddl100_100:DbStorageLocation>C:\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\Backup\Sandboxes\DefaultGeminiServiceApp</ddl100_100:DbStorageLocation>
    <ddl100:ReadWriteMode>ReadOnlyExclusive</ddl100:ReadWriteMode>
    <DatabaseName>BP Sandbox 006bc445-9ea7-419d-ab2d-886f33b89a03</DatabaseName>
    <DatabaseID>006bc445-9ea7-419d-ab2d-886f33b89a03</DatabaseID>
    </ImageLoad>
    <PropertyList xmlns=”urn:schemas-microsoft-com:xml-analysis”>
    <LocaleIdentifier>1033</LocaleIdentifier>
    </PropertyList>

    next the cube database is restored from xml files inside the powerpivot data:

    \\?\C:\Program Files\Microsoft SQL Server\MSAS10_50.GEMINIBI\OLAP\Backup\Sandboxes\DefaultGeminiServiceApp\A45D056461D04A8195CE\MS_8377d9f7-8722-452e-8128-71168e30562b.2.db\Sandbox.492.cub.xml; Size=24KB

    These files are XML so you can open them, this file contains the definition of the cube, what drew my attention was:

    <StorageMode valuens=”ddl200_200″>InMemory </StorageMode>

    Which means the analysis cube itself is being loaded straight into memory We probably can use this to load our own cubes into memory in a future release.

    When the cube is restored the data is then loaded from files within the workbook.
    
    As last step some additional metadata like security is loaded, then the model (The PowerPivot terminology of a cube is model) is available in the Analysis Services Engine Service

• A MDX query is fired to the SSAS cube to return the data:
  

  SELECT {[Measures].[Total last month of year],[Measures].[AVG YTD last year],[Measures].[Trend prev year],[Measures].[Total last month -1 of year],[Measures].[Trend this year],[Measures].[Total same month previous year],[Measures].[Trend 2 years]} DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY CrossJoin(Hierarchize({DrilldownLevel({[DimPromotion].[EnglishPromotionCategory].[All]},,,INCLUDE_CALC_MEMBERS)}), Hierarchize({DrilldownLevel({[DimPromotion].[EnglishPromotionName].[All]},,,INCLUDE_CALC_MEMBERS)})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM [Sandbox] WHERE ([DimSalesTerritory].[SalesTerritoryRegion].&[Central],[DimDate].[CalendarYear].&[2008]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

  <PropertyList xmlns=”urn:schemas-microsoft-com:xml-analysis” xmlns:soap=”http://schemas.xmlsoap.org/soap/envelope/”><Catalog>BP Sandbox 006bc445-9ea7-419d-ab2d-886f33b89a03</Catalog><Timeout>276</Timeout><Content>SchemaData</Content><Format>Multidimensional</Format><AxisFormat>TupleFormat</AxisFormat><DbpropMsmdFlattened2>false</DbpropMsmdFlattened2><SafetyOptions>2</SafetyOptions><Dialect>MDX</Dialect><MdxMissingMemberMode>Error</MdxMissingMemberMode><DbpropMsmdOptimizeResponse>1</DbpropMsmdOptimizeResponse><LocaleIdentifier>1033</LocaleIdentifier><DbpropMsmdMDXCompatibility>1</DbpropMsmdMDXCompatibility><DbpropMsmdSubqueries>2</DbpropMsmdSubqueries></PropertyList>

• The data is then put from the Analysis Services Engine Service into the rendered workbook by the excel services. It goes through PowerPivot System Service but it never actually does anything – from this point onwards, its just about directing the traffic.
• Excel Calculation services passes the workbook to the Excel web access layer who passes it on to the browser.
• The PowerPivot enabled Excel worksheet is rendered in the browser with the new interactive data

Sources for this post are:

Article on BOL which explains how the loading happens: Plan for PowerPivot Integration with Excel Services with a great diagram describing the steps:

(Although Denny pointed out: The diagram about Powerpivot System Service getting data form the content database isn’t right. It will get some metadata from the SharePoint SQL database store, but doesn’t actually get the data from there.)
and  the PowerPivotTwins (Denny Lee and Dave Wickert) downloadable Academy Live presentation with in depth server architecture

• Academy Live: A Preview to PowerPivot Server Best Practices.

A few things to keep in mind:

• Data is not refreshed when you call a workbook, only by scheduling a data refresh
• Caching occurs by loading the SSAS database into memory, after a while of not using the app the database is deleted from the SSAS server (so never connect to the database itself, always to the workbook)
• First time a workbook is interacted will be slow, after that fast.
• Caching of  the PowerPivot data of a workbook is for all users not per user (caching at database level)
• For a Excel workbook at Excel Services, caching is at the user level

I hope this gives you more insight in what goes on on the server side of PowerPivot

One important thing to remember when you call your SharePoint Powerpivot Gallery. Use the computername to connect, so no IP adress or localhost in your IE. No workbooks will show up when you don’t use the computername. When you did make a single box install but still want to access the SharePoint from a remote machine just add the ipadress and computername to your host file, and log in with the sharepoint credentials.

I was searching for a way to run SharePoint 2010 with PowerPivot on my laptop. My colleague Roel van Lisdonk told me about the possibility to run a virtual machine next to your regular OS using Hyper-V, the mayor advantage is you have all your hardware at your disposal without having the hassle of running your machine in dual boot. All you have to do is create a VHD file, reboot, run the installation disc, mount the VHD while installing and install on the vhd. When installation finished you can dual boot from the VHD file. Excellent !

This great post on ZDNET describes how it works, just follow it (use a Server 2008 R2 disc instead of Windows 7) and run SharePoint with all you hardware available:

How-to: Getting started with .VHD files in Windows 7

After installation use the Vidas Matelis install guide to install SharePoint 2010 with PowerPivot.

Happy SharePointing !

In my previous PowerPivot post i created a SSRS report based on a published PowerPivot app with DAX measures. In this post we are going to create a PerformancePoint 2010 dashboard based on the published PowerPivot App and publish it to SharePoint. First you need to enable PerformancePoint in SharePoint 2010, to do this the PerformancePoint team has created an excellent blog post: Deploying PerformancePoint 2010 Soup to Nuts

Ok we start at the PerformancePoint site:

And click start using PerformancePoint Services:

Click the Run dashboard designer button to start the o so familiar dashboard designer (same as 2007). we start by adding a Datasource which points to our PowerPivot xlsx:

I selected the “Per user identity” because i haven’t got my installation really ok (the unattended service account is not installed). Next we can create a scorecard, right mouse on PerformancePoint content, new, Scorecard

Select analysis services and click OK, select the datasource we just created:

We need to create the KPI ourselves, so we select the measures created in PowerPivot, I want to see the the totals sales last month measured against the avg last year, we select the band method “increasing is better”, because more ordeer are better

We select the year we want our KPI shown against

The next steps we keep the default values, click finish to create and load the dashboard:

Now we want to show the sales kpi by Promotioncategory, you can drag the dimension you want into the scorecard. Don’t forget to click update to show the new result.

I rather like other indicators than the default, so go to the KPI, click scoring and patterns and doubleclick the indicator to select a new indicator, I like the indicator below:

save the indicator, go to the scorecard and update again:

Next i wanted to get a graph to show the orderquantity by year, create a new report and select graph. Put the measure in series and calendaryear on the bottom axis.

Last we create the dashboard to show the data, i created a 2 column dashboard with a top bar. I dragged the scorecard to the right column and the chart report in the left column. You can add a date selection in the top bar when want, or even the new time intelligent filter. I did not add one this time. 

Next we save and deploy the dashboard to SharePoint:

It will automaticly open the dashboard in SharePoint, looks great in my opinion:

As last item I wanted to show you one neat thing: the new silverlight decomposition tree. Right click on a bar in the chart and click decomposition. I clicked on the 2007 bar. 

When you click the value you can expand the value to a dimension and so on:

Using PerformancePoint on a PowerPivot App is another great way to show the data to you customers, the creation of dashboards are doable for the analysts.

After installing PowerPivot on SharePoint 2010 thanks to the excelent guid of VidasM i decided to try and install report services 2008 R2.

To start I downloaded the SQL Server® 2008 R2 November CTP Reporting Services Add-in for Microsoft SharePoint® Technologies 2010 and followed the instructions there:

Step 1: Install a SharePoint technology instance. (Already did this when installing PowerPivot with SharePoint)

Step 2: Install SQL Server 2008 R2 November CTP Reporting Services and specify that the report server use SharePoint Integrated mode.

Step 3: Configure Reporting Services.

Step 4: Download the Reporting Services Add-in by clicking the rsSharePoint.msi link later on this page. To start the installation immediately, click Run.

After installing Reporting services and the add-in your reporting server is ready to be integrated with SharePoint, in SharePoint 2010 we have some new admin screens.

To integrate go to central admin, general application settings:

When you successfully installed the add-in a reporting services icon will be there. Click Reporting Services Integration:

Add the report server web service url (To get the URL, open the Reporting Services Configuration tool, connect to the report server, and click Web Service URL. Click the URL to verify it works. Copy the URL and paste it into Report Server Web Service URL.), select your authentication mode (windows authentication is prefered). Add a username and password of your admin account. Click ok to configure and start the integration.

After the installation you can set the reporting services default.

What is changed in SP2010 is that there isn’t a report library available. You have to add content types to a default library. So go to a site collection, site actions,  View all site content.

Create a Asset library:

Now we have to make sure we can add reports to the library. To do this we have to add content types:

Open the library, click on library tools, library settings, Under Content Types, click Add from existing site content types. In the Select Content Types section, in Select site content types from, click the arrow to select Reporting Services. In the Available Site Content Types list, click Report Builder, Report Data Source and Report and then click Add to move the selected content type to the Content types to add list.

Now we are ready to upload reports and execute them from within our webparts:

More reading about this on BOL:

How to: Configure Report Server Integration in SharePoint Central Administration

How to: Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode)