Building Datazen Dashboards from Existing Power Pivot Models

August 12, 2015

Guest Post by Andrew Todd

image48-1024x526
Build awesome dashboards for mobile and desktop using Datazen

Datazen is a great visualization tool that is free to Microsoft SQL Server Enterprise customers. If you haven’t worked with Datazen yet, check out this post by Rob and Chris! Datazen allows you to design an awesome dashboard before you even work with any data! You simply draw a visualization, and then Datazen reverse engineers the exact layout of the aggregate table that you need in order to make that visualization work.

If you already have a PowerPivot model that you use for reporting, naturally you’ll want to use it to build visualizations in Datazen rather than reinvent the wheel entirely. One way to incorporate your dozens or even hundreds of measures and model logic (not to mention dozens of hours) into Datazen visualizations is through DAX queries.

Two Paths to Datazen DAX Query Nirvana…

Ok, you’ve built a fantastic BI dashboard in Datazen and your chakras are perfectly aligned. Now, there are two enlightening paths that you could take to build tables for Datazen visualizations with DAX queries:

A) Create a new ‘Data Connection’ in Datazen to your PowerPivot model hosted on SharePoint/SSAS Tabular, using the Analysis Services Data Provider. You can automate the refresh of data with SharePoint/Power Update and in Datazen itself.

B) Query your Power Pivot model using DAX Studio and save the workbook to a One Drive folder, then import the data into Datazen. This process can also be automated using Power Update.

Let’s take a closer look at both of these methods, starting with a SharePoint hosted Power Pivot model.

Read the rest of this entry »


Datazen = Beautiful Mobile Dashboards to Make Power Pivot Shine

May 12, 2015

Post by Rob Collie and Chris Finlan

Datazen:  A Beautiful, Mobile-Optimized Dashboard Delivery System That Makes Your Power Pivot Work Shine VERY Brightly

Datazen (The Latest Addition to Microsoft’s Suite of BI Tools) is a Mobile Monster
(Monster in a GOOD Way.  Yes, PowerPivotPro has its own DZ Custom Theme – You Can Too)

Datazen Q&A With Chris Finlan

***Intro from Rob:  Today I’m interviewing Chris Finlan of Microsoft about MS’s recent acquisition of Datazen, and what this means to us in the Power Pivot and Power BI community.

ROB:  Last month, Microsoft purchased a company named Datazen.  Most people had never heard of Datazen before, but you had pointed them out to me last summer I think.  You were already a big believer in them at that point, as were your customers.

CHRIS:  Yeah, I love Datazen.  I’m as passionate about it as you are about Power Pivot. I think it’s an extraordinary product, and have felt this way for quite some time Don’t believe me? Check out the date of my review in the Windows Store.  (Spoiler alert:  it was April of 2013 – that’s before I even applied for a job at Microsoft).

ROB:  You’re truly a trendsetter in tech and clothing.  I think one of the natural first reactions/questions from the community is, “wait, did MS just buy one of Power BI’s competitors, and if so, when do I use it versus, say, Power Pivot?”

CHRIS:  No, DZ was designed from the beginning to “only” be a visualization layer on top of the Microsoft Data Platform.  In your post on Visualizations Layers in Perspective: The Last Mile, you pointed out three key reasons at the end on why you’d buy a visualization tool.  Datazen checks all three boxes (and oh by the way, there’s no longer anything to buy – it’s simply a benefit you receive when you license SQL Server Enterprise with Software Assurance).

ROB:  Which means it’s free for many existing MS customers.  More on that later.  But I also want to talk about how DZ can be used to “light up” the great work being done by Power Pivot practitioners, because hey, that’s what we do around here.

Any Flat Table in Excel Can “Power” a Datazen Dashboard

image

EX:  Power Pivot Produces a Flat Pivot (or DAX Query Table),
and DZ Can Use That Excel File as a First-Class Data Source.
(The ONLY Server Required Here is a DZ Server – No SharePoint, No SQL, No SSAS)

Read the rest of this entry »


Displaying Top N, Bottom N, and “All Others”

November 11, 2014

Post by Rob Collie

image

If We Use Excel’s Built-In Top N Filter to See Our Top 1,000 Customers, It Hides the Other Customers Completely.  But Using DAX, We Can Just “Split” the Audience into Two Groups.

This Came Up Recently…

Hey, I absolutely ADORE the TOP N filter capability offered by all Excel Pivots.  It kicks major booty and we use it all the time:

