integrating otfer data into Data mart

View previous topic View next topic Go down

integrating otfer data into Data mart

Post  SnowShine429 on Sun Oct 12, 2014 8:50 pm

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


Last edited by SnowShine429 on Fri Nov 07, 2014 10:29 pm; edited 1 time in total

SnowShine429

Posts : 36
Join date : 2013-02-16

View user profile

Back to top Go down

Re: integrating otfer data into Data mart

Post  nick_white on Mon Oct 13, 2014 3:12 am

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

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: integrating otfer data into Data mart

Post  BoxesAndLines on Mon Oct 13, 2014 3:28 pm

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

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: integrating otfer data into Data mart

Post  SnowShine429 on Tue Oct 14, 2014 3:13 am

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.

SnowShine429

Posts : 36
Join date : 2013-02-16

View user profile

Back to top Go down

Re: integrating otfer data into Data mart

Post  nick_white on Tue Oct 14, 2014 6:17 am

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

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: integrating otfer data into Data mart

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