I have been advised not to use the ConsolidateChildren function (as it can drastically affect performance), so I am looking for an alternative. Here is the situation:
Dimensions in the cube: Year, Period, Location, Package, Product, Customer, Measures
Structure of the customer dimension:
Total Customers
Customer A (n)
Customer B (n)
Customer C (n)
etc.
One of the elements in the Measures dimension is a Unique Customer Count. For n-level customers, Unique Customer Count is either 1 or 0, based on whether or not the customer has units (another element in the Measures dimension). I would like ['Total Customers', 'Unique Customer Count'] to be the sum of the Unique Customer Counts for each n-level customer. Currently, it is following the rule that should only apply to n-level customers.
Many thanks in advance.
[Admin Note: Moved from Useful Code to the correct (Cognos TM1) Forum]
Alternative to ConsolidateChildren
-
jbonoffski
- Posts: 1
- Joined: Tue Sep 20, 2011 11:09 pm
- OLAP Product: TM1
- Version: 9.5.1
- Excel Version: 2007
-
lotsaram
- MVP
- Posts: 3706
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Alternative to ConsolidateChildren
Getting unique customer counts or unique product sales per time period is something that can only be achieved with difficulty in a TM1 cube as there is no CountDistinct like in SQL. You can get there using rules with ConsolidateChildren but it's not pretty and in large cubes the performance is also not good.
Best solution for this requirement IMO is pre-calculation with TI to "crawl" through data in the main reporting cube and write the counts that are needed. Most often this will be to measures in a separate appropriately dimensioned cube(s) (but not necessarily it could all be in the same cube). If separate cubes are used to store these counts then they can always be rulled back into the main reporting cube via a simple DB lookup which will give much better performance than a pure rule approach to generating unique counts.
Best solution for this requirement IMO is pre-calculation with TI to "crawl" through data in the main reporting cube and write the counts that are needed. Most often this will be to measures in a separate appropriately dimensioned cube(s) (but not necessarily it could all be in the same cube). If separate cubes are used to store these counts then they can always be rulled back into the main reporting cube via a simple DB lookup which will give much better performance than a pure rule approach to generating unique counts.