Hi,

Via another post extremely helpful people i.e Andrej and Michael have
helped be have a better understanding regarding the concept of Staging
Area concept...

I would like to post a related question and request if someone can
kindly guide me with an example of the transformation process. The
reason I'm asking is becuase though I have now learned to create
dimensions and cubes (really just the basics becuase the advanced
options that one comes across while creating a dimension; are still
unclear to me) what I'm currently doing is probably a very simple
scenario i.e. just getting sales data from the OLTP into eventually a
cube. My dimensions for this are Product, Geography, and Time and
ofcourse the Sales table ends up being the fact table. Thus maybe there
is no need for transformation in this particular case?? But I am sure
that as I go forward I will soon run into a situation where I might be
requiring to do a transformation..

Thus to summarize my request/question: Can someone kindly provide an
example of a transformation i.e. starting from how the table in the OLTP
looks like and what it ends up as after the transformation.

Many thanks in advance.

Haven't tried that many cases yet but cleansing can be different things. I
like to have my dw environment snowflaked which means i would like to have a
table for each level in a dimension - this isn't always the same structure
in the OLTP system - therefore i have to do something when pulling the data
out - have to make one statement that pulls out the lowest level with
reference to a the above level and so forth....

Here's some other samples - don't know how good they are:

* if you have some products which no longer are being sold (must be sure
that you don't have any historical data on these in the fact though) then
you could prevent these from being pulled out from the OLTP system - and do
you want sales from all the year or just for 3-4 years ?
* how reliable is the datamodel in the OLTP system - could there be products
in the sales table which never was created as a product in the products
table - if
* regarding levels above - could product have any levels that could be
usefull drilling down - or would you need some member properties when
looking at a specific product member .. if you need a member property you
need to pull that out of the OLTP system as well
* One of the transformations that we are doing are adding a possibility to
make levels outside of the OLTP system - if you do this you have to have a
front end for maintaining these of importering this from an excel sheet
* Lets say the product titles are a combination of small- and uppercased
characters - you could do something about this using some sql statements
instead of demanding this corrected in the OLTP system
* and i mentioned the surrogate keys in an earlier post - andrej is propably
right - in smaller environments it isnt' necessary - so it depends on you
data amount

However - I believe cleasing becomes a lot more significant when combining
data from difference OLTP systems - haven't tried it yet - though.

\Michael V.


"Learner" <wantnospam (AT) email (DOT) com> skrev i en meddelelse
news:MPG.1ae9a7e7e593c4699896d4 (AT) msnews (DOT) microsoft.com...

Thanks Michael. That provides an insight as well as ideas.
Many thanks :-)