Importing Manager & Employee and avoiding duplicates

[cardantim]

Hello,

I've been provided with a CSV file to upload into TM1. The file is a list of sales executives and sales managers. All ficticious. Each record (row) has a colomn with an ID, Full name, Email, Sales Manager and Sales Manager Email. Looking to upload with hierarchy where sales executives are consolidated under sales manager. Full name will be an alias. In TI I have Sales Manager as Consolidation but the name of the sales manager also exist as EmployName so I'll get a consolidated element Tom Jones with Joe and Judy underneath but also get an element of 1

EmployID, EmployName, Email, SalesManager, SalesManagerEmail
1, Tom Jones, TJones@test.com, , ,
2, Joe Blow, JBlow@test.com, Tom Jones, TJones@test.com
3, Judy Brown, JBrown@test.com, Tom Jones, TJones@test.com

1
Tom Jones
Joe Blow
Judy Brown

How do I setup TI to avoid this?

Thanks,
Tony

[Alan Kirk]

I've been provided with a CSV file to upload into TM1. The file is a list of sales executives and sales managers. All ficticious. Each record (row) has a colomn with an ID, Full name, Email, Sales Manager and Sales Manager Email. Looking to upload with hierarchy where sales executives are consolidated under sales manager. Full name will be an alias. In TI I have Sales Manager as Consolidation but the name of the sales manager also exist as EmployName so I'll get a consolidated element Tom Jones with Joe and Judy underneath but also get an element of 1

EmployID, EmployName, Email, SalesManager, SalesManagerEmail
1, Tom Jones, TJones@test.com, , ,
2, Joe Blow, JBlow@test.com, Tom Jones, TJones@test.com
3, Judy Brown, JBrown@test.com, Tom Jones, TJones@test.com

Code: Select all

1
Tom Jones
     Joe Blow
     Judy Brown 

How do I setup TI to avoid this?

We'd need to see the TI code to know why it's doing what it's doing, but there are a couple of pointers that I can offer.

The first is that you should DEFINITELY be using EmployID, NOT the EmployName, as the base element name for the sales executives. The EmployName should be an alias. If you want to know why, just wait until one of the female sales managers gets married and decides to take her husband's name. I've no idea why that tradition persists, but it does. It's bad enough having to recreate a TM1 user ID to accomodate the new name, but if you're using a person's name as the base level element name in a data cube you'll need to export the values from the old name element and re-import the values into the new one. Secondly, although unlikely, it is possible that you may some day have two people with the same name working for the organisation.

The next question is whether (say) Tom Jones would need to be stored as an N level element as well; that is, whether data will be stored against his name.

I'll assume for the moment that he doesn't. In that case you could conceivably use his name as the consolidation name (you can change those without losing any data), though I'm not sure that I would; I'd much rather use the employee ID (or better still a fabricated key; something like "SM - Emp ID") as the base name with the manager's name as an alias. Again it'll make name changes easier. Also using a fabricated key like that will make it easier to deal with the situation where a sales executive may later become a sales manager. (If the employee ID existed as an N level element name, you wouldn't be able to later re-use it as a consolidation name.)

Assuming that your hierarchy is flat (that is, each N level Sales Executive reports to a single manager, and you don't have anyone above the managers in the hierarchy) you could do two things (in the metadata tab of course):
(a) If the row is one where SalesManager @='' (implying that the row represents a sales manager, not a sales executive), you can either ItemSkip it, or use DimensionElementInsert to add the employee as a consolidation. I'd probably skip it.
(b) Else, add the sales manager as a consolidation element (if the consolidation already exists, TI will just ignore that instruction), add the sales executive as an N element, then use DimensionElementComponentAdd to add the sales executive to the sales manager's consolidation.

I'd guess that this won't be a one-off load so the other thing that you have to make provision for is the situation where an employee moves between sales managers; you may want to use ElPar to check whether the employee is already a member of a consolidation (other than the one that you're adding them to) and if they are, use DimensionElementComponentDelete to remove them from that consolidation first.

[cardantim]

Main elemnt is Employee ID. Employee Name is an alias. Once I went through and addressed the duplicated names my issue was resolved. Thanks!