Tableau vs. Power Pivot Part 7: Basic Dashboards

Today, we will talk about creating dashboards in Tableau vs. creating dashboards in Power Pivot and Power View.  To keep things basic, we will create dashboards that consist of a few related charts with some basic linking (filtering) and brushing (highlighting) features.  For those of you who are avid readers of this blog, you will note that we do not recommend these types of dashboards.  They are much less useful than they could be.  However, they are what the vast majority of business users tend to create.  As usual, we will use the Superstore Sales sample data set from Tableau.

This test will have three criteria: Ease of Use, Functionality and Aesthetics.  However, before we can begin the test, we need to design our dashboard.  Let's start with a map of Profit by State.  This map will lead you to a list of customers within that state, as well as how much profit they have generated and how many orders they have placed.  Finally, this chart will lead you to a table with information about all of the orders that customer has placed.  Now, let's see how Power Pivot handles this.

Phase 1: Power Pivot

First, let's try to create our map.

Profit by State (Power Pivot)

This looks decent.  However, a little color would be great to distinguish the profitable states from the unprofitable ones.  In order to do this, we need to create a calculated column with some type of discretization of Profit by State.  First, we need to create a calculated column with the Total Profit for each State.

Total Profit by State (Power Pivot)

We chose to use the ALLEXCEPT() function to do this.  There are likely other ways to do this using SUMMARIZE() or some other filtering function.  We also checked this by comparing the value for the states to the values in map.  Now that we know how much profit the state has generated, we can categorize them.  Looking at the map, we see values between -$10,000 and $100,000.  So, let's categorize them in groups of $25,000.

Profit Category by State (Power Pivot)

This wasn't too difficult of a task.  However, these boundaries are hardcoded and would have to be manually changed if the data changed.  Let's see how it looks on the map.

Profit by State (Improved) (Power Pivot)

This map is far more useful than the previous one.  You can easily tell which states belong to which categories.  However, we would love to be able to change the colors so that they follow a recognizable pattern, such as Red-Green, or Blue-Orange.  Alas, this is not possible in Power View.  We could create a much better map in Power Map.  However, we are saving Power Map for a more advanced investigation in a later part.  

Let's move on to the bar graph of Profit and Number of Orders by Customer.  In order to do this, we need to create a Number of Orders measures.

Number of Orders (Power Pivot)

This was easy enough.  Now, let's create the bar graph.

Profit and Number of Orders by Customer (Power Pivot)

This chart isn't bad.  However, we are unable to show Number of Orders in any reasonable way because of the massive difference in scale between the two measures.  We could put it on its own separate graph; but then we would lose the ability to sort by Profit.  So, it seems that must scrap Number of Orders from this chart.  We also have this ugly ribbon telling us that there are too many customers to display.  We could go back and create another calculated column which tells us which customers are in the Top N%.  Although, that's a lot of work.  It's easier to just filter out customers whose profit is less than $500 and move on.  Removing possibly valuable information is not a great thing.  Alas, the tool leaves us little choice.

Finally, let's create a text table that we can filter down to each customer to show what they have purchased.

Order Details (Power Pivot)

There's nothing too complex about this table.  It would be nice to have a very light amount of color to differentiate profitable orders from unprofitable ones.  Once again, Power View is not nice enough to give us such a feature.  All in all, this is a nice dashboard.  It tells us the information we need to know, when we need to know it.  

As far the dashboard construction goes, Power View automatically creates links between charts that allow for an interactive experience.  Therefore, all we need to do is place the charts on the sheet.  We'll show the final reveal at the end.  Now, let's see what Tableau can do.

Phase 2: Tableau

Once again, we will start with the map.

Profit by State (Tableau)

Not only did this only take a few drags, it looks significantly cleaner than Power Pivot.  Next, let's try the bar graph.  First, we need to calculate Number of Orders.  As we saw in a previous post, this is not a simple task in Tableau.  We need to start with Order ID in the chart.

Profit by Customer and Order ID (Tableau)

Once again, this was just a few drags.  The real key is knowing when you are going to need to use these advanced techniques.  Now, we can calculate Number of Orders.

Number of Orders (Tableau)

Number of Orders (Compute Using) (Tableau)

