This article belongs to a two-part series on “Building Ideal Models for Power View reports”. We will study all the reporting properties in PowerPivot Model as well as in Tabular Model that you can configure to build an Ideal model for Power View reports. Part 1 is focused on PowerPivot model and Part 2 is focused on Tabular Model.
If you want an Introduction to Power View please refer to one of our previous articles.
I will encourage you to follow along with me setting these properties. In order to accomplish that, you’ll need Microsoft SQL Server Data Tools i.e. SSDT (formerly BIDS) on your machine and the Microsoft SQL Server 2012 PowerPivot for Excel 2010. These tools allow us to enrich our PowerPivot and Tabular Models in order to create better reports using Power View for SharePoint. Besides that, an installation of some BI Features in our Sharepoint 2010 Farm is required for using Power View. Please check the deployment checklist here: http://msdn.microsoft.com/en-us/library/hh231687.aspx. Note that the tools mentioned above are used to edit the model and set properties for reporting. And to edit a PowerPivot model in SSDT, you’ll need to import it into the BISM model first.
Before we begin working on our model, here is the checklist of steps that building a PowerPivot Model involves:
- You have Imported Data
- If required, you have renamed columns to user-friendly names.
- If required, You have created Relationship(s) between tables in your PowerPivot Model
- You have reviewed Data Types and formatting for fields in your Model.
- If applicable, you have marked the appropriate table as Date Table
- If applicable, you have created a column that has the URL (format: text) of the Images
- If applicable, you have built KPIs and Hierarchies
Now, for you to be able to follow along, you can download a PowerPivot Model here: Adventure-Works-PowerPivotModel. I have used AdventureWorksDW2012 as our Data Source for building a PowerPivot Model because it is popular and is publicly available for you to download! Here you can find the link: http://msftdbprodsamples.codeplex.com/releases/view/55330. After downloading the PowerPivot model sample and the AdventureWorksDW2012, please configure the PowerPivot model sample to use the AdventureWorksDW2012 as the data source. If you wish to work with the PowerPivot model of your choice then that is fine and I believe, you would be able to translate what we will learn in this article to your scenario.
As part 1 is focused on PowerPivot model, on the following section you can find a set of reporting properties and actions that goes into Building Ideal PowerPivot Model for Power View reports.
- Hide from Client Tools
- ImageURL
- Default Field Set
- Table Behavior
- Calculate Columns and Calculate measures
Hide from client tools
Question that you should be asking while building PowerPivot Model is if your End-users really need those columns/Tables while they are trying to “Explore” data. You do not want to overwhelm your End-User, Do you? So we could make their life easier by hiding non-useful columns for your end-user and here’s how you do it.
We are using AdventureWorks as our example here but you would need to translate these requirements to the environment that you are working with. In order to hide columns from client tools, go to your PowerPivot Window and select the column/Table that you wish to hide, Right Click, and select the “Hide from Client Tools” option. You can select multiple columns if you wish to.
Also for Hidden columns/tables, you can unhide from client tools them if you want. Note that the Hidden columns inside the model would appear in Grey background.
To understand what is Grid View and Diagram view, here is a short explanation on two PowerPivot Windows:
- Diagram View: It’s a view that helps you see the tables in a model in an organized manner. This helps developers to view the relationships, add/edit relationships and define hierarchies.
- Grid View: This view lets you view the data in a table that you’ve selected
You can switch between Grid View/Diagram view by selecting the small icons that are in the right bottom of the PowerPivot Windows Or also the icons in the Home Tab of the PowerPivot Window.
For our Model sample, these are the columns that we would hide from client Tools:
So “Hide from client tools” is a property that you can set to hide columns that your end-user would not require for their Data-Exploration.
Image URL
As you may know, Power View is a meant for visually exploring data and Rich Data visualization. Setting the Image URL property to allow end-users explore data via Images enhances their experience. Case in point, Figure 3 where you can see the difference between exploring data which is text-based and image-based. And as you can imagine, the experience of exploring data via meaningful images is much better.
If you have downloaded the PowerPivot Model sample then you need to configure the path first. Open the PowerPivot Model and go to the Product Table. Then select the Product Image column. Here replace inside the DAX formula the word
Also, you can download the AdventureWorksDW2012 Images here:http://msftdbprodsamples.codeplex.com/releases/view/55330. Upload them to your website under “Shared Documents/AdventureWorks2012Images/” folder or if you choose to upload it in different folder structure, update the path of the image in the Product Image column editing the DAX formula, as we said before.
Note that the image path should be valid in order for this to work.Even if the Path of the Image is valid, the image would not be shown in the Power View report because the client does not know that it’s an Image URL or a String. And so you need to explicitly specify that the column value is an Image URL. Follow these steps
Go to your PowerPivot Window and select Advanced Mode in the File Menu, and after that, inside the Reporting Properties section, check the Image URL box for the Product Image column present in the Product Table:
And that’s all you have to do to specify that the column contains an Image URL. Assuming that the Path is correct and accessible, your image would show up in the Power View report!
Default Field Set
This reporting property enables “One Click Tables” feature in our PowerPivot report. That means, rather than an individual field, a group of fields are added to a view in a PowerView when you click the Table Name.
This enhances end-user’s report creation experience because they do not have to manually add group of fields which are invariably required or add fields that are representative ones while creating Power View reports.
So for instance, in our model, let’s assume that in the Product Table, following fields are invariably needed for the reports:
Product Name, List Price, Product Image and Product Line
So to set these fields as Default Field set.
Go to your PowerPivot Window and select “Switch to Advanced Mode” in the File menu. Then, click on Default Field Set
Now, as you can see in the picture, we can select these fields:
Click on Ok when finished.
This is how you can select Default Field Sets.
Reporting Property: Table Behavior
As you can see in the above diagram, as soon as you set the properties called “Default Label” and “Default Image” under the Table Behavior Properties, they will be displayed prominently. In our case, Product Name is displayed notably in the Card visualization that I have shown in the above picture because we have chosen the Product Name column as our Default Label. Therefore, when you set the “Default Label” and “Default Image” properties, as seen in the above picture, you will enhance the Power View reports creation experience for your end-users.
To set the Table Behavior Property, go again to the Advanced Mode in your PowerPivot Window and select the “Table Behavior” icon under the Reporting Properties group.
Now, here you are supposed to configure 4 properties:
- Row Identifier: Select a Field that uniquely identifies each row in the table. Unless you select a Row-Identifier, the other properties stay grayed out. In our case, I have chosen Product Id as the Row Identifier of the table.
- Keep Unique Rows: By Default, if there are two same values in two different rows then PowerView would aggregate them. But if you set this property and the two same values have different identifiers then they will not be aggregated because, internally, Row Identifier acts as the grouping key. In our case, I have chosen Product Name field. You can choose multiple fields if you wish.
- Default Label: Choose a Field as Default Label. In our case, I chose Product Name as our Default Label
- Default Image: Optionally, Choose the default image field. In our case, I chose Product Image as our Default Image field.
Just to recap, when you define the default label and default image, in the card visualization – the label would act as the banner header and the image would be notably visible. You can see the difference after setting these reporting properties in Figure 7.
This is how you could set the Table Behavior property.
Also note that in Power View, the Default Label and Default Image are easily Identifiable by End-Users.
Calculateed columns and calculated measures
Calculated columns and calculated measures are not reporting properties but adding them can help you enrich the model and in this section we will see how you can do that.
First let’s see how calculated columns can help de-normalizing the model and simplifying the End-User Experience.
In our Model, we have Product, Product Category and Product Subcategory Tables. Let us de-normalize them.
In the product table, we are going to create some calculated columns based on following information:
To create calculated columns, open the Table in Grid View. Scroll right until you see “add column”. Rename the column and then click on formula box, enter the DAX formula and press enter. Create following calculated columns in the product table:
And after that, we could hide the whole Product Category and Product subcategory Tables using the option “Hide from client tools”. Doing that has simplified the experience for your end-users
Note that product subcategory and product category are greyed out and they will not be visible in the Power View because we hide it from client tools.
Secondly, let’s add Calculated Columns to create fields for Charts
Sometimes fields are not suitable for charts. Let’s take an example, in our model how would you chart each calendar quarters vs. Sales amount? Try it.
Based on our current model, you would not find an easy way to chart it. So Now let’s add a calculate column in the Date table:
Also here, you’ll need to set the “sort by” property. Select Quarter year column. Go to your PowerPivot Window and click on the Home tab. Then select the “Sort by Column” icon and choose the “Sort by column” option. On right side, select the column: Calendar Year. Click on OK.
Now let’s create a chart based on the updated model:
Note that the quarters are now sorted and this is because we have set the “sort by column” property earlier.
Let’s take few more examples to build an ideal model for our end-users by using calculated columns and calculated measures. Remember that when we add calculated columns and members in the model, it does not change the way Power View shows data but by adding calculated columns and members we are empowering our end-users to perform better data analysis and data exploration.
Calculated columns can also be used to extend the model and thus, eventually enrich it. So in our case, we are going to create some extra calculations. First of all, let’s create a calculated column in the Internet Sales table based on following information:
For fields, remember that by default SUM is the aggregate function. To change this we can go to advanced tab, select “Summarize by” and choose the aggregate function of your choice. We do not need to edit that for our model but this is something to keep in mind while building the ideal model. Remember that by default Power View would apply the aggregate function on columns having aggregate-able data type when these columns are listed in the field list area. But you can override this behavior by setting the “summarize by” property. Go to Advanced mode > Summarize by > select one out of default/sum/count/min/max/average/distinct count/do not summarize. Let’s an example, you create a measure “SUM([sales amount])” then you can also use the column [sales amount] in the model and both the column and the measure would give you same result. This is so because by default Power View has applied the SUM on this column and you could use it as a measure. To avoid the confusion, you’ll have to set the “summarize by” property to “do not summarize”. Also there can be a column “phone number” (with INT data type) on which you do not want Power View to automatically apply an aggregate function and so you’ll have to over-ride the default behavior by setting the summarize by property to “do not summarize”.
Now let’s create a Measure named Total Margin using the Formula:
And once it’s created, format it to currency. Steps: select the column, Go to Home > Formatting section > Set Format: currency and select $ English (United States) as the currency.
Calculated columns and calculated measures are also handy when you want to perform advanced data analysis. For example, if your end-user needs time-based calculations, then by calculated measures, you could include them in the model. For our model, let’s create a Total Margin QTD calculated measure:
And in case you want to describe what column is about to your end-users, you can set Tool-Tip by selecting the Table/Column, Right-click, and select Description. Here you can enter the Tool-Tip. I have entered a Description for the column Margin in the Internet Sales Table. So when you hover over the margin column, you will see the ToolTip:
So that’s how we can use calculated columns and calculated measures to enrich the PowerPivot model which is to be consumed by Power View.
Conclusion
In this article, we saw how to build an ideal PowerPivot Model for PowerView reports.
In next article, we’ll see reporting properties in Tabular Model (Using the SQL Server Data Tools) that enrich our model for the Power View consumption. We will also discuss when to choose PowerPivot model vs. Tabular Model which would help you choose between the two models for your scenario.
This two-part article series is inspired from the SQLPASS 2011 session “Building the Perfect BI Semantic Model for Crescent” by Kasper de Jonge and Carl Rabeler
