GL Account Dimension
Posted: Wed Jul 23, 2014 8:42 pm
Hi there,
We have general ledger Code as 530010.40000.01. The first six digit is (company number of entity (3) + Dept (3)), the second six digit is general ledger's account number (6), rest digit is subsidiary number (3).
Existing general ledger cube has account, company, dept, version, ledger type, Month, Year, Measure. I think it is a general design practice to break down GL. code to GL. account dim and dept dim. For dept dimension, alternative hierarchies were created, one is n level depts. were rolled up to 'admin', 'non admin', 'all depts' three consolidations.
We have an Income Statement's row set as
+ Contribution Margin
Net Revenue (All Depts)
Manpower (All Depts)
Variable Costs (Mixed Depts)
-Supplies (Non Admin)
-Equipment (Non Admin)
-Other (Non Admin)
-Waste Disposal (All Depts)
-Cash Discount (All Depts)
Fixed Costs (Mixed Depts)
-Supplies (Admin)
-Equipment (Admin)
-Equipment (Admin)
-Other (Admin)
-Depreciation (All Depts)
-Insurance ( All Depts)
We like to create an active form so we can drill down to line item details (for instance, view Revenu contributed by n level depts), I developed alternative consolidation for net revenue, manpower, VC, and FC, stacked account dim with department dim side by side on the Active Form's row, this solution almost fulfills our reporting need, but the problem is I can't get variable costs, fixed costs, Contribution Margin calculated correctly due to mixed dept used for their children line items. Instead I created a virtual cube without dept (all calculation results from GL. cube), then copied correct variable costs, fixed costs, CM over. The active form ends up with so many different views combined to make it the way we want.
I wonder any advanced TM1 user out there suggests creating a new "Acct.Dept" combo dimension, how your company design GL. account and dept (or cost center) relationship, so drilling down PL line item by dept possible? or how do you deal with an income statement account structure that is department driven, so I can drill down via consolidation rather than stacked dimensions. I read a few postings, if two dims are large, combination solution is discouraged. In our case, account dim has 2200 elements and dept has 63. Any idea is appreciated.
We have general ledger Code as 530010.40000.01. The first six digit is (company number of entity (3) + Dept (3)), the second six digit is general ledger's account number (6), rest digit is subsidiary number (3).
Existing general ledger cube has account, company, dept, version, ledger type, Month, Year, Measure. I think it is a general design practice to break down GL. code to GL. account dim and dept dim. For dept dimension, alternative hierarchies were created, one is n level depts. were rolled up to 'admin', 'non admin', 'all depts' three consolidations.
We have an Income Statement's row set as
+ Contribution Margin
Net Revenue (All Depts)
Manpower (All Depts)
Variable Costs (Mixed Depts)
-Supplies (Non Admin)
-Equipment (Non Admin)
-Other (Non Admin)
-Waste Disposal (All Depts)
-Cash Discount (All Depts)
Fixed Costs (Mixed Depts)
-Supplies (Admin)
-Equipment (Admin)
-Equipment (Admin)
-Other (Admin)
-Depreciation (All Depts)
-Insurance ( All Depts)
We like to create an active form so we can drill down to line item details (for instance, view Revenu contributed by n level depts), I developed alternative consolidation for net revenue, manpower, VC, and FC, stacked account dim with department dim side by side on the Active Form's row, this solution almost fulfills our reporting need, but the problem is I can't get variable costs, fixed costs, Contribution Margin calculated correctly due to mixed dept used for their children line items. Instead I created a virtual cube without dept (all calculation results from GL. cube), then copied correct variable costs, fixed costs, CM over. The active form ends up with so many different views combined to make it the way we want.
I wonder any advanced TM1 user out there suggests creating a new "Acct.Dept" combo dimension, how your company design GL. account and dept (or cost center) relationship, so drilling down PL line item by dept possible? or how do you deal with an income statement account structure that is department driven, so I can drill down via consolidation rather than stacked dimensions. I read a few postings, if two dims are large, combination solution is discouraged. In our case, account dim has 2200 elements and dept has 63. Any idea is appreciated.