Line items with list dimension - Importing data and design of model

Hi,

I am modeling our warehouse operations and it has a bunch of line items and "categories" as follows:

			Jan-19	Jan-19	Jan-19	Feb-19	Feb-19	Feb-19	Mar-19	Mar-19	Mar-19
	I/O ?	D/I?	Volume	Headcount	Throughput	Volume	Headcount	Throughput	Volume	Headcount	Throughput
Operation1	Inbound	Direct	1000	3	65.8	1000	3	65.8	1000	3	65.8
Op2	Outbound	Indirect	2000	5	72.1	2000	5	72.1	2000	5	72.1
Op3	Inbound	Indirect	3000	5	80	3000	5	80	3000	5	80
Op4	Inbound	Indirect	300	1	80	300	1	80	300	1	80

The line items I have are Operation1, Op2, and so on.

For each line item, they are either a function that is "Inbound"/"Outbound" and belong to "Direct Labor" or "Indirect Labor". These categories go into tracking KPIs eventually.

Each line item also has associated measures like Volume, # of headcount on that operation, throughput and so on.

I have modeled the above as follows:

Module with line items as above.

List dimension that has measures like: Volume, headcount, etc; and "Applies To" all line items

Any ideas on how to model the "categories" and also, how to import the data into the module (I ignored categories for now, and am stuck on how to import it as the columns are three in number for each Date/Time dimension as seen in the data table I have posted)

Please guide on how to model this, and how to import.

Accepted answers

rob_marshall

@visivasa ,

Actually, I would not do ALL of what I said earlier with the concatenation of the codes of I/O and D/O. Can you just have a list of Operations with the metadata about them (I/O, D/O) in a module and then in another module (dimensionalized by Time and Operation), you have the three line items I had above.

thanks,

Rob

All comments

rob_marshall

@visivasa ,

A couple of questions...Is Operation essentially your transaction ID where you could have 1,000's of them? Also, can Operation 1 only be Inbound and direct or can there be another Operation 1 with the values of Inbound and Inbound? If the answer is yes to the last part, create a list having a code of the code of Operation 1 and concatenate that with the I/O and D/I codes. So, the code could look like this:

Codes for IO and DI:

Inbound = 100

Direct = 200

Outbound= 300

Indirect = 400

***Make sure you have a delimiter to separate the different pieces of information in your transactional list ***

Transactional data would look like this:

Oper1_100_200

Op2_300_400

Op3_100_400

Op4_100_400

Create a module based off the above list (without Time) with two line items (I/O and D/I). Since you already know what the values are based off the code, you can formulaically figure out the value using Finditem().

For the actual transactional amounts, create a module based on the list above that you just created with 3 line items: Volume, Headcount, Throughput which is dimensionalized by Time.

Load the data in.

Hope this helps,

Rob

visivasa

@rob_marshall

Thanks for answering my questions on this forum, again!

To answer your question: No, the no. of operations are not too large -- around 20 of them and I do not expect them to change a lot over time. These are not transactions but describe a process -- hypotehtically, say it refers to some operation on the assembly line. For example: Operation1 could be fitting the wheels in a car, Operation 2 could be fitting the body and so on.

Every operation is either part of an "Inbound" or an "Outbound" (cannot be both) process. Every operation is also belonging to "Direct Labor" or "Indirect Labor" and cannot be both.

So the I and D codes are unique to each operation. You cannot have duplicate Operation with different codes.

Under such circumstances, do you still recommend going with your solution?

Note that I have modeled various "Operations" as line items as they will all roll up to various categories in other modules and ultimately into "Direct Inbound Labor Cost" or "Indirect Inbound Labor Cost" or "Direct Outbound Labor" and so on.

If you still recommend the same solution, can you throw more light on how you construct "Operation_100_200" ?? Do you create a list with Codes for IO and DI and then create line items which concatenate these codes? How do you do that?

rob_marshall

@visivasa ,

thanks,

Rob

visivasa

Your second post/response makes sense to me. I am going to try implement that and see if it works for my purpose and update/accept this solution.

Thanks again!

visivasa

@rob_marshall

As I was developing the model, I realized that the formula for different list items (like Operation1, Op2 etc) for line item "Volume" is different. So is the case for "Headcount" and "TPH".

If I have line items as Volume, Headcount, and TPH, and have to write formula where we have a highly nested IF THEN ELSE where the formula changes for each and every list item, it might be inefficient.

The solution I am adopting, hence, is :

Create 3 separate modules: Volume, TPH, Headcount.
Create line items "Ops1, Ops2 Ops 3 etc" for each and every module. This way I can calculate the formulas individually.

I believe this is the best way to achieve it, and will be efficient in terms of formulas too. If you disagree, let me know.

For now, I consider this issue resolved.

rob_marshall

@visivasa ,

Correct, simply is always better than complex and by making the formulas easier to read as well as maintain, you are helping the system. So yes, break them out.

Rob

Quick Links

Can you help?

Unanswered questions

Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385
PlanIQ Error Messages and Warnings
Users may encounter error and warning messages in PlanIQ. The table below includes descriptions of the messages and details what should be done in order to address them, where applicable. Message types: * Errors will fail the relevant PlanIQ step. Users will have to follow the instructions in order to avoid the errors. *…