CDM 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)
No comments:
Post a Comment