Building an Ideal Tabular Model for Power View reports – Part 2

In this article, we will first compare the PowerPivot and Tabular models, which will help you choose between these two models for your scenarios, and then we will study the reporting properties in a Tabular Model that you can configure to build an Ideal model for Power View reports. If you want to use the sample we’ve provided, please see the “Software Requirements” section at the end of the article.

PowerPivot Model vs. Tabular Model

Before we begin discussing the techniques which will help you create the ideal Tabular Models for Power View reports, let’s compare the PowerPivot and Tabular Models:

In simple terms, a Tabular model is the “Server side version” of a PowerPivot model. But let’s first understand the idea behind having both PowerPivot Models and Tabular Models. Understanding Microsoft’s vision will help you understand the strengths of these models, and how to leverage these technologies in your organization.

We’ll first need to briefly touch upon the concepts of Personal BI, Team BI and Organizational BI, along with Microsoft Tools that addresses the need of the segments; we’ll also introduce a concept “Business Intelligence Semantic Model (BISM)” which encapsulates, but is ?not limited to, the PowerPivot and Tabular Model.

So let’s say a business user in your organization creates his/her own model, using the PowerPivot plugin for Excel 2010. This generally falls in the realm of Personal BI. If this business user decides to share it with other team members by deploying the PowerPivot Model to SharePoint, then it will become known as a Team BI model. If this model is a success, and the IT pros decide to upgrade it to a Tabular Model, where the model resides on a server and is available to be used by business users via various client reporting tools (including Power View), – this is generally referred to as Organizational BI. The other advantages of Organizational BI are that IT pros can efficiently allocate resources based on demand, and also that, since it’s a centralized model, it provides a single version of the facts.

You may have heard the following before, but it’s worth repeating:

1. You do not need to start with a Personal BI model; you can go straight to the Organizational BI level.
2. And it’s not necessarily a Business User who builds the model for Team BI – it’s possible that BI/IT pros decide to create a PowerPivot model, like we did in Part 1, and deploy it to SharePoint so that business users can access the model. In the context of Power View, if IT pros upload an (ideal) PowerPivot model to SharePoint, business users can then perform data exploration and create rich visualization by using Power View

As you can see in the previous graphic, there is a path for going straight from Personal BI to Organizational BI, and this is made possible by SQL Server Analysis Services (SSAS) 2012, because of the new Business Intelligence Semantic Model (BISM). It encapsulates (but is not limited to) Tabular Models, PowerPivot Models and Multidimensional Models, and allows business users to create their own model without knowing anything about the Multidimensional world. Thus, BISM is one possible model for all end-user experiences.
If you want to know more about the new Business Intelligence Semantic Model (BISM) please click on the following link: http://blogs.msdn.com/b/analysisservices/archive/2011/05/16/analysis-services-vision-amp-roadmap-update.aspx

Now you understand the vision, let’s look at the technical details of how a Tabular Model is better than a PowerPivot model:

The table above shows you which of the two – Tabular model or PowerPivot model – will best suit your scenarios. But remember that, even though PowerPivot and Tabular Models are meant to address different scenarios, they share the same core.
Now let’s see how you can configure all the reporting properties that will help you create ideal tabular models for power view reports. In this section of the article, we’ll cover the following topics:
1) The various reporting properties of Tabular Models that helps us build an ideal model for Power
View reports.
2) Creating a Tabular Model from an existing PowerPivot model and verifying its reporting properties.
Many of the reporting properties we are going to explain have already been explained in Part 1. This fact just emphasizes that Tabular and PowerPivot are similar – after all, they have the same underlying model, i.e. the Business Intelligence Semantic Model.

Reporting properties in Tabular Model

First, let’s summarize the reporting properties, and then learn how to configure them:

Building an ideal model involves more than just setting the above reporting properties. As we discussed in Part 1, there are other steps that go into this process after the data sources have been identified:

• Use user-friendly field names.
• Configure Data Type and Data Format for fields.
• Create calculated columns and calculated measures to facilitate advanced data analysis.
• Sort the values in the fields that depend upon the values in another field. In part 1, we sorted the
  values in the “Quarter Year” field by the values in the “Calendar Year” field.
• If applicable, mark the appropriate table as Date Table, and configure Date Table settings
• Configure the “Summarize By” property of a field to over-ride the default aggregate behavior, which
  is addition (SUM).

Creating a Tabular Model from an existing PowerPivot model

Now let’s create a Tabular Model from an existing PowerPivot model, with two aims in mind:

1. To demonstrate that an existing PowerPivot model can be “upgraded” to a Tabular Model
2. To learn how to set the reporting properties of a Tabular Model, which will help you to build an
   ideal model for Power View reports

You can download the PowerPivot model that we worked on in the first article by clicking on the following link: http://www.solidq.com/JournalAssets/Adventure-Works-PowerPivotModel-2.zip

Now let’s import this PowerPivot model into a Tabular Project.
SSDT > File > NEW > Project > Templates > Business Intelligence > Analysis Services > Import from PowerPivot

Now verify that the reporting properties are set.

1) Hide from client Tools

Do not hide the “product” table from client tools. That was just for the purpose of illustration.

You can switch between Grid View and Diagram View by using either the small icons located at the bottom right of your model editor or the Toolbar: Model, Model view, select Data View or Diagram view.

Your Task: Verify that the Product Category and Product Subcategory tables are hidden from client tools

