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.





Excel Apps – Not Quite Ready for Primetime

22 01 2015

While this is not a regular Excel tip, but it is about Excel. In my Excel BI Tips series, I am always looking for ways to build Excel dashboards or do BI work with Excel that will help everyone. In this case, I am going to discuss a new feature in Excel 2013 and Office 2013 and some of the drawbacks we discovered while trying to bring dashboards into production on SharePoint.

Excel Apps, What Is That?

With the introduction of Office 2013 and SharePoint 2013, Microsoft added the capability to create apps that can be used in the various Office applications to provide enhanced capabilities. I was most interested in the ability to bring in new visualizations in Excel that could be used for creating dashboards on my projects. One of the key advantages of using them, was that they worked when deployed to Excel Services in SharePoint without installing anything on SharePoint.

Here some examples of visualizations I planned to use.

Gauges by DataVis Design

image

People Graph by Microsoft

image

Modern Chart by Microsoft

image

Bing Maps by Microsoft

image

There are a number of other visualization options that are free or for some charge as well as other functions. You can find more of them and more information about Office Apps here.

Initial User Experience Is Poor

After getting a couple of these visualizations in a dashboard over the period of a couple of weeks were were ready to deploy the dashboards for user acceptance. Each user who opens the dashboard will have to clear the following install message from each app when they load the dashboard.

image image

While not a “big” deal for savvy users, this is really an unacceptable user experience for less savvy or less patient users. Furthermore, this could easily turn into a support nightmare as each new user is likely to call or email support regardless of the amount of instructions provided. Given that some of our audience was likely going to be executives, we determined that this would not work for us and would actually reflect poorly on our project.

Ongoing User Experience Issue

So, if you decide to move forward with these apps, you dashboard can look pretty good. However, this brings up a more long term issue. Each of the visualizations created have one or more settings buttons that remain visible, even after deployment. For instance, the gauges have a “gear” and a “question mark.” One the first requests we got from power users reviewing the dashboard was to hide them. As far as I can tell this is not possible. Next, the question was “why doesn’t the question mark contain information about the metric being displayed?” Great question, but the question mark is there to provide information about the gauge not the content. Once again, users don’t need that information. These issues reinforced our decision to remove them from our executive level dashboards and not recommend their use in other dashboards.

image

image

Concluding Thoughts

I am not sure if the problem lies in the way the apps were created or with what Microsoft has enabled in the API designs. In the end, these visualizations need to have a “deployment view” or something similar that will hide all this as well as deploy cleanly for end users. These apps do provide some cool visualizations that are not readily available elsewhere, but they need to be cleaner or more elegant for use in general dashboards deployed in SharePoint. Understanding these nuances will hopefully help you make the better decisions about dashboard design in Excel with Office Apps.





Excel BI Tip #17: Using the Timeline Filter

20 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!

Before I go much further, I wanted to call out an update to this series. I am planning to continue to cover more about Excel and Power BI components in Excel through these tips as we move forward. The focus will continue to be on Excel 2013 and beyond. So look for “sub series” around Power BI tools such as Power Pivot, Power Query, and Power Map as they are now integral parts of the Excel BI story.

Introducing the Timeline Filter

The Timeline Filter is a great visual filtering tool that can be used with pivot tables, pivot charts and data from Analysis Services and Power Pivot. It is the best way to allow users to have “range” query capabilities in Excel. It breaks down a date into Years, Quarters, Months and Days. The Timeline was first released with Excel 2013.

image

imageYou can find the Timeline filter in the same area on the ribbons as the Slicer filter or by right-clicking the fields area used with pivot tables and charts. If you have a valid date in your model, the option will be available in this area. However, if you have no valid dates, it will not be available. This is by far the most frustrating part of working with this filter. I will go through what I have discovered about getting a date that can be used with this filter in the next section.

When you can use it, Timeline filters greatly enhance the look and feel and the ease of use for Excel dashboards and analytics.

Getting a “Date” Value the Timeline Can Use

