I'm working on a full ETL and BI effort. Eventually I will create a report of this data, but I need to normalize it first to get it into my Data Warehouse. I'm trying to figure out the best way to model and normalize. I'm receiving 6 csv files daily that is spitting out data from a database. The data is related to auditing POs and the AUDIT fields are not system fields. AUDIT\_ACTION will either be after insert, before update, or after update. For each update there are two records captured, one showing the state before the change and then the post change record values. Audit\_ID is just sequential primary key 1234.

Here are the files and their fields

PO Header

AUDIT\_ID, PO\_ID, PO\_RLSE\_NO, PO\_CHNG\_ORD\_NO, S\_PO\_TYPE, BUYER\_ID, VEND\_ID, ADDR\_DC, S\_PO\_STATUS\_TYPE, PO\_TOT\_AMT, SALES\_TAX\_AMT, BLKT\_AMT, TOT\_RLSED\_AMT, CNTACT\_LAST\_NAME, CNTACT\_FIRST\_NAME, ORD\_DT, FOB\_FLD, RETAIN\_PO\_FL, PO\_PRNTD\_FL, CNFRM\_FL, ACKN\_FL, ACKN\_DT, VEND\_SO\_ID, TERMS\_DC, PERF\_START\_DT, PERF\_END\_DT, RLSE\_FROM\_BLKT\_FL, RLSE\_LN\_TOT\_FL, GROSS\_UNIT\_CST\_FL, EXCD\_BLKT\_TOT\_FL, BRNCH\_LOC\_ID, BRNCH\_ADDR\_DC, BILL\_TO\_LOC\_ID, BILL\_TO\_ADDR\_DC, CHNG\_DT, APPRVL\_DT, APPRVL\_USER\_ID, PROCURE\_TYPE\_CD, PHONE\_ID, FAX\_ID, MODIFIED\_BY, TIME\_STAMP, COMPANY\_ID, LOCK\_FL, VCHRD\_AMT, LAST\_CHNG\_DTT, LAST\_MOD\_DTT, GSA\_FL, COST\_SAVING\_AMT, TRN\_BLKT\_AMT, TRN\_CST\_SAVING\_AMT, TRN\_PO\_TOT\_AMT, TRN\_SALES\_TAX\_AMT, TRN\_TOT\_RLSED\_AMT, TRN\_VCHRD\_AMT, RATE\_GRP\_ID, TRN\_CRNCY\_CD, TRN\_TO\_EUR\_RT, EUR\_TO\_FUNC\_RT, TRN\_CRNCY\_DT, TRN\_FREEZE\_RT\_FL, TRN\_TO\_EUR\_RT\_FL, CL\_DISADV\_FL, CL\_HIST\_BL\_CLG\_FL, CL\_LAB\_SRPL\_FL, CL\_WOM\_OWN\_FL, CIS\_CD, ROWVERSION, REL\_TO\_VEND\_FL, REL\_TO\_VEND\_DT, US\_CITIZEN\_FL, ITAR\_AUTH\_FL, SEC\_CLR\_CD, AGENCY\_NAME, PO\_TYPE\_CHNG\_DTT, BUY\_ORG\_ID, PORTAL\_EMAIL\_FL, CNTACT\_EMAIL\_ID, SUBCNTR\_ID, NAICS\_CD, AUDIT\_ACTION, AUDIT\_ACTION\_BY, AUDIT\_ACTION\_DATE,

PO Header Notes

AUDIT\_ID, PO\_ID, PO\_RLSE\_NO, PO\_HDR\_TX, MODIFIED\_BY, TIME\_STAMP, ROWVERSION, AUDIT\_ACTION, AUDIT\_ACTION\_BY, AUDIT\_ACTION\_DATE

PO Header Default

AUDIT\_ID, PO\_ID, PO\_RLSE\_NO, PROJ\_ID, ORG\_ID, ACCT\_ID, PROJ\_ABBRV\_CD, ORG\_ABBRV\_CD, INVT\_ABBRV\_CD, REF\_STRUC\_1\_ID, REF\_STRUC\_2\_ID, SHIP\_ID, SHIP\_VIA\_FLD, DROP\_SHIP\_FL, S\_PO\_TAXABLE\_TYPE, VOL\_DISC\_PCT\_RT, RQ\_ID, ORDER\_REF\_ID, DEL\_TO\_FLD, DESIRED\_DT, DUE\_DT, AUTO\_VCHR\_FL, MODIFIED\_BY, TIME\_STAMP, PROJ\_ACCT\_ABBRV\_CD, ROWVERSION, AUDIT\_ACTION, AUDIT\_ACTION\_BY, AUDIT\_ACTION\_DATE

