Need help connecting text values between two modules

ahoshor

I have two modules with a common text formatted line item Employee ID. The modules are DATA03 and ADM07. In ADM07 there is a corresponding text line item called C4 Code. I want to bring the C4 Code value into DATA03 into the C4 Connect line item based on where the Employee ID line item matches between the two modules. What is the best way to accomplish this?

HimanshuRaj

Hi @ahoshor

You need to follow below steps for efficient calculation:

1. In DATA03 Module, create a new employee list formatted line item as here I am assuming, emp code is the code of the employee dimension you have used in ADM07 module
2. In C4 connect line item, use pull c4 code from ADM07 module and use lookup on that new line item created

Cheers!!

ahoshor

Hi @Himanshu1998

Thanks for answering so quickly! The Employee ID is actually just part of the code of the employee dimension I'm trying to connect to. The code is actually the C4 Code line item in ADM07. My data module is only pulling in the portion of the code that is after the "." which happens to be the employee ID. In ADM07, the C4 Provider line item is list formatted. I'm trying to connect DATA03 to it.

rob_marshall

@ahoshor

A couple of questions:

• why are you loading the data this way? I believe it would be better to have two modules, one for the transactional data (Sum % in your first picture) and another for the attributes.
  • 1st module - dimensionalized by Employee, Location, and Time…One line item, the Sum %). This is will have massive performance and size benefits because you don't actually have to build that transational list which has Time as part of the code/property in the list.
  • 2nd module - dimensionalized by Employee and Location which holds/stores the master/metadata
• Why are you loading text formatted data instead of list formatted? TEXT is horrendous from a performance and size perspective.

To answer your original question, Employee ID should be list formatted, not TEXT. By having it as list formatted, you can do lookups/sums/etc., with it being TEXT, you can't do much with it.

If you don't redesign DAT03 and keep it the way it is, you will need to change the Employee_ID in DAT03 to be list formatted (you can create a new line item using a finditem(Employee, Employee_ID) and using that within a lookup. The formula would be ADM07.'C4 Code'[lookup:Employee list member] where Empoyee list member is the line item containing the finditem.

Rob

ahoshor

@rob_marshall Thanks for the info! This will be a one-time data import into calc module that assigns a % allocation by Provider(employee) across locations. We're essentially trying to seed an allocation dashboard that will roll the values forward each month, and end users will review/update as needed. Unfortunately the seeding data doesn't come over with the full code of Provider dimension. The code is a cost center concatenated with the employee ID with a "." between them. So the Employee ID line item in DATA03 is only a portion of the code I need to connect to the list. Since it's a one-time load, I was trying to find a creative way to connect them formulaically with by creating the ADM07 module.

rob_marshall

@ahoshor

I get that, but please remember your default thought in loading data should be list formatted, not text. Again, Text is bad and causes performance to suffer while bloating the model.