Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Alternative to drill through in Excel 2013-Quick Explore

Posted by thomasivarssonmalmo on November 24, 2013

I sometimes get feedback on features from customers that I did not recognized when testing the feature on your own. Quick Explore in Excel 2013 is such a feature.

When we build BI solutions and start to deliver data in a tool customers usually would like to validate the data against referenced transactions in the source system. BISM Tabular and BISM Multidimensional both have a feature for this called drill-through but you have limited control over what will be presented.

I start with a Pivot Table in Excel 2013 that use a multidimensional cube but it will work the same in Tabular. You probably recognize the Adventure Works multdimensional  cube.

StartUpPivotTable

I the next step I will highlight the CY 2005 and United Kingdom cell that tells that the order quantity was 96. You will see a small magnifier glass appear to the right of the cell. That is Quck Explore.

QuickExplore

Qlick on that icon and a small window will open up. I have choosen to go down drill to Internet Sales Order Details –> Sales Order Line to get the reference numbers.

 

DrillToQE

The result might seem disappointing first since we will se all years of order quantity for United Kiingdom not only 2005.

 

UK for all years

This issue can be quickly fixed by moving years from columns in the report to the report filter box.

 

MoveDateToFilters

Finally we have a clean list will the sales order numbers for United Kingdom and the year 2005. It is a clean list without any other redundant information.

 

EndResult

In a Tabular or Multidimensional model you will need to creat a 1-1 dimension with the fact table refered to as a degenerate dimension and in large fact table scenarios this can be a performance problem.

Happy Quick Explore!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: