Another Time Dimension question

I want to track sales vs. both date scheduled to be shipped and date actually shipped. Both are fields in my OLTP. I can set the SSIS program to extract data any why I choose.

Would it be best to create a fact table and 2 different dimension tables or combine the time fields in one dimension table or leave them in the fact table and let SQL Server extract the time dimension?

Thanks.

Hello! In SSAS2005(Analysis services 2005) you have something called 'Role-playing' dimensions that solves this problem. You will only need one time dimension in the starschema that you join to all dates in the fact table.

If you have these relations designed in the source system/star schema or in the data source view SSAS2005 will detect this relation and create separate time dimensions automatically when you build the cube.

HTH

Thomas Ivarsson

|||I don't want to apply one time dimension to many fact tables. I want to apply many time dimensions to one fact table. Right now my date fields are in the Fact Table.|||

I do not think that I have said that but my explanation, perhaps, was not good enough.

You use one time dimension and join the fact tables different date dimensions to the same date key in the time dimension table.

Connect the key for order date in the fact table to the date key in the time dimension. Connect the date key for invoice date in the fact table to the date key in the time dimension.

HTH

Thomas Ivarsson

|||

Ahh...that's better....I think I need to take my dates out of my fact table and create a dimTime table and then I can create the multiple relationships.

Thanks.

|||

That seems to be working but I'm a little fuzzy on the theory.

Either or both date fields may be null in my OLTP table, so I can't use either for the key field. My OLTP table has an integer key that is just a counting number.

I created the dimTime table with the same type key field. When I did my SSIS run I just copied the Fact table key field to the dimTime table key field along with the date fields.

The tables are linked but not throught a date field. SSAS is smart enough to build its aggregates on the date fields and pretty much ignore the key field? It only cares that the tables are linked somehow?

Thanks!!!!

|||

You can add a theoretical time member to the time dimension like '2099-12-31" and point fact records without time members to that time member.

There will be many more SQL Server releases until we reach that date.

HTH

/Thomas Ivarsson