Continuing this series of blog posts, we will cover the LedgerDimensions region highlighted in pale yellow in the model below in figure 1. This region will be discussed in two parts covering this post and the next post.
Once all of the configuration data is set up, ledger account combinations can be entered, validated and persisted. The primary consumption of the dimension framework occurs when the application leverages this area.
Figure 1: Ledger dimension storage in framework
Ledger dimension storage without rules
To understand LedgerDimensions, we need to understand how a ledger account combination is entered by a user.
Using the account structure and rule setup from the previous blog post (#4 - Advanced rules), we will walk through the user interaction with the account entry control when an account is entered. To refresh, the account structure appears as follows:
Figure 2: Basic structure and constraints
There is a single account rule associated with the structure:
Figure 3: Single rule added
With the added structure defined as follows:
Figure 4: Single structure added
When a user first sees a ledger account field on a form, it will appear as follows when focus is not in the field:
Figure 5: Empty ledger account field (without focus)
When the user clicks into the field, since the account structure has 2 segments, the following will appear:
Figure 6: In-edit ledger account field
The user then enters the combination of [ 150 - A ].
Figure 7: Completed ledger account field
As soon as the second segment is entered and the user tabs out of the control, the dimension framework saves the combination and then validates if the combination is valid based on the constraints. In this case, the combination is valid.
The following is known about the combination:
- The account structure is "MyAccountStructure"
- The first segment is the "MainAccount" dimension with a value of 150.
- The second segment is the "Customer" dimension with a value of A.
- No additional segments were added on as the values did not match any advanced rules associated with this account structure.
Therefore, the 2 segment values are stored as follows across the 4 tables:
Figure 8: Ledger dimension storage query results
The first table is named DimensionAttributeValueCombination as it stores a full multi-segment account combination along with some de-normalized information about the combination such as the concatenated segments as a single string, a foreign key (FK) reference to the Account structure, and a FK to the MainAccount (150) that was used.
Skipping the second table for now, the third table is named DimensionAttributeValueGroup. It has this name as it stores each related group of segments associated with each structure that is present in the combination. In this case, there is only one structure, the Account structure, so there is only one record here.
The fourth table, named DimensionAttributeLevelValue, stores the individual segment values for each segment within the associated group or structure. One record exists for each segment entered. When a segment is empty, no values are stored for it. Each record references the corresponding DimensionAttributeValue record. If an existing one is found it is referenced, or if one does not exist for the value, it is created. This is the data linking the DimensionAttribute to the real backing entity record. In this case, one is for the MainAccount record with an ID of 150, and one is for the CustTable record with an ID of A.
In order to link the group of the account structure values and the segment values to the main record in the DimensionAttributeValueCombination table, a record is inserted into the second table above in figure 8, DimensionAttributeValueGroupCombination.
In order to save a single segment in a new combination at least one record is inserted in each of these 4 tables. An additional record is inserted in the DimensionAttributeLevelValue table for each additional segment entered. Abstractly, these 4 tables are referred to as a LedgerDimension. A LedgerDimension is expressed as a FK that references the RecId in the DimensionAttributeValueCombination table.
The use of advanced rules plays a role in the number of records involved in these tables for a combination. This will be covered in the next blog post.