Clients, Roles and Rates

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,

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,

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

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.

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.