image

The Top 10 / Top N Value Filter in Pivots:  Get to Know It, It Does Amazing Things

But If we set that to show us the top 10 customers, it shows us JUST those 10 customers:

image

OK cool, we see those top ten customers, and that they collectively purchased $132,026 of stuff from us.

But we want to ALSO see how much the OTHER (non top 10) customers are worth too.

Read the rest of this entry »


Toggling Between Different Units via Slicer?

July 29, 2014

Is this possible?

Someone at Microsoft asked me this question the other day:

“Sort of like how you’ve used a slicer for conditional formatting, is it possible to use a slicer to change the custom formatting of a number?  In my use case, I want to be able to display currency as either full number ($1,500,000.00) or abbreviated ($1.5M) as the viewer wishes.  See below for an example of the desire.”

Use a Slicer to Change Number Formatting from Raw to Millions/Thousands M/K?

Can We Do This in Power Pivot?

My Answer:  No, not possible.  Wait, maybe.  Hmm.  OK, yes, mostly.

All of these thoughts flashed before my eyes:

  1. Power Pivot measures/calc fields must always have a consistent data type.  You can’t have a measure return numbers sometimes and text other times, for instance.  All “exits” from an IF or a SWITCH must have the same data type.
  2. Apparently, #1 is no longer true in SSAS Tabular, in the 2014 release.  They now support “variant” data type measures. 
  3. But no, Power Pivot still lacks that “variant” measure capability, at least for now.
  4. Whoa, hold on a second.  The desired result above does NOT use different data types!  It’s all numbers!  So we just need to change the math!
  5. Oh, ouch, not so fast.  The “M” and the “K” – I don’t know how to add those labels in a numerical data type.

So this means…  text measures!

Read the rest of this entry »


Compare product performance after launch

June 26, 2014

By Avichal Singh

As part of the microsoft.com/learning team we release products throughout the year –courseware, books, exams and eLearning (check out MVA for tons of free courses). We often need to understand how our products perform after launch and how they compare against each other (in the first month since launch, first quarter, first year etc.). For Example: we would compare the various courses we launched around Visual Studio 2010. We may compare Visual Studio 2010 against Visual Studio 2012 courses. We may even compare Visual Studio against SQL Server. Or compare adoption by geography or customer segments.

I can imagine similar need for other businesses, e.g. a car manufacturer who needs to compare performance of various year, make and models.

Power Pivot and Power View can allow us to go from View 1 below, which is inscrutable at best, to View 2 which really helps us understand and differentiate the adoption ramp of various products. In this article, I would explain how you can go from View 1 to View 2 using the car manufacturer example.

View 1: Monthly Sales by Car Model
Typical view available in BI, but not very insightful

Power View Graph Monthly Sales by Car Model

 

View 2: Cumulative Sales since Launch, by Car Model
Clear view into adoption ramp of various products

Power View Graph Cumulative Sales since launch by Car Model

Read the rest of this entry »


Cross-Sheet Hyperlinks Part Two

May 15, 2014

 
Power Pivot Scorecard Mockup With Clickable KPI Metrics

Scorecard with Clickable “Key Performance Indicator” Metrics
(Yes, the colors are awful, sorry – I lack that touch)

Not Just for Sheet Navigation, But Also for “Drilldown”

Picking up from Tuesday’s post

When you click that “Customer Behavior” metric in the image above, you are taken to this detail page:

Detail Drilldown Report - Just a Pivot Designed by Me On Another Sheet

The single “19%” Number in the Scorecard “Expands” to this Full Detail on Click!
(Also note the “Back to Scorecard” Link)

That Was a Cube Formula Cell!

Read the rest of this entry »


Pro Tips: Cross-Sheet Hyperlinking Part 1

May 13, 2014

                            image

Hyperlinks Are a MUCH Better than Making the User Find the Right Sheet Tab         

Sorry About Last Week Smile

Yeah, we were a bit “light” on new material here on the blog last week.  That happens when I have four straight days of presentations out of town (more on PASS BACON later).

Inspired by Kasper’s Book!

I was reading Kasper’s new book on the plane (yes, I was home less than 24 hours, and am now back on the road), and its singular focus on building a full-featured set of dashboards inspired me.

It is clearly time for a mega-post on hyperlinks!

First Usage:  a Menu Sheet

As pictured at the top of this post, hyperlinks are a great way to construct a “menu” sheet.

