PowerPivot Nuggets - Part 3 - Drill-through

Analysis Services, PowerPivot's older and bigger brother, has had drill-through functionality for quite some time now. Basically this feature allows you to drill-through a cube and go to the underlying datasoure and fetch the detail rows.

This feature is now coming to PowerPivot. Right click on a measure and choose "Show Details". Or simply double-click on a measure.

Excel will open up a new sheet and fetch (by default the first 1000 rows) of the underlying table.

You can change the number of rows to fetch in the connection properties of your PowerPivot Data Connection.

A nice new feature that will make data analysis a lot easier.

PowerPivot Nuggets - Part 2 - Hierarchies

About two years ago I opened the following item on Microsoft Connect.

"Dimension Hierarchies (eg. Category - SubCategory - Product) are a key element for making cube concepts easier to understand and use for a lot of business people.

I didn't find a way to create hierarchies in the current CTP of Gemini. I hope this will make it in future CTPs and the final relase."

Hierarchies didn't make in PowerPivot V1 but they are here now on V2.

As highlighted in Nugget 1, hierarchies are created in the Diagram View.







Once created you can use them in your pivot table in the row and colum areas. Another cool thing is that you can also drag a hierarchy to the vertical or horizontal slicer areas. Excel will automatically create a slicer for every level in the hierarchy.

Cool stuff!

PowerPivot Nuggets - Part 1 - The Diagram View

"Relationships are the most important thing to understand in PowerPivot". It's one of the first things I teach my students in every single PowerPivot training. Missing or incorrect relationships will lead to incorrect DAX calculations and wrong results in your pivot tables. The funny thing in V1 was that you needed to create and debug relationships in a tiny message box with only 4 drop down boxes.

The good news in V2 is that PowerPivot ships with a great diagram view.

The diagram view allows you to

• get a clear ERD like view of your model and navigate trough it
• create and manage your relationships
• rename tables and columns
• Create hierarchies
• ...

A long anticipated feature and a very nice addition to the tool.

Announcing PowerPivot Nuggets

I have been working with PowerPivot for about two years now. I still think it is one of the most fascinating pieces of software that Microsoft has ever released. PowerPivot will dramatically change the way we do business intelligence projects in the coming years.

Earlier this month Microsoft released SQL Server Denali CTP3 to the public. Along with the CTP came a new (CTP) version of PowerPivot. This public release is great news because we can finally blog and talk about the cool stuff coming to the next release of PowerPivot.

So what am I announcing today? PowerPivot Nuggets. In the coming weeks I will blog almost daily about one new feature in PowerPivot Denali. No long in-depth articles, but just small nuggets highlighting one new feature.

First nugget is coming tomorrow on the Datagram view.

Enjoy!

• PowerPivot Nuggets - Part 1 - The Diagram View


• PowerPivot Nuggets - Part 2 - Hierarchies


• PowerPivot Nuggets - Part 3 - Drill-through


• PowerPivot Nuggets - Part 4 - Perspectives


• PowerPivot Nuggets - Part 5 - Mark As Date Table


• PowerPivot Nuggets - Part 6 - Sort By Column


• PowerPivot Nuggets - Part 7 - Multiple Relationshi...


• PowerPivot Nuggets - Part 8 - Parent Child Relatio...


• PowerPivot Nuggets - Part 9 - Descriptions


• PowerPivot Nuggets - Part 10 - KPIs