Load Dimensions

[neo.helios]

What is the industry general / best practice to load the dimensional data in to the warehouse. Whether to load it directly into a dimensions using ETL or to create a table in warehouse database with the dimension structure and load data into it so that a logical dimension can be created from it later in the BI tools.

Thanks for your insights into this in advance

[ngalemmo]

Not sure I follow the question...

Best practice is to load a relational database from the source systems. If you have deployed a BI layer that includes cubes (SSAS, Hyperion, etc...) those would be loaded from the relational DW.

Under the Kimball methodology, the relational database would be constructed using a dimensional model (star schema), consisting (primarily) of dimensions and fact tables. Which is why your question "load it directly into a dimensions using ETL or to create a table in warehouse database" is confusion. A dimension is a table, and could be exposed through the BI layer. Use of cubes is optional, it is purely a deployment option independent of how the data warehouse is constructed.

[neo.helios]

Thanks ngalemmo...........

In case if I am using Oracle database... I need to have a dimension table(with create table statement) to load the data and on top of it I can create the dimension((with create dimension statement) by defining the levels and hierarchies. My question is more of .. is there any use of creating the dimensional structure in the database itself rather than just using a de-normalized table to load the data

[ngalemmo]

there any use of creating the dimensional structure in the database itself

Well... yeah. That is the whole point of dimensional modeling. Its another technique for developing relational models.

I developed quite of few Oracle based dimensional DWs and never found the need to use Oracle's 'dimension' structures. Clients were using other BI environments (BOBJ, Cognos, Microstrategy, etc...) that function off the relational tables.

[Sponsored content]