Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Date Dimension refresh for all data marts nightly ??

2 posters

Go down

Date Dimension refresh for all data marts nightly ?? Empty Date Dimension refresh for all data marts nightly ??

Post  Scoop 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

Back to top Go down

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

Post  BoxesAndLines 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.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Back to top

- Similar topics

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