We see that Number of Orders is already at the Customer level.  Next, we need to aggregate Profit up to the Customer level.

Total Profit by Customer (Tableau)

Total Profit by Customer (Compute Using) (Tableau)

This was very similar to the Number of Orders, albeit using a different table calculation.  I could have also achieved this with the TOTAL() function.  Now, we need to aggregate out the Order ID to get the chart to look the way we want it to.

Aggregator (Tableau)

Aggregator (Compute Using) (Tableau)

Finally, we need to clean up the chart by removing the extra horizontal lines, hiding the Order ID header and adding labels to the bars.

Add caption

Creating this chart was significantly more involved than creating the one in Power View.  However, this chart is far superior in both usability and aesthetics.

EDIT: This could have been done significantly more simply by using the COUNTD() function, which doesn't exist in live Excel connections.  The solution would be to create an extract and use the COUNTD() function from that source.

Now, on to the Order Details table.

Order Details (Tableau)

As you can see, Tableau allowed us a tremendous amount of flexibility when creating this table.  This version is far more useful to the analyst than just a table full of numbers would be (which is exactly what we used in Power View).  Unlike Power View, Tableau does not automatically link sheets on a dashboard.  Each "Action", as they are called in Tableau, must be created individually.  While this is more work, it leads to a much more flexible product.  First, we need to be able to filter the bar graph and table using the map.

State Filter (Tableau)

This box shows us just how flexible Tableau's Action system is.  Take note of the "Exclude all values" selection.  This will allow us to demonstrate a neat trick that, to our knowledge, Power View cannot do.  Next, we need to allow the table to be filtered by the bar graph.

Customer Filter (Tableau)

This filter is very similar to the State Filter, albeit with a few different selections.  Finally, let's move on the main event.

Phase 3: Dashboard Showdown

First, let's see how Power View turned out.

Power View Dashboard (No Selections)

This dashboard looks very clean.  The white background and smooth colors give it a very professional feel.  We can easily see which states belong to which categories.  However, the color scheme is a little confusing.  Shouldn't Green be a good thing?  The bar chart of customers is very clean as well.  We can easily see that there are three customers who generate more profit than the rest.  The table at the bottom is pretty useless as of now; there's way too much information in it and it's sorted by date.  Now, let's select a state and see what happens.

Power View Dashboard (State Selected)

The bar graph is now highlighted, showing us how much of the customers' profit is coming from this state.  The table also filtered to only show orders from this state.  It does seems somewhat difficult to find which customers actually purchased in this state, there are too many empty bars in the bar graph.  Also, the table is still far too large to be useful for some type of analysis.  Next, let's select a Customer and see what we get.

Power View Dashboard (Customer Selected)

We can somewhat see which states this customer ordered from.  However, the wild colors make this process slightly less desirable.  Finally, we see that we have a useful table at the bottom.  We can see all of the orders placed by this customer.  However, it would be nice to be able to easily see which of these orders  were profitable and which are not.  All in all, this was a nice dashboard.  It was very clean and professional.  An analyst would be able to make good use of this dashboard.

Lastly, let's see how Tableau fares.

Tableau Dashboard (No Selection)

This is really crisp.  The colors are not overpowering and it is easy to see where each state ranks.  The map takes up the entire screen, which allows us to see what's going on in New England.  Let's select a state and see what happens.

Tableau Dashboard (State Selected)

Cool!  The map automatically resized to allow room for the bar chart.  Hint: This was caused by that "Exclude all values" selection we mentioned earlier.  We can also see how profitable each of the customers were in this state.  As a bonus, we can see how many orders they placed.  Apparently Rick Reed placed one really big order, as opposed to most of the other customers, who place series of smaller orders.  We would have never seen that using the Power View dashboard.  Let's select a customer and see what happens.

Tableau Dashboard (Customer Selected)

Everything resized again to allow room for the table.  The table also allows us to easily see which purchases were profitable and which weren't.  As an added bonus, Tableau allows us to fit more columns in our table.  Who knows when that extra information might come in handy?  There is one irritation we would like to point out.  When you select charts in Tableau Desktop, you get those borders around the chart.  It is very difficult to avoid this and we wanted to show it.  Power View also does this; but it is significantly less pronounced.  All in all, this dashboard is terrific.

Conclusion

It was pretty easy to see that Tableau blew Power View away when it came to dashboard functionality.  However, some of the techniques employed in Tableau were advanced and/or time-consuming.  So, we must give credit to Power View for its simplicity.  But, we believe that Tableau demonstrated so much more flexibility and analytical capabilities that it is worth the extra effort.  It is worth noting that Power View is a free tool that comes with Office 2013.  Therefore, if you are looking for a first-step into the world of visualization, at little upfront cost, then Power View could be a good start for you.

Winner: Tableau

If you are interested in learning how to create the dynamically sizing charts in Tableau, check out Creating a Dashboard with Variable Container Heights in Tableau.  This post was written using Tableau 7.0; but the task didn't change much in 8.0.  Thanks for reading.  We hope you found this informative.

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/in/bradllewellyn
http://breaking-bi.blogspot.com

Tableau vs. Power Pivot Part 6: Performance

Today, we will look at how each of these tools handles very large data sets.  We are going to examine them on how compactly they store the information as well as how quickly they can retrieve it.  Unfortunately, we cannot give any information on the source of this data set, we can only say that it is an enterprise fact table with 53.5 million rows, 22 columns, and takes up almost 8GB as a csv text file.

Now's the time to note some limitations in the tools.  Tableau and Power Pivot both have limitations on the data they can connect to.  Power Pivot is unable to connect to any data source larger than 2GB.  This was a design choice by the developers in order to keep Power Pivot in the "Self-Service BI" freeware niche.  If you want to work with data sources larger than 2GB, you should upgrade to BISM Tabular.  BISM Tabular is basically Power Pivot all grown up.  It is a much more powerful version of Power Pivot that you would need to buy from Microsoft.

On the other hand, Tableau is designed as an enterprise tool as well as a self-service tool.  Therefore, when your file reaches the limit, which we think is also 2GB, then you still have the option of creating a "Tableau Data Extract."  This is a proprietary file format which includes columnar compression and storage, similar to Power Pivot.  Also, we have never seen a limit to the size of Tableau data extract.  The only limitation we have seen is of the processing power of your machine.

Now, let's talk about our examination.  We uploaded the data into SQL Server and typecasted the top ten million rows from strings into numbers to keep the size under the limitations of the tools.  Please note that these tests are done using a single laptop PC with 8GB RAM.  Your results may vary.  A recap table will be provided at the end of this post.

Category 1: Initial Data Upload

The first step to our process was to upload the data into our tools.  In Power Pivot, this involved loading the data into the Power Pivot window.  In Tableau, this involved creating a Tableau Data Extract.  Here are the times:

Power Pivot: 2 minutes
Tableau: 3 minutes 43 seconds

As you can see, Power Pivot uploaded the data much more quickly.  Data Upload is separated into two steps: Retrieval and Compression.  Power Pivot was about 30 seconds quicker at retrieving the data than Tableau was.  Moreover, Power Pivot compressed the data in a matter of seconds; whereas Tableau took about a minute to complete this process.  All in all, Power Pivot wins this part.

Winner: Power Pivot

Category 2: Data Refresh

Now that the data is in the tool, how quickly does it refresh?  Let's find out.

Power Pivot: 2 minutes 10 seconds
Tableau: 4 minutes 2 seconds

This is the same story as before.  No commentary needed.

Winner: Power Pivot

Category 3: Saving the Workbook

An important part of a business analyst's job is saving their work.  Let's see how these tools fare.

Power Pivot: 15 seconds
Tableau: 7 seconds

We're not sure why Tableau saves faster than Power Pivot.  It probably has something to do with the fact that Excel workbooks contain many more features than Tableau workbooks.  Regardless, this is an easy call.

Winner: Tableau

Category 4: Opening the Workbook

When you arrive at work the next morning, you need to be able to resume your work.  Let's check them out now.

Power Pivot: 15 seconds
Tableau: 4.5 seconds

Tableau takes this one as well.  This victory is probably caused by the same features as the quicker saving was.

Winner: Tableau

Category 5: Compressed Data Size

Now, let's look at how large these workbooks are when they only contain the compressed data.  This should be a good benchmark to compare the different compression algorithms.

Power Pivot: 247 MB
Tableau: 73 MB

