Ledger account combinations - Part 6 (Ledger dimensions (B))

  • Article

Introduction

Continuing this series of blog posts, we continue the discussion on the LedgerDimensions region highlighted in pale yellow in the model below in figure 1.

 

Figure 1: Ledger dimension storage in framework

Ledger dimension storage with rules

Building on the ledger dimension storage example started in the previous blog post, we will add to the scenario and assume the user will go back and change the values from [ 150 - A ] to [ 145 - Q ]. As we know from the advanced rules previously set up, this will trigger a third segment to be added to the account structure.

 

Figure 2: In-edit ledger account segment (before tab)

When the user tabs from the second segment, a third segment is added to the control and focus placed in it:

 

Figure 3: In-edit ledger account segment (after tab)

Now, the user can enter a license number:

 

Figure 4: Completed ledger account field

As soon as the third field is entered and the user tabs out of the control, it will trigger the validation of the combination. If it is valid, the combination will be saved as a LedgerDimension.

The following is known about the new combination:

  • The account structure is "MyAccountStructure"
  • The first segment is the "MainAccount" dimension with a value of 145.
  • The second segment is the "Customer" dimension with a value of Q.
  • 1 additional segment was added due to an account rule structure "MyRuleStructure1" being added due to the values matching the rule for the first two segments.
  • The third segment is the "LicensePlate" dimension with a value of AAA 111.

 

 

Figure 5: Ledger dimension storage query results

For this combination, a total of 8 rows were inserted across the 4 tables storing the ledger dimension. The difference between the first ledger account combination, discussed in the previous post, and this one is that multiple structures are being used to drive the dimensions that make up the ledger account combination. There are 2 records stored in the DimensionAttributeValueGroupCombination and DimensionAttributeValueGroup tables, each one representing a structure used and joined to the full combination.

Notice that each record has a new RecID assigned to it. The combination of the previous values is not updated, but rather a new combination is created making the LedgerDimensions immutable. This was done because there is no reference counting maintained on the use of the combination. The same [ 150 - Q ] combination originally entered may have been referenced from multiple tables within the application before the user decided to change an instance to [ 145 - Q - AAA 111]. Therefore, a new combination must be created and the reference changed to it only from the table that the ledger account combination is being changed on.

Because a user may change the combination on a record by adding or removing segment values and a new LedgerDimension created, it is possible to end up with unreferenced or orphaned LedgerDimensions over time. Allowing orphaned combinations improves performance of the overall dimension framework to not issue deletes across the tables in question when combination is changed. It is also likely after a combination is used once that it will be reused again and removing it instantly on removal of the last reference may only result it in being recreated again. Orphaned LedgerDimensions are still structurally valid and can be reused in the future if the combination of values in relation to the structures and rules are entered again. If a combination is ever entered a subsequent time, no records are inserted and the existing reference is reused providing greater performance.

Optimizations are also made for storage size and insert cost when advanced rules are used. Consider the following example as a new account combination is entered:

 

Figure 6: Changed ledger account field

In this case, the only difference between the new combination and the previous is that the license plate number (provided by the advanced rule) was changed. The data storage of the combination will appear as follows (new records in white):

 

Figure 7: Additional ledger dimension storage query results

In the creation of the new combination, the 5 records highlighted in white were inserted:

  • 1 in DimensionAttributeValueCombination
  • 2 in DimensionAttributeValueGroupCombination
  • 1 in DimensionAttributeValueGroup (instead of 2)
  • 1 in DimensionAttributeLevelValue (instead of 3)

This is because the values stored as part of the account structure 'group' are the same between the previous combination (DAVC2) and this combination (DAVC3). Those DimensionAttributeValueGroup and DimensionAttributeLevelValue records did not need to be recreated. Instead, we were able to reuse 3 records and save their insertion cost.

Alternately, had the structure associated with the account rule allowed blanks for the license plate number, and a combination of just [ 145 - Q ] was created, there would only have been 2 new records inserted instead:

  • 1 in DimensionAttributeValueCombination
  • 1 in DimensionAttributeValueGroupCombination
  • 0 in DimensionAttributeValueGroup
  • 0 in DimensionAttributeLevelValue

This is because all of the DimensionAttributeValueGroup and DimensionAttributeLevelValue records already existed and could be fully reused on the new combination. This is the primary reason why data should never be directly modified within the LedgerDimension storage tables. A change to a single record could affect not only all references to that ledger dimension but also one or more other ledger dimensions and references to them.

Although partially collapsed in the above examples in figure 5 and figure 7, there is a Hash code assigned to the DimensionAttributeValueCombination and DimensionAttributeValueGroup tables. The purpose and source of this advanced data column are discussed in the next and final blog post in this series.

Comments

  • Anonymous
    January 29, 2015
    Hi BillThanks for your informative posts.   I have found your articles invaluable.  When there is a scenario where there are multiple DAVGC records listed for a given DAVC record.  How does one select the right one that's associated to the transaction such as GeneralJouranlAccountEntry.LedgerDimension.  Based on your description above I thought I could simply select the record from DAVGC that was last inserted (going by higest RecID value) but it in my case the active record is the older record and its not clear how one selects.  if i look at the DisplayValue field from DAVC, clearly its the older group that's being used.
  • Anonymous
    January 29, 2015
    ok i think i figured it out.  I can use DAVC's Account Structure to further narrow down the list to give me my "Live" set in DAVG
  • Anonymous
    February 05, 2015
    The DAVGC table associates all structures used to build the combination. It isn't a case of a current "live set" of DAVG's for a particular DAVC -- all of them apply to the combination; always.The first DAVG linked to the DAVC via the DAVGC table (where DAVGC.Ordinal = 1) is always associated with the current account structure, and is de-normalized into the DAVC.AccountStructure column (matching the DAVG.DimensionHierarchy FK reference). If there are additional DAVGC records with .Ordinal > 1 for a single DAVC, then these are the added account rule structures that add additional dimensions to the combination above what is already in the account structure. These are as important as the ones in the account structure, as the accounting setup required they be added when certain criteria was met with dimension values entered into the dimensions specified by the account structure.Their values should be included as part of the display value, tacked on at the end. The only time they don't appear is if the added dimensions through the advanced rule are a duplicate of dimensions already appearing in the account structure. Then the DAVC.DisplayValue simply shows their first occurrence, not replicating across the added rules. However, the database, for referential integrity reasons still does persist the individual duplicates appropriately.
  • Anonymous
    December 22, 2015
    Thank you Bill for the superb postings on dimensions. This is must read for anyone who does not want to reverse engineer all the dimension tables themselves as this post saves us a lot of time.