Data Modeling: Schema Generation Issue with ERwin Data Modeler 7.3

We are using Computer Associate’s ERwin Data Modeler 7.3 for data modeling. In one of our engagements, we are pushing data model changes to the physical database (Oracle 11g R2) via ERwin’s Forward Engineering / Schema Generation functionality.

The Forward Engineer / Schema Generation wizard offers the “Physical Order” option under “Column”. Checking this box is supposed to force the ERwin DDL generation engine to preserve the column order (i.e. the column order in the physical database must match the column order in the logical/physical model).

Problem: Even though our data modelers pay very close attention to the column order in the logical and physical model in ERwin, the Forward Engineering / Schema Generation process seems to lose the physical column order when generating the DDL (even though the “Physical Order” option is checked). Thus, the column order in the Oracle database never matches with the column order in the ERwin data model.

Solution / Work-Around: This is a bug as the behavior of this option seems to be inverted. If you want to preserve the column order, the “Physical Order” must be un-checked.

Oracle Data Warehouse

A data warehouse (DW) is MAINLY a database used for reporting.

Because of this purpose the DW is structured differently as an OLTP (transactional) database.

A data warehouse maintains its functions in three layers: staging, integration, and access.

         Staging layer is used to store raw data (for analysis and support);

         The integration layer is used to integrate data and to have a level of abstraction from users;

         The access layer is for getting data out for users.

A data warehouse is a consolidated view of your enterprise data, optimized for reporting and analysis.

Basically it is an aggregated, sometimes summarized copy of transaction and non-transaction data specifically structured for dynamic queries and analytics. In data warehousing, data and information are extracted from heterogeneous production data sources (different databases, different database technologies) as they are generated, or in periodic stages, making it simpler and more efficient to run queries over data that originally came from different sources. Data is turned into high-quality information to meet all enterprise reporting requirements for all levels of users. Interactive content can be delivered to anyone in the extended enterprise – customers, partners, employees, managers, and executives – anytime, anywhere.

Conclusion:

A DW (Data Warehouse) is a database:

- optimized for generating quick reports;

- where the data is not modified in real time;

- where the tables are denormalized (the information can be dupplicated in the same schema);

- which has a staging area with row information and an access area with prepared information (ready for reporting);

- generally bigger than 1 Tb of information;

- accessed by different tools for generating different reports;

- is used for strategic decision. 

Attention:

A data mart is a repository of data gathered from operational data and other sources that is designed to serve a particular community of knowledge workers. In scope, the data may derive from an enterprise-wide database or data warehouse or be more specialized. The emphasis of a data mart is on meeting the specific demands of a particular group of knowledge users in terms of analysis, content or presentation.

Data Warehouse cubes, dimensions

 What is a CUBE in data warehousing concept ?

Cubes are logical representation of multidimensional data. The name of "cube" is visually related to a 3 dimensional model, but we can have more than 3 dimensions.

Here is a 3 dimensional cube:

You can see that a cube has some dimensions. In this case we have 3 dimensions: product, location, time. In real life we can have more than 3 dimensions. In that case, inside a cube we can have another cubes.

A 3 dimensional cube is made of cubes. Each cube can be imagines as a sum of cubes.

Each cube store a value at the intersection of each dimension and that value is named measure. In that cube, 200, 100 are measures.

What is data cube technology used for ?

 This kind of representation allows one to view aggregated data from a number of perspectives. So, this is used for reporting.

Data warehouse concepts

Dimensional Data Model      Conceptual Model Design      Logical Model Design      Physical Model Design

Data Integrity    OLTP     OLAP     MOLAP     ROLAP     HOLAP      Measure

Cube      Dimension      Hierarchy      Fact table      Dimension tables       Star chema       Snowflake schema

Dimensional Data Model     

Dimensional modeling (Data Model) is a design technique that puts the data in a standard framework and provides easy access. You must create a database model in order to provide quick access and to get the information you need for reporting. Two kinds of schemas are used when designing data models, either a star schema or a snowflake schema. 

Conceptual Model Design     

