Microstrategy Blog

The first in an ongoing conceptual series.

It isn’t a very good idea to make a Attribute that is just a date.

In a fit of “noob“, I decided that I wanted to add date field to my report. We had a specific project that started and ended at a specific date, and instead of adding extra parts to the Attribute itself…I created a new attribute called “Project Start Date”. The ID was the date. In my little head it made sense…in retrospect I have no idea why. The problem arose when a report I was working on made multiple passes against my project table due to a combination metric I’d created. The SQL likes to join against ALL common elements in the table. This means that you’ll see a lot of (in shorthand):

a11.PROJECT_ID=a12.PROJECT_ID and
a11.PROJECT_START_DATE=a12.PROJECT_START_DATE
a11.PROJECT_END_DATE=a12.PROJECT_END_DATE

See the problem here? Yep…waaaaay more joins than are necessary…PLUS, if you have the distinct pleasure of having a null value, then any join along those lines is worthless. The report ends up being a mess.

The quick and easy solution is to create the date attribute using the key column of your table as the ID, in my case PROJECT_ID, and the date column as the DESC. This forces all joins to be off of one primary key. The other solution is to convert the date into an easily indexable value that you can then join against sort sort of date hierarchy (ex: 20090428). In my case…the first was more practical, as I like having one true date hierarchy, and it’s not based on project dates (we’re very transactionally centric for our time lines).

Any one else out there have “the hard way” stories? I’ll post them for you. Email them to microstrategyblog@gmail.com