Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).

PowerPivot – Creating Hierarchies

Hierarchies are one of the long awaited features that will now be available in the latest version of PowerPivot. Hierarchies are useful because it allows a single click for a user to bring in all the fields that exist in a natural table hierarchy.  Normally the user would have to drag in all fields individually so this can be a time saver especially if it is an 8 level hierarchy. 

Hierarchies are very simple to create and easy to interact with in the reporting layer as well.  To get started you must be in the Diagram View of the PowerPivot Window.  Find the table you wish to create the hierarchy on and either right-click on it or click the Create Hierarchy button shown below. 

 

 

After the hierarchy is created you will immediately be able to rename it.  After renaming it you drag the fields from the table down to the hierarchy that was just created.  You can also rename the fields so they are more user friendly by right-clicking on each individual field.  Here I’ve renamed my hierarchy and all my fields to something my users are more familiar with.

 

Let’s take a look at some of the options you are given when right-clicking on fields:

·         Hide Source Column Name:  Removes the source column name that appears in parenthesis next to the column name that you have in the hierarchy.  This only removes it from the diagram view because the source column is never shown in the reporting layer.

·         Move Up:  Moves a field to a higher level in the hierarchy.

·         Move Down:  Moves a field to a lower level in the hierarchy.

·         Remove from Hierarchy:  Takes to field completely out of the hierarchy.

·         Rename:  Allows you to rename a hierarchy field to something more user friendly.

When you are happy with the hierarchy you have created you can use it in the reporting layer of excel.  The hierarchy interacts with the pivot table just like it would if it had come from an Analysis Services cube.

Comments

Posted by sanjay-590546 on 3 August 2011

Hi Devin,

this looks like very interesting. But where did you get this version of Excel from ? if it available as a public download ?

Regards

Sanjay Shah

sanjay@prosysinfotech.com

Posted by Jason Brimhall on 3 August 2011

Very useful information.  Thanks for sharing.

Posted by GPO on 3 August 2011

Ain't it always the way. This feature appears to be related to Denali. We've just fought tooth and nail to get our databases (well most of them)   moved to 2008 r2 so that we could use power pivot at all. We even got 64 bit desktop machines to overcome the memory hobbling in 32 bit Excel. We've found Power Pivot to be a somewhat half-finished product because of the lack of hierarchies and grouping on the fly. There's no way our change-averse management is going to let us move to denali any time in the next three years. Will the important improvements you describe be available to those of us not using Denali?

Posted by knight_devin@hotmail.com on 4 August 2011

-Sanjay

As long as you already have Excel 2010 you can download the PowerPivot CTP3 now.

www.microsoft.com/.../details.aspx

Posted by knight_devin@hotmail.com on 4 August 2011

-Greg

This is actually independent from SQL Server.  The download is labeled as Denali but all you need is Excel 2010 .  The data sources you use is up to you!

Posted by Bob Press on 11 August 2011

Do I need to uninstall the current power pivot add in, or can this be installed over it?

Thanks

Bob

Posted by knight_devin@hotmail.com on 1 November 2011

Bob i think you'll need to uninstall the current first.

Leave a Comment

Please register or log in to leave a comment.