Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Archive for June 23rd, 2010

Sorting PowerPivot Labels Manually

Posted by denglishbi on June 23, 2010

One of the items that you are going to run into is the sorting order of items in PowerPivot.  For the most part you might get by with items being in ascending or descending order, but there are going to be times when a more manual approach might be needed.  One particular case would be the order to calendar month labels in a report.  Initially it would be displayed in alphabetical order like the following:

image

So how can you get this in the order that you are used to seeing the months displayed?  As you can see this is not how you want it and it isn’t like Analysis Services where you can define an additional column to sort the data by.  One option would be to have an additional sort order column that you could concatenate with the month label like the posting here by the PowerPivot Team – Sorting month by natural sort order.  Another option is to use the manual sorting functionality that is available.  How does that work?  It isn’t extremely obvious and after a bit of playing around I finally figured it out.  Here is how you can do it:

1. Right-click the label and select Sort and then More Sort Options…

   image

2.  In the Sort dialog box select the Manual (you can drag items to rearrange them) option and then click OK

   image

3.  Now in the PivotTable select an item and place the cursor towards the right-hand side of the cell until you get the arrows displaying in all four directions

   image

4.  Now click on that cell and drag it to the desired spot where you would like it to be displayed

   image

5.  Then drop the item and now you have not only moved the item with this display, but everywhere it is being displayed in the PivotTable

   image

6. Now simply repeat the process until everything is in the proper order that you want for reporting.  Note: this is just for this PivotTable.

   image

There you go.  Just another option that you have for sorting items in your PowerPivot reports.

UPDATE (11/22/2010) – I have another blog posting here in regards to sorting that you might find interesting as well – Sorting PowerPivot Labels Continued
Advertisements

Posted in PowerPivot | Tagged: | 6 Comments »

PowerPivot Relationship Detection Experience

Posted by denglishbi on June 23, 2010

I just wanted to put together some notes and feedback with the relationship detection feature in PowerPivot.  I was working through the samples in the recently published Professional Microsoft PowerPivot for Excel and SharePoint book and had run into an issue with loading the data PowerPivot Memory Error – Not Enough Storage.  This caused me to have to import the last fact table, FactSalesQuota, separately.  When I did this none of the foreign key relationships were formed on import into PowerPivot and I realized this when I started to do the first PivotTable example and saw that the numbers for Sales Amount Quota were not being reflected properly:

image

Since the relationship Detection is turned on by default in the PowerPivot tab in the Ribbon:

image

I was prompted at the top of the Field List in the Excel worksheet that ‘Relationship may be needed’ and I can click on the Create button to have PowerPivot automatically try and detection and create relationships between the tables that are currently being used in the PivotTable analysis.

image

So I go ahead and run this option

image

And unfortunately it was not successful

image

So I have to go back into the PowerPivot window and manually create the relationship

image image

And when I go back into the Excel worksheet with the PivotTable I get a new message above the Field List stating ‘PowerPivot data was modified’ along with a button to Refresh.

image

After click on that the data in the PivotTable is updated and now I get the correct view of the data (minus the proper sorting of the calendar months, but that will be resolved in a later post):

image

So why didn’t the auto detection wizard work? This seems like an extremely easy detection since the column names have the same name minus the case – in the Date dimension the primary key is Datekey and in the Sales Quota fact table the foreign key DateKey (see the Create Relationship screenshot above) .  You will notice that in the Contoso Retail DW database not all of the columns have camel casing setup the same.  I thought this might be the issue, but I tried the same scenario with the Channel dimension with the Sales fact table

image

 image

and that was successful – in the Channel dimension the primary key is ChannelKey and in the Sales fact table the foreign key is channelKey.  Since this worked properly I would have to say that the issue then is with the data type.  I did find it really odd that the primary key for the Date dimension was a datetime data type

image

so as of now I would have to say that the auto relationship detection feature will not work with datetime fields.  If the fact table would have imported into PowerPivot the first time without the storage errors then the relationship would have been created automatically because of the relationships and constraints that are defined in the underlying database.  In my case though they were not and I was forced to create them manually.

If we hop over to the ever famous Adventure Works DW database and do a similar detection with the Date dimension and one of the fact tables it does work

image

image

image

but in this case the big difference is that this database uses a int data type versus the datetime like the new Contoso sample uses

image

I am assuming that they went with the datetime data type to simulate more real world data that an end-user might receive in a data feed or source extraction.  As long as the end-user knows how to create manual relationships this shouldn’t be a show stopping issue, just something to be aware of when working with different data types.

The other thing to note was that once I created the relationship manually in the Contoso example and later deleted it to try and perform some additional relationship testing, I was unable to get the relationship detection wizard to reappear.  It would be nice if you could some how force this to appear instead of having it magically display above the Field List.  It is just a wizard, so I would expect an option in the Detection portion of the PowerPivot Ribbon to be able to manually call this feature.  Well, this is version 1, so maybe in the next release this data type issue will be resolved and a new option will be available for detection.

If you think that this appears to be an issue and should be worked on please vote here (I posted as a suggestion instead of a bug, wasn’t sure what the proper classification was for this) – PowerPivot DateTime Relationship Detection.  Thanks for voting!

Posted in PowerPivot | Tagged: | Leave a Comment »