It seems we have found the reason why Tableau saves and opens faster.  Its workbooks are much smaller.  This is likely due to the many extra features contained within Excel.

Winner: Tableau

Category 6: Tabular Chart Refresh

Next, let's see how quickly these tools can refresh a very large tabular chart.  Note that these charts were made using a Pivot Table in Excel versus a Text Table in Tableau.

Power Pivot: 22 seconds
Tableau: 43 seconds

We see that Tableau is much slower at refreshing this table than Power Pivot.  This must be the performance that everyone talks about when they talk about Power Pivot.

Winner: Power Pivot

Category 7: Bar Chart Refresh

Finally, we're getting to the good stuff.  We saw that Excel was fast at creating Pivot Tables.  The question is, "Will Power View fare as well as Excel?"  Let's see how it goes.

Power Pivot: 6.5 seconds
Tableau: <1 second

The Tableau chart loaded so quickly that we couldn't even time it with a stopwatch.  This is easy to call.

EDIT: It should be noted that the timing methods were somewhat different.  We could not find a reliable method for refreshing Power View and Tableau from scratch.  Therefore, we chose to open up the workbooks to a blank Tableau worksheet and a blank Power View worksheet.  Then, we opened up the sheet we wanted to display.  The time it took for the populated sheet to open is what we recorded here.  The same logic applies to the dashboard refresh as well.

Winner: Tableau

Category 8: Dashboard Refresh

Lastly, we get to the main event.  How quickly can these tools load a complete dashboard.  Note that the dashboard consisted of four varying types of charts created to stretch the tools.

Power Pivot: 9 seconds
Tableau: 3 seconds

It seems that Tableau has won again...Wait a minute!  Notice how the difference between times for Tableau and Power Pivot for the Bar Chart and the Dashboard are 6 seconds for each tool?  Turns out, this is actually caused because switching between Power View sheets in Excel causes a little bit of overhead.  If we remove that 6 second overhead, the tools seem to fare equally well at this point.  Alas, winning is winning.

Winner: Tableau


Summary

Let's take a look our results to see how the tools matched up.

Performance Results

Looking at quantity of category wins, it seems that Tableau is the winner.  However, if we look at the categories that really matter, Initial Data Upload and Data Refresh, Power Pivot takes this one.  This puts us in a bit of quandary.  Which of these tools is the winner?  If we consider our original idea of the 53 million row data set, we see that Power Pivot was completely unable to deal with this quantity of data, while Tableau had no issues at all.  So, we can finish with a two-part decision.  If you are using small data sets by yourself for a side project, you will probably see better results with Power Pivot.  However, once you reach a certain point, you must use a different tool.  This means that for larger data sets, Tableau wins by forfeit.  Thanks for reading.  We hope you found this informative.

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/in/bradllewellyn
http://breaking-bi.blogspot.com

Tableau vs. Power Pivot Part 5: Point-and-Click KPIs

Today, we will talk about what we are calling "Point-and-Click KPIs."  These are calculations that can be performed using built-in non-coding functionality within these two tools.  To be a little more clear, Power Pivot has a feature called "KPI" which allows the user to quickly compare measures to goals with nice visual output.  On the other hand, Tableau has a feature called "Quick Table Calculation" which allows the user to quickly create some advanced calculations with only a few clicks.

In our previous post, we compared the tools on how you could programmatically create some simple KPIs.  In other words, we compared "apples to apples."  In this post, we will be comparing related features of these tools that are not quite so easily compared.  Therefore, there will be no winner assigned in this post; it is purely for informational purposes.  On another note, the term KPI takes on somewhat of an ambiguous meaning in this post.  We typically refer to run-time calculations as KPIs.  It turns out that Power Pivot's feature is also called KPI; however, we'll try to be deliberate about our word choices.  Now, let's get on with the examination.

Power Pivot KPIs

In the Power Pivot ribbon (among other places), you will see an icon that says "KPI."  Clicking on this icon will allow to select "New KPI" or "Manage KPIs."  Click on a KPI, or creating a new one, will open up the following window:

KPI Window (Power Pivot)

