Thanks for reviewing the course. It has been awhile since I reviewed the course (I think I recorded it 4.5 years ago before SYS modules were thought of) but if you are referring to a line item that is hardcoded to 1, then yes, absolutely. The module that does the sum of the count needs to be in a different module because of the sum.
Thanks for the response Rob. I have it working in the same module as the Properties, but may need to take another look to see what the risks are. Re: your statement on SYS modules, is there a better way to balance a hierarchy using those (SYS)? Is there a resource you could point me to? Thanks again!
At the time of the video, there was no such thing as SYS modules, but in essence, that is exactly what the Build Hierarchy module is since it is not dimensionalized by Time and is only dimensionalized by the flat list.
I recreated a version of this for training purposes and noted the count line item can be stored in the SYS properties module.
Take a look at the attached blueprints:
SYS00 - Flat Attributes
SYS01 - Composite Hierarchy Build
Use case = Client source system gives a parent child format, list is ragged. 4 columns, a) member name, b) member code, c) parent name, d) parent ID.
Ragged explained - If the lowest level is level 6, certain branches will only go down to L3 and client needs to plan something at L6, the lowest level.
Steps to complete:
Step 1. Load CSV file to 'Flat list' (import code and name), if numbered, import code and display name property.
Step 2. Create 'SYS00 - Flat Attributes' (flat list applied), import the properties/attributes into this model, Parent ID and Parent Name (match on code, will need code line item)
Step 3. Add line items and formulas to SYS00, refer to excel attached, purpose here, is to identify the members with no children, aka "base member"
Step 4. Create 'SYS01 - Composite Hierarchy Build' (flat list applied) refer to excel attachment, purpose here, is to create dummy members where the hierarchy is ragged --> append (LX).
Step 5. Create saved views in SYS01, a saved view for each level of the hierarchy
Step 6. Create X number of lists, relate them into a composite hierarchy by assigning the parent in general lists
Step 7. Import saved views from step 5 into each of the lists.
Lastly, in practice, the composite hierarchy lists, would be created in the spoke model and saved views from the datahub would be imported into the spoke. Level 2 Model Building gets this concept across. However, it does not cover the specifics of leveling a ragged hierarchy.