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.