RDA Corporation

Creating a PerformancePoint Dashboard Using a SQL 2012 SSAS Tabular Instance

By Chuck Rivel on July 12th, 2012 // 2 Comments

Introduction

With the recent release of SQL Server 2012, a new SSAS instance has become available to develop business intelligence solutions against – the Tabular instance. The Tabular instance is different in how the cube is developed and designed in that it does not have traditional dimensions and measure groups like a Dimensional SSAS instance. Also, the Tabular instance is an in-memory data storage cube as opposed to a MOLAP or ROLAP dimensional cube. Lastly, creating calculations within the cube requires a different coding language for the Tabular instance known as DAX whereas Dimensional instances require MDX as the coding language.

With these design differences in place between the two different instances,you may wonder if you can still use existing SharePoint 2010 services, specifically PerformancePoint Services (PPS), for your business intelligence needs against the new Tabular model, and  - if so – how to create a dashboard against the Tabular instance. The simple answer to the first part of the question is YES! The second part requires a little more explanation.

 

Technical Overview

When creating a PPS dashboard that contains Analytic charts and grids, there are several typical steps for the development of a PPS dashboard:

• Create a data source against an SSAS cube for the reports and filters that will be on the dashboard.
• Select the Analytic report type (Bar, Pie, Line or Grid) and then drag and drop elements onto the report.
• To help with user interaction, create filters for the dashboard.
• If there are KPIs built into the cube, create a scorecard using cube KPIs.
• Create a dashboard, adding the filters and reports to the dashboard, and then connect the filters to the reports in the dashboard.

In the steps above, for previous versions of SSAS, this was pretty straightforward since there was only one type of cube that could be built (the dimensional model), and PPS was built to use the dimensional model as a data source. However, now that a new type of cube is available, how do you use this cube to build new dashboards for your SharePoint 2010 farm?

The key is that even though the new tabular instance uses a new language (DAX) to build its calculations, MDX can still be used to retrieve data from the tabular instance. Therefore, the components to build a dashboard are exactly the same for reports and filters and the data source to choose for the dashboard is the same Analysis Services type.

For example, to create a data source connection, a typical scenario would be to execute the following steps:

1. Open the Dashboard Designer by going to the Connections Library for the site, click on Documents and then select New Document -> PerformancePoint Data Source.

 

 

 

2. In the Connection Template, select the Analysis Services connection type:

 

 

3. Type in the name of the server for where the SSAS tabular instance is running; select the Database from the drop down and then select the Cube from the drop down.

 

 

4. Click on the Properties table and type in a name for the Data Connection and click on the Save button.

To create an MDX Query type filter against the Tabular model data source created above, the steps are no different than for a dimensional data source. The following steps illustrate how to create a filter for only those years in the date dimension that have an existing measure in the measure group:

1. In SharePoint, navigate to the PerformancePoint Content List, click on the Items menu and click the New Item button and then select PerformancePoint Filter from the list of selections.

 

 

 

2. Select MDX Query from the Filter templates and click OK.

 

 

3. Select the Data Source and click Next.

4. For the MDX formula, enter in the following MDX statement – this statement will limit the year members to only be those members that have Reseller Sales.

EXISTS([Date].[Calendar].[Year].Members, , ‘ResellerSales’)

 

 

5. Click Next.

6. Select the Display Method, such as Tree, and click Finish. After finished, click on the Properties tab and supply a name for the filter.

 

 

 

However, there is one difference when using a dimensional data source as compared to a tabular data source when retrieving KPIs that were built in a tabular model cube. To retrieve the KPIs for use on a PPS scorecard, the KPI data mapping requires using an MDX tuple formula to retrieve the KPI. The reason for the custom MDX is that the KPI Import is not currently supported for a tabular model cube.

The following steps illustrate how to import KPIs that were built in a tabular model cube.

1. In SharePoint, navigate to the PerformancePoint Content List, click on the Items menu and click the New Item button and then select PerformancePoint KPI from the list of selections.

 

 

2. In the KPIs template, select Blank KPI and click OK.

 

 

3. On the Actual row for the new KPI, click on the 1 (Fixed values) link in the Data Mapping column.

 

 

4. Click the Change Source… button and then select the data connection for the tabular model and click OK.

5. In the Dimensional Data Source Mapping screen, mark the checkbox for the MDX tuple formula and enter in the MDX statement to return the KPI’s actual value and then click OK.

KPIValue(“Sales Performance”)

 

 

6. On the Target row, perform the same steps as the Actual row by clicking on the 1 (Fixed Values) link under Data Mapping, click the Change Source… button, select the tabular model data source and click OK.

7. In the Dimensional Data Source Mapping screen, check the Use MDX tuple formula checkbox and enter in the target MDX statement.

KPIGoal(“Sales Performance”)

 

 

8. If there is a target to compare to using the Band by Stated Score method, with the Target row highlighted, click the Set Scoring Pattern and Indicator button to present the Edit Band Settings screen.

9. In the screen, select the Banding Method from the drop down to the Band By Stated Score (advanced) and click Next.

 

 

10. Select the indicator and click Next.

11. Click the Specify Data Mapping button.

12. Perform the same steps as the Actual and Target line: click on the Change Source… button, select the tabular model data source and then click the OK button.

13. In the Data Source Mapping screen, similar to the Actual and Target lines, check the Use MDX tuple formula checkbox and then enter in the status KPI property.

KPIStatus(“Sales Performance”)

 

 

14. Click OK.

15. Click on the Properties tab and supply a name for the KPI.

16. Now that the KPI has been imported, you can create a scorecard using the PPS KPI in a scorecard.

 

Conclusion

In conclusion, developing a PPS dashboard against a SQL 2012 SSAS tabular model cube is not much different than building a PPS dashboard against a dimensional model cube. The performance of the dashboard will be similar and the navigation and drill-through capability remains the same. The bottom line is that PerformancePoint is still a key component of the MS BI stack for providing insights to your business user community, regardless of the type of analytical engine that the dashboard is sourced against.

To learn more about dashboards and business intelligence, check out our on-demand webcasts and other resources available here.

Image Source