KPI's Mapping from multiple lists

joshuastockwell
joshuastockwell
in Integrations and extensibility

HI, 

 

In my datahub I have transaction list that I deconstruct into financial elements, profit centre, cost centre, gl account, order number in a mapping table. 

joshuastockwell_1-1576597520249.png

 

I have to report KPI's that are built up from multiple GL accounts and multiple Internal Orders, and I am wondering what approach to take to create a secondary KPI mapping from the original transactions?

 

I have tried using a List Property module but it falls down where there is duplication in one of the lists being mapped for a KPI. In this example the same Internal Orders are used for both KPI's but the GL04 is different.

 

eg

KPIGL04IO
AAHEDCO13807001321
AAHEDCO13807001322
AAHEDCO13807001404
AAHEDCO13807001405
AAHEDCO13807000209
AAHEDCO13807001261
AAHEDCO13807001294
Ad HocsO15807001321
Ad HocsO15807001322
Ad HocsO15807001404
Ad HocsO15807001405
Ad HocsO15807000209
Ad HocsO15807001261
Ad HocsO15807001294

 

Another idea I had was to create a module dimensioned with all 3 lists, (KPI, GL04 and Order Number) with a Boolean line item to capture the valid combinations, but I couldn't work out how to map that into an output using my original transactions. 

 

Any suggestions would be greatly appreciated!

Tagged:

Answers

  • JaredDolich
    JaredDolich

    @joshuastockwell I think your idea at the end is really close to the right solution but instead of building one module with all three lists, use the transaction module to create a list of combinations. Then create a system module using that list and create the Booleans or transformations you're looking for. If you haven't already, you can also read up on the DISCO method, by @DavidSmith. It's one of the best modeling techniques Anaplan has to offer. 

     

    Let me know if that's close to what you were asking for. If so, I can use your data to create an example.

  • nathan_rudman
    nathan_rudman
    can you explain more your KPIs ? Is it just summing different transactions depending on their GL/other properties ?
  • usman.zia
    usman.zia

    Hi @joshuastockwell 

     

    You need to be very careful when combining so many dimensions to create a module but if the number of items are not expected to grow massively then this will be ok.

    As @nathan_rudman has requested it would be really helpful to know the purpose of the KPIs as there may be a more efficient solution. 

     

    Equally as @JaredDolich method of creating combinations and using a boolean to identify is also great and booleans are the most efficient calculations for performance!

     

    Thanks,

     

    Usman

  • joshuastockwell
    joshuastockwell

    Hi Jared, I think what you're saying makes sense. 

     

    in my transactions module I create a new column of concatenated GL and IO combinations (KPI Mapping column below). 

    joshuastockwell_0-1576656481516.png

    Then create a new list in the same format and map that to my KPI's

    2.png

     

  • nathan_rudman
    nathan_rudman

    Personally, I try to avoid to have to maintain list, I prefer the instantaneous and safety of formula.

     

    You could create a mapping module by dimensioned by GL/IO/CC/whatever with one line item formatted as KPI.

    Then you have one KPI line item in your transaction module and you bring the KPI like this: mapping module.KPI[LOOKUP:GL,LOOKUP:whatever]

    Then you can simply SUM in this line item:

    transaction.value[SUM:KPI]

  • joshuastockwell
    joshuastockwell
    Hi, thanks for your help so far.

    The purpose is an alternate P&L report for a subset (3 profit centres) and will go to our regulator. The KPI's will roll up to a summary lines of Direct Revenue and specific Cost lines, EBIT etc.


  • ChrisAHeathcote
    ChrisAHeathcote

    The use of a concatenated unique identifiers adheres to best practice when creating mapping modules. However, the use of nested dimensions does work and will allow the user to map successfully across multiple dimensions without having to create a series of data transformations.
    I have used this approach successfully in my own models. However, try not to use more than one line item in order to limit the number of cells in the module.

  • usman.zia
    usman.zia

    Hi @joshuastockwell 

     

    Assuming your 3 profit centres are in the original list module you will be able to create module to report KPIs just for these.

     

    It is possible to simplify what you would like to achieve by creating a subset of the list of the 3 profit centres.

    This will then allow you to create a module dimensioned by this subset where you can reference the original reporting module to create your alternate report KPIs. 

     

    I hope this helps,

     

    Thanks,

    Usman

     

     

     

Categories