Home » Other » General » Design Approach (ORACLE 11G)
Design Approach [message #652561] Mon, 13 June 2016 22:30 Go to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
Hi,

I have a use case for which i got 2 way of approach for development. Can i have suggestion on both approach.

My Requirement and  approach 1


    Country wise source data will be received for every month ( Here i want to identify each source with source id )
    Using EXTERNAL TABLE methodology to populate data into staging tables.
    Populating data from staging tables to Main table ( Here all staging table's data will be populated into 1 main table) with the
    identification of source id and month id.
    I need to populate data with some transformations like
    Source data will have party number to derive party key i have virtual column methodology , its a additional column in staging table
    and column definition would be
        PARTY_KEY VARCHAR2(150) GENERATED ALWAYS AS ('764'||CUSTOMER) VIRTUAL VISIBLE
        Here CUSTOMER column is party number 
       

    Some transformations will need look up tables- For this i have created view with all staging table's columns with look up joins.
    The idea of view i had here to load main table with this view instead of staging table.

    For example

External Table

  CREATE TABLE "SS_REPO"."TH_FREQ_INV_PROF_CHNG_XT" 
   (	"BRANCH" VARCHAR2(100 BYTE), 
	"RC" VARCHAR2(100 BYTE), 
	"AO" VARCHAR2(100 BYTE), 
	"AO_NAME" VARCHAR2(100 BYTE), 
	"CUSTOMER" VARCHAR2(100 BYTE), 
	"CUSTOMER_NAME" VARCHAR2(100 BYTE), 
	"RELATIONSHIP" VARCHAR2(100 BYTE), 
	"RELATIONSHIP_TITLE" VARCHAR2(100 BYTE), 
	"RISK_PROFILE" VARCHAR2(100 BYTE), 
	"LATEST_RISK_PROFILE" VARCHAR2(100 BYTE), 
	"LATEST_RISK_PROFILE_DATE" DATE, 
	"PREVIOUS_RISK_PROFILE" VARCHAR2(100 BYTE), 
	"PREVIOUS_RISK_PROFILE_DATE" DATE, 
	"MONTH_ID" VARCHAR2(100 BYTE), 
	"ROW_NUM" VARCHAR2(100 BYTE)
   ) 
   ORGANIZATION EXTERNAL 
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "ETL_WORKING"
      ACCESS PARAMETERS
      ( RECORDS DELIMITED BY '#$/' 
    LOGFILE ETL_ORA_LOG:'th_rdsinv8_freq_inv_prf_chg.log' 
    BADFILE ETL_ORA_LOG:'th_rdsinv8_freq_inv_prf_chg.bad' 
    DISCARDFILE ETL_ORA_LOG:'th_rdsinv8_freq_inv_prf_chg.disc' 
    DATE_CACHE 0 FIELDS TERMINATED BY '~' LRTRIM 
    MISSING FIELD VALUES ARE NULL ( 
BRANCH	char(100),
RC	char(100),
AO	char(100),
AO_NAME	char(100),
CUSTOMER	char(100),
CUSTOMER_NAME	char(100),
RELATIONSHIP	char(100),
RELATIONSHIP_TITLE	char(100),
RISK_PROFILE	char(100),
LATEST_RISK_PROFILE	char(100),
LATEST_RISK_PROFILE_DATE	char(20) date_format date  mask 'mmddyyyy hh24:mi:ss',
PREVIOUS_RISK_PROFILE	char(100),
PREVIOUS_RISK_PROFILE_DATE	char(20) date_format date  mask 'mmddyyyy hh24:mi:ss',
MONTH_ID	char(100),
ROW_NUM	char(12)
)                )
      LOCATION
       ( "ETL_WORKING":'th_rdsinv8_freq_inv_prf_chg.ssf'
       )
    )
   REJECT LIMIT UNLIMITED ;

Staging table with virtual column

  CREATE TABLE "SS_REPO"."TH_FREQ_INV_PROF_CHNG_STG" 
   (	"MONTH_ID" VARCHAR2(50 BYTE), 
	"BRANCH" VARCHAR2(100 BYTE), 
	"RC" VARCHAR2(100 BYTE), 
	"AO" VARCHAR2(100 BYTE), 
	"AO_NAME" VARCHAR2(100 BYTE), 
	"CUSTOMER" VARCHAR2(100 BYTE), 
	"CUSTOMER_NAME" VARCHAR2(100 BYTE), 
	"RELATIONSHIP" VARCHAR2(100 BYTE), 
	"RELATIONSHIP_TITLE" VARCHAR2(100 BYTE), 
	"RISK_PROFILE" VARCHAR2(100 BYTE), 
	"LATEST_RISK_PROFILE" VARCHAR2(100 BYTE), 
	"LATEST_RISK_PROFILE_DATE" DATE, 
	"PREVIOUS_RISK_PROFILE" VARCHAR2(100 BYTE), 
	"PREVIOUS_RISK_PROFILE_DATE" DATE, 
	"ROW_NUM" VARCHAR2(100 BYTE), 
	"UPDATED_DATE" DATE, 
	"PARTY_KEY" VARCHAR2(150 BYTE) GENERATED ALWAYS AS ('764'||"CUSTOMER") VIRTUAL VISIBLE , 
	"NEW_RISK_PROFILE" VARCHAR2(500 BYTE) GENERATED ALWAYS AS ( REGEXP_REPLACE ("LATEST_RISK_PROFILE",'[^0-9]')) VIRTUAL VISIBLE , 
	"LAST_RISK_PROFILE" VARCHAR2(500 BYTE) GENERATED ALWAYS AS ( REGEXP_REPLACE ("PREVIOUS_RISK_PROFILE",'[^0-9]')) VIRTUAL VISIBLE , 
	"RELATIONSHIP_KEY" VARCHAR2(150 BYTE) GENERATED ALWAYS AS ('764'||"RELATIONSHIP") VIRTUAL VISIBLE , 
	"ERROR_FLG" CHAR(1 BYTE), 
	"SOURCE_ID" VARCHAR2(50 BYTE) GENERATED ALWAYS AS ('TH_RDSINV8') VIRTUAL VISIBLE , 
	"EMPLOYEE_KEY" VARCHAR2(100 BYTE), 
	"BRANCH_KEY" VARCHAR2(100 BYTE), 
	"ACCOUNT_KEY" VARCHAR2(100 BYTE)
   ) ;

My view 

  CREATE OR REPLACE FORCE VIEW "SS_REPO"."TH_FREQ_INV_PROF_CHNG_VW" ("AO", "AO_NAME", "BRANCH", "CUSTOMER", "CUSTOMER_NAME", "LATEST_RISK_PROFILE", "LATEST_RISK_PROFILE_DATE", "MONTH_ID", "PREVIOUS_RISK_PROFILE", "PREVIOUS_RISK_PROFILE_DATE", "RC", "RELATIONSHIP", "RELATIONSHIP_TITLE", "RISK_PROFILE", "ROW_NUM", [b]"EMPLOYEE_KEY", "BRANCH_KEY")[/b] AS 
  WITH ext_data AS
  (SELECT a.AO,
    a.AO_NAME,
    a.BRANCH,
    a.CUSTOMER,
    a.CUSTOMER_NAME,
    a.LATEST_RISK_PROFILE,
    a.LATEST_RISK_PROFILE_DATE,
    a.MONTH_ID,
    a.PREVIOUS_RISK_PROFILE,
    a.PREVIOUS_RISK_PROFILE_DATE,
    a.RC,
    a.RELATIONSHIP,
    a.RELATIONSHIP_TITLE,
    a.RISK_PROFILE,
    a.ROW_NUM,
  [b]  b.employee_key,
    d.branch_key  [/b]
  FROM ss_repo.TH_FREQ_INV_PROF_CHNG_XT a
LEFT JOIN ss_repo.party_info c
ON (('764'||a.CUSTOMER) = c.party_key)
LEFT JOIN ss_repo.rm_ao_geid b
ON ( b.rc_ao_code = c.rm)
LEFT JOIN ss_repo.employee d
ON (b.employee_key =d.employee_key)
  )
SELECT AO,
  AO_NAME,
  BRANCH,
  CUSTOMER,
  CUSTOMER_NAME,
  LATEST_RISK_PROFILE,
  LATEST_RISK_PROFILE_DATE,
  MONTH_ID,
  PREVIOUS_RISK_PROFILE,
  PREVIOUS_RISK_PROFILE_DATE,
  RC,
  RELATIONSHIP,
  RELATIONSHIP_TITLE,
  RISK_PROFILE,
  ROW_NUM,
  employee_key,
  branch_key
FROM ext_data;

Approach 2


    Without virtual column and view concept, we first load all data into staging and main table.
    Using MERGE or UPDATE I will populate KEY columns in to Main tables.


Re: Design Approach [message #652565 is a reply to message #652561] Tue, 14 June 2016 01:53 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I would think that the optimal approach would be whichever is the simplest. You describe your number 2 in just two lines; your number 1 seems very complicated.
Re: Design Approach [message #652567 is a reply to message #652565] Tue, 14 June 2016 03:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not really sure why you've even got a staging table since you have an external table. Can't you do the whole thing with a merge from the external table to the main table?
Re: Design Approach [message #652573 is a reply to message #652567] Tue, 14 June 2016 03:40 Go to previous messageGo to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
The purpose of staging table will hold all data as such comes from files in every month. when i transform data to main table is with transformations.

[Updated on: Tue, 14 June 2016 03:40]

Report message to a moderator

Re: Design Approach [message #652574 is a reply to message #652565] Tue, 14 June 2016 03:42 Go to previous messageGo to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
Number 1 will have all the sample DDL and DML
Re: Design Approach [message #652581 is a reply to message #652574] Tue, 14 June 2016 05:26 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Still doesn't explain why you need the staging table
Re: Design Approach [message #652586 is a reply to message #652581] Tue, 14 June 2016 05:50 Go to previous messageGo to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
Source files will be placed in same path every month with same naming convention.

My aim is to have all months data into staging table for reference in future.
Re: Design Approach [message #652591 is a reply to message #652586] Tue, 14 June 2016 07:00 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
In that case, insert/select from external table to staging table followed by merge to main table.
Re: Design Approach [message #652623 is a reply to message #652591] Wed, 15 June 2016 02:35 Go to previous messageGo to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
Yes , Its insert into staging table from external table .
Also insert into main table.

But i need suggestion on staging tables having additional columns as transformations ( as VIRTUAL COLUMNS)?

[Updated on: Wed, 15 June 2016 02:37]

Report message to a moderator

Re: Design Approach [message #652624 is a reply to message #652623] Wed, 15 June 2016 02:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Up to you really, you don't actually need them but having them means you don't need to code the transformations when querying it. I would question the point of the source_id since you appear to have hard-coded it to a single value.
Re: Design Approach [message #652625 is a reply to message #652624] Wed, 15 June 2016 03:13 Go to previous messageGo to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
Thanks cookiemonster, The reason i have hard-coded source id is it will be constant value forever.
Re: Design Approach [message #652629 is a reply to message #652625] Wed, 15 June 2016 03:55 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
And if it's constant for ever then it doesn't really need to exist.
Re: Design Approach [message #652739 is a reply to message #652629] Fri, 17 June 2016 04:57 Go to previous messageGo to next message
ORAGENASHOK
Messages: 240
Registered: June 2006
Location: Chennai
Senior Member
It is needed because every month different sources data will be populated to single main table. And my rule logic will differ from each source for this i need to identify from which source the transaction if from.

Re: Design Approach [message #652742 is a reply to message #652739] Fri, 17 June 2016 09:40 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If it's hard-coded then it never changes and won't identify anything.
If it is supposed to have different values for different things then obviously it shouldn't be hard-coded.
Previous Topic: How to read data from SQLite database?
Next Topic: date format/timestamp with milliseconds
Goto Forum:
  


Current Time: Fri Mar 29 05:24:51 CDT 2024