Create fact table from a dimension (detect change)

View previous topic View next topic Go down

Create fact table from a dimension (detect change)

Post  AlexanderG on Thu Aug 04, 2011 2:10 pm

Hi,

The business question is to see the mutation (new, updated) over some attributes in the product dimension. I designed a type 2 dimension and hand it over to our report specialist and told him that he only need to dates (today and yesterday) and pick the two records from the dimension table and compare the attribute changes. If the reports determine a change then show it on the report. However our report specialist told me that he expect one table (maybe a fact table) to see the old and new value in one record. I understand his concerns but Iím wondering how to build this without harming the current architecture too much.

I was thinking about an all key fact what refer to the time dimension and refers twice to the product dimension (old and new record) or make a fact table with the attributes old and new value per attribute that also populates. Both tables are only populated when a change have been detected. Can someone give me some point where I have to pay attention to design the new table.

Greets,
Alexander

AlexanderG

Posts : 1
Join date : 2011-08-04

View user profile

Back to top Go down

Re: Create fact table from a dimension (detect change)

Post  ngalemmo on Thu Aug 04, 2011 3:30 pm

Investigate LEAD and LAG functions available in most all SQL implementations today. It allows looking at the previous or next row based on a sort order without having to do a self-join. Or, create a view with a self-join. There is no need for another table.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

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