Data Modeling problem

[Rayan12]

My problem is “Same data are showing multiple time in Cube Views” in Forecasting module. Reason is 'One Accounts belongs to multiple Sales man'.
Let me explain my business Process.
Business Process : We sale product to Store (we call as ‘Accounts’) across the country in 12 category.
Sales Hierarchy is , Accounts->Category-> Salesman. Most of the cases One Accounts Belongs to Multiple Sales man. Example:
AccountName - Category - SalesMan
--------------------- ---------------- --------------------
AB Store Cat1 ABC
AB Store Cat2 ABC
AB Store Cat3 ABC
AB Store Cat11 XYZ
AB Store Cat12 AAA
I have a cube with 7 dimension (Year, Quarter, Accounts, SalesMan, Category, Royalty,
Forecast Measures). My Accounts Dimension has 3 level +TotalAccounts->+Salesman->-AccountsName
Example :
Accounts Dimension Setup :-
-------------------------------
+ TotalAccounts
+ABC
-AB Store
+XYZ
-AB Store
+AAA
-AB Store
When ‘ABC’ login to contributor then all 'AB Store' data is display including (XYZ and AAA data). Is there any way to control this. My requirement is while ABC will connect then only his 'AB Store' will display.
Thanks.

[Martin Ryan]

Reason is 'One Accounts belongs to multiple Sales man'.

There are only two ways to solve this problem:
- Make each account only have one salesman
- If that is impossible, split it out into two dimensions

This is a generic multi dimensional problem. To think about it another way, you could have Products rolling up into Cities, e.g. "iPad" rolls up into "Washington", but then there's no where to put Las Vegas' iPad sales. That means you need to have two separate dimension. Product and City.

Generally it's a bad idea to have people in any dimension other than "Staff" because people always change.

You actually already more or less already have this setup. You have separate Accounts and SalesMan dimensions. So just take the Sales Man part out of the Accounts dimension. The sales men are not part of the account. The might manage that account, but that is not the same as being the account.

(A third alternative is having the sales man as an attribute of the account, but I think this is likely to have similar problems, because it still relies on having only one salesman per account.)

Martin

[Rayan12]

Thanks Martin. Due to business process
- Unique Accounts Code not possible.
- I am fully agree with you not to setup 'Salesman' in Accounts Dimension, Reason is
with out sales man it will be 10,000 Accounts list in Dimension. After breaking
by Salesman in Accounts Dimension , Now list is small.
- Your already mentioned it will not be possible to control by Attribute, because same accounts belongs to multiple sales man.

I have an idea, but i don't know it will work or not and don’t know how to do that, Reason is i am very new in TM1 , "only 2 months baby".

Proposals is : -
- Take out the Sales Man from Accounts Dimension
- Create 2nd mix Cubes with 'Sales Man' , Accounts and Category Dimension' ,Setup will be :---
Sales Man - Accounts - Category
( Cat1) - (Cat2)- (Cat3) (Cat12)
-------------- -------------- ------------ -------- ------- ------
ABC AB Store 1 1 1 0
AAA AB Store 0 0 0 1

-Create Rules in Main cube to control based on 2nd Mix Cube.

Martin, what do thing it will work or any other idea

[Martin Ryan]

I am fully agree with you not to setup 'Salesman' in Accounts Dimension, Reason is with out sales man it will be 10,000 Accounts list in Dimension. After breaking by Salesman in Accounts Dimension , Now list is small.

Won't it be 10,000 accounts with or without Salesmen? It's just that the salesmen allow you to create an intermediate consolidation, rather than rolling up the 10,000 accounts directly to "All Accounts"? I hope I'm understanding the problem correctly.

You could use a different level of intermediate consolidation, for example a letter of the alphabet, or the region if that will work (but it may not if accounts can be in multiple regions).

Or you could just have the 10,000 accounts rolling direcly into "All Accounts". I know it's ugly and really annoying if you accidentally leave the Properties Window open in subset explorer, then click on "All Accounts", because it takes a while to load all the properties. But that's a lot less ugly and annoying than having a cube that's wrong/difficult to use!

I think your proposed solution will work, but not sure what you mean by "Create 2nd mix Cubes". If you mean having two cubes then I think that's the wrong solution. You should only need one cube for this data, it just needs to be dimensioned correctly. But it sounds like Sales Man, Account and Category are the correct dimensions to use.