ETL as a source for some attributes of a dimension

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

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

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

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.

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").....