OWB Dimensional Modeling to Oracle RDBMS Object Translation

OWB Dimensional Modeling to object translation - This is a crucial thing in data warehousing. The reasons are -

• You should know the objects which will be modified during ETL operation. It will be helpful for sql tuning and monitoring. You can understand execution plan well and do the necessary things.
• It is also necessary from DBA perspective. Not only from storage perspective, is it also necessary to utilize parallelism.

In this discussion, we mainly focus on ROLAP. This is because for MOLAP most of dimensions and facts are managed by Oracle itself within the Analytical Workspace (AW). At first we start with dimension. A dimension will be translated in the following objects -

• Table - Table will be available in Physical Binding. One dimension may create more than one table depending on implementation. If you choose star implementation, just one table will be created and if you choose snowflake implementation then for each of the level, one table will be created. Note that, in dimensional modeling you have to create at least one level for each dimension.
• Level & Hierarchy - As mentioned in previous paragraph, every dimension has at least one level. For each dimension, one dimension object must be created. This object has the levels and hierarchy. Hierarchy is optional. If your dimension has hierarchy then it will be reflected in hierarchy of dimension object.
• Surrogate Key - It is available in attributes tab. For each surrogate key, one sequence will be created.
  

It is well known that in data warehouse there are three types of slowly changing dimension (SCD). If you are using Type 2 or Type 3 SCD, then you have to define mapping for the dimension. In this case, there will be different input table which maps data to dimension output table. The mapping is always converted to pl/sql procedure.

Next comes the fact table. As we discuss ROLAP model, the fact translation is simple.

• One table is created for each fact.
• For each dimension, one bitmap index will be created.
• For each dimension, one foreign key will be created also.

Multi-dimensional OLAP:
Within the context of the relational database, the AW is a Large Object (LOB) that is a multidimensional data type. It is stored in a column in a relational table and this table will be a partitioned table. The name of the relational table will be AW$workspacename. All dimensions and facts are maintained internally within this LOB column.