Applied dimensionality

Big dimensions are ep’s doom for sure. But nevertheless we need them to get going.
Problem statement:
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.

	Qty	Price	Income
Product Group#1
Product#1
Product#2
…
Product group#n
Product #n

2. See Product groups results in some way.

	Qty	Price	Income
Product Group#1
…
Product group#n

Ideas:
Create a contributor application with full product list.
And then to take full advantage of No Data access tables and cut-down models.
Pro’s:

Simpliest variant

Easy maintenance and support

Contra:

i fear that setting up full 4-dim (product, sales, months, versions 10k*5*15*5=3750,000 for a cube) cubes with 10k product list will not work, even using No Data extensivly

if it will work, it’ll be a hard burden to calc cut-down models with the “ghost” of huge product list.

I fear that it just won’t work.

Create an separate contributor application for each region. Applications will only differ by product-list.
Pro’s:

Fastest (for user) variant possible.

Not working on software’s edge.

Contra:

Adding a new region introduces a full new contributor app creation, with all access-tables, elists creation

each new application has to be connected to other part of system (like company P&L calculation)

Macro’s and links to be added for new application integration

A change in non-product dlist triggers n synchronizations

Introducing increasing complexity factor in system

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

	Product Name	Qty	Price	Income
1	Product#1
2	Product#2
…
n	Product #n

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.
Pro:

Ony one application, easy maintenance and so on

Only one, rather small cube containing an full product list

Application is build on dlist, containing only a maximum number of product per region, instead of full product list

Contra:

Slow execution of dlinks using virtual product dim and this huge alloc table (yep, 10k allocations is trouble)

More variants.
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

	Product Name	Qty	Price	Income
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

	Product Name	Qty	Price	Income
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).

Pro:

We still avoid full product list

Speed of links is acceptable

Contra:

Only one hierarchy can “unwraped” by such method.

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?