7 Replies Latest reply on Oct 4, 2012 7:17 AM by f amores - oracle

    Workaround for HFM Drill-Through when using custom dimensions (>4) in FDM

    f amores - oracle
      Hi all,

      we have the following scenario:

      - Target Custom 3 is defined as a combination of Target Product and Target Cost Center
      - Target Product needs to be obtained by mapping field "PRODUCT" in the source file
      - Target Cost Center needs to be obtained by mapping field "CC" in the source file

      So,
      One approach would be to enable Custom5 and Custom 6 dimensions in FDM, and configure sequence so these two dims are mapped before custom 3 which has a conditional map to get
      values from target c4 and c5 (using varvalues...)
      The issue here is that drill-through does not work as the sql query for the intersection is looking for UD5x and UD6X as NULL, N, or [NONE], and these two columns have a value as they were mapped.

      Trying to solve this issue, we configured C5 and C6 as lookup dimensions so we could create the mapping tables for them although in order to import data from source file fields we used attr2 and attr3. Besides UD5 and UD6, as expected, were not displayed in the Import and Validate/Export grids
      Then the idea was to update the work table with attr2->ud5 and attr3->ud6 in a event script (importaction or befprocmap..) so ud5 and ud6 are mapped.
      The problem is that we noticed that ud5 and ud6 are not updated from attributes. It seems that as they are configured as lookup dims, values may be mapped but not copied from work table to tdataseg.

      If this would work, then after custom3 is mapped we could set ud5x and ud6x to [none] in order the drill-through query to work.

      Having all in custom 3 dim and use mappings on combinations is not desired by the customer as they want to keep product and cc mappings separately.

      I was thinking to use a dummy location for product and cost center mappings and then emulate mappings using SQL but would like to avoid this.

      Any other suggestion?

      Thanks!
        • 1. Re: Workaround for HFM Drill-Through when using custom dimensions (>4) in FDM
          SH_INT
          How many products and and cost centres source codes do you have. Is it too prohibitive to stop you generating a C3 map based on these codes. Do you have any spare dimensions in your customs i.e. which doesn't require any specific mapping?
          • 2. Re: Workaround for HFM Drill-Through when using custom dimensions (>4) in FDM
            user10757003
            What is the criteria for deciding if the Product or CC code needs to be used as the source code, and is that criteria in the source file (e.g. Account Code). If it is, then you could use an import script (or scripts) to test the criteria and set the source value accordingly.
            • 3. Re: Workaround for HFM Drill-Through when using custom dimensions (>4) in FDM
              f amores - oracle
              Hi
              currently there are:

              - 9 products
              - Aprox 200 CC in the source
              - Aprox 60 CC in the target HFM app (there will be N to 1 mapping rules)

              Regarding mappings in C3, that would be the last option for the customer as financial users want to maintain mappings for Cost Centers and for Product independently and not for the combinations.

              Currently Custom 4 will be always [None]

              Thanks
              • 4. Re: Workaround for HFM Drill-Through when using custom dimensions (>4) in FDM
                f amores - oracle
                The criteria for mapping C3 is:

                - If Plan = 6 Then Target C3 = SUBACCOUNT
                - If Plan <> 6 AND Account IN range [70000,70999] Then Target C3 = Target CC
                - If Plan <> 6 AND Account NOT IN range [70000,70999] Then Target C3 = Target CC_Target Product

                Fields in the file
                - PLAN
                - ACCOUNT
                - SUBACCOUNT
                - Source CC
                - Source Product

                So current solution actually is to put that logic in the import format so:
                - If Plan = 6 Then Source C3 = SUBACCOUNT
                - If Plan <> 6 AND Account IN range [70000,70999] Then Source C3 = "RULE2"
                - If Plan <> 6 AND Account NOT IN range [70000,70999] Then Source C3 = "RULE3"

                In the mapping of C3, we managed a simple script mapping checking the source C3
                If source c3 = RULE 2 -> Target C3 = Target CC
                If source C3 = RULE 3 -> Target C3 = Target CC_Target Product
                Else Target C3 = source C3 (which is SUBACCOUNT)

                C5 (Cost Center) and C6 (Product) are mapped before C3.

                In order to make Drill-Through working, we save UD5X and UD6X in ATTR2, ATTR3 so the user can see it, and we set then UD5X and UD6X to [None] so the Drill_Through SQL Query returns the values from FDM. If after loading the user goes to validate step, he will see [None] in C5 and C6 but actually this does not really matter as C3 has already been mapped and is the value to be loaded. If the want to review target product or target cc then they drill down to attribute information.

                regards
                • 5. Re: Workaround for HFM Drill-Through when using custom dimensions (>4) in FDM
                  SH_INT
                  You could manage this with some complex logic accounts. Your CC and Product mappings would be done in the criteria for the C3 dimension (Pass CC source) and C4 dimension(Product Source). You would use the Grouping fuctionality of the complex logic to define the target names to be returned as the source values for the logic accounts for these dimensions. Then in your mappings C3 would be a conditional map based on the logic account source values for these two dimensions and your C4 map would default to None. Obviously this would not work if you needed to map your C4 dmension using based on values from the source but with your curret setup you can use it to process Product.
                  1 person found this helpful
                  • 6. Re: Workaround for HFM Drill-Through when using custom dimensions (>4) in FDM
                    user10757003
                    So current solution actually is to put that logic in the import format so:
                    - If Plan = 6 Then Source C3 = SUBACCOUNT
                    - If Plan 6 AND Account IN range [70000,70999] Then Source C3 = "RULE2"
                    - If Plan 6 AND Account NOT IN range [70000,70999] Then Source C3 = "RULE3"

                    why can't you just map the source values for sub-account, CC and Product as per your rules above in the import script against the C3 member and map the targets via the normal C3 mapping? You are already doing the test in the import script and the appropriate source values can easily be accessed from the strRecord?.
                    • 7. Re: Workaround for HFM Drill-Through when using custom dimensions (>4) in FDM
                      f amores - oracle
                      Thanks both for the suggestion.

                      We cannot map in the standard way mappings in C3 as the target C3 may be a combination of target Product and target CC.

                      The test in the import script is just to easily map Custom 3.

                      The fact is that they want to create mapping tables for Products and Cost Centers so I agree using logic accounts may be the solution although I'm not 100 sure how Custom 4 is going to behave in the nearly future.

                      They are OK with having c5 and c6 enabled in FDM although drill-through is a req.

                      By the way, regarding lookup dimensions. Should I see them in the mapping tables or it might be a bug??? we are in 11.1.2.1

                      Thanks!