Customized VSTS/TFS Reporting Part IIb - Using the OLAP database

In my last article on this subject, I detailed how you could use the Report Wizard to create a simple report that detailed information about WorkItems, such as the number of state changes and revision summarized on a per project basis. The issue with that approach is that the information stored in the Data Warehouse is really designed to support the more efficient OLAP database for this type of reporting. In a future article on this subject I'll dig into the structure of the databases and discuss which one is used for a given purpose, but I wanted to take a moment and illustrate it here. Generally speaking, most of the customized reporting that deals with summarized data will come from the OLAP database as opposed to the data warehouse.

With this in mind, let's go ahead and construct the same report, but using the OLAP cube as a data source. If you still have your project open from the last exercise, you should have a screen that looks something like this:

To add a new report, right-click on the Reports folder and select Add New Report as shown below:

This will start the now familiar Report Wizard as shown here:

Click Next to bring up the Data Source panel. Create a new Shared Data Source as shown below:

Select "New Data Source", give it a name (I used TFSWarehouseOLAP) and select Microsoft SQL Server Analysis Services as the Type. Select Edit to get to the Data Source Connection Properties editor as shown below:

Type the name of your TFS Database server, choose the TfsWarehouse database, and select Test Connection to ensure that it all works. (Remember that this is an OLAP connection, so even though the name of the database is TfsWarehouse, you are not talking to the SQL Server database engine, you're talking to Analysis Services.) then choose OK. Select Next to move to the Query Designer screen as shown below.

On this screen you can either input a valid MDX query, or use the query builder to construct it for you. (Word of caution here, the Query Builder doesn't do the best job of creating MDX, as you might notice in a few screens) Choose the Query Builder button to load the MDX Query Builder as shown below:

This window is fairly straightforward to use if you've done any work with Analysis Services before, and very convoluted if you've never done so. Basically there are 4 main portions to this screen; The upper left details the cube structure as you can work with it. By default, it selects the first perspective (alphabetically) and displays the metadata contained within that perspective. In the case of the screen above, it's the Build Perspective and associated measures and dimensions. Since we are going to work with WorkItems (specifically WorkItem History) we will change the perspective. Click the ellipsis (...) next to the Build perspective in the upper left and then select Work Item History as shown below:

(By the way, a saavy ready might notice that the screen above tells you to select a "Cube" and I'm telling you to select a "Perspective" -- There is only 1 cube in the TfsWarehouse OLAP database, but there are multiple perspectives. You can think of a perspective as a logical view of a cube - and most simplistic tools like this treat them as the same thing)

Click OK to return to the Query Builder, but with the "correct" cube perspective selected this time as shown below:

The left hand side of this screen shows the structure of the Work Item History perspective. There are Measures, which provide the details of our analysis (these are the things that we can calculate and use), Key Performance Indicators (KPIs) which tell us if we're doing good or not, and Dimensions, which are the things that describe our analysis. The upper right-hand side is a filter area that allows us to filter information that will be provided in the result set, and the lower right hand side is essentially a pivot table, this is where we drop our measures and dimensions to build the analysis. To Start, open the Measures tree item and drop both Revision Count and State Change Count measures to the details area, then open the Team Project Dimension and drag the Project member to the pane just to the left of the measures. Then in the upper section, select the Team Project dimension and select all non-deleted projects in the filter expression as shown below:

Click on OK in the Filter expression dialog and OK in the Query builder to return to the Query string dialog as shown below:

(now you see what I mean about the MDX builder)

Click Next and select a Tabular report type (Normally we would use a Matrix report for OLAP data, but this is a simple example so we'll stick with simple report types) as shown below:

Select Next to bring up the table designer as shown below:

Move the Team_Project to the Group section, and the Revision Count and State Change Count fields to the Details section.

Select Next and choose your preferred report layout (I prefer block, but this is purely a personal preference)

Then choose the style that you will use (I prefer Corporate, but again, it's a personal choice)

Select Next to bring up the summary dialog

Name the report, and save it. This will return you to the Visual Studio window, where you can preview the report or make modifications to the layout (which you will need to do in this case if you followed my directions, because the columns will not quite be wide enough to support the fields)

And there we have it, a report that pretty much mimics the report we made in the last article. The point here is that we can use either the relational data warehouse or the OLAP cube to provide very meaningful information in a very short period of time. In the next article, I'll start digging in to the structure of the databases to help understand what data is available and how to use it.