Logic for extract generation

dr46014 · Post by **dr46014** » Mon Jan 29, 2007 9:49 am

I have a table name cust_eqx_acct_dim.I am maintaining a type 2 SCD in the table.The columns in the table are :
CUST_EQX_ACCT_DIM_KEY
EQUIFAX_CUST_ID
CURRENT_ACCOUNT_NBR
EFFECTIVE_END_DATE
LOAD_DATE
CLIENT_ID

When a new record with a new EQUIFAX_CUST_ID is inserted the EFFECTIVE_END_DATE field is set to NULL.If there is some change in existing EQUIFAX_CUST_ID then EFFECTIVE_END_DATE is made the system date for the previous record and the new record gets inserted with EFFECTIVE_END_DATE as NULL.This is the logic i am using for implementing type 2 dimension.I have developed the job for loading cust_eqx_acct_dim.So now my requirement is generating both onetime and weekly extracts for the newly entered customer.Extract is nothing but a flat file containing only two fields EQUIFAX_CUST_ID and CURRENT_ACCOUNT_NBR.Its relatively easier to generate onetime extract selecting those two fields where EFFECTIVE_END_DATE is NULL.But for weekly extracts i mean to say both for inserted nad updated record i need the extracts in every week.I need both records which are new to the Table and which are existing but some field gets change i.e updated recoreds.

Can u please suggest some logic for implementing both the onetime and weekly extract.Suggest me atleast 2-3 methods for achieving it.

kris · Post by **kris** » Mon Jan 29, 2007 11:12 am

dr46014 wrote: Can u please suggest some logic for implementing both the onetime and weekly extract.Suggest me atleast 2-3 methods for achieving it.

There is no field that would give you week's worth of data as you are not storing any record of when the actual record is being updated.

Easiest way would be to have a date field 'UPDATE_DATE' in the table and you populate it with sysdate whenever you update a record.

That way you can extract using where EFFECTIVE_END_DATE is NULL and UPDATE_DATE>=(SYSDATE-7)

Hope this helps!

Kris~

DSguru2B · Post by **DSguru2B** » Mon Jan 29, 2007 11:15 am

I would write a trigger that would fire upon every insert and it would go if pk is present, update the EFFECTIVE_END_DATE .

dr46014 · Post by **dr46014** » Mon Jan 29, 2007 11:23 am

DSguru2B wrote:I would write a trigger that would fire upon every insert and it would go if pk is present, update the EFFECTIVE_END_DATE .

can u please explain the concept in detail.that trigger concept..
if i add a new column to the table like update_date..wat it exactly does.will it be populated for aal the records or only updated records.if it is the case i will not be able to get the latest records

DSguru2B · Post by **DSguru2B** » Mon Jan 29, 2007 12:43 pm

Its a plain and simple concept. It will be trigger for insert that will scan the table for incoming pk. If the pk is found and the EFFECTIVE_END_DATE is NULL then it will update the record and put current timestamp for EFFECTIVE_END_DATE and insert the new record with NULL.
If you are not familiar with triggers I suggest you stick with doing it in Datastage. You asked for 2 or 3 methods of getting this done, hence i suggested a different approach.

dr46014 · Post by **dr46014** » Wed Jan 31, 2007 10:32 am

Hi All
Sorry for the late response from my side.I have a little chang in my requirement.I m having a Staging table with the following columns
EQUIFAX_CUST_ID
CURRENT_ACCOUNT_NBR
CLIENT_ID
This staging table is only to load weekly data and the main table is for maintaining type 2 dimension with the following columns
CUST_EQX_ACCT_DIM_KEY
EQUIFAX_CUST_ID
CURRENT_ACCOUNT_NBR
EFFECTIVE_END_DATE
LOAD_DATE
CLIENT_ID
I have the jobs to load the dimension table and staging table with proper logic.But my requirement is i need to keep the records in two flat files for newly inserted record and updated records.
Tell me some approach.I have to use the tables as source and flat files as target.So that i can easily know which records are inserted and which are updated..
Please share some logic with use of stages....It would be better if any one provide me the layout of the job
Thank you all

ray.wurlod · Post by **ray.wurlod** » Wed Jan 31, 2007 3:02 pm

Code: Select all

              HashedFile    HashedFile
                 |              ^
                 |              |
                 V              |
new data ---->  Transformer Stage  ---->  DimTable (inserts)
                                   ---->  FlatFile (inserts)
                                   ---->  DimTable (updates)
                                   ---->  FlatFile (updates)

The Hashed File is pre-loaded with an image of the dimension table, but keyed on the natural key rather than the surrogate key. The Transformer stage looks after maintaining that, determining existence, determining change, and issuing inserts and updates. This logic could be split over multiple stages for ease of maintenance.

Inserts are both new records and new versions of existing records.

Updates are both changed records and expired versions of existing records.

dr46014 · Post by **dr46014** » Wed Jan 31, 2007 9:35 pm

could not get u

ray.wurlod · Post by **ray.wurlod** » Wed Jan 31, 2007 10:53 pm

For just a few cents per day you can read premium posts, and help to pay for DSXchange's bandwidth.

Or is it that you're searching for the mysterious "u" cited often on DSXchange but whose identity remains to be definitively established?

Logic for extract generation

Logic for extract generation

Re: Logic for extract generation