Our company has many named customers and many units. We need to do planning by customer for each unit and each unit has about 20 customers (if there's more, they can use the "Other" customer). We do not know ahead of time which units will have which customers. I am looking for advice on best practices to implement this. We must implement using Cognos EP 10.1.
The data size is:
- ~1000 business units (elist)
- ~1000 customers
- 12 months
- 5 submissions (prior year, budget 1, budget 2, budget 3, forecast)
- 3 currencies (local currency, FX rate, USD)
- ~20 sales metrics (revenue, headcount, ...)
Any advice is much appreciated. Thank you!
Hi,
There are two possible approach
A) Introduce all dimension at full and create a big cube with multi million cell count. Have a complex access table with relevant Customers to Unit mapping.
Advantages:
You will get all information in one single cube.
All dimension elements will be available and data will at correct dimensional intersection Reporting will become easy.
Control over Unit users and Customer mapping
Disadvantages:
Its will take huge time for GTP and Admin link.
Due to size performance will be slow.
Will take more storage and memory for processing
B) Have a serialized dimension of Item 0 to 25 instead customer dimension. Insert an additional Item Customer Code/Name in your measure dimension. Tell user to manually enter customer code/name for each value or push customer code/name to that item. (Though there is a d-list format option, I will not recommend it as you have 1000+customers)
Advantages:
Small cube.
Faster GTP. No access table required for Customer to Unite Mapping.
Much better performance.
Easy to maintain and modify.
Disadvantages:
Users has to enter customer code manually which might lead to typos.
It will be difficult to have customer level visibility of data.
Customer level data reporting will require additional transformation.
No control on user for Customer selection.
As mentioned above you have two different options here.
I will recommend option B but it all depends on various things and especially actual business requirements.
Regards,
Jitesh