Aggregating type 2 attributes for all facts
2 posters
Page 1 of 1
Aggregating type 2 attributes for all facts
Hi and apologies in advance if a similar question has been asked; I have looked.
I sort of trying to get my head round the aggregation of Facts using an attribute that has recently had a type 2 change.
Fairly new to Data warehousing, so I'll use a simple example.
Let's say the DW has a customer table and customer - Joe Blog's moves and his Postcode changes from sw6 to sw7 A reporting user - using an excel pivot for instance - connects to the DW and wants to view all fact information of Joe Blog and adds the post code to the report, it will split the report into two aggregated lines; Facts belonging to the old Postcode -sw6 (or in another way, belonging to the old surrogate key) and Facts that belong to the new Postcode sw7.
Is there anything I should be doing within the DW so that Fact data for Joe Blog is aggregated to one line using sw6? or is this handled within BI reporting tools?
Any help is appreciated.
I sort of trying to get my head round the aggregation of Facts using an attribute that has recently had a type 2 change.
Fairly new to Data warehousing, so I'll use a simple example.
Let's say the DW has a customer table and customer - Joe Blog's moves and his Postcode changes from sw6 to sw7 A reporting user - using an excel pivot for instance - connects to the DW and wants to view all fact information of Joe Blog and adds the post code to the report, it will split the report into two aggregated lines; Facts belonging to the old Postcode -sw6 (or in another way, belonging to the old surrogate key) and Facts that belong to the new Postcode sw7.
Is there anything I should be doing within the DW so that Fact data for Joe Blog is aggregated to one line using sw6? or is this handled within BI reporting tools?
Any help is appreciated.
cidr- Posts : 5
Join date : 2013-01-23
Re: Aggregating type 2 attributes for all facts
This is handled in the BI layer. There are three possible reporting scenarios:
1. Joe at the time of the transaction. This is a direct join to the dimension using the type 2 key.
2. Joe as he is now. Add a self join to the dimension to locate the current version.
3. Joe at some moment in time. Add a self join to the dimension to locate a specific version of Joe.
Most BI tools have sufficient functionality to support all three. You can also create dimension views to do the same and keep things simple for users.
1. Joe at the time of the transaction. This is a direct join to the dimension using the type 2 key.
2. Joe as he is now. Add a self join to the dimension to locate the current version.
3. Joe at some moment in time. Add a self join to the dimension to locate a specific version of Joe.
Most BI tools have sufficient functionality to support all three. You can also create dimension views to do the same and keep things simple for users.
Re: Aggregating type 2 attributes for all facts
ngalemmo wrote:This is handled in the BI layer. There are three possible reporting scenarios:
1. Joe at the time of the transaction. This is a direct join to the dimension using the type 2 key.
2. Joe as he is now. Add a self join to the dimension to locate the current version.
3. Joe at some moment in time. Add a self join to the dimension to locate a specific version of Joe.
Most BI tools have sufficient functionality to support all three. You can also create dimension views to do the same and keep things simple for users.
This is what I suspected. Thanks kindly for your help.
cidr- Posts : 5
Join date : 2013-01-23
Similar topics
» Facts or Dimension Attributes?
» Handling late changes to Type 2 attributes
» Common attributes across multiple facts
» Tracking history of multiple SCD type 2 attributes
» type 1 dimension - new requirements for attributes that will be updated often
» Handling late changes to Type 2 attributes
» Common attributes across multiple facts
» Tracking history of multiple SCD type 2 attributes
» type 1 dimension - new requirements for attributes that will be updated often
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum