Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

ETL as a source for some attributes of a dimension

Go down

ETL as a source for some attributes of a dimension  Empty ETL as a source for some attributes of a dimension

Post  bmoraillon Wed Aug 25, 2010 7:42 am

Hi all,
is it allowed to programmatically add value on a row of a dimension by the etl process ?
For instance :
Source contains only books :
NK | NAME
142 | invisible

Etl source the DW dimension 'Product' and programmatically add a category : book

so 'Product' looks like :

SK | NK | NAME | CATEGORY ...
1 | 142 | invisible | book

or do i have to add the category in my source table system ?

Many thanks
Best Regards

bmoraillon

Posts : 12
Join date : 2010-06-06

Back to top Go down

ETL as a source for some attributes of a dimension  Empty Re: ETL as a source for some attributes of a dimension

Post  meb97me Wed Aug 25, 2010 11:17 am

no thats absolutely acceptable,

you'd add a "Derived Column" called CATEGORY and give it the value "book" assuming your using SSIS.

Obviously if your source system changes so that you are storing more than just books in that table you'd need ome way to identify which items were books and which weren't in which case a sepearte category field would be required


meb97me

Posts : 34
Join date : 2010-07-28

Back to top Go down

ETL as a source for some attributes of a dimension  Empty Re: ETL as a source for some attributes of a dimension

Post  ngalemmo Wed Aug 25, 2010 11:56 am

Sure, that's fine. Its part of the 'Transformation' that ETL is supposed to do.
ngalemmo
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

http://aginity.com

Back to top Go down

ETL as a source for some attributes of a dimension  Empty Re: ETL as a source for some attributes of a dimension

Post  Jeff Smith Fri Aug 27, 2010 5:12 pm

Just curious, but wouldn't it be better to create a reference table that had "Invisible" and "Book"?

If a new value is added to the source system, such as "opaque", wouldn't you have to open the code to assign "opaque" to "Magazine"? If you had a reference table, you could simply add Opaque and Magazine to the table and not have to open the code.

I'm just wondering about the pros and cons of creating lookups or hierarchy levels in a reference table vs building it in the ETL.

Jeff Smith

Posts : 471
Join date : 2009-02-03

Back to top Go down

ETL as a source for some attributes of a dimension  Empty Re: ETL as a source for some attributes of a dimension

Post  LAndrews Fri Aug 27, 2010 7:49 pm


I think the answer is "it depends".

As in your "opaque" example, if the derived column is based on the data value in another column (i.e. a typical lookup or mapping), then yes, it probably makes sense to create a lookup table for the ETL process to utilize.

Sometimes its just easier to hardcode a value, often when the ETL process or source system determines the derivation.

In the product dimension example, you may have a situation where you have multiple streams of data coming in (say in the case of different source systems). One stream is loading books (hardcode the value "Book"), another stream is loading DVD's (hardcode the value "DVD").....


LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

Back to top Go down

ETL as a source for some attributes of a dimension  Empty Re: ETL as a source for some attributes of a dimension

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum