Month: July 2013

Drill-down Behaviour in Power View

Drill-down Behaviour in Power View

July 25, 2013

I usually don’t write a lot of posts asking for favours from my readers (in fact, this is the first one), but an incident happened today at my client site which has aggravated a pet peeve that I have with Power View – drill-down behaviour in Power View. The drill-down behaviour in Power View is really unintuitive and this is at least the the third client of mine that has raised this issue of drill-down. That is when I decided to raise my voice and ask for your help too in letting Microsoft know about this issue, so that they can consider changing this in a future release. For those of you who don’t have the time to read this post of mine, I would really appreciate if you could vote this Connect issue that Leonard Murphy has raised. For those of you who have the time, follow my post below to see the current drill-down behaviour in Power View and why I feel that this is not right.

Drill down in Power View

For this demonstration, I am using Power View in Excel 2013 (although this behaviour can be reproduced in Power View for SharePoint also) and a very simple table (which shows the sales by countries and states) as my source.

Source Data

I) How to Implement Drill-Down in Matrix

1) Open Power View and then select the three columns to make a table.

Simple table visualization

2) Click on the Table icon and select the Matrix option. Now you can see that the table has been converted into a matrix.

Convert table to Matrix

3) Now select the Show Levels icon and click on Rows – Enable Drill Down One Level at a Time.

Enable drill down

4) You can see that we have achieved the drill down functionality in the matrix. Click on US and see the results.

Drill down in matrix - Power View

Also note the drill down and drill up icons.

II) How to Implement Drill-Down in Chart

1) As in Step 1 of previous section, select all three columns to make a table. Then click on the Bar Chart option and select Stacked Bar. Ensure that the Country and State are in the Axis and Sales is on Values.

Chart in Power View

2) You can see the drill-down functionality by clicking on Canada. Also note the drill-up icon on the top right of the chart once you have drilled down.

Drill Down in Chart - Power View

III) Unintuitive Cross-filtering behaviour

1) Everything looks fine when you just have one chart in the canvas. Now let’s see what happens when we have 2 visualizations – lets say, a bar chart with drill-down and a table which just has State and Sales.

Chart and table in Power View

2) Now when you single click on a country (say India) in the chart, the table gets cross filtered for the states of the selected country.

Chart cross-filters table

3) Next, see what happens when you double click on a country (say India).

Double clicking in chart removes cross-filter in table

You can see the bar chart has drilled down into the states for the selected country as expected but look what has happened to the table. The table is now showing states for all the countries and not just the states of the selected country. This is so unintuitive as a person seeing the report will expect to see only the sales of the three states of India. This can get even more confusing if the states are not displayed on the table, and there will be no way of knowing what has been cross-filtered on the table.

4) To add to the confusion, I can press CTRL and then click on all three states. Look at the image below for the results.

CTRL+Click on the three bars -> table is cross-filtered

You can see that the bar chart looks exactly the same as the previous image, but the table looks different. In short, the report looks exactly the same and there is no way to differentiate whether the data in the table is correct and if some cross-filter has been applied or not. What I would expect is that the drill-down always cross-filters the rest of the visualizations. Honestly, I can’t think of a scenario where I would not want the cross-filtering to happen and I would love to hear your views as comments.

IV) Call for Action – Vote up the Connect issue

If you agree with me that the cross-filtering behaviour is so non-intuitive, please vote up the connect issue. You can do so by clicking on this link, log-in and then clicking on the green up arrow.

Click to vote

Hopefully we will have enough votes for Microsoft to look into the issue seriously!

Posted by SQLJason, 11 comments
Power BI for Office 365

Power BI for Office 365

July 9, 2013

Great news!!! Today, at the Worldwide Partner Conference, Microsoft announced a new offering – Power BI for Office 365. Quoted from Microsoft – “Power BI for Office 365 is a cloud-based business intelligence (BI) solution that enables our customers to easily gain insights from their data, working within Excel to analyze and visualize the data in a self-service way. It works with Office 365 to help customers share insights, find answers and stay connected to their data from their favorite mobile devices.” Announcing Power BI for Office 365 For those of you who have the time, I would recommend going through the links that I have collected below. And for those who don’t have the time, let me do a very quick summary:- 1) Discover, analyze, and visualize with Power BI for Office 365, which includes Power Query (formerly known as Data Explorer), Power Map (formerly known as GeoFlow), Power Pivot (formerly known as PowerPivot – notice the space between the words) and Power View (for a change, I like that the names are standardized with a space after Power). 2) Create Power BI Sites to share live interactive reports. Power BI Sites are dedicated collaborative BI workspaces in Office 365 for sharing data and insights with colleagues. 3) Ask questions and get back instant answers with the new Q&A feature that uses natural language query technology to provide you with immediate answers in the form of interactive charts and graphs based on the questions you type into the speech bubble. 4) Browse Excel and Power View reports with HTML 5. Stay connected with the touch-optimized Power BI mobile app. BI users can access and receive live updates on their reports through their browser with HTML5 or through a mobile application designed for their tablet or touch-enabled device, either Microsoft Power BI for Windows or Microsoft Power BI for iPad. 5) Refresh your data on demand or define a schedule. The Data Management Gateway allows your IT department to enable data refresh from Office 365 to your favourite on-premises data sources. 6) No announcement on pricing or preview date (except for “later this summer”). The natural language feature looks pretty impressing even though I doubt whether the experience is going to be as seamless as shown in the demo. Also, the bubble chart shown in the demo (also shown in the image below) looked extremely jazzy but may end up just being eye-candy in real life. I would love to finally see the Mobile (and ofcourse iPad) solution come through with Power BI, although I am a tad bit disappointed that this feature is not there for the SharePoint version. Bubble chart from Amir Netz keynote demo - Power BI There are a couple of official posts on this offering and I have collated them below:- 1) What powers Power BI in Office 365?
2) Introducing Power BI for Office 365
3) Announcing Power BI for Office 365
4) Register for Power BI Preview Watch the Day 1 Vision Keynote from WPC 2013 on demand here! You can also read some of the initial reactions to Power BI below:- 1) Some Thoughts About Power BI – Chris Webb
2) Microsoft Office 365 Cloud Power BI – Jen Underwood
3) Microsoft Announces Power BI for Office 365 – Andrew Brust
4) Power Business Intelligence for Office365 – Jen Stirrup Exciting times indeed for Microsoft BI and Self Service BI in general. I can’t wait to get my hands on the preview.

Posted by SQLJason, 2 comments