PO Line Item

AUDIT\_ID, PO\_ID, PO\_RLSE\_NO, PO\_LN\_KEY, PO\_LN\_NO, S\_PO\_LN\_TYPE, ITEM\_KEY, PO\_LN\_DESC, ORD\_QTY, RECVD\_QTY, ACCPTD\_QTY, VCHRD\_QTY, PSTD\_QTY, ORD\_DT, DUE\_DT, DESIRED\_DT, DISC\_PCT\_RT, RECPT\_TOL\_PCT\_RT, GROSS\_UNIT\_CST\_AMT, NET\_UNIT\_CST\_AMT, PO\_LN\_EXT\_AMT, PO\_LN\_TOT\_AMT, TOT\_RLSED\_AMT, BLKT\_BAL\_AMT, RECVD\_AMT, ACCPTD\_AMT, VCHRD\_AMT, PSTD\_AMT, SALES\_TAX\_AMT, SHIP\_ID, S\_LN\_STATUS\_TYPE, S\_PO\_COMMIT\_TYPE, TAXABLE\_FL, DEL\_TO\_FLD, SHIP\_VIA\_FLD, PO\_LN\_UM\_CD, MISC\_LN\_CHG\_TYPE, RQ\_ID, MANUF\_PART\_ID, MANUF\_PART\_RVSN\_ID, VEND\_PART\_ID, VEND\_PART\_RVSN\_ID, CERT\_OF\_CNFRM\_FL, QC\_REQD\_FL, SRCE\_INSP\_FL, OVRSHP\_ALLOW\_FL, BLKT\_PO\_RLSE\_NO, BLKT\_PO\_LN\_KEY, ITEM\_ID, ITEM\_RVSN\_ID, DROP\_SHIP\_FL, ORDER\_REF\_ID, ORIG\_DUE\_DT, USER\_RVSN\_FLD, INVT\_ABBRV\_CD, VCHR\_RCV\_FL, S\_MATCH\_OPT\_CD, PO\_LN\_CHG\_AMT, PO\_LN\_CHG\_TAX\_AMT, REJ\_REP\_QTY, REJ\_PAY\_QTY, REJ\_CR\_QTY, REJ\_REP\_AMT, REJ\_PAY\_AMT, REJ\_CR\_AMT, APPRVL\_DT, APPRVL\_USER\_ID, ORDER\_REF\_LN\_KEY, S\_ORD\_REF\_TYPE\_CD, WHSE\_ID, PO\_LN\_CLOSE\_DT, TOT\_RLSED\_QTY, S\_MATCH\_CD, COMM\_CD, MIN\_QTY, LT\_DAYS\_NO, S\_PURCH\_AGRMT\_CD, ISSUE\_FL, PROCURE\_TYPE\_CD, AUTO\_VCHR\_FL, SUBLINE\_NO, RQ\_LN\_KEY, AWARD\_QT\_ID, MODIFIED\_BY, TIME\_STAMP, SALES\_TAX\_RT, MIL\_SPEC\_ID, NSN\_ID, ORDER\_REF\_RLSE\_NO, CLIN\_ID, PERF\_START\_DT, PERF\_END\_DT, BILL\_CYCLE\_CD, BILL\_CYCLE\_PAY\_AMT, ORDER\_REF\_LN\_NO, LAST\_VCHRD\_DT, PLANNER\_ID, OP\_ID, S\_OE\_CST\_TYPE\_CD, TRN\_ACCPTD\_AMT, TRN\_BILL\_CYCLE\_AMT, TRN\_BLKT\_BAL\_AMT, TRN\_GR\_UN\_CST\_AMT, TRN\_NET\_UN\_CST\_AMT, TRN\_LN\_CHG\_AMT, TRN\_LN\_CHG\_TAX\_AMT, TRN\_PO\_LN\_EXT\_AMT, TRN\_PO\_LN\_TOT\_AMT, TRN\_PSTD\_AMT, TRN\_RECVD\_AMT, TRN\_REJ\_CR\_AMT, TRN\_REJ\_PAY\_AMT, TRN\_REJ\_REP\_AMT, TRN\_SALES\_TAX\_AMT, TRN\_TOT\_RLSED\_AMT, TRN\_VCHRD\_AMT, TRN\_RECOVERY\_AMT, RECOVERY\_AMT, RECOVERY\_RT, BOM\_CONFIG\_ID, MANUF\_ID, PO\_LN\_INT\_NOTES, PO\_LN\_EXPDT\_NOTES, IND\_CLASS\_CD, S\_CL\_SM\_BUS\_CD, SO\_LN\_COMP\_KEY, COMPLT\_AMT, COMPLT\_PCT, STORED\_AMT, COMPLT\_RET\_PCT, STORED\_RET\_PCT, COMPLT\_RET\_AMT, STORED\_RET\_AMT, RQSTD\_AMT, TRN\_COMPLT\_AMT, TRN\_STORED\_AMT, TRN\_COMPLT\_RET\_AMT, TRN\_STORED\_RET\_AMT, TRN\_RQSTD\_AMT, CIS\_WH\_FL, ROWVERSION, LOT\_REQD\_FL, SERIAL\_REQD\_FL, US\_CITIZEN\_FL, ITAR\_AUTH\_FL, SEC\_CLR\_CD, AGENCY\_NAME, SUB\_KEY, RQ\_PO\_KEY, CL\_WOM\_OWN\_FL, CL\_LAB\_SRPL\_FL, CL\_VET\_FL, CL\_SD\_VET\_FL, CL\_DISADV\_FL, CL\_HIST\_BL\_CLG\_FL, CL\_ANC\_IT\_FL, S\_PORTAL\_ACTION\_CD, PROP\_ORD\_QTY, TRN\_PROP\_EXT\_AMT, PROP\_DUE\_DT, CL\_8A\_FL, CL\_ABIL\_ONE\_FL, NAICS\_CD, AUDIT\_ACTION, AUDIT\_ACTION\_BY, AUDIT\_ACTION\_DATE

