Exploring Excel 2013 for BI Tip #16: Exposing “Values” from a Tabular Model

19 06 2014

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

From Power Pivot to SSAS Tabular

As companies move through the cycle of building Excel based solutions for business intelligence and analytics, they eventually end up with a SQL Server Analysis Services Tabular Model. The tabular model comes into play when you need more data in your model or want to support more granular security.

Up to this point, users have been happily using Power Pivot models in Excel to build their analysis solutions. However, once the model is deployed to tabular some functionality or interaction with the model changes in significant ways.

To summarize this point, power users or data modelers will create Power Pivot models in Excel. These models may or may not be deployed SharePoint, but they need to take them to the next level. You can migrate a Power Pivot model to tabular with ease by using the import option in SQL Server Data Tools.

image

Interacting with Power Pivot

I started by creating a simple Power Pivot model using Adventure Works DW data based on the Internet Sales fact table. I am using seven tables in my model as shown here.

image

I am not going to add any calculated measures to the model because Power Pivot allows me to use the data as it sets. Next we create a pivot table based on this model. I dropped the Fiscal Year onto rows and added OrderQuantity and ExtendedAmount to the values region. When OrderQuantity and ExtendedAmount are added to the pivot table, Excel defaults to a sum calculation when working with the data. Basically Excel creates the calculation for you based on what it knows about the data.

The point here is that I have data that can be used as values without doing any additional work with the model. I saved the workbook, closed Excel and moved on to the next step.

Interacting with Tabular

First we need to convert the Power Pivot model to a tabular model. Which is done by importing the model we just saved in SQL Server Data Tools. Once we have the project open, we need to deploy the model to a SSAS tabular instance so we can connect to it with Excel.

image

Now that it has been deployed to SSAS we can reopen our workbook and add a connection to the tabular model. In the field list we notice three differences now that the model is tabular.

1. The SUM symbol (sigma) is used to highlight values or measures that can be calculated.

2. The values we created in the Power Pivot model show up here.

3. In the Values section, “_No measures defined” is shown.

image

When working with multidimensional models, the Values section are represented the same. That makes sense as the connection that Excel is using is based on MDX not DAX. This significantly changes the user experience.

Let’s add a new measure to our Power Pivot model and try to do the same in the tabular model. We can still drop the DiscountAmount into the values section in our pivot table based on Power Pivot. However, when we try to do the same on tabular we get an error saying that we cannot add it to that area of the report.

image