A conceptual data model identifies the highest-level relationships between the different entities. Features of conceptual data model include:
   -  Includes the important entities and the relationships among them
   -  No attribute is specified
   -  No primary key is specified

Logical Model Design     

The Logical Model is a complete model and has all the information for building the database entities.

The Logical Model include:

   -  Includes all entities and relationships among them
   -  All attributes for each entity are specified
   -  The primary key for each entity is specified
   -  Foreign keys (keys identifying the relationship between different entities) are specified

In this model we don't have informations related to a specific database (Oracle, DB2, SQL Server, MySQL, etc. ). For instance, a VARCHAR2 column can have a generic name "String(30)".

Physical Model Design

The Physical Model Design is the Logical Model adapted to a specific database.

For instance, instead a generic "String(30)" we have  VARCHAR2(30).

Data Integrity   

Data integrity is a term used to refer to the accuracy and reliability of data.

OLTP   ( On Line Transaction Processing ) 

We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.

OLAP    ( On Line Analytical Processing )

We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.

MOLAP  ( Multidimensional OLAP )   

In MOLAP, data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats.

        Advantages:

              -  Excellent performance: MOLAP cubes are built for fast data retrieval

              -  Good data compression techniques

              -  Can perform complex calculations: All calculations have been pre-generated (results returned quickly) when the cube is created.

       Disadvantages:

              -  Limited in the amount of data a cube can handle

              -  A cube rebuilt could be very long

              -  Requires additional investment: to buy the proprietary format + investments in human resources

Examples of commercial products that use MOLAP are Cognos Powerplay, Oracle Database OLAP Option, Microsoft Analysis Services, Essbase, TM1, Lilith Hicare and Daptech Keystone. There is also an open source MOLAP server Palo.

ROLAP   ( Relational OLAP ) 

In ROLAP, data is stored in the relational database.

        Advantages:

              -  Can handle large amounts of data: limited to the database storage limit

              -  Can leverage functionalities inherent in the relational database

              -  Cost less than the MOLAP

       Disadvantages:

              -  Performance can be slow: the measures are not pre-generated

              -  Limited by SQL functionalities

HOLAP  ( Hybrid OLAP )

HOLAP technologies attempt to combine the advantages of MOLAP and ROLAP. For summary-type information, HOLAP leverages cube technology for faster performance.

Cube     

Cubes are logical representation of multidimensional data. The name of "cube" is visually related to a 3 dimensional model, but we can have more than 3 dimensions.

Here is a 3 dimensional cube:

You can see that a cube has some dimensions. In this case we have 3 dimensions: product, location, time. In real life we can have more than 3 dimensions. In that case, inside a cube we can have another cubes.

A 3 dimensional cube is made of cubes. Each cube can be imagines as a sum of cubes.

Dimension     

In the pink cube you can see, there are 3 dimensions:  product, location, time. A dimension is a structure that categorizes data in order to enable end users to answer business questions.

Measure

Each cube store a value at the intersection of each dimension and that value is named measure. In that cube, 200, 100 are measures.

Hierarchy

A hierarchy defines a set of parentage relationships between all or some of a dimension's members.

Fact table    

                          

The star shows how the data is stored. The middle is the central table (the fact table) containing the basic information and the points are dimension tables that show different views of the data. The central table has foreign keys to the dimension tables.

Dimension tables

The star shows how the data is stored. The middle is the central table (the fact table) containing the basic information and the points aredimension tables that show different views of the data. The central table has foreign keys to the dimension tables.

Star schema (= a form of dimensional model )

The star shows how the data is stored. The middle is the central table (the fact table) containing the basic information and the points are dimension tables that show different views of the data. The central table has foreign keys to the dimension tables. There are no foreign keys on the dimension tables.

Snowflake schema (= a form of dimensional model )

The snowflake shows how the data is stored. The middle is the central table (the fact table) containing the basic information and the points are dimension tables that show different views of the data. The central table has foreign keys to the dimension tables. There are also  foreign keyson the dimension tables. In snowflake schema, as opposed to its counter part star schema, relational keys are present inside dimensions also.