In part 1 of this post I wrote about first two types of Status Indicators : Sharepoint List based and Excel based.
In this part I will demonstrate what the possibilities are for building Status List Indicators with SSAS KPIs and also with Fixed Values.
SQL Server Analysis Services based Status Indicator
This is a great time to talk about NOT using tabular data sources for everything. Some people may be thinking how they can get around not using OLAP cubes and simply point PPS towards SQL tables and SharePoint lists.
The tabular data source provider is not built to scale with large volumes of data. Because it creates a multi-dimension structure on the fly it is not a particularly efficient structure to use. Tabular data sources should be small and simple (ca under 10,000 records is a good guideline).
The main point to make is: “if your data tabular is well-organized enough to parse into an OLAP structure on the fly, why isn’t it in a cube?!”
While we are at it so let me explain a few things for those of you who’d like to user PowerPivot as a datasource for SSAS based Status Indicator.
PowerPivot data sources are not natively supported by PPS. However, because they support most of the same interfaces and functionality as Analysis Services, it is possible to connect
to a PowerPivot workbook hosted in Excel through a connection string as follows using an Analysis Services data source
provider=molap;datasource=http://<server>/<document library>/<Power PivotFile>.xlsx
Notice the name of the cube. All cubes used with PowerPivot are called Sandbox, and that is the only cube you will see in the cube dropdown list