Date Dimension refresh for all data marts nightly ??

View previous topic View next topic Go down

Date Dimension refresh for all data marts nightly ??

Post  Scoop on Thu Mar 26, 2009 12:38 pm

We are working at client site where we recommended using the BUS architecture and we are using PPS Monitoring for dashboards. Our client has been re-building each data mart nightly for now and they are re-building each data mart dimdate dimension with rows that are equal to the the nightly source system refresh process. Meaning; only placing date data inside each data mart dimdate equal to the dates for that pull of source data each night. So, if tonights pull of source data only equals dates (fulldate) "2008-12-26 00:00:00.000" to "2009-03-25 00:00:00.000" these are the rows they load into DimDate dimension only. This is occuring for each of the 4 data marts and the data is different for each data mart dimdate dimension since we are pulling data from lultiple source systems for each data mart. We recommend DimDate be part of the BUS conformity so, they can pull data from multiple data marts, etc.

We run into issues with PPS Dashboards filters for Fiscal Month and Fiscal Week because the data marts dimdate dimensions are re-built every night equal only to the dates from the source each night. So, the fiscal month and fiscal week filters list changes each night and these do not get refreshed with the new data each night we have to go re-do the dashboards every morning which does not seem right and we are looking into this and advice or help would be greatly appreciated on this if you have any.

Thoughts and advice?

We want the DimDate dimension to consist of data back when systems went live in production and currently house transactions from 1998 for some sources and 2003 for others. We would like the DimDate to consist of start fulldate back to 1998 and to 2020 for now and to build this dimdate once or as needed but use this dimdate for each data mart going forward.

Thoughts advice?

Thank you in advance.
Scoop

Scoop

Posts : 18
Join date : 2009-02-10

View user profile

Back to top Go down

Re: Date Dimension refresh for all data marts nightly ??

Post  BoxesAndLines on Thu Mar 26, 2009 1:33 pm

Build the date dimension once and forget about it. I've never heard of anyone rebuilding the date dimension on a nightly basis. It's a dimension, not a fact. It doesn't need to reflect the min and max of the data range in the fact table.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum