Big dimensions are ep’s doom for sure. But nevertheless we need them to get going.
A regional sales planning model to be created. 10k products(with product subgroups), spread among number of regions selling some subsets of product list (each subset is relatively small, about 2k els). Regions are contributors.
Oh yes, cognos innovation center gives variants with 25 element product list. crivens.
Users need the following input forms.
1. Input Sales.
2. See Product groups results in some way.
Create a contributor application with full product list.
And then to take full advantage of No Data access tables and cut-down models.
I fear that it just won’t work.
Create an separate contributor application for each region. Applications will only differ by product-list.
So we’re buying speed for support pain.
Create a contributor application not with full product list, able to incorporate all possible regions.
We’ll focus on the fact, that each region sells only a subset of full product list. Let create a “1-max_number_of_products” list, and then build our app on this list, instead of full product list.
We’ll have following for sales cube
That’s better, we can use only “1-max_number_of_products” in such application, instead of full product list meaning our cubes will be number_of_products/max_number_of_products times small.
How to calculate Product Groups results?
If we create cube, called product classifier:
|Product Name||Product Group||Other Hierarchy #1|
|1||Product#1 inputed by this region||Product Group #1|
|2||Product#2 inputed by this region||Product Group #1|
|n||Product #n inputed by this region||Product Group #n|
We can use this cube as allocation cube for calculating Product Group results, mapping “1-max_number_of_products” list to Product Group list. Great, isn’t?
Surely it isn’t. This product classifier needs an elist in it, and we can’t use an allocation cube with an elist in it in contributor apllication. It really drives me mad, banging into this times and times again. It would so simple.
Anyway, there’s a workaround, of course.
This product classifier can be an assumption cube. But it will look like:
|Product Group||Other Hierarchy #1|
|Product#1||Product Group #1|
|Product#2||Product Group #1|
|Product #n||Product Group #n|
And our link will be of consolidation type, mapping products of virtual dim to product groups.
But this cube has contain a full product list. Compensation — this full product list is included only into one, rather small (2 dims 10k*2) cube.
And a huge minus. Links using such a big cube as allocation table via virtual dim work very slow.
But it works for me with a 12k product list.
Use product groups a serparate dimension
This will allow us to have only one hierarchy – Product goups.
We are trying to avoid dlink from previous variant, 10k alloc table one, remeber?
So we’ll add a new dim to sales cube — Product Goups one, and have “1-n” product inside that group. Therefore our headache link will be Product Groups to Product Groups, match descriptions 🙂
To illustrate this 3-dim (sale,product,product,group) cube via 2 html-table, i’ll use MS AS notion of pages.
Page Product Group 1
|1||Product#1 of PG1 (Product Group 1) inputed by this region|
|2||Product#2 of PG1 inputed by this region|
|n||Product #n of PG1 inputed by this region|
Page Product Group 2
|1||Product#1 of PG2 inputed by this region|
|2||Product#2 of PG2 inputed by this region|
|n||Product #n of PG2 inputed by this region|
So for all PG’s.
We’re increasing the total volume stored, but in this cases length of “1-n” should be less than in previous variant, it now equals maximum number of product in PG, inputed by region. Assuming sane product grouping, we get n close to 300 in case of ~50 PGs. So we No Data all PG’s not inputed by current region (elist). Furthermore, we can set Product Name to text, since we are using PG’s only (gives us no plusses in this variant, but is useful for another trick, which i will decribe later).
Use product list as an elist
Don’t know how it will help, but 10k elist is possible 🙂
Which variant to choose, any suggestions? Any ideas, how to speed up links, from the last variant?