First of all, this will be the most frustrating part of working with this filter. The Timeline requires a date field, not a date dimension. This means that traditional cubes will have the least success working with this feature unless the cube design is modified. Typically, we create date dimensions that use a surrogate key that is a integer data type. Even when we choose to make this a “smart” key (e.g. 20120131 = YYYYMMDD), the value we place in the related fact tables is an integer. However, we often include an actual date as an attribute so there can be workarounds. If you are working with a cube design that has no dates typed as dates, it is likely you will be unable to use this filter.

The key point is that the Timeline must have a date value in order to be implemented. The Timeline will work with any field that is a date.

Adding a Timeline to a Pivot Table

In my example, I will be using a Power Pivot model in Excel. This is not built on a star schema, but the model has a date table that will be used in one of the demos as well. My starting pivot table will be from one table that summarizes polls by state (I am using the MyVote sample data from Modern Apps Live). As you can see in the screenshot below, it has states and the sum of submissions.

image

The next step is to add a Timeline filter. If we right-click the PollSubmissionDate field, we will see the option for adding it as a Timeline. image

image

Voila! We have a Timeline that works with our pivot table. In the next section, we will break down the parts of the filter and its options.

Timeline Parts and Options

image

The Timeline Caption and Header properties affect the same section. In our current Timeline, we have a caption of “PollSubmissionDate” which is the name of the field. This is the default when creating the Timeline. You can change the header by changing the caption. If you do not what to show the header, unselect the Header box in the options.

The other three options also allow you to hide or show features in the Timeline. By default, all of the features are showing.

The Scrollbar is located at the bottom of the Timeline. It allows users to scroll through the available dates in the filter. This is helpful when dealing with a underlying large date range.

The Selection Label is the portion that shows what has been selected in text form. In the example above, you can see that Jan-Feb 2014 has been selected and that is what is shown in the label. I find that this reinforces to the user what they have selected. If the label is not visible, then the bar under the dates is the only way to see what has been selected and that is not always clear to users.

The last option that can be turned on or off is the Time Level. This is the drop down list that shows Years, Quarters, Months, and Days. This can be used to change the granularity of the selection bar. Depending on the implementation, you may want to limit the Timeline to a particular view. However, if you are using this dynamically and the data exists to support all of those levels, then you are best served giving users the option to select the granularity of the selection bar.

The truly “cool” feature is the selection bar. Users can “grab” the edges to expand or contract the range of dates they wish to see. As they change the granularity with the time level, they are able to select days, months, quarters, or years. This truly allows for dynamic range filtering which has typically been very difficult to implement in a simple fashion in Excel.

Connecting the Filter to a Second Pivot Table

As with slicers, we can have the Timeline filter apply to multiple objects in the workbook through Report Connections. Let’s add another pivot table and try to apply the date and you will see the issue. In this example, we are adding the PollResponseCount from the PollResponse table with the ResponseDate.

image

By right-clicking the ResponseDate, I am able to confirm that it is a candidate for a Timeline filter. Let’s see if we can create a connection with our Timeline filter.

image

As you can see in our example, by adding PivotTable2 from Sheet1 we have filtered the data in the second pivot table. The data is now limited to the January and February of 2014. Of course, we should change our caption now as the filter will apply to multiple data sets and different date fields.

Post Publication Update from Chris Webb (@technitrain). There are additional limitations when using the Timeline filter with SSAS multidimensional. See Teo Lachev’s blog post on the topic.





Part2 Adding a New CheckBox – Create your own SQL tools with PowerShell and Windows forms

14 01 2015

Steve:

This is James’ second post on creating SQL tools with PowerShell and Windows forms. James is a DBA responsible for the management of a large number of SQL Server instances.

Originally posted on JamesDataTechQ:

This blog post is about adding a new CheckBox to the SQL Tool base form from my first blog post. In the first blog post I just gave an introduction on the SQL Tool, now this is where the fun starts getting creative by adding your own SQL queries and PowerShell scripts. How cool is that! You’re making your own SQL tools tailored to your own needs. The CheckBox that I am going to be adding is for an SQL process, but it is universal. The CheckBox is just starting a PowerShell Function, what ever you dream up you can start that process from your own SQL Tool. If you started making your own SQL Tools or you would like for me to cover a topic please leave me a comment.

