Data-Warehouse Designing

1. Conceptual Data Model - Design Step 1

This is First step for DWH Designing.

Conceptual Data Model is the first step in Data Warehouse design. In conceptual data model, very high level relationships between dimension and fact table is depicted. Conceptual data model not necessarily includes keys, attributes of tables. Conceptual data model gives a very high level idea of proposed Data Warehouse design including possible fact and dimension table. Conceptual data model is the stepping stone to design logical data model of Data Warehouse.

Characteristics of Data Warehouse Conceptual Data Model

It shows only high level relationship between tables.

It does not show primary key or column names

It is the stepping stone of Logical Data Model

2. Logical Data Model - Design Step -2

This is Second step for Designing

Good Logical data model in data warehouse implementation is very important. Logical data model has to be detailed (though some might not agree) as it represents the entire business in one shot and shows relationship between business entities. Logical Model should have following things to make it detailed and self explanatory.

All entities to be included in data warehouse

All possible attributes of each entity

Primary keys of each entity ( Natural Keys as well as Surrogate Keys )

Relationships between each every entity

Characteristics of Data Warehouse Logical Data Model

It has all the entities which will be used in data warehouse

It shows all possible attributes of all entities

It depicts the relationships between all entities

3. Physical Data Model - Design Step -3

This is third step for Designing for DWH Design

Physical Data Model is the actual model which will be created in the database to store the data. It is the most detailed data model in Data Warehouse data modeling. It includes

Tables names

All column names of the table along with data type and size

Primary keys, Foreign Keys of a table , Constraints

Physical Data Model can be converted to actual SQL DDL statement by using different tools. ERWIN is the famous tool to do this.

Comments

ODI KM Adding Order by Option

You can add Order by statement to queries by editing KM.I have edited IKM SQL Control Append to provide Order by. 1) Add an option to KM named USE_ORDER_BY, its type is Checkbox and default value is False. This option determines you want an order by statement at your query. 2)Add second option to KM named ORDER_BY, type is Text. You will get order by values to your query by this option. 3) Editing Insert New Rows detail of KM. Adding below three line code after having clause. That's it! <% if (odiRef.getOption("USE_ORDER_ BY").equals("1")) { %> ORDER BY <%=odiRef.getOption("ORDER_BY" )%> <%} %> If USE_ORDER_BY option is not used, empty value of ORDER_BY option get error. And executions of KM appears as such below; At this execution, I checked the KM to not get errors if ORDER_BY option value is null. There is no prove of ORDER BY I'm glad. Second execution to get Ord...

Synchronous and Asynchronous execution in ODI

In data warehouse designing, an important step is to deciding which step is before/after. Newly added packages and required DW data must be analyzed carefully. Synchronous addings can lengthen ETL duration. Interfaces, procedures without generated scenario cannot be executed in parallel. Only scenario executions can be parallel in ODI. Default scenario execution is synch in ODI. If you want to set a scenario to executed in parallel then you will write “-SYNC_MODE=2″ on command tab or select Synchronous / Asynchronous option Asynchronous in General tab. I have created a package as interfaces executes as; INT_JOBS parallel INT_REGIONS synch INT_REGIONS synch INT_COUNTRIES synch INT_LOCATIONS parallel INT_EMPLOYEES parallel (Interfaces are independent.) Selecting beginning and ending times and durations from repository tables as ODI 11g operator is not calculating these values. It is obvious in ODI 10g operator. SELECT sess_no...

Oracle Data Integrator tools: OdiFileDelete and OdiOutFile

Hello everyone! It’s time for another cool ODI tutorial. Last time, I spoke about the OdiZip tool and how it can be used to create zip files from a directory. Through this post, I will talk about two more tools related to Files namely OdiFileDelete and OdiOutFile . 1. OdiFileDelete The OdiFileDelete is a tool used to delete files present in a directory or a complete directory on the machine running the agent. Usage OdiFileDelete -DIR=<dir> | -FILE=<file> [-RECURSE=<yes|no>] [-CASESENS=<yes|no>] [-NOFILE_ERROR=<yes|no>] [-FROMDATE=<fromdate>] [-TODATE=<todate>] If -FROMDATE is omitted, all files with a modification date earlier than the -TODATE date will be deleted. If -TODATE is omitted, all files with a modification date later than the -FROMDATE date will be deleted. If both parameters are omitted, all files matching the -FILE...

TechWare

Search This Blog