image image

These Two Columns Both Contain Dates, Right? Sure They Do!

Relationships that should work, but aren’t?

Here’s a quick tip, one that I think we’ll all need sooner or later. 

When I got back from last week’s amazing consulting/training trip, the team let me know that we had a problem.  An existing PowerPivot model, one that had been working just fine for a long time, had stopped working when we refreshed the model with the latest data:

image

All the slicers were now indicating that there wasn’t any data.  And those empty rectangles on the right?  Those are charts.  They just weren’t showing any data anymore.

What’s the problem?

We pretty quickly determined that it had something to do with the date relationships.  When we cleared the date slicer, data came back.  So it was just when we were filtering by Date that data disappeared.

imageIn cases like that, the first thing I like to check is data types.  Somehow, did one of the columns get changed from Date to Text?  Nope, not this time.  They both are definitely still Date, as evidenced by this screenshot from the PowerPivot window ribbon, here at right.

 

But Data Type Isn’t Enough!

Notice the Format option there, though?  Try setting both related columns to a format that displays time as well as date:

image

Now look at the two columns:

                                         image   image  

Like I said, sneaky little Date/Time data type.  When we got new data this time, one of the source systems in the loop decided to include the time of day, whereas before, it had been omitting it.

So yeah, the two columns don’t match up anymore even though by default they LOOKED like they matched.  Sneaky.

So…  if you have a relationship on two Date columns, and the relationship does not seem to be working (all of your measures are returning blanks), this is a likely culprit.

Two ways to fix this

The quickest fix is to create a “clean” calc column that strips the time:

   =DATE(YEAR([CalendarDate]),MONTH([CalendarDate]),DAY([CalendarDate]))

Then you use the calc column for the relationships rather than the original.

But the better, more reliable way to clean your date columns is in the underlying SQL (as long as that’s an option).  If your SQL sources always trim your Date columns down to pure Dates, and truncate Time, then you never have to write calc columns for this purpose again – solve it one place, and save yourself a lot of ongoing work.

Furthermore – columns imported from SQL end up being a lot better compressed by PowerPivot than calc columns, AND this often results in faster pivot performance as well.

If you do the trimming via Views, you can keep the time component in your SQL tables for later, in case you want to do “time of day” analyses.  But your default views that you import should always be protected against this.

And remember, if you aren’t a SQL pro yourself, having a good relationship with the folks who run your databases is a very positive thing.  At Pivotstream for example, I never touch SQL.  But my colleagues who maintain SQL are very helpful, and our cooperation lets us do things that otherwise would be impossible.  One of my favorite themes – cooperation between PowerPivot pros and SQL pros – and I will be hammering said theme every chance I get Smile

  Subscribe to PowerPivotPro!
X

Subscribe

Rob Collie

One of the founding engineers behind Power Pivot during his 14-year career at Microsoft, and creator of the world’s first cloud Power Pivot service, Rob is one of the foremost authorities on self-service business intelligence and next-generation spreadsheet technology. 

This Post Has 7 Comments

  1. I’ve run into that also. We’ve had to be very careful when creating our cube data to specifically format the data in our ETL and in the cube so when we pull it into PowerPivot we can just drag and drop. It doesn’t help that SQL Server 2008 R2 SSAS defaults everything in the cube to a “text” data type, rather than defaulting the underlying data type from the table, like it used to. It seems SQL 2008 R2 went backwards a few steps. They also don’t handle many-to-many relationships the same way, which limits capability when trying to do relationships between dimensions.

  2. I have run into this issue myself and agree that, ideally, this conversion would be handled in a data warehouse. That said, in my own experience, PowerPivot is great for quick-and-dirty prototyping that may require data that doesn’t yet exist in the data warehouse. For example, I’ve built a mashup using multiple dimensions, including a fairly complete date dimension, that are in a data warehouse and a fact table (really just a table from a new source system) that does not yet exist in the DW. This new fact table contained a datetime column with minutes. My first thought was to use a calculated column to convert the fact table date into the integer surrogate key of the DW date dimension. However, something that surprised me in working with PowerPivot, was that while DW “best practices” suggests using integer surrogate keys for dates, PowerPivot “best practices” discourages it. I ended up creating a new column using Excel’s DATE() function and created the relationship with the date dimension’s alternate key (datetime format) and it worked well. A view would have been better but for my immediate prototyping needs the calculated column worked fine.

    My takeaway here is to trim the date values in a view or the SQL source query when you can, for performance and usability sake, and, if dates are not delivered this way, a PowerPivot user may need to understand date conversions to make some table relationships valid, even more so because the default date format hides the issue. Thanks for sharing, Rob!

  3. Rob describes how the Date relationship wouldn’t work at all with the extra time component. Interestingly, one of Marco’s readers experienced a different problem. His relationship remained intact despite the time component but it seems that PowerPivot used the rounded value of the DateTime field to map to the Date field which caused some unwanted results.
    http://sqlblog.com/blogs/marco_russo/archive/2011/06/14/strange-date-relationships-with-powerpivot.aspx

Leave a Comment or Question