integrating otfer data into Data mart

hi all -

i have a fact table built form our time entry system (and it records our employee's time-sheet info such as hours etc.). we use a different software for payroll and i am trying to figure out how to integrate the two systems. the data from payroll is bi-weekly and the one from the time entry system is at daily level. there are two dates coming from the 2 systems - work date from time entry and check date from payroll.

how should i integrate these two data sources? any advice would be highly appreciated...

Hi,

Your DW consists of conformed dimensions and facts. You load the Dims from your source systems and if you have the same type of entity in more than one source (e.g. Employee) then you need to decide whether both systems contain a record for the same Employee and, if they do, whether there are different attributes in each system that you want to bring into your Employee Dim.
If you have different employees in the two systems then you would load them both into the Employee Dim
If you have the same employee in the two systems then you need to define which attributes you want to bring into the Employee Dim, which source is the master for each attribute (if necessary) and then define your insert/update strategy for the Dim.

Regarding the facts, you need to define what they are and then how to populate them.

From your post it sounds like you are just trying to re-model your source systems as a star schema; this is the wrong approach IMO. You need to define your start schema based on your reporting requirements and then work out how to populate it from your source systems.

Hope this helps?

Regards,

nick_white wrote:Hi,

... You need to define your start schema based on your reporting requirements and then work out how to populate it from your source systems.

Hope this helps?

Regards,

That's not a best practice either, although it is a common approach that can be successful. The problem with this approach is that the reporting may not require the data to be at the lowest grain. If you don't recognize this fact via data profiling or other data analysis methods, you'll end up with a design that won't deliver the full potential of the data.

nick_white wrote:Hi,

Regarding the facts, you need to define what they are and then how to populate them.

From your post it sounds like you are just trying to re-model your source systems as a star schema; this is the wrong approach IMO. You need to define your start schema based on your reporting requirements and then work out how to populate it from your source systems.

thank you, can you please give me more details on what you mean here? for example, if you were in my shoes, how would you define the facts and populate them?
Also, any pointers etc on the second item would be very helpful. thanks for your help.

Hi,
if I was in your shoes I would read "The Data Warehouse Lifecycle Toolkit" which explains the processes you should be following in order to build a successful reporting system.
The design of your dimensions and facts falls out of your Bus Matrix, which is developed from the business requirements, which you gather by interviewing your users - who were identified as part of the scoping exercise that fed into the business case that your business used to decide to implement a data warehouse etc. etc

Regards,
Nick