Bo is a Principal Consultant for ThreeWill. He has 18 years of full lifecycle software development experience.
If you have stood up SharePoint Server 2010 Enterprise, you have no doubt noticed that PerformancePoint is now a first class citizen. In MOSS 2007 there was integration, but it wasn’t the sort of integration that stares you in the face and begs to be taken for a spin. The improved integration and bundling will no doubt encourage more and more businesses to dip their toes into the business intelligence waters. But the water can be deep and the opposite shore can look dauntingly far away. So where do you start? My recommendation is to stick close to the shore at first, get your bearings and then work out your plan for crossing the ocean.
Staying Close To Shore
Starting with a proof of concept that leverages SharePoint lists is great way to get your feet wet. This is especially true if your company is not yet mature with full blown business intelligence solutions but does understand the power of custom lists in SharePoint. Even if you do have a robust helping of Analysis Service cubes to draw on for data, starting with SharePoint lists can still be a good way to get an idea of how you can organize your UI and establish how users will interact with it.
Some advantages of prototyping with SharePoint lists include:
- Allows for rapid creation and modification of your underlying data
- PerformancePoint manages pivoting the data for you and extracting your facts and dimensions
- You can easily plug your data into KPIs, build scorecards and create dashboards
- Your users can see a working solution without the time and expense of building a SSAS cube
Starting With What You Know
Whether you are a SharePoint administrator or developer, there may come a time when you are asked to dive into the business intelligence waters. Below is a brief overview of the approach I took as a SharePoint developer exploring these new found capabilities. If you are in need of some help with the deployment of PerformancePoint, there is a great PerformancePoint Services blog post that walks you all the way through to a Business Intelligence Center, which is where I begin.
First, you will need some data to work with. I started by creating two SharePoint lists that act as highly de-normalized data sources. My goal was to quickly create some data that could be displayed in PerformancePoint. The lists were very simple with essentially the same list schema to hold sales data and sales goals.
- Product (Single line of text)
- Product Category (Single line of text)
- Purchase Date (Date and Time)
- Product Cost (Number)
- Purchase Quantity (Number)
- Sales Amount Calculated (calculation based on other columns)
Naturally, you can create as many columns to describe your data as you need (I have used Product and Product Category). Make sure to leverage the SharePoint field types including single line text, numeric and date as these help PerformancePoint figure out the purpose of the data in a given column.
Going Into Deeper Waters
Once I created my SharePoint lists, it was time to launch Dashboard Designer. Dashboard Designer is the tool for managing your PerformancePoint content within SharePoint. I created a new data source using the SharePoint list template. On the Editor tab, I provided the SharePoint site and list information and chose to use an unattended service account. I could have selected the per-user identity, but I would have also needed to configure Kerberos.
On the Time tab, I chose my time dimension. The time dimension is important since you will most often want to see data broken out by month, quarter, year, etc. This is one place where PerformancePoint is trying to make sense of the field types from my list. Since Purchase Date was a date and time field, I selected it as my default time dimension and specified my time period levels.
Lastly, on the View tab, I adjusted the columns to suit my KPI and scorecard needs. PerformancePoint does a pretty good job at this already, but the option is there to change the column type. If you want to use it for slicing and dicing then select dimension and if you plan to aggregate data in it then select fact.
Time To Explore Further
With a useable data source created, it was time to really start tip-toeing out into PerformancePoint and set up my KPIs. The KPIs will be the building blocks for scorecards and dashboards.
I began by creating a new “Blank KPI” and naming it “Sales of Natural Things” since I planned to filter on the “Natural” product category. By default, a KPI has one actual and one target (goal), but it can have multiple actuals and targets. Clicking on the data mappings column allowed me to select my SharePoint list data source and choose my Sales Amount measure. Measures are created from my fact columns. I selected a dimension filter for product category and set my calculation to be sum of children. Sum of children allows for child items, such as products, to be summed for my KPI when I build my scorecard.
I recommend you do some exploring on the many other options available on a KPI including specifying numbers formats, deciding which visual indicators to use and providing thresholds for the icons of that indicator. This process can be tedious and time consuming. If you multi-select rows, you can actually bulk edit many of these settings. You can even copy and paste entire KPIs when you need to create similar KPIs.
To populate my new scorecard, I brought over my KPIs and dimensions from the tool pane on the left, making sure to stack them in the appropriate order. For some KPIs and dimensions I bumped them under others as children. I also changed the metric settings by right-clicking on my target cells and choosing the metric settings option. These settings allow me to modify how the indicator, variance, scoring and values are shown in this column. Finally, I spent a little time selecting rows and formatting the fonts and colors to break up the scorecard visually.
Admittedly, I haven’t done this phase of PerformancePoint justice, in part because it has so many options that it really requires a solid exploration to establish your bearings. I encourage you to spend a good amount of experimentation time here seeing the effects of different approaches to your scorecard. The exploration in this phase is why starting with simple SharePoint lists is so helpful. It allows you time to spend discovering your options with KPIs and scorecards before going full steam ahead.
Inviting Others Into The Water
Dashboards are the containers for your scorecards and other PerformancePoint content. They are how you get your hard work into your user’s hands so they can begin using what you have produced. Dashboards can be created using standard web part pages and there are sometimes good reasons to do this (covered in a future post).
For my prototype I used the standard dashboard available in Dashboard Designer. By dragging content (scorecards, reports or filters) onto a web part zone, I created a dashboard. I then gave my page a name and deployed it to the SharePoint dashboards library where end users could access it. The image below shows the scorecard after I expanded it to the details.
SharePoint 2010 coupled with the more tightly integrated PerformancePoint Server provides a lot of Business Intelligence features that your company or your clients will want to leverage. The SharePoint list data source provides a quick way to begin learning about the PeformancePoint KPIs, scorecards and dashboards and allows you to prototype solutions to help educate your business users about these new and powerful capabilities. And while KPIs and scorecards are not all that can make up this solution, they are a good start at dipping your toes in before going headlong into things like analytic grids and the decomposition tree which I will cover in an upcoming post.