Excel Tip #22: Combo Charts – Out of the Box Functionality

24 03 2015

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 and later.  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!

Creating a Combo Chart

Excel supports a couple of options for overlaying lines on bars or columns. The most common method is to use the combo chart. In this option, you would put metrics into the chart and then select which is the bar and which is the line. This is particularly helpful when using different types of metrics such as counts and percentages. To set this up, you create the initial chart with the metrics you want and then change the type. In the example below I have text and email usage counts with internet usage as a percentage in a column chart.

image

Select the Change Chart Type option on the PIVOTCHART TOOLS menu.

image

In the dialog that is opened, choose Combo Chart at the bottom. Excel will separate the values into lines and columns. In my case, it picked correctly, but be sure to check as it does not do what you want all the time. Because I am using percentages and counts, I want to have a secondary axis. My result is below. We can now look at percentages and counts in the same chart.

image

This functionality can only be used with column charts and lines or areas. The value is being able to show two types of data on the same chart. Up next, we will look at overlaying charts in Excel which gives you more design options for your dashboard.





Check Out Josh’s Blog on Creating Percentile in DAX

24 02 2015

I would like to introduce a “new” blogger to you. Joshuha Owen has restarted his blog and will be covering topics on business intelligence and data. I have worked with Josh for years at a Magenic and now Pragmatic Works. I look forward to seeing what he will be writing about in the future as well on Bits, Bytes, and Words.

Here is his most current post. Enjoy!

Replicating Excel Percentile in DAX

Currently, DAX has no native percentile function so if you want to replicate a version that matches what the Excel Percentile.INC (inclusive) function does you have to jump through a few hoops. This will involve having to create several measures to hold some intermediate values to apply a final formula. In theory you could do it all in one DAX expression but it would very difficult to read and test.

Continue reading…





Excel BI Tip #18: Using CUBE Functions to Break Out of Pivot Tables

27 01 2015

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 and later.  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 Need to Break Out of Pivot Tables

Pivot tables are a great way for users to interact with data from an analytical source such as SSAS Multidimensional Models, SSAS Tabular Models, and Power Pivot Models. Users can connect to the data model and start working with the data. However, when building dashboards, we often need to display content in different ways – such as a header or clarifying value on a sheet. I have done this by creating a single cell pivot table but I do not think that is the best way to accomplish this. What I really wanted to do is put the value into a cell, but have it still honor slicers or filters. In this post, we will take a simple pivot table with a slicer and decompose it using CUBE functions within Excel and create dynamic cell values which respond to the slicer even after the pivot table has been decomposed.

A Look at Our Starting Point

We are going to start with a simple pivot table with a matching slicer. In the example, we have categories on columns and the gender of poll authors on rows. We are measuring the number of polls created. The slicer will show which polls have had a response [1] versus those with no responses yet [0].

image_thumb[1]

We are going to decompose this table and show how it allows you to move the cells around while retaining the connection to the underlying data.

Decomposing the Table

The first step is to select the table and open the analyze tab on the ribbon. (NOTE: I have reduced the size of my window to make the next steps clearer. The ribbon will adjust with the size of the window.)

image

On the Analyze ribbon select OLAP Tools and Convert to Formulas.

image

The result is that your pivot table will “disappear” and all the selected values will be created using formulas as shown here.

image

The next sections will look at how the table was converted and how we can use the results.

A Look at the Formulas

The pivot table is converted to formulas. You can see the formulas for any of the cells by clicking the cell with the formula bar visible. Let’s look at how each area was converted. We are going to start with the first cell on the upper left and work our way through the cells from left to right and then down the rows.

“Count of Poll ID”

This is the name of the measure we added to the pivot table. Here is the formula:

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Measures].[Count of PollID]”)

The CUBEMEMBER function returns the name of the member, in this case, “Count of PollID”. The basic syntax includes the name of the connection as it is referred to in the data connections of the workbook – “ThisWorkbookDataModel”. In our example, the name is that of the Power Pivot model in the background. If you connect to a server based model, you would provide a name with the connection or use the default name. The next portion is the member.

One important note, if you are familiar with MDX and multidimensional models, you will understand the syntax quite quickly. If you have never worked with MDX, you may find the syntax complex or confusing. The Measures dimension referred to here, will apply to any values in the Values section of the Pivot Table fields window.

Finally, you can add a caption if you choose to the value. This will change what is shown in the field. For instance, if I add a caption of “Poll Counts”, it will show “Poll Counts” in the field. This on way to clean up a database name to look more user friendly.

“Column Labels” and “Row Labels”

These are converted to text values in the sheet and provide no value

“Entertainment”, “Fun”, and the Other Category Headers

These are created the same way as the Count of PollID was created. Here is the formula for “Entertainment”.

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Category].[CategoryName].&[Entertainment]”)

As you can see the connection is the same, however the structure of the value is different. In this case, it is [Dimension].[Hierarchy].[Member]. The ampersand (&) signifies that the member is the key. Sometimes a number will show up here. You can see the structure that is used based on what is displayed in the Pivot Table Fields window as shown here.

image

 

“Grand Total” (Column and Row)

The Grand Total headers both use captions. The actual syntax uses the “ALL” member of the hierarchy being displayed. Here is the example from the column Grand Total header.

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Category].[CategoryName].[All]”,”Grand Total”)

Those familiar with MDX will note that this is a standard way to roll up the data in a cube. Excel uses the same key word here to roll up all the values from the category to total.

“F” and “M”

The gender labels also use the CUBEMEMBER function as shown here.

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Poll Owner].[Gender].&[F]”)

As you can see, it uses the Poll Owner as the dimension and Gender as the hierarchy.

The Values or Numbers in the Table

The values section is the last part of the conversion or decomposition to review. It is also the most interesting. Up to this point we have been using the CUBEMEMBER function. The values use the CUBEVALUE function. For those familiar with MDX, each value cell represents a tuple. A tuple is an address for a value in a cell of an analytic structure. When you click inside the cell, then inside the formula bar you can see how the value is created.

image

Here is the formula:

=CUBEVALUE(“ThisWorkbookDataModel”,$B$4,$B8,C$5,Slicer_Poll_Has_Submissions)

Let’s break this down now.

The first parameter is the connection name which is the same as we saw in the CUBEMEMBER function. Next we have three cell references. $B$4 refers to value or [Measures].[Count of PollID] which is the first cell we evaluated. The next reference, $B8, is to row header or [Poll Owner].[Gender].&[F]. The third refers to the current column header which is Entertainment or [Category].[CategoryName].&[Entertainment] which is in cell C$5. The final reference is to the slicer. We will discuss that more in detail in a moment.

The actual value being sent to the underlying data model is

=CUBEVALUE(“ThisWorkbookDataModel”,”[Measures].[Count of PollID]”,[Poll Owner].[Gender].&[F],[Category].[CategoryName].&[Entertainment],[Poll].[Poll Has Submissions].[ALL])

What is important about understanding this is that you can change everything around. You can move the headers, etc, and refer directly to the measure you want or you can move header and use the new cell reference. The cell does not even need to be on the same sheet which is the ultimate level of flexibility.

A quick note on the slicer. The is a filter object that has background reference to the data. The name used in the formula is “Slicer_Poll_Has_Submissions”. It is constructed from the name of the slicer as found in the Slicer Options dialog with a Slicer prefix and underscores to replace spaces. Our slicer is named Poll Has Submissions and was converted to Slicer_Poll_Has_Submissions in the formula.

image

Converting Filters

We used a slicer to provide built in filtering to our formulas. If you have a filter you will be provided with a choice. You can either leave the filter intact or convert the filter. Let’s look at both options.

image

Here is the pivot table we will be using for these examples:

image

The filter is for Poll Has Submissions. We are looking at completed polls for each category.

Leaving the Filter

image

As you can see the filter remained intact while the rest of the table was converted to formulas. The primary difference is that the filter reference in the formula for CUBEVALUE is the cell that the filter shows the value in.

=CUBEVALUE(“ThisWorkbookDataModel”,$C$13,$B18,C$15)

image

This is nice if you want to use the filter format and not a slicer to enable users to filter the value. You can also map that same cell to the other values we were looking at as it returns the member value just like the slicer. Because the filter still functions as a pivot table, when you select the filter you get the Pivot Table ribbon. From here you can move the filter to the position on the worksheet you desire.

Converting the Filter

When you choose to convert the filter, it converts the filter value to the currently selected value. In the case of this example we have All selected and the ALL member is selected.

=CUBEMEMBER(“ThisWorkbookDataModel”,”[Poll].[Poll Has Submissions].[All]”)

What Does It Mean to Me?

By pulling apart an existing table that has data you want to display, you are able to move the cells around and be more creative in your dashboard design. For example, we can highlight the number of polls related to sports created by men. Then we can create an entire dashboard with other details around this without dealing with a pivot table.

image

Have fun creating more creative dashboards with these functions.





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.





2013 – A Year In Review

2 01 2014

It is in our nature as humans to look back in order to understand where we have been.

Warning – some of this blog contains stuff about my family… In case you only want the technical stuff.

Family Fun

This past year has been very interesting for me personally and professionally. In the past year, my youngest, Mikayla, has entered Junior High officially taking our family out of elementary schools. Mikalya joined me at the SQL Saturday event in Omaha. At the same time, my oldest, Kristyna, is now a senior at Burnsville Senior High School. Both of my boys, Alex a junior and Andrew a freshman, are both taller than me and staying active. Alex joined us at the Minnesota SQL Saturday and did a lot of volunteering. Andrew probably had the best event of all as he joined me at SQL Saturday in Fargo. There he got to see Bill Gates in person. I am proud of all of them, they are great kids. This was also the year I celebrated 20 years with the woman I love, Sheila. Without her support, I would not have been able to get this far in my career as well. Yep, it has been a busy year personally. Soon there will be lots of college, marriage, and maybe even grandkids. Wow, I must be getting old.

Magenic and the Server Development Practice

2013 is my first full year as a Practice Lead at Magenic. I started out as the Practice Lead for our Business Intelligence and Data Practice. In August, my role expanded to include SharePoint, Biztalk, and TFS. This allows us to focus server technologies at Magenic. Along  the way, I have had to learn a lot about VMs (still a work in progress). I really enjoy working with the pros across the company that we have. We some very talented BI, SharePoint and BizTalk consultants including a few virtual TSPs in SQL Server, Business Intelligence, and BizTalk.

During this past year, I have traveled around the country to consult, to speak, and to meet customers. I have had the privilege of speaking at multiple SQL Saturdays, Modern Apps Live, SQL Live, and Code Mastery events. It has been fun. I almost made it to all of our offices including the locations we opened this year. I made it to Minneapolis, Chicago, Atlanta, Charlotte, Boston, New York City, and San Francisco. Still need to get out to Los Angelos and Manila.

image

While it has been hard at times, the travel experience has been good overall. I try to keep my speaking engagements up to date, maybe I will see some of you next year.

This year I also authored outside of the blog. Chuck Whittemore (The Insight Analyst)The Changing World of Business Intelligence: Leading with Microsoft Excel - Custom Software Development White Paper and I coauthored a white paper on Leading with Excel: The Changing World of Business Intelligence. This was a fun project where we bring together Microsoft Excel and Microsoft BI in a real world way. We continue to successfully work this strategy with our customers and it was the impetus for my Excel BI Tips blog post series. I SQL Server Analysis Services 2012 Cube Development Cookbookalso had the privilege to coauthor a book that is just being released: SQL Server Analysis Services 2012 Cube Development Cookbook by Packt Publishing. This the third book I have worked on and it has been a while since was last published so this was a good experience for me. I still don’t know if I would take an entire project on, but maybe someday.

This year wraps up with me becoming a virtual TSP with Microsoft to further support their efforts with SQL Server and Business Intelligence in the marketplace.

One other thing that has been interesting for me is that with the release of Power Pivot and SQL Server Analysis Services Tabular Model, I am seeing a huge shift in how I work with and sell BI. I have always worked with cubes, but now I see the in-memory space as a more compelling and leading edge solution that will continue to change what my career will look like. While I had a lot of fun being a cube and MDX wizard, the ability to deliver results to business users in a timely fashion with great visualizations is actually more fun. The more things change …

Happy New Year!

I hope you and your family had much to look back and celebrate this year. I thank God for the blessings of a great company to work for and an awesome family to be with.





The Changing World of BI, A New White Paper for Magenic

19 06 2013

MagenicLogo2012x70tallIn the ever changing landscape that is Business Intelligence (or is that Business Analytics?), a fellow business analyst from Magenic, Chuck Whittemore (B), and I authored a white paper based on our experiences over the past months.

What I think makes our work unique, a BI architect and a business analyst came together to show our worlds colliding in the age of modern BI tools.  While the goal has always been to bring the data to the users and let them work with it as creatively as possible, the tools to do this were IT focused.  What we see now is that with the advent of in-memory, client-side BI tools, users are now able to get to this on their own.  Microsoft has invested heavily in Excel to make it a first-class BI tool.  Our paper discusses this disruptive nature of the new tools and how Excel is being pushed to the next level.  After all, Excel is everywhere already.

Enjoy the read and I welcome your feedback.

The Changing World of Business Intelligence: Leading with Microsoft Excel








Follow

Get every new post delivered to your Inbox.

Join 889 other followers

%d bloggers like this: