Forum Stats

  • 3,874,089 Users
  • 2,266,676 Discussions
  • 7,911,721 Comments

Discussions

Account(1 to many mapping) in PBCS Data Maps

User_VP89I
User_VP89I Member Posts: 183 Blue Ribbon
edited Feb 8, 2018 7:08AM in Planning and Budgeting

Hi Gurus,

I have "AssetClass" dimension and an account member "Depreciation" in my Capex cube.

Now, based on "AssetClass" members I need to push data to different GL codes in my financials cube.Somethinng like below : (AssetClass does not exists in Financials cube).

pastedImage_5.png

Do I need to create different data maps for each of the GLCode or we can do it in one Data Map.

If anyone could help me understand with an example screenshot it would be of great help

Thank you,

~RN

Tagged:
Robert Angel

Best Answer

  • Sidharda Chava
    Sidharda Chava Member Posts: 124 Blue Ribbon
    edited Jan 18, 2018 12:11AM Answer ✓

    Datamaps cannot do Cross dimension mappings.

    For this you can use smartlist to dimension mapping where in you can have one more smartlist which says to which account each asst class depreciation data should be going to.

    Then through data maps you can push data to target cube. Data maps allows smartlist to dimension mappings

    User_VP89I

Answers

  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Jan 17, 2018 7:29AM

    I don't believe data maps can handle any kind of complex mapping, you can look at the possibilities of a business rule with XWRITE/XREF depending on the cube type or using Data Management.

    Cheers

    John

    User_VP89I
  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Jan 17, 2018 7:41AM

    Other alternatives are export the data and manipulate using middleware.

    Or copy the data into another (non-user) version and run transformational business rules on it to allow single dimension mapping.

    User_VP89I
  • Matthias Heilos
    Matthias Heilos Member Posts: 94
    edited Jan 17, 2018 8:09PM

    RN,

    If I understand correctly what you are trying to do, I think you can accomplish this with 2 steps:

    1. In your Import Format, map two dimensions from your PBCS application to one Fusion application. Sounds like you should have Asset Class and Account in the Source drop-down and GL Code as a Target dimension (to get a second row to map to this target dimension, you need to click on Add >Dimension Row)

    2. You should see <AssetClass>-<Account> in your source column for GL Code (or whatever the name of the dimension is). Define your mappings for this dimension exactly like you have it in the screenshot and that should be it.

    Hope this helps,

    Matthias

    User_VP89I
  • Sidharda Chava
    Sidharda Chava Member Posts: 124 Blue Ribbon
    edited Jan 18, 2018 12:11AM Answer ✓

    Datamaps cannot do Cross dimension mappings.

    For this you can use smartlist to dimension mapping where in you can have one more smartlist which says to which account each asst class depreciation data should be going to.

    Then through data maps you can push data to target cube. Data maps allows smartlist to dimension mappings

    User_VP89I
  • Sidharda Chava
    Sidharda Chava Member Posts: 124 Blue Ribbon
    edited Jan 18, 2018 12:12AM

    Similar thing is done in EPBCS for Capex and Financials integration

    User_VP89I
  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Jan 18, 2018 3:29AM

    Hi Chava,

    I am not fully following what you describe, as I too would find this useful, and others I suspect, can you break it down for me into a brief worked example of the key points?

    User_VP89I
  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Jan 18, 2018 3:44AM

    Go to search engine, type "PBCS smart list to dimension mapping" , there are quite a few different blogs looking at it.

    Robert AngelUser_VP89I
  • Robert Angel
    Robert Angel Member Posts: 4,535 Bronze Crown
    edited Jan 18, 2018 3:49AM

    Thanks - appreciated, one for out of hours - blogs are blocked here - bane of my life local internet policies!

    User_VP89I
  • Kyle Goodfriend
    Kyle Goodfriend Member Posts: 185
    edited Jan 20, 2018 1:45PM

    This is possible with Groovy calculations.  If you have ePBCS or PBCS+1, you have Groovy calculations. 

    This is a collection created and stored in a calc.  This can be dynamically created based on a mapping from a form, but this is a simple explaination.

    def acctMap = ['Regular_Cases':'Regular_Cases',

                   'Net_Sales':'42001',

                   'Cost_of_Sales_without_Samples':'50001',

                   'Gallonage_Tax':'50015',

                   'Depletion_Allowance_Manual_Chargeback':'56010',

                   'Gain_Loss_Inv_Reval':'50010',

                   'Supplier_Commitments':'56055',

                   'Supplier_Spend_Non_Committed':'56300',

                   'Samples':'56092',

                   'GP_NDF':'56230',

                   'GP_BDF':'56200',

                   'GP_Contract_Amortization':'56205',

                   'Sample_Adjustment':'56090'

                    ]

    then, you create a source and target grid

    Cube lookupCube = operation.application.getCube("rGP")

    DataGridDefinitionBuilder builder = lookupCube.dataGridDefinitionBuilder()

    builder.addPov(['Years', 'Scenario', 'Currency', 'Version', 'Channel','Material_Group','Source','Vendor','View'], [['&v_PlanYear'], ['OEP_Plan'], ['Local'], ['OEP_Working'], ['Tot_Channel'],['Total_Material_Group'],['Tot_Source'],['Tot_Vendor'],['MTD']])

    builder.addColumn(['Period'], [ ['ILvl0Descendants("YearTotal")'] ])

    for ( e in acctMap ) {

        builder.addRow(['Account','Company'], [ [e.key],['ILvl0Descendants("Company")'] ])

    }

    builder.setSuppressMissingBlocks(true)

    DataGridDefinition gridDefinition = builder.build()

    // Load the data grid from the lookup cube

    DataGrid dataGrid = lookupCube.loadGrid(gridDefinition, false) 

    def povmbrs = dataGrid.pov

    println "POV:"

    println povmbrs*.essbaseMbrName

    println ""

    def rowmbrs = dataGrid.rows

    println"ROWS HEADER:"

    println rowmbrs.headers*.essbaseMbrName

    println ""

    def colmbrs = dataGrid.columns

    println"COLUMNS:"

    println colmbrs[0]*.essbaseMbrName

    println ""

    def tmpColMbrs = []

    //colmbrs[0].each{println it.essbaseMbrName}

    println ""

    //Target Grid Setup

    Cube finCube = operation.application.getCube("Fin")

    DataGridBuilder finGrid = finCube.dataGridBuilder("MM/DD/YYYY")

    finGrid.addPov('&v_PlanYear','OEP_Plan','Local','OEP_Working','No_Center','WRK_Model')

    def colnames = colmbrs[0]*.essbaseMbrName

    String scolmbrs = "'" + colnames.join("', '") + "'"

    finGrid.addColumn(colmbrs[0]*.essbaseMbrName as String[])

    dataGrid.dataCellIterator('Jan').each{ it ->

      def sAcct = "${acctMap.get(it.getMemberName('Account'))}"

      def sValues = []

      List addcells = new ArrayList()

      colmbrs[0].each{cName ->

        sValues.add(it.crossDimCell(cName.essbaseMbrName).data)

        addcells << it.crossDimCell(cName.essbaseMbrName).data

        }

      finGrid.addRow([acctMap.get(it.getMemberName('Account')),it.getMemberName('Company')],addcells)

    //  println  "${acctMap.get(it.getMemberName('Account'))}" + "," + sValues.join(",")

    }

    DataGridBuilder.Status status = new DataGridBuilder.Status()

    DataGrid grid = finGrid.build(status)

    println("Total number of cells accepted: $status.numAcceptedCells")

    println("Total number of cells rejected: $status.numRejectedCells")

    println("First 100 rejected cells: $status.cellsRejected")

    finCube.saveGrid(grid) 

    Hope this helps.

    User_VP89I
  • User_VP89I
    User_VP89I Member Posts: 183 Blue Ribbon
    edited Feb 8, 2018 7:08AM

    Thank you very much, I will try this

This discussion has been closed.