Business Intelligence Semantic Model – Creating Your First Tabular Model Project – Part 2 of 2

This blog continues from where I left off in Part 1 – “Creating Your First Tabular Model”.

As covered in part 1 you'll need to meet the following requirements in order to create your first new tabular model project:

1. Have an instance of SQL Server 2012 running in Tabular mode.
2. Have SQL Server Data Tools (SSDT) – installed as part of SQL Server 2012.

Microsoft provides different sample databases – the one I use in this blog is AdventureWorksDW2012 and can be found at http://msftdbprodsamples.codeplex.com/releases/view/55330.

In part 1 of the blog series we created and deployed our tabular model using SQL Server Data Tools, and we created a simple report using Power View within Excel.

It’s a simple report. A very simple report. As promised in the last blog, I’m going to show you how to enhance it with some created measures, and you’ll learn how to make your report more visually striking using different styles within Power View.

Let’s start by creating some measures in the tabular model.

The Data Analysis Expression language (DAX) can be used to create measures and other custom formulas that you use in tabular models. In tabular models, a measure is a calculation created using a DAX formula for use in a reporting client. Unlike a calculated column, a measure is not evaluated at the row level. A measure is evaluated based on a filter or slicer that the user applies in the reporting client.

Keep in mind that if you build a tabular model and deploy it in DirectQuery mode, there are some restrictions on the formulas that you can use. Not all DAX functions are supported in DirectQuery mode. For more information on these restrictions, see http://technet.microsoft.com/en-us/library/hh213006.aspx.

Measures can be based on standard aggregation functions, such as DISTINCT COUNT, COUNT or SUM, or you can define your own formula by using DAX. In addition to the formula, each measure has properties, defined by the measure data type, such as Name, Table Detail, Format, and Decimal Places.

When measures have been defined in a model, users can add them to a report or PivotTable. Depending on perspectives and roles, measures appear in the Field List with the associated table, and are available to all users of the model. Measures are usually created in Fact tables; however, measures can be independent of the table it is associated with.

Adding a Calculated Column

It is important to understand the fundamental differences between a calculated column and a measure. In a calculated column, a formula evaluates to a value for each row in the column. For example, in the Internet Sales table, we’re going to add a calculated column named TotalProfit. In the tabular model, while in data view, select the Internet Sales tab and scroll all the way to the right end of the table until you see “Add Column”. Right-click on it (when in doubt, always right-click on objects to find out what you can do with them!):

By clicking on “Insert Column”, a new column is created. Its default name is CalculatedColumn1. By right-clicking on the heading, the following options are presented:

Select the “Rename Column” option and enter TotalProfit as the new column name. Now we’ll add a calculation to it. To do this, the column should be selected by clicking on its name; this places a black border around the entire column and displays a set of boxes above the table to enter the formula.

Clicking on the two downward pointing arrows to the right of the formula box expands the formula box for lengthy formulas. Our formula is simple so we’ll leave it as is.

We are going to create a formula that will subtract the product cost from the price to arrive at the total profit. Start by typing an “=” in the formula box, then scroll to- and click on the UnitPrice column to add the name of the column to our formula:

We’ll complete the formula by adding a minus sign, scrolling to, and clicking on the ProductStandardCost column to add it to our formula. Once the Enter key is pressed, the formula is complete and the results are displayed for each row in the TotalProfit column:

The TotalProfit calculated column can then be used in a reporting client just as any other column.

Adding a Measure

In contrast to a calculated column, a measure evaluates to a value based on a user selection; a filter context set in a PivotTable. Let’s create a measure in the Internet Sales table.

There are two sections of the table. The top section is filled with the table’s data. Each table has a measure grid. The measure grid is shown below the data and is separated by a grey bar. This grey bar can be moved up or down to display either more of the measure grid or more data. You can place the measures in any of the boxes in the bottom section.

We are going to create a measure to sum up the total profit and call it “Sum of Total Profit”. Logical names are desirable – this will make your different measures self-explanatory when being used in reports.

By clicking on the first cell in the measure grid’s first column, we’ll see our formula box appear above the table as it did when we were creating the calculated column’s formula. Start by typing the name of the measure (you’ll see it appear in the formula box) and follow it with a colon and an equal sign. If you don’t type in a name and start your measure’s formula with an equal sign, the measure will be assigned a default name, like “Measure1” which you can edit subsequently.