You can now also hide columns in a table: go to Product Table and select the product id column. You can see that currently it is grayed out and that means that it is “hidden from client tools”. You can right click the column and see an option to hide/unhide this column from client tools. Do not change the current setting for now.

Alternatively, in the Properties Pane of “Product Id” column, you’ll see that the “Hidden” property is set. If you are not able to see the properties pane, press F4 or go to view and select properties window.

Set the “Hidden” Property to true if you want to hide the column from client tools.

You may have noticed there is a “Reporting Properties” section. We’ll get to that in a moment.

Earlier in the article, we said that building an ideal model involves other steps like configuring the Data Format, Data Type, user friendly names, Sort by column – and you can see in Figure 6 that you can set these properties in the “Basic” section of the properties for a column.

Your Task: Verify that the Product Alternate Id and Customer Id columns in the ‘Internet Sales’ Table are Hidden from Client Tools
2) Image URL
Let’s verify that the “Image URL” property of the Product Image column in Product Table is set to True.

Go to Product Table and select the Product Image column.

In the Properties pane, you’ll see that the “Image URL” reporting property is set to True

3) Default Field Set
Let’s verify the Default Field Set for the Product Table.

Select the Product Table. In the properties pane, you’ll see a reporting property called Default Field Set, select the property and click on the “…” box to edit it.

After you have clicked to edit the property, you’ll see the Default Field Set Dialog Box:

You can add or remove columns from the Default Field Set.

Note the Order of the Default Field Set here. You can edit the order here or you can set the property called “Table Detail Position” for a default field.

Click on OK, and let’s see how we can set the “Table Detail Position” of a field.

In the product table, select the Product Name column.

In the properties pane, you’ll see that the “Table Detail Position” of Product Name column is set to 0

Your Task: Verify the “Table Detail Position” Reporting property of the Product Image column and List price column in the Product Table.
4) Table Behavior
Now, let’s verify the “Table Behavior” reporting Property.

Select Product Table in the properties pane. You’ll see the “Table Behavior” Reporting property. Select it and click on the “…” button to edit the property and you’ll then see the “Table Behavior” Dialog box:

Here we can verify the following information:

After verifying it, click on the OK button and close the dialog box.

So we have seen how we can configure and/or verify the reporting properties in the Tabular model.

Now, you can also enrich the model by adding meaningful calculated columns and calculated measures. We’ve already seen a demo of this in Part 1 and so are not going to repeat that part again. For your scenario, there’s a chance that the PowerPivot model was built by a business user who may not have included the calculated columns and calculated measures which facilitate advanced data analysis. So while upgrading it to Tabular Model, as an IT pro, it’s your role to write DAX formulas for adding calculated columns and calculated measures to enrich the model. Note that if your Power Pivot Model has calculated measures/columns, then they will also be imported into the Tabular Model.

Now, after verifying/configuring the reporting properties, deploy the model to your Dev/Test environment and test it by creating a BISM connection in our SharePoint 2010 Library that points to this model and thus, by building a Tabular Model, you have empowered your end-users to create reports for themselves in Power View!

Conclusion:

In this article, we have seen how to set the reporting properties for a Tabular Model that helps you build an ideal tabular model.

In this two-part series, we have covered the following topics:

• Microsoft’s overall vision and the difference between a PowerPivot Model and a Tabular Model.
• We saw an example of how calculated columns and measures can enrich the model.
• And for both Tabular Models and PowerPivot models, we saw how to set the following reporting
  properties:
  • Hide from Client Tools
  • Image URL
  • Default Field Set
  • Table Behavior
  • Sort by column
  • Summarize by
  • Data Types and Data formats

Here are some images that summarize what we have learned in these articles:

1) Reporting Property: Hide from Client Tools

2) Reporting Property: Image URL

3) Reporting Property: Table Behavior

4) Adding Calculated Columns and Calculated Measures

Software Requirements

If you want to follow the examples by using the sample that we provide, you’ll need Microsoft SQL Server 2012 SSAS Tabular instance running in your Testing/Production environment

• Microsoft SQL Server 2012 Data Tools
• BI Features for SharePoint 2010 (SSRS 2012 integrated with SharePoint)

You do not need a SQL Server 2012 SSAS instance when you are developing a PowerPivot Model. And note that in this article, we have demonstrated SQL Server Data Tools (SSDT), a Tool that’s used to edit Tabular Models (and set reporting properties) in a Development Environment. Excel does not support editing a Tabular Model; it only supports editing PowerPivot models.
Also please see the official deployment checklist: http://msdn.microsoft.com/en-us/library/hh231687.aspx.

Resources:

Building your first Analysis Services Tabular BI Semantic model with SQL Server 2012:
http://technet.microsoft.com/en-us/video/building-your-first-analysis-services-tabular-bi-semantic-model-with-sql-server-2012.aspx

Analysis Services – Vision & Roadmap Update:
http://blogs.msdn.com/b/analysisservices/archive/2011/05/16/analysis-services-vision-amp-roadmap-update.aspx

An Introduction to Power View in SQL Server 2012

When to use choose tabular models over PowerPivot Models:
http://blogs.msdn.com/b/cathyk/archive/2011/10/27/when-to-choose-tabular-models-over-powerpivot-models.aspx
PowerPivot BI Semantic Model Connection (.bism): http://technet.microsoft.com/en-us/library/gg471575

Analysis Services Vision and Roadmap Update:
http://blogs.msdn.com/b/analysisservices/archive/2011/05/16/analysis-services-vision-amp-roadmap-update.aspx