Not only is this a BIG convenience for the “consumers” of your workbooks, but it makes the whole thing “feel” much better too.  More professional, more like an application and less like a spreadsheet.

Yes, hyperlinks can link to other locations in the workbook!  It’s easy in the Insert Hyperlink dialog:

Read the rest of this entry »


Optimal Set Selection, Power Pivot Plays Pokemon, and My Brain Just Exploded

February 13, 2014

image

Un.  Freaking.  Believable.
(See bottom of post for video of this in action!)

Back to Our “Regular” Programming Today

First of all, THANKS for being patient with the promos for PowerPivotPro University – we do have to pay the bills here of course, AND I have poured six months of my best work into it, so thanks for giving it your consideration.

But yeah, it’s time to get back to some serious magic tricks with data.

A Funny Thing Happened On Reddit and Twitter…

I don’t play Pokemon – neither the video nor card game variety.  But I recognize my kind of sickness (cough cough fantasy football) when I see it.

And people who know me, ALSO know my sickness.  So they point things out to me:

And that led me to this Reddit thread:  Pokemon & Power Pivot…

Read the rest of this entry »


Another Way to Find the Top-Performing “Thing”

January 14, 2014

 
Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

Pretty Nifty PivotTable

Best Selling Product, or Date, or Top Performing Player - All Can Be Handled, Including Ties, in Power Pivot!

Same PivotTable, Diagrammed/Explained

Revisiting an Older Topic

Read the rest of this entry »


Grand Total Mania! (Totals at Top & Multiple Totals)

January 7, 2014

Let’s start 2014 with a bang…

I’ve been dying to write this post for awhile now.  Let’s say you want your grand totals to appear at the top of your pivot, like this:

Grand Totals at the TOP of the Pivot?  Yep, no problem.

Grand Totals at the TOP of the Pivot?  Yep, no problem.

Typically you’d want to do this when your pivot is really “tall” – lots of rows – and you don’t want to force people to scroll down in order to see the grand total.

Pivots do NOT let you display grand totals at the top of the pivot – only the bottom – so a frequent workaround is to write a formula in Excel itself that sums the whole column of the sheet.  Very clumsy, and damn near impossible when you have two fields on rows like above.

How DO You Do It, Then?

The trick is simple – add a dummy table to your Power Pivot data model:

Read the rest of this entry »


A Simple Trick for Combating “Stale” Slicers

December 3, 2013

 
Help!  My Power Pivot Slicers Don't Auto Update to the Latest Date on Refresh!

Two Weeks of Refreshes Later…  the Report Still Thinks
Nov 15 is What Everyone Wants to See First!

Your Dashboard is Refreshed, But its Slicers are Stuck in “Yesteryear”

This is a trick I’ve been using forever but never blogged about.  Enough of you are now using Power Pivot for SharePoint (PP Server) that its time has come.  And really, it’s relevant on the desktop too.

On the day you first made this report, you selected the most recent Date (or Week, etc.) in the slicer.  And you saved the report.  All was right with the world!

But then, tomorrow comes.  And all of your slicers still have that “old” date selected, even after you refresh everything.  Ick.  Who wants to go and update all of those slicers to point to the latest date?

I sure don’t.  So, like me, you just let them sit on an old date (or Week, Month, etc.)  This forces the consumers of that report to ALWAYS click the latest date, sometimes after scrolling the slicer to the bottom.  Every time they open the report.  They.  Don’t.  Like.  That.  And neither would you.

Read the rest of this entry »


Custom Toooltips in Dashboards!

November 12, 2013

 
Power Pivot Dashboard Tooltips/Comments

Custom “On Hover” Tooltips on Each Cell in the Dashboard!
(The yellow dot and distortion around mouse pointer are GIF side effects and do NOT appear in Excel)

Question from PowerPivotPro School!

Got a great question the other day from Oscar, a student in PowerPivotPro School

[OSCAR]:  “Is it possible to have a tooltip in powerpivot which shows additional information based on the cells selected (or mouse roll over). the info to be displayed comes in from a table created with cube fuctions on the same data source. So the coordinates of the highlighted cells would be inputs for the cube formula and result displayed in a tool tip dynamically.”

My first thought was “no, not possible.”  Then ten seconds later, a guerilla-style hack came to mind.  And then, my reply:

[ROB]:  “Oscar you are a very, VERY bad man. I am now obsessed with this problem. There goes my Sunday.”

The Trick:  Hyperlinks to Nowhere!

Read the rest of this entry »