If you’re unsure what formula you want to use, click on the “fx” next to the formula box and you’ll see the available formulas along with a brief explanation of each.

We’ll select “SUM” from this list but we could have just typed “SUM(“ into our formula box. Scroll over to the TotalProfit column and click on it to place it in our formula. Add a closing parenthesis, hit the enter key and your measure has been created in the selected cell in the bottom section. Hover over that cell and you will see the result of the formula:

We want to make sure that the Sum of Total Profit measure will display in currency format. Click on the cell holding the formula and you will see its properties in the properties section at the right of the screen:

Select currency format and two more properties appear: Currency Symbol and Decimal places. We will keep the defaults of “$” and 2.

This is the sum of the entire TotalProfit column but the benefit of creating a measure is that it can be filtered in a report by using a slicer. For instance, we may want to see the Sum of Total Profit for each Product Category. In our report, we would select Sum of Total Profit and add the Product Category field as the slicer.

You can also add a measure using a standard aggregation function by clicking on a column, then clicking on the AutoSum button (∑) on the toolbar, and then clicking on a standard aggregation function. We’re going to get a distinct count of customers that placed orders using this method:

By selecting DistinctCount and hitting the enter key, the measure is created and placed under its respective column:

By using the AutoSum method to add an Average of ExtendedAmount measure, the format was automatically set to the same format of the ExtendedAmount column so I didn’t have to change the format to have it display in currency.

Making Use of Your Measures

Now that we have some measures added, let’s re-deploy and make use of them in our report!

We will open our previous report and click on Refresh in the Power View tab. When we expand the fields for the table Internet Sales, we can now see the measures that we added to this table:

Before we do anything else, let’s make our original view a bit more visually appealing. First of all, looking at the order quantities for all the model names in one chart would be too crowded. We’re going to just look at the Sales for tires. One way to do this is by dragging the ModelName field from under the Power View Fields area to the Filters area. Or you could also simply click on “Table” in the Filters area and all the columns are displayed:

For this chart, we are going to look at the model names for tires. Clicking on (All) under ModelName presents us with a checkbox list of all the ModelNames. We could go through this list and find all the model names for tires, clicking on them individually but this is a long list and thus quite a bit of tedious work.

Instead, to the right of the field, you’ll see a small box with a right-pointing arrow in it. Click on this box and you will be able to utilize the Advanced Filter Mode where you can specify what you want to see. In our case, we want to see only tires and not tire tubes, so we make selections for the value to contain “Tire” and does not contain “Tube”:

List view:

Advanced Filter View:

Click on “apply filter”, and you will see our table only shows the order quantities for tires:

Now, Let’s Add Some Style!

Click the table and click the Design tab in the ribbon. Select the pie chart by clicking the arrow on the Other Chart button. Since the numbers don’t vary widely, the differences don’t really show up that well on a pie chart.

By clicking on the column chart arrow and selecting the stacked or clustered chart you can change it from a pie chart to a column chart. In order to see the difference between the two types of charts, I right clicked on the table, copied, pasted and moved the pasted table below the original and changed it to a clustered column chart. Same data but different ways to illustrate it:

There are also all types of options to change the look of your report. There are themes that you can use to change the overall look of your report. You can also change fonts, backgrounds, images, and much more. I selected a different theme and background from the Power View tab and, from the Layout tab, I removed the tables’ individual titles and moved the legend for the pie chart to the left:

Now, let’s change that pie chart to utilize some of the measures we created in our tabular model. We’ll remove the order quantity by unclicking its checkbox under fields and add the Sum of Total Profit in its place. I didn’t like the default titles for each chart so I inserted text boxes with my own names for the two charts and positioned them above each. I also reduced the size of the pie chart to make the legend closer to the chart and centered it in the report.

Lastly, for a bit of Power View magic, if you click on a section of the pie chart above or a column in the column chart, all the other sections in both charts are transparently masked to make the clicked sections stand out:

These are still pretty simple reports but illustrate a small portion of the options available when creating your own reports.

Creating comprehensive and visually appealing reports is easy to do with BI Semantic Models and Power View reports!

Feel free to reach out should you have any questions.

Written by:
Debi Johnson, BI Developer, FMT

Posted by: Jakob Bechgaard