ETL as a source for some attributes of a dimension

View previous topic View next topic Go down

ETL as a source for some attributes of a dimension

Post  bmoraillon on 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

View user profile

Back to top Go down

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

Post  meb97me on 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

View user profile

Back to top Go down

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

Post  ngalemmo on Wed Aug 25, 2010 11:56 am

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

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

View user profile http://aginity.com

Back to top Go down

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

Post  Jeff Smith on 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

View user profile

Back to top Go down

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

Post  LAndrews on 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

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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