Add a new CheckBox

Step 1. In this step we will be adding a new CheckBox object called…

View original 514 more words





Intro to Data Factory–Training on the T’s Follow Up Post

13 01 2015

PragmaticWorks-LogoThis is a follow up blog post based on the Intro to Data Factory session I gave on the Training on the T’s with Pragmatic Works. Find more free training from the past and upcoming here. I did my session on January 13, 2015.

 Intro To Data Factory

In this session, I gave a simple introduction to new Azure Data Factory using a CopyActivity pipeline between Azure Blob Storage and Azure SQL Database. Below is a diagram illustrating the factory that is created in the demo.

image

I have published my presentation materials here. This includes the sample JSON files, the Movies.csv, and PowerShell scripts.

Q & A

Here are a few questions that were answered during the session.

1. Does Availability refer to when data that has been transferred will be available? Or when the data source is actually available for query?

Availability refers to when the datasets will make a slice available. This is the when the dataset can be consumed as an input or be targeted as an output. This means you can consume data hourly but choose to push it to its final destination on a different cadence to prevent issues on the receiving end.

2. What pre-requisites are must haves?…e.g.(Azure account, HDInsight, Blob Storage Accounts, etc.)

    • An Azure Account is the only real must have. You could use two on premise SQL Server instances.
    • HDInsight if you want to use the HDInsight activitities
    • An Azure Storage account to use blob or table storage

3. How do you decide to use a Factory or Warehouse?

The factory is more of a data movement tool. A warehouse could be a source or target of a factory pipeline.

4. Is this similar to SSIS in SQL Server?

Yes and no. SSIS is definitely more mature and has more tooling available such as data sources and transformations. SSIS also have a good workflow constructor. The focus of the Data Factory initially was to load HDInsight tables from a variety of sources with more flexibility. The other note here is that Data Factory is being built from the ground up to support the scale of the cloud or Azure.

5. Can this be used for Big Data?

Absolutely. I would say that it is one of the primary reasons for the tool. In reference to the previous question, it will likely be the tool of choice for big data operations because it will be able to scale with Azure.

Links to Additional Resources on Data Factory or tools that were used in the presentation:

Azure Data Factory on Azure’s Website

Azure Data Factory Documentation

Azure Data Factory Pricing

Azure Storage Explorer

Azure PowerShell Documentation

Thanks for joining me for this presentation. We look forward to seeing you at the next Free Training on the T’s.





She Taught Me to Code–A Tribute to Sheila, My Wife

12 01 2015

A tribute is an expression of gratitude or praise. A couple of years ago, I started a series about individuals who have impacted my career. I do this as a tribute to my father-in-law, Ed Jankowski who passed away five years ago this past December (2014). Check out my original post about him and his impact on me being in software development today.

Picture - WeddingShe Taught Me to Code

My wife, Sheila, actually did get me started down the programming path. We had been married just over a year when she showed me how to use Microsoft Access to create databases, entry forms, and reports. She knew how to do some of the code behind to solve problems. As I noted in the first paragraph, her dad was a significant influence during the start of my career. His influence was not lost on her either. It was that work she did with me that got me interested in computers. If you ask her today, she is more a user of software and not a builder of applications. However, she was the first to show me the possibilities and joy of creating applications for practical uses.

My Success and My Wife

Over the years, I have worked in the corporate world and in consulting. As my career began to take off, Sheila supported me and the effort required to learn and move up in a career which I started after completing college. From Bethany Press to Magenic to Xata to Magenic and now Pragmatic Works, she has been supportive, even when it made life harder at home. Without her, I would not have been able to do much of what I have accomplished.

I really had this perspective reinforced with the article from Harvard Business Review – The One Thing About Your Spouse’s Personality That Really Affects Your Career. Here are a couple of highlights from that article that speak volumes about Sheila’s influence on my career:

First, conscientious spouses handle a lot of household tasks, freeing employees to concentrate on work (“When you can depend on someone, it takes pressure off of you,” Solomon told me). Second, conscientious spouses make employees feel more satisfied in their marriages (which ties in to the first study I mentioned). Third, employees tend to emulate their conscientious spouses’ diligent habits.

… what isn’t obvious is the extent to which so many people are parts of teams, in a sense — two-person teams that are based outside the office.

Being a data guy, it is really cool to see research reinforce the impact my wife has on my career. Interestingly, I have made job changes to retain that support as well. The companies that have supported my wife have been the most enjoyable to work at. Even when the job demands were rough, those managers and leaders who cared about my wife and family both in word and action were the best places I worked at. Here is one last quote from the HBR article about this topic as well:

We can’t and probably don’t want to know the details about these teams, but as Solomon points out, if organizations really understood the workplace effects of strong outside relationships, they might be more receptive to policies like flextime and telecommuting that make it easier for employees to spend time with their significant others.

I have been married over 20 years and have 4 teenage children. Without my wife and her support, I think that we would be in a very different place and I definitely would not be as happy.

So, to the love of my life, thanks for putting up with the long hours, working weekends and travel. I know it has been hard, but with you I am a better person and more successful. Thanks so much.





2014 Year In Review

11 01 2015

imageAs is our want, we must look back over the past year to see what happened. While I normally focus on work related items, this year was a crazy year for our family as well as my career. So let’s have a look at what happened this year.

Traveling Family

2014 was a year that saw our family do a bunch of traveling. Although our trips were not all done together, it was travel all over the world. Here are some of our highlights:

  • My two oldest children, Kristy and Alex, went on a tour of Italy with the Burnsville High School Band. They saw Venice, Rome, and a few other cities. They were able to perform with the band during that trip.
  • Kristy journeyed to Israel with Grace Church right before the missiles started being launched. She was doing a Holy Land tour which she enjoyed a lot. However, as parents, getting a text that said, we left before the missiles landed around Bethlehem did make us a bit nervous.
  • Alex worked in an orphanage in Romania. He was significantly impacted with the conditions there and is looking for his opportunity to return and serve some more.
  • Andrew and Mikayla went to a town in Indiana for a weeklong trip with Teenserve and our church. They had the opportunity to join Family Cancunteens from around the country and perform repairs and general maintenance for a town in need.
  • Alex visited colleges in LA and Lynchburg
  • Andrew traveled to Chicago with band and a church group.
  • Our entire family enjoyed a true break in Cancun, Mexico. Truly a lot of fun and great downtime.
  • We followed the Cancun trip up with a cross country trip to Los Angeles to drop my oldest, Kristy, off at Biola College for her freshman year.
  • Andrew and I went to Key West with the Boy Scouts and sailed around the Keys for a week. That was truly enjoyable. I loved being on a boat.
  • Sheila and I enjoyed our company Holiday party in the One Ocean Resort in Florida
  • We wrapped up the year visiting family for the holidays in Kentucky.

Overall, we were all over the country and even the world. We were blessed to have the opportunities to experience so much this year.

Changing Employers

In the middle of all the travel, I celebrated 10 years at Magenic in March and transitioned to Pragmatic Works in October. I loved working at Magenic. During this year, I came to the realization that I wanted to focus more on data and BI solutions, so I made the move to Pragmatic Works. I enjoy my new company as much as my old one which is very good. Thanks to everyone at both places for supporting me and my career.

More…

This past year, I also contributed to my third book. Hopefully you found it helpful. I also did a first for me this year, I reblogged a post from a friend and fellow Scouter, Jim Larson. His PowerShell work is awesome and I wanted to share it with my readers as well.

Thanks to My Readers

Finally, I wanted to thank all my readers. I appreciate your support. It has been cool to see my readership increase this year. I hope you find value in the technical content here. I look forward to hearing from you or even better, seeing you at SQL Saturdays and other events throughout the year.

Here’s to a great year in 2015!








Follow

Get every new post delivered to your Inbox.

Join 892 other followers

%d bloggers like this: