The goal of this project is to rebuild, as faithfully as possible, the Tfs_Analysis cube in a tabular model for use with Power View or Excel or any other reporting tool which can connect to SQL Server Analysis Services.
You can download the Team Foundation Server Tabular Model from the Visual Studio gallery.
Note that Microsoft has announced that they are working on changing Power View so that it will work against an MDX model so in the future you will be able to use Power View to report on the Tfs_Analysis cube out of the box. It is important to remember that the current limitations of the cube will still exist in this scenario – and at the end of this post I hope to show you with an awesome example of what you can easily do using a tabular model.
This project also provides additional benefits that were also part of the goals for this project.
- Minutes and hours are not reported to the TFS Cube, using this model you can still perform calculations on these values (as we typically want to do when measuring progress of lean approaches)
- Performing calculations using DAX is much, much easier than using formulas in a multi-dimensional model
- It is much easier to create data marts for individual teams or groups of teams using this approach than trying to slice the cube
- TFS does not support security on the cube at the row level (Analysis Services supports it but TFS may run into problems if this type of security is in place so it is not supported). With the tabular model it is very easy to do (this functionality is not supported as part of PowerPivot but the Tabular model is more powerful and scalable)
- It is far easier to make changes in a tabular model than in a dimensional model
It is important to note that there are drawbacks to this approach (at this time) so you need to weight these against the benefits. The good thing to note though is that this can be used in parallel with the standard TFS Cube.
- When you add a new field to the work item type and you want to report on it, you have to update the model – it will not show up automatically.
- In TFS this is handled by how the data adapters read the warehouse and programmatically add fields to the cube – you can achieve this with a tabular model but that was not the goal of this implementation.
- Fields are displayed differently in Excel – they aren’t grouped into namespace folders – they are just a flat list of fields. In addition, if you have hierarchies, those appear at the root of a dimension and the remaining fields appear in the “More Fields…” folder
- Can you do some things with MDX that you can’t do with a Tabular model and DAX – yes. In particular the ability to scope calculations with a high degree of precision is not available. For this reason you will see some differences in the measures in the Tabular model than you would the multi-dimensional model. I will point these out – if someone knows a way around them, please let me know.
- Many-to-many relationships are not supported in tabular models. For this reason, one of the big items to report on is lost in the tabular model – the ability to report on test results for a given requirement (there is a way around this which I’ll talk about later but there is no direct way to report on it as you would with Excel and the multi-dimensional model).
Using this model
First, while this model will work out of the box for any of the MSF templates included with TFS. It has not been tested with the Scrum template. Second, not all of the fields may be included, so you may need to update the model to include various fields. The goal was to take the lowest common denominator and work off of that. Finally, there are some additional fields and calculations that are included to demonstrate concepts you can apply for your own needs. But not to worry, this will walk you through how to make changes.
Setting up to use the model
This is a SQL Server 2012 Data Tools model so you must have the SQL Server 2012 Data Tools installed and you must have a SQL Server 2012 Analysis Services server in Tabular Mode installed (somewhere – it does not have to be the same machine). Beyond that, there is nothing more needed to use this model. While this is not a tutorial on everything you can do with Power View and tabular modeling, I will cover enough for you to get going and I will point you to some additional references at the end of the series of posts. To get going, make sure you know the server that the Analysis Services is installed on, ensure that you know where the Tfs_Warehouse relational database is located and that you have an account to access both servers (read for the data warehouse, administrator on the analysis services instance).
To start off with, open the solution model. You will get an error message when you first open the model because it doesn’t know where the analysis services server is located in order to generate the workspace version of the model.
To fix this, follow these steps:
- Click OK
- In the Solution Explorer, right-click the Model.bim file and select Properties
- Enter the name of the Analysis Services server in the Workspace Server property field (Figure 1)
Once you put in a valid server you can then double-click the Model.bim file to bring up the designer (the first time it will take a little while as the schema is written to the Analysis Services server) in the Grid (as opposed to the Diagram) view.
At this point I’m going to skip a discussion of the individual tables for later (how the data gets into the tables, how the schema of the table is created and how relationships and hierarchical structures work) and get to how to set the data source so the model knows where your Tfs_Warehouse database lives and how to load data into the model. And after that I’ll show you how to schedule an update of the tabular model and then we’ll look at the individual tables and hierarchies and some of the limitations.
Pointing your model to the Tfs_Warehouse database
Once the model is open, select the Model > Existing Connections… item from the main menu in Visual Studio. The Existing Connections dialog is display in Figure 2.
Click the Edit button to display the Edit Connection dialog (Figure 3).
Change the server name to your database server which contains the Tfs_Warehouse database, set the log on information. Before selecting Test Connection, click the Impersonation… button to specify how the Analysis Services server connects to the Tfs_Warehouse database (Figure 4).
You will see a warning in the Error List window if you use the Service Account. However, since service accounts for SQL Servers are usually protected accounts (i.e. the password isn’t passed around a lot) and aren’t needed by designers of tabular models I consider this a better alternative. I’m sure someone can give me a reason why this isn’t so but if you desire, enter a separate windows account to connect to the Tfs_Warehouse. Make sure this account (either the service account that runs Analysis Services or the credentials you enter) have read access to the Tfs_Warehouse. You’ll need to re-enter this information again for another process later on. When you’re done, click OK on the Impersonation Information screen and click Test Connection on the Edit Connection screen then click Save to close the dialog.
At this point you are ready to work with the model. To verify that everything is working correctly, from the main menu in Visual Studio select Model > Process > Process All. This will display the Data Processing dialog with 32 items remaining. When all have processed successfully the Close button will become enabled (Figure 5).
If this does not process successfully (and there are many reasons why it would not process – mostly around working against a customized template) do the following:
- Click the Error Message column for the row, determine what the problem is and make the appropriate change. In my experience it is usually one of two problems:
- The user using VS does not have access to the analysis services server or the Tfs_Warehouse or
- The column does not exist
- For the first item, add the account as appropriate to the right location
- For the second item, select the table, view the properties and click the Source Data link.
- Depending on the table there are various options to make changes which will be discussed later
What this process does is to populate your workspace model – this does not populate any deployed model. Next I’ll talk about how to deploy the model.
Deploying a tabular model
Deploying a tabular model is pretty simple – right-click the Tab_Tfs_Analysis solution in the Solution Explorer and select Properties which displays the screen shown in Figure 6.
Set the server to the name of your tabular Analysis Services server that you want to deploy the model to and provide a database name and a cube name (if they are different from the default) and click OK. All done. Next, right-click the solution again and select Deploy. As with updating the local model, if any errors are present (there should not be if the local update of the model is successful), check the errors and correct any problems.
Updating the deployed tabular model from the Tfs_Warehouse
Once the model is deployed, you need to be able to pipe data into it on a regular basis. The TFS cube by default is updated once every two hours. This can obviously be changed. With the tabular model, what I have found so far (using fairly large data sets) is that you can update it every 30 minutes with really no impact to production or anything else. Part of this also depends on where your Tfs_Warehouse is and what type of system you are running it on but I certainly would not try to update it more than every 30 minutes.
To begin with, you need to create the script (which is an XML script that SQL Server will execute). The easiest way to do this is to open SQL Server Management Studio and connect to the analysis services tabular model (Figure 7).
You’ll note in this figure that my working model is on the same server as the deployed model – I don’t have a lot of servers to spare but obviously you should try to separate them out if possible (into different instances or different servers).
Right-click the database name and select Process Database. This displays the Process Database dialog shown in Figure 8.
Select the Scrip dropdown (highlighted above) and select Script Action to File. For this demonstration the file is called “ProcessTfsAnalysisModel.xmla”.
Next, connect to the database engine which contains the Tfs_Warehouse database. Expand the SQL Server Agent and right-click the Jobs node and select New Job (Figure 9).
Give the job a name, select the Steps tab and click New. This displays the step dialog shown in Figure 10.
Enter the step name and select SQL Server Analysis Services Command. Next, enter the server name and in the command section, select open and open the file saved in the previous step which will populate this field with the XML from the file then click OK.
Set the schedule by going to the Schedules tab and selecting New. Set this schedule according to your needs and click OK and then click OK again to finish creating the job. When you are finished, scroll down to the job, right-click it and select Start job at step… which will execute the job and leave you (hopefully) with the dialog in Figure 11.
At this point you can now update your model with the latest information from production. But what does the model contain and how do you make changes to it? I’ll cover this in the second part of this post which you can read here: Understanding the TFS Tabular Model.