In order for us to use DiscountAmount as a measure we will need to create an OLAP measure (See Excel Tip #8 for details) to use it in this Excel workbook or we will need to add it as a calculated measure in tabular and redeploy for it to be available.

What’s Happening

Because Excel treats a tabular model the same as a multidimensional model in SSAS you will need to add calculated measures for all measures you want to use as values in pivot tables in Excel. Multidimensional models are highly structured using the dimension and measure group techniques. While tabular “feels” like Power Pivot, to be used by Excel it needs to appear structured like multidimensional cubes.

Making this more interesting is that Excel uses MDX to communicate with tabular models, not DAX. As a result, we are able to use the OLAP tools in the PivotTable Tools ribbon.

image

This option is not available when working with Power Pivot models in Excel.

Impact to Users

Overall the impact to users, in particular power users and report builders, is that they have less “freedom” to design when using a tabular model. If they want to add more calculations, they need to be familiar with MDX. Furthermore, if they want the calculations to be generally available they need to work with IT to deploy updated models.

Hopefully we will see DAX supported interaction with SSAS in the future, but for the moment you need to understand how tabular and Power Pivot differ when using pivot tables in Excel.





Exploring Excel 2013 for BI Tip #15: Locking Slicer Position

18 06 2014

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

The Issue

A picture, or in this case two pictures, are worth a thousand words. I created a pivot table from Power Pivot and then added two slicers above the pivot table. The pivot table contains a date hierarchy which can be expanded and collapsed. During this process the slicer moves around which is not optimal when you are creating a visualization in Excel such as a dashboard. Here are the screenshots which highlight the issue.

How I set it up:

image

What happens when the date gets expanded:

image

How to Fix the Slicer Position

Right click the slicer you want to keep from moving, in my case that is the second one. I first looked in the settings, but saw nothing. I stumbled onto the Size and Properties option which opened the Format Slicer slide out menu. If you expand the Properties section select “Don’t move or size with cells” option, the slicer will no longer move.

image

This is just one more way to use slicers to improve the user experience in your Excel dashboards and reports.





Exploring Excel 2013 for BI Tip #14: Sparklines and Pivot Tables

7 01 2014

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Sparklines and Dashboards

There are a lot of visualization possibilities with Excel. When creating dashboards, sparklines are a good visualization of what happened over a data series. My goal was to add sparklines to a pivot table so it could be added to a dashboard. After many failed attempts, I was able to get the following to work.

On the INSERT tab, you will find the Sparklines options. In my pivot table I am going to add Line and Column Sparkline visualizations using the MyVote submission counts.

image

Here are the steps that I used to add this visualization to my pivot table.

First, I created a pivot table with Submission Count as the measure, the rows were the Poll Categories, and the columns are the quarters of the year. Here is what the original data looks like.

image

In this case, I kept the Grand Totals for both columns and rows turned on. I am going to use these areas as the targets for the sparklines. I am going to use lines for trends over time on the Grand Total column. Then I am going to use the column visualization to show the category distribution on Grand Total row.

Adding the Line Sparkline

To add the line sparkline, select all of the data cells (no grand totals). Next, select the Line Sparkline option. This will open the Create Sparklines dialog. In the dialog, you can see the Data Range is already populated with the highlighted cells. The Location Range is empty as shown below.

image

Next, you select the columns in the Grand Total column, and that cell range will be added to the Location Range field. This will put the sparklines in those columns and they will match the data trend. For clarity, the final step would be to change the column name to “Trend” and change the font color to white so the text is not seen. Here is the result.

image

Adding the Column Sparkline

Next up, we will add the Column Sparkline. Highlight the same cells as before. Once the cells have been highlighted, select the Column Sparkline option. Select the Grand Total row for the location. This will show the distribution within the quarter for the categories. Changing the font to white does not hide the value in this case. I actually reduced the font size to 1 to make it nearly invisible. (There is no transparent font available.) Here is the result.

image

I also added lower right corner by selecting the Grand Total column cells as the data and that cell as the location to get a consistent look at distribution. One other note, the Grand Total row is called “Trend” as well because they have to have the same name. But, overall, this was the look I was working toward.

Limitations and Nuances with Sparklines

Now for the stuff that doesn’t work as you would like. Sparklines are technically not part of the pivot table. As a result, the table needs to be static in shape. This means rows and columns need to stay the same in count and position.

I am going to add a category slicer to my example. When I select the Entertainment category, all of the sparklines are “stranded” in space. Quarter 2 disappears because it has no data and as a result the trendlines are no longer in the table. This is also true for the columns as four categories are eliminated by the filter. Worse yet, if you look at the filter, you will notice we have no poll submissions in the News category. When that is added the sparklines will end up in the last data row as opposed to the Grand Total sections.

image

Sparklines are a nice tool to have, but you need to understand what is the best way to use them in the context of what you are doing.

Reference and Credit

I ran across this during my search for how sparklines work in pivot tables: http://answers.microsoft.com/en-us/office/forum/office_2010-excel/how-do-you-insert-a-sparkline-into-a-pivot-table/e072570d-b367-41f1-b2d6-2dbe939db311.  As I note with the limitations to my solution, the forum post above calls out some alternatives which allow for more dynamic approaches, but they also involve coding. Furthermore, the comment from Andrew Lavinsky (MVP) confirmed that this was possible and that it is supported in SharePoint Excel Services.





Exploring Excel 2013 for BI Tip #13: Connecting Slicers in Pivot Tables Sourced from a Power Pivot Model

17 12 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

The Issue

At Magenic’s Code Mastery event in Boston, Stevo Smocilac and I were presenting on Power BI and Tabular Models. After our presentations, one of the attendees presented an issue to us about how he was unable to get a slicer to filter two separate pivot tables in his workbook. This will be the third tip on slicers as we investigate this issue to find a resolution. (Tip #4: Adding a Slicer and Tip #5: Cleaning Up Slicers)

To reproduce the issue, you will need to create a Power Pivot model with two related tables. In neither case will we have calculations. The primary table in my illustration is the Poll table with two fields PollID and PollQuestion. Create a flattened pivot table from the Power Pivot window as shown below.

image

Now that I have the Power Pivot connection and the pivot table ready to design in the workbook, I added the PollID and PollQuestion fields to the pivot table. Next, turn off subtotals for all fields. The next step is to add the slicer for the PollID.

Once all this is complete, we need to add another pivot table. In my case, I can use the PollOption table which has each of the valid options for each poll, essentially a clean one-to-many relationship that was built into the Power Pivot model. I added the next pivot table to the worksheet using the following steps:

1. From the Insert tab on the ribbon, select Pivot Table

image

2. In the dialog that opens, select “Use an external data source” and click “Choose Connection.”

image

3. Select the Power Pivot model as your source and click Open. Then select OK on the next dialog. This will result in another pivot table which shares the Power Pivot model as the source.

image

Once you have the new pivot table add the columns from the related table. In my worksheet I selected the PollID and OptionText from the Poll Option table. The final step to reproduce the issue is to relate the existing slicer to the the new pivot table. This is done by right-clicking the slicer box and choosing the Report Connections option. From here you can add the new pivot table to the Slicer connections.

Now that this is set up, when you click a slicer option, it filters the first table, but not the second as shown here:

image

As you can see the first table filtered correctly, but not the second.

Solving the Issue

It turns out the solution is fairly straightforward, but not something you would normally think of. If you add an aggregation to the related table, Poll Option, then the slicer works just fine.

image

It does not seem to matter what is aggregated, but an aggregation is required. While I was unable to find any reason why, my best guess is that this is related to the fact that pivot tables still communicate with Power Pivot models using MDX. I have seen this happen when working with multidimensional models where you needed a value in order to get the slicer or filter to work correctly, even in MDX itself.

While this solves the problem, we are now left with a column we did not want on the pivot table. It would appear that the only option we have to make the column not appear is to hide the column. However you will need to tread carefully here as it hides the column for all tables below as well.





Exploring Excel 2013 for BI Tip #12: Icon Sets

3 12 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Using Icon Sets

So far we have covered the data bar and color scale options in conditional formatting. Up next is Icon Sets. This conditional formatting option can be very valuable when creating dashboards using Excel.

image_thumb1_thumb

Icons in Brief

Excel has a number of icon sets that can be used to visualize status and trends. These are organized in Excel into groups based on their usage as shown in the image below.

image

Your specific use case including the data measured and the message to communicate will determine which icon set you choose. Often icon sets are used in scorecards and with key performance indicators (KPIs).

Creating a KPI with an Icon Set

When making a choice on icons, it is important to keep in mind what you want to communicate and who your audience is. In the following example we will look at setting up a KPI or status icon set using the tri-color, differing shape set.

image

This is a good choice to represent data that can be shown as “good”, “warning”, or “bad” status.

Using shapes as well as colors is particularly helpful if you have color blind users.

In my example, I am using a pivot table using Power Pivot data. I have a series of poll questions and the number of submissions. Let’s start with the default implementation. First, click into the cell with the value (in my case, Submission Count). Next, select Conditional Formatting > Icon Sets > <<select the shape noted above>>. This will put an indicator in the field as noted below.

image

As you can see only the field selected has the icon set. What we need to do is apply the formatting to the entire column. That is done by clicking in the cell and then clicking the formatting button that pops up by the cell.

image

This button will show the following selections for applying formatting rules:

  • Selected Cells (currently selected)
  • All cells showing “Submission Count” values
  • All cells showing “Submission Count” values for “PollQuestion”

Obviously, you would see your value column and your reference column names. If you have nested reference columns (e.g. a poll type) you would want to select the third option so it only was measured against the values in scope with the initial reference column. The second option will apply the formatting to the entire column no matter the level in the pivot. For this example, we will use the third option. Now look at the column results:

image

As you can see, the formatting has been applied to the entire column. Curious how it was applied? In the ribbon, select Conditional Formatting > Manage Rules. In that dialog, you should see the rule you created. With that rule selected (which is the only rule if you started new), select Edit Rule. In the resulting dialog, you can see that you can change how the rule is applied, the rule time, and the description. Here we will focus on the description. In the description area, you can see the Icon and the values that are applied. By default, it groups it in to percents. This is done by taking the maximum and minimum values and dividing the values into thirds and applying the value.

image

You can change the type from Percent to Number, Formula or Percentile. I often find that I need to use a number for that goal. However, if you use a formula you can refer to a sheet with goals, for instance, that let’s you do some what if scenarios as well.

You can also show the icon only which gets closer to the look and feel of a scorecard. I encourage you to explore the possibilities and create scorecards that can be used by your executives, managers, and other users who need to get an overview of how things are going.





Exploring Excel 2013 for BI Tip #11: Color Scales

28 08 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Using Color Scales

In the last tip we covered using conditional formatting with Data Bars. Next up is Color Scales.

What Are Color Scales?

Color Scales are a conditional formatting feature that can be applied to cells in Excel.  Color scales “color” the cell based on the data that the formatting is applied to.  The color scale feature works with pivot tables and standard cells in Excel.  Our focus will be on using color scale with pivot tables.

You will find the option to add data bars on the Conditional Formatting button on the HOME ribbon as shown below.

image_thumb1

image

First starting with a single column, we can use use Quick Analysis or the Ribbon to apply our Color Scale rules. While the data bar used size to highlight the differences in the data, color scales will, obviously, use color. The most common color scale to use is to use Red for lower numbers to green for higher with yellow or white in the middle. Let’s apply that to our column of data.

image

This is particularly effective when working with percentages or other values where red can mean “bad”.  Keep in mind that you can change the order of the color scaling so that a higher number can be red as well.  You can also use two color scales when that is all that is needed.  I have used two color scales in cases where only two values are present such as a Boolean value.

When setting up the formatting, you may notice a “box” in the corner.  This can be used to tell Excel how to apply the formatting.

image

This works great when you pick the first cell in question then use this short cut to set the formatting based on how you want to apply the changes. The  only rule type that applies in this scenario is the “Format all cells based on their values” which options are shown in the shortcut.

While a very simple visualization technique it can be to highlight differences in a compelling way.

Next tip will cover Icon Sets. See you then.





Exploring Excel 2013 for BI Tip #10: The Data Bar

30 07 2013

As I mentioned in my original post, Exploring Excel 2013 as Microsoft’s BI Client, I will be posting tips regularly about using Excel 2013.  Much of the content will be a result of my daily interactions with business users and other BI devs.  In order to not forget what I learn or discover, I write it down … here.  I hope you too will discover something new you can use.  Enjoy!

Using the Data Bar

This feature has been a part of Excel for a long time.  However, as with any tool, some of the oldies are really good.  As a BI architect who worked with SQL Server tools, I am always amazed at what has been around in Excel for years.  So as part of this series, I will also highlight some important visualizations that have been around.

What is the Data Bar?

The data bar is a conditional formatting feature that can be applied to cells in Excel.  Data bars “fill” the cell proportionally based on the data that the formatting is applied to.  Data bars work with pivot table and standard data in Excel.  Our focus will be on using the data bar with pivot tables.

You will find the option to add data bars on the Conditional Formatting button on the HOME ribbon as shown below.

image

image

As you can see Data Bars are one option under Conditional Formatting.  Look for future tips to come on some of the other Conditional formatting options.

Adding Data Bars

The following sample is from the MyVote data generated from the Modern Apps Live! project.  In this sample, I have a simple pivot table which shows the Age range and number of poll submissions as shown below.

image

To add data bars highlight the area to add the bars and choose a format.

image

Advanced Settings

By clicking More Rules … you will be able to apply advanced options.

image

In my opinion, the most important setting to use here is at the top.  When working with pivot tables, you should at least choose Apply Rule to “All cells showing {field name} values.” By choosing this option, if new row values are added, the data bars will be appropriately applied. This also is necessary for filters and slicers to work correctly.

However, this option will also highlight the any total columns (in our case Grand Total was included).  If you only want counts with the row labels, you would choose “All cells showing “{field name}” values for “{field name}”.   You will find that this is the most common option to select when using data bars for data visualization.

Modifying Existing Data Bars

Once it has been added, you can modify the data bar choosing the Manage Rules option in the Conditional Formatting drop down.  This will open a dialog box which has the formatting rules for the selected pivot table.  There is a drop down, which allows you to select the rules for the sheet or other parts of the workbook.  From here you can see all of the rules applied and can edit the rule, create a new rule, delete a rule, or reorder the rules.

Data bars are a simple, but effective data visualization when you need to highlight the variance between values.  With the ability to apply the data bar to a field in a pivot table, it becomes a flexible visualization as well with very little effort involved.








Follow

Get every new post delivered to your Inbox.

Join 787 other followers

%d bloggers like this: