By Ronald Barrett
Yet again another virtual lab title which had me thinking “That’s a mouthful”, but one must admit the idea of using Analysis Services in Office is in itself a great motivator for moving forward with this lab. Anytime you can move data from the SQL Server and into users’ hands via a product like Microsoft Excel, it’s bound to be a worthwhile thing to learn.
This Virtual Lab titled TechNet Virtual Lab: SQL Server 2008 Analysis Services- Delivering Analysis Services Capabilities through Microsoft Office will take about 30 to 45 minutes to complete (Microsoft says the lab is 45 min.), but if you already have a good understanding of pivot tables and Analysis Services you can move through it a bit quicker. This lab suggests you have familiarity with Excel functionality (in particular Excel 2007 and the new ribbon navigation bar). In addition, Microsoft suggests that you be familiar with Analysis Services concepts and terminology.
TIP: If you need some additional help with Analysis Services, there is a great article in the Windows IT Library for SQL 2005 that introduces both the changes in terminology from SQL 2000 and a good overview of terms you need to understand. Just follow the link above. It's worth the 10 minute read.
In this lab, we will go through three exercises:
1. Working with Analysis Services Data through Microsoft Excel 2007
2. Using OLAP Formulae in Excel
3. Adding a KPI to a SharePoint Web Page
Exercise 1
So let’s begin by looking at the demo of working with Analysis Services data through Excel. This exercise takes us through the process of connecting to Analysis Data, creating and browsing Pivot Tables and Pivot Charts, and adding KPI’s using an action in Pivot Tables and finally connecting a perspective.
As you go through the exercises, it becomes very apparent how easy it is to manipulate the data and drill down to see cross sections of information. This information is easily moved to a secondary worksheet and transformed into a Pivot Chart for a visual representation of the numbers, and just as easily, KPI’s can be added to track performance indicators.
Exercise 2
Next we look at Using OLAP formulae in Excel. Here we complete several tasks including, creating a connection to the Analysis Services Cube, creating a report by entering Formulae into workbook cells, sorting the data, and lastly, converting Pivot Tables to Formulas.
Once we have seen how to enter formulae into workbook cells, we look at how to convert Pivot Tables to formulas in Excel.
TIP: Do not ignore the request to save the Excel files in the Exercise 2. These files will be required for the last set of exercises.
Exercise 3
In our final exercise, we look at how to upload KPI data to a SharePoint Website. To do this we will need to perform the following tasks:
Upload an Office Data Connection file to a SharePoint site; Create a KPI List; Add a KPI to the KPI List; and for the final task, add a Web Part to a web page for display of the KPI List.
Once the data connection is established, the exercise walks us through setting up the KPI’s for the SharePoint Site.
Finally the lab ends with a view of the KPI in the SharePoint site.
This lab was a great way to either learn or refresh you on dealing with Pivot Tables, Formulas, and KPI’s within Excel. The Delivering Analysis Services Capabilities through Microsoft Office lab gives us a great look at how Analysis Services can be used to deliver reporting and analysis capabilities to end users using the very familiar Microsoft Excel spreadsheet software to empower users to create, edit and publish effective information for your organization.