Project Configaration

                 

Database design :

Temparary Database(oracle)

username : scott

password : tiger

(storing the metadata for src files)

source Database(oracle)

username : source_stage

password : source

Target Database(oracle) -DWH

username : dim_target

password : target

Procedure  for creating and grant the previliges to the users(Schemas)

sql >create user source_stage identified by source;

sql >create user dim_target identified by target;

sql >grant dba to source_stage;

sql >grant dba to dim_target;

1. procedure for creating source file  structure in scott (schema)

   connect to the scott schema (username = scott and password = tiger)

   copy and paste the source table structures in the scott schema.

    the source table structure is available on the below document file

   Rafi\CDM_Project\FILE_Source_Data\Table_Structure.doc

2. procedure for creating source file  structure in source_stage (schema)

  

    connect to the source_stage schema (username = source_stage and password = source)

    copy and paste the stage table structures in the source_stage schema.

    the stage table structure is available on the below document file

   Rafi\CDM_Project\FILE_Source_Data\Table_Structure.doc

3. procedure for converting source file structure (oracle) to flatfile

   

      import all tables from scott schema (source analyser - in Informatica)

     change all the tables, database type as oracle to database type as flatfiles.

4. create a Date table in the source_stage  (schema)

    Create Table T_DATE

     (

        FULL_DATE Date

      );

.

  procedure for loading the dates into T_DATE table(source_stage schema)

    CREATE  OR  REPLACE procedure load_date

    as

    start_date date := to_date('01-01-2007','dd-mm-yyyy');

    end_date date := to_date('31-12-2012','dd-mm-yyyy');

    begin

    for i in 1..2500 loop

    insert into t_date values(start_date);

    start_date:= start_date + 1;

    end loop;

    end;

5. Start maping for passing the soruce file data (scott schema)  into  stage tables (source_stage schema)

   

     as follows

              scott                                      stage

     account_src  (flat file)                       t_account (oracle)

     product_src  (flat file)                       t_product (oracle)

     market_src  (flat file)                        t_market (oracle)

     employee_src  (flat file)                     t_employee(oracle)

     client_order (flat file)                        client_order (oracle)

     client_allocation (flat file)                  client_allocation (oracle)

     client_execution (flat file)                  client_execution (oracle)

                                                                 t_date(oracle)

6. procedure for creating Dimension table structure in dim_target (schema) - DWH

   connect to the dim_target schema (username = dim_target and password = target)

   copy and paste the Dimension table structures in the dim_target schema.

    the Dimension table structure is available on the below document file

    Rafi\CDM_Project\FILE_Source_Data\Dimension Table Structure.doc

7. Start maping for passing the stage table data (source_stage)  into Dimension tables (dim_target schema)

     as follows

                 stage                              target (DWH)

    a)  t_account (oracle)                        dm_t_account_dim (oracle)

                                                              dm_t_account_dim_except (oracle)

    b)  t_product (oracle)                        dm_t_product_dim (oracle)

                                                               dm_t_product_dim_excep (oracle)

    c)  t_market (oracle)                         dm_ t_market_dim (oracle)

                                                              dm_ t_market_dim_excep (oracle)

    d)  t_employee(oracle)                       dm_t_employee_dim (oracle)

                                                              dm_t_employee_dim (oracle)

a and d  are SCD Type - 2

b and c are SCD  Type - 1   

    

8. FACT table loading (In progress)