Hi ALl,

I am implementing OLAP where many divisions( about 10) are involved.
Same product could be sold by different division. Each division has
different product coding. Problems comes when I try to consolidate
the sales by product for all division.

This a design question, how can I get the total sales of one product
from all division ?
How to work around this in OLAP ?
SHould I total by name ?

Thank you for any help,
Krist

Hi Krist,

If the products sold by all divisions are *identical* then you could do a
mapping during data load to map all SKU-Numbers or keys to one general
product SKU (key) - if you don't want to change the operational systems to
use the same SKU-Numbers. In my opinion this is the cleanest way. A more
dirty approach is to create an artificial Name-element and put the products
as its descendants. This would allow you to continue book onto different
product numbers and analyse the totals on this artificial level. The
combination of individual product and dimension wouldn't be of much interest
for the users though so I would regard to not show the lowest level to them.
This design isn't fine art though.

Kind regards
Joerg

"Krist" <xtanto (AT) hotmail (DOT) com> schrieb im Newsbeitrag
news:cb48a3b.0406240306.6316d571 (AT) posting (DOT) google.com...

Hi Joerg,
Thanks for your reply..

Not *All products are identical in all division, but some or many can
be randomly exist in more than one division, with different SKU.

Do you mean that I should create 'small application' at OLAP side for
user to input General SKU and what Operational SKU that use it, into a
'mapping table'. This mean there will be user effort here.
Then ETL read this 'mapping table' to load the general SKU into the
fact table and dimension ?

Is it possible to do this without user effort, all automatically by
design and ETL process, transparent to user ?

Thank you for your help,
Krist

"Joerg Narr" <n_o_spa_mjoerg_narr (AT) hotmail (DOT) com> wrote

Hi Krist,

your question seems to have its origin rather from an organizational source
than from anything else. So...

I would recommend to solve (cleanse) this problem by either implementing one
SKU within all your divisions (in the operational system(s)) or to do it by
means of ETL.

You will have to create some kind of mapping table or do it in DTS if you
are using Microsoft or the tool of your choice or which is available if you
use a different product. You could map 9 out of 10 product SKU's to the
first SKU. Before doing so though, I would recommend thinking about a
performant key (data type) and maybe implement a surrogate key. The users
don't need to tell you the surrogate key but they need to tell you which
products should be mapped to this key. The load and transformation process
can be automated, but the maintenance of the business information need to be
done by somebody who understands and knows the business. The ETL-way will
count for constant time consumation for all your data load processes and
also a maintenance effort since you or a user will maintain the mapping
table. So if possible the first way would be the cleanest and over a long
period maybe cheaper. It could result in a huge project though to cleanse
the operational systems if it is not impossible. (I don't know your product
master data or its data quality respectively).

If you cleanse the data in the ETL-process, yes. If there are any patterns
and the SKU's can be determined by an algorithm you might be able to write
some procedural code.

You will always be required to maintain changes and to reproduce the changes
made in the operational systems if doing it in the ETL-phase unless you
can't use an algorithm. This can be quite hard especially if the product
master data changes frequently.
It's a pleasure.

Joerg

Hello Krist,

If you are using Cognos technology, this problem should be solved "on
the fly" (Then no data manipulation in the DB source..) after creating
the model and after reading the very first time the DB source.

In fact in order to classify data from different datasources, Cognos
Transformer, the tool who creates the hypercube, helps the OLAP
Administrator with some "Grouping facilities".

In any case, this operation should not be so easy. It depends on the
cardinality of your "product type"....

If you are using other technologies, probably you could find these
facilities as well.

hope this helps,
Giuseppe