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:
Since the relationship Detection is turned on by default in the PowerPivot tab in the Ribbon:
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.
So I go ahead and run this option
And unfortunately it was not successful
So I have to go back into the PowerPivot window and manually create the relationship
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.
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):
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
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
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
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
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!