Clients, Roles and Rates

View previous topic View next topic Go down

Clients, Roles and Rates

Post  MontyBI on Thu Mar 06, 2014 11:43 am

Hello everyone,

I'm fairly new to dimensional modelling in a DW scenario, and have found the Kimball Group site very helpful so far.

I have a particular dimensional model that I'm trying to implement with type 2 changing dimensions, but am unsure of the *best* (or most appropriate) way to achieve it.

My scenario involves Employees working on a daily granularity for Clients, with specific Rates for each Client/Role combination.

Each Client has a particular Rate for each Role.

At first I tried to model this with separate dimensional tables for Employees, Roles and Clients, with a ClientRate mapping table containing the following:

ClientId, RoleId, Rate

for example:

Client1, Developer, $1000
Client1, Project Manager, $1100
Client2, Developer, $800
Client2, Project Manager, $900

However, this seems like it would lead to lots of dimension update headaches if a Role or Client changes, all of the rates would need new entries between the updated dimensions. I'm not sure if I should even have mapping tables between dimensions or try to have a flat as possible star schema.

I'm struggling to implement a star schema for this scenario where I don't need to replicate values between dimensions.

Should I just go with an approach that stores the following in the fact table:

ClientId, EmployeeId, DateId, Rate (measure)

Where I don't try to model the relationship between clients, roles and rates in the DW?

Any experienced views on this sort of model would be much appreciated, my Kimball book is still in the mail.

Thank you,



MontyBI

Posts : 3
Join date : 2014-03-06

View user profile

Back to top Go down

Re: Clients, Roles and Rates

Post  nick_white on Thu Mar 06, 2014 12:10 pm

Hi - it may help if you consider whether you are trying to report on the amount charged or the rate. As the grain of your fact table is daily and your rate is daily they end up being the same value but conceptually they are different. For example, if your rate was $1000/day but your "charged amount" fact table grain was a working week (5 days) then the fact table measure value would be $5000, not $1000.
So if you want to report on amount charged then your rate table is just a reference table held in your staging area and used in calculations as part of your fact table load.
If you want to report on rates (how many employees charged out at $x/day, etc.) then your rate amounts would probably be attributes in a dimension (or possibly a degenerate dimension) but probably not a fact measure. You might have a reporting requirement where the rate was a measure (average rates charged?) but this is less likely as most forms of aggregation of rate values have no meaning (sum of rate amounts, etc.) - and if you can't aggregate them then they aren't measures.

Hope this helps

Regards,

nick_white

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

View user profile

Back to top Go down

Re: Clients, Roles and Rates

Post  MontyBI on Fri Mar 07, 2014 3:23 am

Thank you Nick,

I had started to design it with the client/rate mappings as a reference table, and your response does make it clearer in my head about just using the daily rate as a measure, and not a dimensional Rate.

Coming from a relational background I'm still fighting the urge to build a fully relational model, so I'm trying to stick to a star schema as much as possible in these early days.


Much appreciated

MontyBI

Posts : 3
Join date : 2014-03-06

View user profile

Back to top Go down

Re: Clients, Roles and Rates

Post  ngalemmo on Fri Mar 07, 2014 12:18 pm

The thing to always keep in mind is the purpose of the DW is to report what happened, not making something happen. There is little utility for a table that maps clients, roles, and rates as that information is already carried in budget and billing facts. There is no need to 'look up' a rate as that information should be coming in the planning and/or invoicing system data.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Clients, Roles and Rates

Post  MontyBI on Mon Mar 10, 2014 5:08 am

Thank you both,

There aren't actually systems modelling the relationships and data that I'm being asked to include in the prototype DW (just spreadsheets and documents), so I've been confusing DW design with designing the source systems for the data itself (clients, roles, work order and rates etc.).

I'll put the source data in a relational model elsewhere and just report the facts in the DW tables.


MontyBI

Posts : 3
Join date : 2014-03-06

View user profile

Back to top Go down

Re: Clients, Roles and Rates

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