GL Account Dimension

[LUS]

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.

[jim wood]

Have you considered creating a reporting cube with department removed? You could either then connect it by rules or TI. The issue you have is different levels of department within the same report. If you remove department from the equation then drilling down will become easier. Obviously for this, how you connect the reporting cube is the key to this working,

Jim.

[LUS]

Jim, we like to keep dept in the report because we want to drill down PL line item by depts to conduct business analysis. I did consolidated line items without dept such as net revenue, FC, VC, manpower, and CM, but it would be nice to drill down consolidated line items by department as well which I was not able to do so using this approach.