RQ Header

AUDIT\_ID, RQ\_ID, RVSN\_NO, RQ\_DT, RQST\_EMPL\_ID, RQST\_ORG\_ID, RQST\_PHONE\_ID, RQST\_PHONE\_EXT\_CD, LAST\_CHNG\_DT, S\_RQ\_STATUS\_CD, PRINTED\_FL, RQ\_APPR\_PROC\_CD, PROCURE\_TYPE\_CD, ENTR\_USER\_ID, ENTR\_DTT, COMBINE\_RQ\_FL, BUYER\_ASSIGN\_DT, TGT\_PLACE\_DT, NEXT\_APPRVL\_SEQ\_NO, RQ\_PROC\_FL, S\_REAPPRVL\_CD, MODIFIED\_BY, TIME\_STAMP, COMPANY\_ID, BUYER\_ID, TOTAL\_EST\_REQ\_AMT, EST\_SALES\_TAX\_AMT, AUTO\_GEN\_FL, GSA\_FL, OVR\_BUD\_FL, PROJ\_ID, PROJ\_ABBRV\_CD, ROWVERSION, EUR\_TO\_FUNC\_RT, RATE\_GRP\_ID, TRN\_CRNCY\_CD, TRN\_CRNCY\_DT, TRN\_FREEZE\_RT\_FL, TRN\_TO\_EUR\_RT, TRN\_TO\_EUR\_RT\_FL, TRN\_TOTAL\_REQ\_AMT, TRN\_SALES\_TAX\_AMT, RQ\_EXPORT\_CD, RQ\_EXPORT\_DTT, RQ\_TYPE\_ID, RQ\_NOTES, SUBC\_AGR\_FL, US\_CITIZEN\_FL, ITAR\_AUTH\_FL, SEC\_CLR\_CD, AGENCY\_NAME, AUDIT\_ACTION, AUDIT\_ACTION\_BY, AUDIT\_ACTION\_DATE

RQ Header Approval

AUDIT\_ID, RQ\_ID, RVSN\_NO, RQ\_APPR\_TITLE\_DC, NEXT\_SEQ\_FL, APPRVL\_SEQ\_NO, APPRVL\_EMPL\_ID, APPRVL\_USER\_ID, APPRVL\_DTT, S\_RQ\_APPRVL\_CD, APPRVL\_NOTES, RSN\_CD, S\_RSN\_WH\_USED\_CD, MODIFIED\_BY, TIME\_STAMP, CHK\_PROJ\_FL, QC\_APPRVL\_FL, S\_RQ\_APPR\_TYPE, CHK\_ORG\_FL, PAO\_APPRVL\_FL, ROWVERSION, COMPANY\_ID, AUDIT\_ACTION, AUDIT\_ACTION\_BY, AUDIT\_ACTION\_DATE

I was thinking doing something like the following for each file

PO\_AUDITS

audit\_id, po\_change\_id

PO\_CHANGES

po\_change\_id, po\_id, all\_other\_columns

and will uses SSIS to insert data accordingly

Lastly I will need to eventually create a BI report that answers these questions: Who created a PO and is it within authority?

What is the Change Order history?

What is the PO Value change history?

What is the Order Date history?

What is the NET terms history?

What is the Buyer assignment history?

What is the Order Reference field history?

What is the Header Notes history?