Large product list handling. Sales planning.

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?

    • Alexis Lawrence

      Hi Yuri,

      Thanks for this post – I’m just tackling some very large dimensions in an Analyst-only model. One of the changes in 10.1 is a big improvement in how cut-downs are processed, specifically with the aim of dealing with sparse cubes better. This might make the A-Table solution a bit more viable.

    • ykud

      Hi Alexis,
      Thanks for stopping by. Haven’t seen 10.1 yet, looking forward to seeing better cut-downs in action.