Modeling with M:N and avoiding duplicates

Question

Modeling with M:N and avoiding duplicates

ttdeveloper

SSCrazy

Points: 2370
More actions
March 24, 2015 at 1:02 pm

#305966

Good Day guys.
I have attached a screenshot of my current dilemma. Essentially, I have two sets of tables.
One with an org structure and the other with Sales by accounts.
I am trying to resolve this M:N situation that I have and generate a way for queries to be run with dups.
Using the attached, we can see that multiple associates manage the same account. If I wanted to pull up their sales, its pretty straight foward by just joining to the account table and looking for the individual sales.
The problem I have is that when I go to rollup the sales values to Assistant Manager and above level, the sales values are duplicated and in my example i run the risk of reporting $600 in sales for account 123 and $1200 for account 456 when reporting at AM level.
I know i need to stick some kind of table in between to resolve the M:N - ie new table say [Org structure sales] - with three columns
org_str - PK
Sales fk
org fk
however this scenario still produces dups for me.
Any help is appreciated.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

RonKyle SSC-Dedicated Points: 31537 More actions · Answer 1

A column to identify the portion of the overall value would seem to work. Not enough information presented to be sure that's the ideal solution.

ttdeveloper SSCrazy Points: 2370 More actions · Answer 2

A column to identify the portion of the overall value would seem to work. Not enough information presented to be sure that's the ideal solution.

Thanks for getting back. Not sure I understand what you mean by this statement.

What other information do you need to help you understand my situation? Sorry if I wasnt clear.

I need to resolve the M:N relationship between

Account MANAGED BY multiple associates

and

Associates MANAGING multiple accounts.

My current setup wont allow me to report correct sales for AM - C,

Manager - B and Director A.

RonKyle SSC-Dedicated Points: 31537 More actions · Answer 3

Add a column to the child table with the proportion broken. One line would have 0.6 and another 0.4 for example. The total is multiplied by these amounts. As long as each separate account totals 1.0, there should be no problem. Let me know if that works.

TomThomson SSC Guru Points: 104773 More actions · Answer 4

A solution, perhaps not the best one, is to use a query like

WITH DistinctOrg as (

select distinct ID, Director, Manager, AssistantManager, Region, Account from OrgStructure)

select D.AssistanManager, SUM(S.Value) as TotalValue

from DistinctOrg D inner join SalesByValue S on D.Account = S.Account and D.Region = S.Region ;

Rolling up to a higher level -

for Manager: replace all occurrences of DistrictManager in the query by Manager (and, if you want to be tidy, make sure Manager occurs only once in any select list by eliminating one occurrence when Manager is duplicated)

for Director: starting with the Manager version, replace all occurrences of Manager by Director (and clear up repeats of Director in the select list).

Of course if the version for manager can simply be the version for assistant manager if each manager controls only one assistant manager, and similarly the version for director isn't needed if each director controls only one manager, but it would be foolish to assume that you can get away without the additional versions - organisations change from time to time.

Or you can create the three auxiliary tables as ordinary tables instead of generating them on demand using CTEs if the performance difference outweighs the table space difference.

Tom

ttdeveloper SSCrazy Points: 2370 More actions · Answer 5

Hey Ron

While this works as a solution for a one off run. This is going to be difficult if not impossible to implement in a model because it means everytime we add a new associate, this "additional column" will need to be manually updated across all affected accounts and org levels.

TomThomson SSC Guru Points: 104773 More actions · Answer 6

RonKyle (3/24/2015)
Add a column to the child table with the proportion broken. One line would have 0.6 and another 0.4 for example. The total is multiplied by these amounts. As long as each separate account totals 1.0, there should be no problem. Let me know if that works.

It might be better to hold the count of associates for an account and assistant manager pair, and divide, since the count can be held exactly as an integer (maybe a tinyint) rather than as an approximation potentially occupying a little bit more store. And if managers can have multiple assistants and directors can have multiple managers a divisor would have to be held for each of those levels too.

Tom

ttdeveloper SSCrazy Points: 2370 More actions · Answer 7

Hey Tom

Thanks for getting here. The Cte definitely works for one off queries. This model I am looking to fix however is for a DataWarehouse and I need some sort of a more permanent solution.

The DW feeds an SSAS cube and there are requirements to allow drilling at any given time from Director to Rollup so essentially i need all values calculated and preloaded.

From a modeling standpoint, my ideal situation (which I am currently trying to map out now) is to resolve the M:N relationship by 1,2,3 Normal forms and creating the model from there.

I posted this to see if anyone had any ideas specifially from a modeling standpoint.