Forum Discussion

DKSutherland's avatar
DKSutherland
New Contributor II
3 years ago

Invalid Intersections When Using an Alternate UD1 Structure

Hi all, We have a question about invalid intersections in reports and whether it is just a presentation issue or if we need to be worried about calculations or transformation rules as well.

We have Cost Centers in our UD1 dimension and we have some Accounts that require specific UD1s. For instance, Account 62000 Gross Sales has a constraint of UD1 65000_F Sales. The event handler ensures this combination is maintained when loading data using transformation rules.

For reporting purposes, we have now created an alternate UD1 structure that does not include all UD1s for some very specific reasons. Now, when running reports such as the P&L, the row for Gross Sales shows pink when the alternate UD1 structure is used because that alternate structure does not include UD1 65000_F. The displayed data is still the correct Account 62000 and UD1 65000_F combination, but the cells are pink since that UD1 of 65000_F isn't in the alternate UD1 structure.

This alternate UD1 structure is only used for reporting. It is not used for data input.

So, our questions are:

1. Is there an easy way to adjust the reports that use the alternate UD1 structure so the cells aren't pink?

2. Besides the presentation issue of the pink cells, will Formulas and Rules still calculate correctly using the correct Account and UD1 combination? Or, do we need to be concerned about Formulas and Rules not calculating correctly?

Thank you very much for any assistance or suggestions.

Cube View
  • JackLacava's avatar
    JackLacava
    3 years ago

    Ah, so the key is just the conditional rules making it inaccessible. That shouldn't be a problem.

    If you don't like the pink background, you can use formatting on the column. Check the section "Cube View Formatting" in the Design and Reference Guide.

  • JackLacava's avatar
    JackLacava
    Honored Contributor
    3 years ago

    Here's a few questions to try and understand your problem a bit more...

    1. A few screenshots showing how the structures look, how CubeViews are defined, and what the result is, might help visualize the problem.
    2. when you say "we have now created an alternate UD1 structure", what do you mean? Did you add a new branch to the existing UD1 dimension? Did you create a different dimension and assigned it to a different scenario type/cube?
    3. if you're in doubt about which intersections are actually stored (i.e. where calculations happen), you can use the Data Records tool in System -> Tools -> Database, or in rules you can peek into a databuffer object (use the "Log Data Buffer" snippet)
    • DKSutherland's avatar
      DKSutherland
      New Contributor II
      3 years ago

      Thank you very much for the reply. Very sorry for my late response. I was away on vacation.


      I have attached two images below.

       

      The first image shows our primary UD1 structure and our alternate regional UD1 structure. We use the UD1 structure for departmental and cost center reporting. The intent of the alternate regional UD1 structure is to remove global corporate controlled expenses from the regional unit financials. We kept the numbers the same, but just added _Reg as a suffix to the original UD1s.

       

      As an example, expenses related to global IT departmental costs are not included, so that UD1 is removed from the regional UD1 structure. So, the regional UD1 structure is identical to the primary UD1 structure with the exception of removing specific UD1s of that type.

       

      The second image shows a Quick View of our P&L accounts in the rows and our primary UD1 and alternate UD1 in the columns. Here you can see that some items under the alternate regional UD1 structure are pink for invalid. The reason for this is that we have conditional rules that require certain accounts to have certain UD1s.

       

      For instance, Account 62000 Gross Sales has a constraint of 65000_F Sales. However, in the alternate regional structure the UD1s have _Reg as a suffix, so the account is shown as invalid because in the alternate structure account 62000 doesn’t have that UD1.

       

      Since the alternate UD1 structure is only used for reporting, we don’t think it should cause an issue since the data is loaded using the primary UD1 structure. However, we just want to get some reassurance of this as we roll out this new alternate reporting structure.

       

      Thank you again for all of your help!

       

       

       

      • JackLacava's avatar
        JackLacava
        Honored Contributor
        3 years ago

        Ah, so the key is just the conditional rules making it inaccessible. That shouldn't be a problem.

        If you don't like the pink background, you can use formatting on the column. Check the section "Cube View Formatting" in the Design and Reference Guide.