Aggregating type 2 attributes for all facts

View previous topic View next topic Go down

Aggregating type 2 attributes for all facts

Post  cidr on Wed Oct 07, 2015 10:40 am

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.




cidr

Posts : 5
Join date : 2013-01-23

View user profile

Back to top Go down

Re: Aggregating type 2 attributes for all facts

Post  ngalemmo on Wed Oct 07, 2015 12:02 pm

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

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

View user profile http://aginity.com

Back to top Go down

Re: Aggregating type 2 attributes for all facts

Post  cidr on Thu Oct 08, 2015 4:40 am

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

View user profile

Back to top Go down

Re: Aggregating type 2 attributes for all facts

Post  Sponsored content


Sponsored content


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