Simple AS 2005 migration question (Time Dimensions)
I am updating work done on cubes under AS 2000.
In AS 2000 a Datetime field could be linked directly to a Time Dimension from which the levels inside that dimension could be selected. This included being able to break a date into fiscal quarters. It was not necessary to divide up the field containing the full date and time into Day, Week, Months, year columns.
In AS 2005 it seems I must break the date up into Day, month, and Year then link these fields to time dimension and specifying whether they are fiscal or calendar. Is there is a way to have the same intelligence as AS 2000 where the date partitioning is done for you without breaking the field up ?
Can someone point me in the right direction ?
Any help appreciated
Have you tried server generated time dimension? If your fact table contains a field of type DateTime then it is enough to relate it to a server generated time dimension. The key attribute of such dimension is also of type DateTime, while everything else is also generated.
Have been looking into this and coming up against some further questions.
The data source fact table I am accessing is a SQL Server view, therefore no specific Datetime data type specified (not sure whether this is having an influence).
I create a Server Time Dimension and specify a from and start date I wish to include from my dataset. After doing this I add this dimension to my cube and process it. When browsing the cube the time dimension (Year-Quarter-Month) does not influence the values in the dataset. I can drill through on dates included between the from and to date but they do not affect the measures within the cube.
It seems to me as the "DATE" field inside the fact table is not linking at all to the Time dimension.
Where have I gone wrong with this ? and How can I just select the "DATE" column in the fact table and have it split automatically into the different levels as it did under AS 2000? Ideally do not want to have to DatePArt out the date field to link Year-Year, Month-Month etc etc
Any help much appreciated.!
Have you check the relation between the time dimension and the fact table in the data source view(DVS)? You will only need a primary key -foreign key relationship in the DSV.
If you are missing any time members you can add a named calculation, for each member, in the DSV(right click on the table in the DSV). If your data source is SQL Server you can use TSQL-functions like YEAR(DateField) and MONTH(DateField) to create Year and month columns.
I do not now about any other shortcuts except adding these columns in your data warehouse.
Regards
Thomas Ivarsson
This helped alot thankyou.
The part I was missing was linking the Dimension Usage page back to the Server Time dimension.
This works really well and allows me to use just the one time dimesion in numerous cubes linking back tothe DATE column in the fact table for each.
Now I just have to play with the formatting of the dates themselves.
Thanks Andrew,