As you can see, this window allows you to define your base value, a goal (calculated or absolute) and a display mechanic to be associated with that KPI.  The KPI will calculate a simple ratio to determine how close the base value is to the goal.  You can adjust the acceptable percentages using the sliders on the multi-colored bar in the middle of the window.  You can also change how the bar is arranged to allow for a little bit more flexibility in your KPI.  Now, let's create a KPI that compares this year's sales to last year's sales.

Sum Sales KPI (Power Pivot)

Sum Sales KPI by Region and Year (Power Pivot)

Now, we can see which Years saw a decline in Sales for each Region.  There are a couple of things to note about this feature.  First, you can only have one KPI per measure.  If you want to compare this year's sales to last year's sales and this year's sales to this year's budget, you will need to duplicate your measure for SUM( [Sales] ).  Second, you need to create each underlying measure individually and you cannot use implicit measures.  Third, if you select any display styles other than the default Red-Yellow-Green Circles, Excel will still display them as Red-Yellow-Green Circles.  You can change the display style by editing the conditional formatting.

To be honest, this feature is not very useful within Excel.  The KPI calculation can easily be made using [Base] / [Goal].  Also, the target audience for Power Pivot is Excel Power Users.  These Power Users should already have a good understanding of how to create conditional formatting.  Therefore, we don't see many people using this feature inside of Excel.  However, this feature does become useful when you are creating dashboards inside of Power View, which does not have built-in functionality for conditional formatting.  When you are using these KPIs inside of Power View, you will see that changing the display style of the KPI will be reflected in your dashboard.

So, we will end this examination by saying that this feature is neat, albeit weak, addition to the Power View Dashboard toolkit.  It allows the users to spice up their text tables to add that next level of usability, and we all know that text tables are an Excel Users best friend!

Tableau Quick Table Calculations

Whenever you drag a measure onto a chart in Tableau, you can right-click the measure and select "Quick Table Calculation."  This will open up a list of common calculations that Tableau will calculate for you.

Quick Tableau Calcuation (Tableau)

If you select a calculation, Tableau will replace the measure on your chart with the calculation you selected.  Now, let's calculate Year over Year Growth using this feature.

 

Year over Year Growth by Region (Tableau)

This took one click and we have a calculation that most people would have to think about how to calculate.  This calculation would also take some knowledge of table calculations to create on your own.  What if you wanted to know how Tableau calculated this value?  Right-click the SUM( [Sales] ) pill on the Text Shelf and select "Edit Table Calculation", then select "Customize."  This will open up the Calculated Field window where you can see the exact syntax for creating this calculation.  In fact, this is how we learned table calculations when we were first getting started.  It should also be noted that this feature can be used on implicit measures, like SUM( [Sales] ), or any calculated fields you want to create, except for other table calculations.  All in all, this is an extremely easy way to create most of the common calculations you would want, and it's not a bad way to improve your Tableau skills either.

Conclusion

Power Pivot's KPI feature is an easy way to add a little color and readability to your Power View dashboards.  It also allows you to easily create and maintain simple indicators in one central location.  On the other side, Tableau's Quick Table Calculation feature allows the user to create some complex calculations such as Year over Year Growth and Moving Averages with only a couple of clicks.  It is also useful for learning how to better utilize table calculations in Tableau.  Thanks for reading.  We hope you found this informative.

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/in/bradllewellyn
http://breaking-bi.blogspot.com

Tableau vs. Power Pivot Part 4: KPIs

Today, we will talk about creating KPIs.  More generally, we will talk about creating more complex functions than simple aggregations.  One of the defining characteristics of a KPI is that it is a measure that cannot be calculated at the row or column level; it must be calculated in real time.  By this definition, KPIs are also non-additive or, less commonly, semi-additive.  KPIs are the backbone of most enterprise reporting and should weigh very heavily on the choice of a tool.  Therefore, we will attempt to examine them very closely.  As usual, we will use the Superstore Sales sample data set from Tableau.

Simple Ratio (Profit Margin)

Unfiltered Ratios are some of the simplest KPIs.  So let's look at how to calculate Profit Ratio by Region in Power Pivot.

Profit Ratio (Power Pivot)

This calculation is quite simple.  It's also nice that Power Pivot lets you select the format here.

Profit Ratio by Region (Power Pivot)

