An Introduction to PerformancePoint Services Part 1 of 2: OLAP Design

An Introduction to PerformancePoint Services Part 1 of 2: OLAP Design

Introduction

SQL Server Analysis Services (SSAS) and PerformancePoint Services are tools in the Microsoft BI stack used for displaying data. Analysis Services allows analysts to investigate data quickly and dynamically without IT having to write queries. PerformancePoint Services displays high level Key Performance Indicators (KPIs) to executives to be viewed at a glance. These tools leverage the data warehouse to users who may not have technical expertise.

The first part of my two part article will focus on the creation of an Online Analytical Processing (OLAP) cube as well overview of what is required for the delivery of the solution as a whole. The second part will focus on the aesthetic side of displaying data via the dashboard.

What is required?

In order to get a PerformancePoint dashboard up and running off an Analysis Services Cube (using a relational database instead of a cube is an option but the advantages of using a cube include: faster aggregation of measure values, hierarchies of members, and KPIs) a cube will need to be created. SharePoint 2010 is required in order to create a dashboard using Dashboard Designer. Creating a Business Intelligence site in SharePoint will allow for the download of the Dashboard Designer and the ability to deploy the web parts created in the designer to the SharePoint site. Having SharePoint is also a great way to expose Business Intelligence to users; whether it is reports, PowerPivot models, or complicated Excel files. These components of business intelligence all would benefit by being viewed by analysts.

Creating a SQL Server Analysis Services Cube

Only use what is required in the dashboard in order to reduce the work effort. If the idea is to have data that can be used in a cube and then on a dashboard, not to design a Corporate Data Warehouse, in most cases the data model that is used should be straight forward. It would, of course, be ideal that the business will embrace a data warehouse and want to use it in ad-hoc reports etc. so in that case more time will have to be spent in order to ensure that all of the needs of the users are being met.

The following will describe how to create a simple cube for use later in the PerformancePoint dashboard. Open Business Intelligence Development Studio and create a new Analysis Services project. Create a Data Source to the data warehouse.

<!--[if !vml]--><!--[endif]-->

Create a Data Source View with all the tables that will be used to slice the data (a dimension) and select the tables that has all the metrics (the fact or measures).

Provided the relationships are set up correctly in accordance with data warehouse design standards, SSAS will be able to create the necessary data relationships and the Data Source View should resemble the data model.

Resist the temptation to format data in the Data Source View. It’s best practices to keep all of the data transformations in the ETL process that loads the data warehouse. This way all the format changes will be kept in a single place as opposed to spread out all over the solution.  Create a New Cube using the existing tables (the tables in the Data Source View).

<!--[if !vml]--><!--[endif]-->

Select the table where the metrics are located as the measure groups and then confirm the tables we use to slice the metrics are the dimensions that the Cube Wizard wants to create.

<!--[if !vml]--><!--[endif]-->

Editing a dimension will allow adding attributes from the data source view and creating hierarchies. It’s a good idea to keep dimensions as lean as possible, so only add attributes that are required.

<!--[if !vml]--><!--[endif]-->

Creating a hierarchy is way to improve how data is displayed. When a hierarchy is used the metrics can be aggregated at all levels. The date dimension is an example of a dimension where a hierarchy should be created. Profit can be displayed by year, then by quarter, then by week, and then by day. If the hierarchy is not created the metrics would still be visible at each level (year, quarter, week, and day) but the ability to drill-through (for example a year to a semester metric value) will not be available.

In order to refresh the data in the cube, it needs to be processed. Processing a cube is an art in itself. If the data being refreshed is small than processing doesn’t need to be optimized. It would be just as well to process the entire database; however, if the data set is large, further considerations need to be made. Deeper knowledge is required for regular processing configuration. There are a number of different options for processing each of the objects (database, cube, dimensions, etc.) and those options are documented extensively online. It’s a common to either process the cube using a SQL Server Agent Job (a scheduled operation) or embedding the cube processing task within the ETL that loads the data warehouse.

After the cube is processed it’s ready to be browsed. A number of different options exist to view the cube data. Excel, SQL Server Management Studios, Business Intelligence Development Studios, and Performance Point Services are only a few ways to expose the cube to analysts. If the cube is designed specifically for the dashboard then it makes sense to only allow access to the dashboard users through the dashboard. If the data warehouse was made to serve the entire enterprise, it makes sense to give every access through their Windows Authentication. SSAS offers a great deal of role based security that is customizable to the row level (a role can be designed that makes the profit total of only the bank the user work for visible while the total for all banks would not be visible).

Conclusion

This has been a quick overview of building a cube in SQL Server Analysis Services. Although the finer points of the cube haven’t been discussed, it’s enough to create a simple cube that can be used for a PerformancePoint Services dashboard. To reiterate the cube will provide us with ad-hoc query ability, fast analysis through Excel, and a foundation for creating a PerformancePoint Services Dashboard. We’ll look closer at PerformancePoint in the next part of this two part article. A lot of value has been created by the creation of a cube and a dashboard will leverage the data even further.

Follow @Ideaca