same field on two different dimensions

[osmereqork]

Hi,
I've got two dimensions, Customer and Area. Both of these contain a Sales Rep field (this is the only common element between the two). The dimensions are linked to a Revenue Fact table and the rep is liable to change over time, but as we would want historic records to move with the rep change this is a type 1 field.

Am i ok to have the same rep information on both dimensions? that seems like a no-no to me. However i'm struggling to see how i can model this to have just one instance of the sale rep field, or even if i should?

Alternatively, I am thinking i should put the sales rep into its own dimension and link it as an outrigger/snowflake to each of the Cust and Area dims. Is this a valid reason to snowflake?

Thanks,

osme

[ngalemmo]

You have two different ways to attribute revenue to a sales rep.  Which is the correct one?  The customer rep or the area rep?

There can be a valid business case to have the rep in both places, so it is not necessarily wrong.  The question is: is there such a case, and what does each instance represent?

And the outrigger doesn't change anything. There is no benefit in doing so.

[osmereqork]

Hi,
Thanks for the reply.

Both are correct in this case.

A rep is assigned a number of demographic areas and a number of non-demographic customers. Therefore some revenue may be generated by a customer for rep 1, but an area controlled by rep 2. This is an entirely valid approach in our business.

I think in more generic terms you can replace the our rep with an employee dimension. A slightly different scenario is the challenge of having some cases where an employee will never change, but others where it will (and any history needs to move to the new employee) -
i.e
1.the salesman who generated the revenue is Mike Smith
2.the manager of the branch that generated the revenue is Dave Jones today, but Steve Evans tomorrow.

With (1) i can link DimEmp to FactRev, since that specific join is always valid. With (2) I can't do the same, since the joins for all the historic records themselves would have to change when Steve Evans takes over.
I can't see how (2) can be modelled as anything other than DimEmp as an outrigger joined to DimBranch which joins to FactRev. Is there an alternative?

In my salesrep scenario i have two employee fields, both of whom would need to be treated as per (2)

Thanks,
Os

[Jeff Smith]

I don't think it's the same field although it might the same dimension key.

I would put the Sales Person on the Fact and call the field something like Sales Person Employee Key.

I would put the Branch dimension on the fact table and put the Branch Manager on the Branch Dimension. Depending on the way the Branch Manager info is used, I would probably put the Branch Manager's Name on the Branch Dimension along with the Branch Manager Employee Key which would link to the Employee Dimension.

You could also put the Branch Manager at the time of the sale on the fact table, calling it Selling_Branch_Manager_Employee_Key or something to distinguish this Branch Manager from the branch manager on the Branch table. This would allow you to know the Sales Person, the Branch Manager at the time of the sale and the current branch manager.

Or, you could put both branch managers on the branch dimension (Branch Manager and Current Branch Manager), creating a new row every time the branch manager changes and updating the current branch manager field.

[ngalemmo]

Snowflaking off the area should be ok, presumably these are fairly small dimensions, so there is little performance impact.

Jeff's point of placing the customer sales rep as its own dimension off the fact is a good one. It eliminates a snowflake off what one would hope is your largest dimension.

[osmereqork]

thanks both, thats given me some things to think about

os

[Sponsored content]