Cognos Transformer - Achieving drill-down via a hierarchy ...

[pass_cognos]

Good Folks,

I'm in the process of trying figure out what's going wrong with my Cognos Transformer model.  In essence, I am trying to achieve drill-down on my data but are failing miserably despite providing the model with a hierarchy to work from.

Basically, I was hoping based on the data that I'm providing my Cognos Transformer model (via two seperate data providers) that I would be able drill-through/drill-down my sales data from top-level (Item Category) to middle-level (Item Sub-Category) to the lowest-level (Item Description).

Architecturally, my Cognos 8 Transformer has two data providers (both CSV extracts) linked simply base on having "Item Description" in both data providers.  Correct me if I'm wrong, but think should link two data providers??

Anyway, the contents of the data providers are ...

* Data Provider 1 - Contains Point Of Sale (POS) data

e.g.  POS ID, "Transaction ID, Operator Name, Item Description, Unit Price and Quantity

* Data Provider 2 - Contains a Product Hierarchy

e.g.  Item Category, Item Sub-Category, Item Description

What I am trying to do and are currently failing at is to achieve drill-through/drill-down functionality.  Basically starting at the top level ...

______________________________________________________
** Level 1 - ITEM CATEGORY **

I would like to be able to see the high-level sales figures for each Item Category, e.g. Haberdashery, Fresh Produce, Baked Goods, e.t.c.

e.g ...

                          Sales (Sum $)         
                    ____________________________
Haberdashery |      $1,505.55                         
                   |
Fresh Produce|      $9,203.22                   
                   |
                   _____________________________
                         
       TOTAL         $10,708.77

______________________________________________________

** Level 2 - SUB-CATEGORY **

Then be able to drill-down to the next level (Sub Category) and see a break-down of the sales figures at Sub-Category level (the next level)

e.g. for Haberdashery ...

                          Sales         
                    ____________________________
Bed Linen       |      $505.55                         
Pillow Cases    |     $400.00
Pillows           |      $600.00
                    ____________________________
                         
       TOTAL         $1,505.55                         


** Level 3 - ITEM DESCRIPTION level **

Then be able to drill-down to the next level (Item Description) and see a break-down of the sales figures at Item Description (the next level and lowest level)

e.g. for Bed Linen ...

                                              Sales         Quantity
                                       ____________________________
Queen of Asia Bed Linen       |      $505.55          1               
                                       ____________________________
                         
                             TOTAL         $505.55                         

______________________________________________________

Any thoughts or suggestions on what I'm doing wrong and/or how to correct it would be greatly appreciated!!!

Thanks for your time!

Cheers,
Cogs in a wheel

[cognostechie]

Not sure if you already figured this out...

and I don't know what you mean by saying that you cannot drill down. Where do you see the problem of not being able to drill down ??

Item description in both the data sources should be able to do the job as long as Item Description is used as the 'Source' in the lowest level of the heirarchy in the dimension. Ideally, a Code should be used but technically, the description should work too.

Yes, multiple data sources can eb related because Transformer does NOT join any data sources. It merely reads one by one and links by common field name. So it does not matter how many databases/sources you use.

Right-click on the data source that has the fact (CSV file) inside Transformer and select 'show scope'. If you see Yellow color at the lowest level then the data should roll up for all heirarchies since your dimension would be cetagorised by the other CSV that has all 3 heirarchies.

[s_chukka]

transformer never join the tables/datesources like DIM.column1=FACT.column2

but to provide the relationships between the tables/data sources we have to name the joining columns exactly same name ( case sensitive) and the last level in the dimention should be unique.

Assume that the dataprovide 1 has the columns namely "Transaction ID, Operator Name, Item Description, Unit Price and Quantity along with PRODUCT_NUMBER" and the data proviser 2 should have the column 'PRODUCT_NUMBER' ( case sensitive) and this PRODUCT_NUMBER column shold be the last level of the dimension and this level property should be Unique.