From Database to MPP to Data Lake

Fexp : .ROUTE MESSAGES WITH ECHO TO FILE 'log_file' ;   .DATEFORM ANSIDATE;   .RUN FILE logon_file .LOGTABLE tabnam; .BEGIN EXPORT    TENACITY 4   SESSIONS 5   SLEEP 6;   .EXPORT OUTFILE 'outfile name and locations' MODE RECORD FORMAT TEXT; 'SQL Query .You can concatenate all fields and then cast them to particular length to avoid Junk Characters ' ) X ; .END EXPORT;      .LOGOFF; -- Fast load dateform ANSIDATE; errlimit 1000000; tenacity 4;        sessions 10; sleep 6; .LOGON ipaddress/logon,password; DROP TABLE .ET_tab1 ; DROP TABLE .UV_tab2; DELETE FROM STG_tab3; SET RECORD VARTEXT "|"; define  Field1 (VARCHAR(10))  Field2(VARCHAR(18))  Field3 (VARCHAR(20))  file=:CF.ImportFileName; show; begin loading STG_tab3 errorfiles ET_tab1, .UV_Tab2 checkpoint 25000000 ; insert into STG_tab3 ( Filed1, Field2, ...

clclbteq << eof > /odi47831/Scripts/Del_App_Work_Tables.txt.log .SET ECHOREQ ON .SET RETRY ON .SET TIMEMSG DEFAULT .SET ERRORLEVEL 3807 SEVERITY 0; .run file logon.txt .set width 200 .set titledashes off .set format off .set rtitle '' .export report file=del_app_work.run SEL 'DEL FROM ' ||Trim(Databasename)||'.'||Trim(Tablename)||';' (Title '') FROM DBC.TABLES WHERE CREATORNAME='xyz' AND TABLEKIND='T' ; .Export report file=del_app_work.sql .run file = del_app_work.run .logoff .quit

DROP MACRO MLOAD_EXPORT REPLACE MACRO MLOAD_EXPORT ( DB VARCHAR(100), TB VARCHAR(100), PATH VARCHAR(100), FS VARCHAR(100) ) AS ( SEL 1,CAST(1 AS BIGINT), CAST('.DATEFORM ANSIDATE; .LOGON server/username,password; .LOGTABLE USR_WORK.ML_UMS_' || :TB || '_1; DROP TABLE USR_WORK.WT_UMS_' || :TB || '_1; DROP TABLE USR_WORK.ET_UMS_' || :TB || '_1; DROP TABLE USR_WORK.UV_UMS_' || :TB || '_1; .ROUTE MESSAGES WITH ECHO TO FILE ''' || :PATH || '.ldrlog'' ; .BEGIN IMPORT MLOAD TABLES ' || :DB || '.' || :TB || ' WORKTABLES USR_WORK.WT_UMS_' || :TB || '_1 ERRORTABLES USR_WORK.ET_UMS_' || :TB || '_1 USR_WORK.UV_UMS_' || :TB || '_1 ERRLIMIT          0 CHECKPOINT     100000 TENACITY 10000 SESSIONS        10 SLEEP 6 ; .LAYOUT InputFileLayout; ' AS VARCHAR(6000)) FROM SYS_CALENDAR.CALENDAR WHERE CALENDAR_DATE=DATE UNION SEL 2,CAST(COLUMNID AS BIGINT), CAST('.FI...

REPLACE MACRO .BTEQ_EXPORT ( DATABASENAME VARCHAR(100), TABLENAME VARCHAR(100), FILENAME VARCHAR(500), SERVER VARCHAR(100) default '' , USERNAME VARCHAR(300) default '', PASS VARCHAR(15) default 'password' ) AS ( SEL '                      .SESSIONS 5  .SET ECHOREQ ON .SET FORMAT OFF .SET QUIET ON .SET WIDTH 1000 .SET RETLIMIT 1000000 .SET RETCANCEL ON .LOGON  '   || :SERVER || '/' || :USERNAME || ', ' || :PASS || '; '  ||     ' .EXPORT REPORT FILE =' || :FILENAME BTEQ_SCRIPT     FROM SYS_CALENDAR.CALENDAR WHERE DATE=CALENDAR_DATE       UNION   SEL '                   SEL TOP 1000000            '   FROM SYS_CALENDAR.CALENDA...

Background on Abinitio : Abinitio is considered one of the major ETL players  in line with informatica ,datastage ,SSIS ( if it can be considered major ETL player ;-) ) .In fact, in most respects it is considered better than all of the above . It has user interface and debugging simpler than Informatica  ( one of the positive points about informatica ) ,It has parallelism which data stage may still take years to reach . Superiority : (HearSay ) It has the ability to convert a 6 people ,6 month ETL project to 2 month 4 people ETL project . (Sounds hyperbole ,But yet it has been established in many cases that it takes half the development time compared to INFA with much better quality of code ) . Other statistics suggest that if you call Abinitio ,there is 75% chance you will be talking to a PhD holder.In other words it is considered to be ingenously simple. Secrecy : In spite of all this there is very little you would know about it (If at all ) . ...

Data warehouse for any enterprise is always in an evolving state. We seem to be now maturing from report only to analytical phase and therefore one step closer to our dream of real time data warehouse. Every change in phase brings along with it new sets of challenges and therefore, often, new sets of tools and technologies. As we move from reporting to analytical phase, BI giants such as Cognos, MSTR, BO, OBIEE, Hyperion seem to be getting more and more obsolete .This is where we welcome data visualization tools like QlikView, Corda, Tableau, Panoptican, Spot Fire and Tibco. Advantages of these tools are very much in your face.  They have very less build up time i.e. to say you can complete your first report within two days of installation (Including the learning phase). This is preferable as against traditional reporting tools, where you would spend considerable time building metadata layer.(MSTR claims you can bring down a 30-day activity to 1 hour).  They are visual...

       Skewed Tables in Teradata                                                                  Teradata distributes its data for a given table based on its Primary Index and Primary Index alone .If this Primary Index is not selected appropriately it can cause performance bottle necks. Following are general parameters we should consider while creating Primary Indexes       1:Access Path       2:Volatility       3: Data Distribution        Volatility is usually not an issue in a well designed database .That is to say we do not expect...