As you can see, this was simply one calculation and two drags.  Let's see how Tableau fares.

Profit Ratio (Tableau)

This formula is just as simple as the one for Power Pivot.  Now, on to the chart.

Profit Ratio by Region (Tableau)

This chart looks identical to the Power Pivot chart.  However, we did have to go through one extra menu to set the format of the KPI.  Also, you might notice that Power Pivot is showing a total while Tableau is not.  For KPIs, totals may or may not be significant.  Since this is such a situational need, we do not consider it when deciding our verdict.  The extra menu was enough to give Power Pivot the edge in this category.

Winner: Power Pivot

Filtered Ratios

To the untrained user, a filtered ratio is the same as a simple ratio.  They simply see it as a non-additive measure.  However, they are much more complex to create because the creator must have an awareness of the current filters, as well as how to alter them.  Let's see how Percent of Total Sales per Category works in Power Pivot.

Percent of Total Sales (Power Pivot)

The calculation leverages this nifty ALL() function which does the heavy lifting for us.  Now, on to the pivot table.

Percent of Total Sales by Category (Power Pivot)

As usual, the pivot table was a breeze to create.  Let's give Tableau a shot.

Sales per Category (Tableau)

In typical Tableau fashion, we have to make our chart first.  Now, let's take a look at the calculation.

Percent of Total Sales (Tableau)

Just like with Power Pivot, we can leverage a nice function here.  In fact, the TOTAL() function in Tableau works pretty similarly to the ALL() function in Tableau.  Finally, on to the chart.

Percent of Total Sales by Category (Tableau)

As usual, creating the charts is a drag-and-drop experience.  These exercises seemed almost identical between the tools.  They both leveraged similar functions to accomplish an identical task.  Not much else to say about it.

Winner: Tie

Lookups (Previous Year's Sales)

Lookups are another important part of KPIs.  Many companies like to compare KPIs for this period to KPIs from a previous period.  Let's see how it's done in Power Pivot.

Previous Year's Sales (Power Pivot)

This DAX expression is more complex than the last one.  Power Pivot prefers your data to be in a Star Schema.  Therefore, many of the DAX functions, especially the time intelligence ones, will leverage this.  However, this also means that there is a learning curve associated with these functions.  Let's check out the pivot table.

Previous Year's Sales by Year (Power Pivot)

The pivot table was easy to create once the DAX was finished.  Let's check out Tableau.  Remember that Tableau table calculations work based on what's in the chart.  So, we have to make our chart first; then we can calculate our KPIs.

Sales by Year (Tableau)

Now, we can make our calculation.

Previous Year's Sales

Finally, we can finish our chart.

Previous Year's Sales by Year (Tableau)

This wasn't too complicated if you know how table calculations work.  The predicament is this: We took two very different paths to get these answers.  So, how do we compare?  Personally, we feel that Tableau's "Canvas" concept is a little bit easier to grasp because it focuses on what you can already see in front of you.  However, Power Pivot has this handy PREVIOUSYEAR() function which almost does the work for you.  Therefore, we can't make up our minds on this one.

Winner: Tie

Summary

All in all, there's not much of a difference between the difficulty of using these tools.  Tableau's "Canvas" concept is very intuitive; however, it always makes u go through extra steps to get the formatting right.  Power Pivot seems to finish much cleaner; however, it requires a pretty star schema behind it (which does take time to create).  Therefore, we'll call this whole part a tie.  If you are looking to create these simple KPIs, feel free to pick the tool that you like best.

On a related note, some of the great features of Power Pivot are its built-in “Time Intelligence” functions.  We used PREVIOUSYEAR() earlier, but that’s just the tip of the iceberg.  We will devote an entire post to these types of analyses.  Thanks for reading.  We hope you found this informative.

EDIT:  Zen Master Jonathan Drummey keenly stated that no credit was given to Tableau's QUick Table Calculation feature.  On the other side, no mention was made of Power Pivot's KPI feature.  Both of these are extremely valid points.  These features are very useful for business users and the next section will be entirely devoted to these features.

Brad Llewellyn

Associate Data Analytics Consultant

Mariner, LLC

brad.llewellyn@mariner-usa.com

http://www.linkedin.com/in/bradllewellyn
http://breaking-bi.blogspot.com