In this post we assume that the reader already knows what PowerPivot is and has already created some PowerPivot “models”; the following notes describes the results of our investigations ;–)
When we deploy to SharePoint an Excel Workbook using a PowerPivot datasource , an (almost hidden) instance of SQL Server Analysis services handles the server side PowerPivot request: this instance is named .\PowerPivot.
Let start from a clean situation where no PowerPivot workbook has been deployed; we launch SQL Server 2008 R2 Management Studio and select Analysis Services as the Server type and .\PowerPivot as the Server Name as illustrated in the next picture :
We get, well…nothing : no database.
Now, let’s create a PowerPivot workbook with 4 tables :
1 Excel linked table (Date) and 3 tables fetching data from SQL Server (“Sales”, “Employee” and “SalesTerritory”).
From this, let’s create a small dashboard based on a Pivot table & slicers :
When we save this workbook ( with the filename PowerpivotDemoYYYY) into a document library, no database is created in the .\PowerPivot instance.
When we click on the workbook and display it in the browser, we don’t see anything yet in the .\PowerPivot instance.
When we click on a slicer then a database (with a name including workbook name) is created:
When we expand the databasename node, you will notice that :
- there is a datasource for each PowerPivot connected to the outside world (but not for the Excel linked table).
- there is one Cube (name = ‘Sandbox’)
- Each PowerPivot table is a measure group and a dimension :
Now if we save the workbook on the file system and if we rename it with a .zip file extension, we will find an interesting file in the internal .xl\customdata folder : item1.data which is a SQL Server Analysis Service backup file embedded into the workbook :
Indeed, if we change its extension to abf (from item1.data to item1.abf).
and if we restore it into a database named (for instance) powerpivotrestore, we get a similar cube :
Now let’s try that again with SQL Server 2012 (here I’m using the Denali CTP 3 VM provided by Microsoft).
In SQL Server 2012 the .\PowerPivot instance is decorated with a nice SharePoint icon (see picture below).
I’ve created a PowerPivot workbook with 3 tables (named “Picknic_session”) fetching data from a SQL Server database.
Same behavior: clicking on a slicer generates the Database, but this time we don’t really get a traditional cube in Analysis Services:
I also noticed that starting the (new) PowerView editor from a PowerPivot workbook also generates the database.
Indeed, according to the new SQL Server 2012 BI religion, the BI Semantic Model (BSIM), Analysis Services can be installed in one of the following modes : tabular or multidimensional; the “PowerPivot” mode is based on the tabular mode (works with the Vertipaq engine).You will find more information about the BI Semantic model in this post.
One of the consequences is that we can use the Vertipaq engine without having to install…SharePoint (Vertipaq on SharePoint requires the Enterprise version of SharePoint).
One of the thing that is interesting with the BSIM is that you can import a PowerPivot workbook from BIDS (Business Intelligence Development Studio), which is now hosted in Visual Studio 2010: indeed, Visual Studio 2010 provides several new project templates like the new “Import from PowerPivot Business Intelligence” :
After importing the PowerPivot workbook into Visual Studio 2010, you get the PowerPivot functionalities of Excel-PowerPivot in your favorite environment (which also provides team functionalities like source control and much more)
The BIDS project & Solution looks like this
The project property page provide a bunch of interesting options, like the Server location and the Query Mode, which is Vertipaq by default (but can also be switched to DirectQuery,…)
This (Tabular) project must be deployed to a Tabular instance of Analysis Service :
It looks like the project cannot be deployed (from BIDS) to the ( default) SharePoint instance of Analysis Services (.\powerpivot).If you try it, you will get the following error message:
This message is obviously strange ; we believe that we cannot deploy the tabular project to this instance because the instance is protected/reserved to SharePoint.
Having a separate tabular instance of Analysis service makes sense if someone wants to use the VertiPaq engine without having to rely on the (expensive) SharePoint 2010 Enterprise or if a company want to move from a Team BI model (based on SharePoint) into a Corporate BI model (with or without SharePoint). To be honest, I believe that most companies will adopt the SharePoint approach.
Using PowerView to access a BISM located in a tabular instance of Analysis Service.
Now that our BISM has been deployed to a separate instance of Analysis Service, it can still be accessed by PowerView (formerly know as Crescent) a new very powerful tools for end users & power users (we won’t describe PowerView in this post, we can easily start PowerView from a PowerPivot workbook stored in a document library).
To achieve this, we need to create BSIM connection file pointing the tabular database: from a standard document library we have to provide the BSIM content type which is available in the site collection by activating the following site collection feature PowerPivot feature integration for Site Collections.
In my case I created a connection to my Tabularpoject21 Tabular database (the one deployed earlier from BIDS)
When the BISM connection file is created, we can start PowerView (Crescent) :
In the released of SQL Server 2012, PowerView (which is part or Reporting Services) will only be able to create reports from Tabular databases and not multidimensional databases; this last functionality will be provided later…
The BI semantic Model is not yet fully unified…
So yes, you will still need a tool like PerformancePoint which allows us to quickly navigate from one dimension to another via the Decomposition Tree as illustrated below :
Even if PowerView allows you to create dynamic reports from external instances of Analysis Services, don’t forget that SharePoint 2010 (Enterprise) is still required…
Same story with the new Reporting Services Data Alerts (which allows more efficient reports subscriptions based on data value) : SharePoint 2010 (Enterprise) is required.
The BISM tabular models can be queried in Reporting Services with MDX. That can be a way to bypass the SharePoint 2010 requirement and integrate Reporting Services in native mode to other portals. Excel 2010 is also supported as a client and Excel also uses MDX to query BISM tabular models.