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

visivasa
visivasa
in Integrations and extensibility

Hi,

 

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

 

   Jan-19Jan-19Jan-19Feb-19Feb-19Feb-19Mar-19Mar-19Mar-19
 I/O ?D/I?VolumeHeadcountThroughputVolumeHeadcountThroughputVolumeHeadcountThroughput
Operation1InboundDirect1000365.81000365.81000365.8
Op2OutboundIndirect2000572.12000572.12000572.1
Op3InboundIndirect300058030005803000580
Op4InboundIndirect300180300180300180

 

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.

 

 

Tagged:

Best Answer

  • rob_marshall
    rob_marshall
    Answer ✓

    @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

Answers

  • rob_marshall
    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
    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?

     

     

     

  • visivasa
    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
    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
    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

Categories