Forum Stats

  • 3,826,865 Users
  • 2,260,714 Discussions
  • 7,897,108 Comments

Discussions

Categories

Using Period as Condition in Data Load Mapping

R.Brambilla
R.Brambilla Member Posts: 3
edited Sep 9, 2019 1:22PM in Financial Data Management

Hello! This is my first post here, this is one of the few questions I haven't been able to find an answer elsewhere, so I hope someone will be able to help me!

What I need to do sounds pretty straightforward: I need to set different target values on a Multi Dimension mapping based on the period I'm importing data from.

But we do have one requirement: no SQL scripts, since our client will be constantly updating it, so it must remain as user friendly as possible.

To make it clear, I've put it on a sheet:

     pastedImage_1.png

So, we are importing all the data from Oracle's Financials Cloud to PBCS, and there's this specific dimension that only exists in PBCS, so we need to create a mapping to it.

The whole point is, the target member within this dimension, depends solely on when the data is from. If we are importing data from 2018, the target member needs to be "Year2", however, if we're importing from 2019, the target member is now "Year1", as an example.

And this would be a mapping that would be updated every year, since in 2020, "Jan-20" would be "Year1", and "Dec-19", "Year2", and so on... Hence the "user friendly" requirement.

I'm afraid this is beyond Cloud Data Management's capabilities, but I wanted to hear some expert's opinions about this!

Thank you very much for your time!

Tagged:
R.Brambilla
· Share on TwitterShare on Facebook

Answers

  • user6692921
    user6692921 Member Posts: 179 Silver Badge
    edited Aug 31, 2019 7:36AM

    In on prem FDMEE you would add a custom dimension as a lookup and assign a field to it in the import format. Then populate the lookup dimension. This dimension will then appear in MULTIDIM maps. I think that will also work for Cloud DM. The only thing I have doubts about is whether you can populate the lookup dimension with the period info from OF - I don't know what control you have over the source adapter.

    R.Brambilla
    · Share on TwitterShare on Facebook
  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Sep 1, 2019 11:19AM

    Are you saying you have an additional dimension just to hold members Year1, Year2 as well as having a period and year dimension.

    If so this seems a little odd, anyway the problem is that even if the financials accounting period dimension is set as generic in Data Management and it is mapped in the import format it will not populate with the accounting period. This is is due the way the data is extracted, accounting period is a column header with the data values against it. So if you map accounting period it will just be populate with data values.

    One possible solution which will require a simple SQL mapping, I know you said no SQL mappings but this is a fixed SQL mapping and will not need maintaining, the accounting periods will still be able to be mapped to Year number by the user.

    You can create a lookup dimension, you should be able to use an existing dimension as well if it used a pass through.

    pastedImage_1.png

    This will used to hold the accounting period. I have but the sequence as 1 as I want it to be mapped before the Year Number dimension.

    If you look at the process logs you will see the accounting period is automatically populated in an attribute column. For me it is assigned to ATTR11.

    Now you can put a SQL mapping against the lookup dimension which will copy the accounting period from the attribute to the lookup dimension, it also assigns the accounting period to the year number dimension which in my case is UD1. I have added a substring so for "01-19" to "12-19" it will populate the year number dimension with "19". For "01-20" to "12-20" it will populate with "20" and so on.

    pastedImage_2.png

    Once in place this SQL mapping will not need to change so need for the user to worry.

    Now for the Year number dimension, explicit maps are added which the user can manage.

    pastedImage_5.png

    In the workbench, the accounting period is visible but not loaded to the target. The Year number dimension will be mapped to Year1, Year2 etc

    pastedImage_9.png

    Alternatively an option without the lookup dimension and SQL mapping would be with a multi dim map.

    pastedImage_0.png

    Though this method would require uploading a mapping file as the UI will not let you select an attribute column.

    Cheers

    John

    R.BrambillaR.Brambilla
    · Share on TwitterShare on Facebook
  • DhawaShah
    DhawaShah Member Posts: 10 Red Ribbon
    edited Sep 2, 2019 3:00PM

    Hi,

    There is one possibility where in we have data integration in PBCS, so there we can use the Substring function for the period field while importing the data so for example:

    its Jan-19 after substring function it would become 19 and then you can try using multidimension mapping as John suggested.

    Thanks,

    Dhawal

    R.Brambilla
    · Share on TwitterShare on Facebook
  • JohnGoodwin
    JohnGoodwin Member Posts: 30,471 Blue Diamond
    edited Sep 3, 2019 2:53AM

    The source is Oracle Financials though which source period field are you talking about.

    · Share on TwitterShare on Facebook
  • R.Brambilla
    R.Brambilla Member Posts: 3
    edited Sep 9, 2019 1:19PM

    Hello, John! Thank you so much, this guide will be extremely helpful! We are still discussing the best approach, and it looks like this will solve our problem, so I'm definitely going to try it. I'll let you know in the future if it works!

    Have a good one!

    Rodrigo.

    · Share on TwitterShare on Facebook
  • R.Brambilla
    R.Brambilla Member Posts: 3
    edited Sep 9, 2019 1:22PM

    And also, thank you all for your time and answers, they'll be really helpful!

    I'll update this thread in the future with what did and didn't work.

    Rodrigo.

    · Share on TwitterShare on Facebook