Dimensions and fact dimension
I need to provide several columns not in a cube for the drilldown - this is done using a fact dimension in SSAS 2005 I learned. I set up the dimension with the id (int) primary key as finest grain level and a lot of columns as attributes. This works quite nice, but I wonder if it is the best setup.
The table that the fact dimension is based on has quite some fields (40) and 7 datetime fields. The table is normalized, the date fields are "Order date", "Invoice date", "Ship date", "Received date", "Billed date" and so on for example, real different meaning, not just badly designed.
All of them need to be available in drill, so they are all attributes of the fact dimension. Each date is split up in year, month, day to build a hierarchy upon, and therefore there are 2 more fields with month_as_text and day_as_text, so 5 calculated fields x7... and a lot of other attributes. The fact table is 2 million lines long... takes a while to work through that...
Should I put each date in a separate dimension, also based on the int id? That would mean my cube gets 7 extra dimensions, but since all are really based on the id like the fact table, they should not really "add" to the overhead... or do they?
-
Another question around my datetime fields: They all do use hours, minutes and seconds. When drilling, I need that data to be visible, when slicing in the cubes hierarchies all I need is year->month->day and not the time. I cannot say how that plays into the situation, but I wanted to mention it.
-
And my final question: The table that the fact dimension and measure group is set on has a couple of columns that are created by boolean expressions, like "is only books in the order" or "was paid with credit card" and so on, and atm they show 0 and 1 for true and false. What would be the best way to make around 15 of these show "yes" and "no" instead of 0 and 1?
Making the boolean expression return "yes" and "no" is a problem, since the same fields are sum'ed up to provide a counter of events. The fields are used as dimension base and measure at the same time... this is bad, right?
If you want to comment any of this, please do! I am looking forward to any ideas of improvement!
