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

Create fact table from a dimension (detect change)

2 posters

Go down

Create fact table from a dimension (detect change) Empty Create fact table from a dimension (detect change)

Post  AlexanderG Thu Aug 04, 2011 2:10 pm


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.



Posts : 1
Join date : 2011-08-04

Back to top Go down

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

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

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

Back to top Go down

Back to top

- Similar topics

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