Create fact table from a dimension (detect change)

[AlexanderG]

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

[ngalemmo]

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.