Archive for the ‘large dimensions’ Category

You are currently browsing the archives for the large dimensions category.

Ways of calculating Running Count to import data into Multi-Line models

I've nicked the term "Multi-Line" model out from some cognos best practices presentations. Never known it was called that way )

Multi-Line is only way to go when you have a potentially huge dimension only a tiny bit of which should be available to end-user at a time. Like employee planning, whole dimension of 10k people, strictly less than a 100 in department. So you create a fake dimension of 1..100 and add an Employee name column, decreasing cube volume by 100. Access tables + cut-down might help, but it's sometimes better to allow people pick up any employee,client, product given up that there won't be more than fixed number of rows.

Main problem with this modeling technique arises when it's time to import data in such cube. That data usually doesn't have an 1..100 running count attached, so it's your task to add it.

In this post I'll sum up the ideas of how to calculate running count split by elist (that's usual, ain't it?).

As a example, I'll use this simple table

dept emp salary
Finance Pete 100
Finance Ann 200
Finance Jo 300
HR Nick 100
HR Sam 200

And you have departments as an elist, so you have to number rows so that numbering will restart for each dept. Numbering requires an order so let's alphabetical order of employee names.

So this is what we want to get:

dept emp salary running_count
Finance Ann 200 1
Finance Jo 300 2
Finance Pete 100 3
HR Nick 100 1
HR Sam 200 2

Read the rest of this entry »

Posted on February 1st 2008 in BI&CPM, bi, cognos, ep, ep cookbook, large dimensions, open source | No Comments »

More on Contrib APs.

It's more a list of demands for Cognos :)

We need allocation cubes with an elist. There is no other way round the idea of dynamic Contributor applications. Dynamic as Analyst ones -- by changing allocations in cube, you change data flow. Ain't that analytic?

What if we use footage as driver for lease costs? Or, maybe, headcount?

That's a click&go in Analyst, and a click&synchronize&reconcile&what happened to everyones budgets in Contributor.

And, of course, we need some kind of alloc cubes/allocation tables for Administration Links. Because any way of manual allocations is potential threat to system "clearness" and reason ability. And, just for remark, no "virtual" dims in amdin links.

But we need to build systems right now, so I propose such variant.

Two Contrib APs, A and B. Data ought to be transfered from A.Cube1 to B.Cube2 and they are of entirely different formats. So possible admin links will have a huge amount of manual allocation.

So we create a "transformation application" (TA), that has, for our example, Cube1, Cube2 and a set of allocation cubes, used to transform data X->Y. Those allocations cubes do not have an elist and are updated in Analyst and synchronized later.

So to send data from A to B we first send it to TA.Cube1 and then get data from TA.Cube2 to application B.

A.Cube1->TA.Cube1->TA.Cube2->B.Cube2

Click on the scheme to enlarge it.

TA_Scheme

Pluses of such scheme are:

1 speed compared to A->Analyst transformation->B. Everything is transferred via Admin links, clustering and etc

2 control. All Admin links are mathced desc, and transformation is dictated by alloc cubes, managed from Analyst.

Minuses

1 speed compared to A->B, with manual alloc in admin links. Minus 1 link executed and 1 gtp (getting data to TA).

I'll stick to this scheme until elist-enabled allocation cubes are avallaible.

UPDATE: There's a workaround for making simple allocation cubes in Contributor -- see this post

Posted on October 11th 2006 in cognos, contributor-only, large dimensions | 4 Comments »

Tip of day

@AddLocalPreselection macro allows you to "slice" all cubes in macro on dlist selection.

You can choose one month and all commands in macro will be applied only to this month slices.

By setting up an input variable for this macro, you make choice dynamic. Macro starts, you choose month and them all commands are applied only this month.

Posted on August 29th 2006 in cognos, large dimensions, tips | No Comments »

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?

    Posted on June 27th 2006 in cognos, ep cookbook, large dimensions | No Comments »

    FireStats icon Powered by FireStats