HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
 Class:   Search:   for:      |<  <  > 
History in data
    by Ludek Bob Jankovsky, 02-Sep-2008 (DATA ARCHITECTURE)
Most of our data is changing in time. Sometimes we need project that changes into our data. There are several ways how to realize that.  ...more
CREEPING_DEATH algorithm ETL Transformation pattern
    by Ludek Bob Jankovsky, 02-Sep-2008 (ETL PATTERNS)
One of special patterns related to cool specific way of maintaining Slowly changing dimensions of type 2 (valid_from, valid_thru). Differently of "standard" way obsolete records are terminated just based on existence of newer record for the same instance. Module based on the pattern is independent on source of transformation (common for all mappings filling table) and much more efficient in bulk processing. ...more
FULL_REFRESH_DELETE ETL Transformation pattern
    by Ludek Bob Jankovsky, 31-Aug-2008 (ETL PATTERNS)
ETL Pattern means standard functional task defining what has to happen despite technical implementation.
FR_DELETE pattern task marks as deleted all records missing in a source. ...more
Analytical functions - gaps between intervals
    by Ludek Bob Jankovsky, 28-Aug-2008 (ORACLE STUFFS)
There are several usages of analytical function related to time intervals. It could be useful to mention few samples to reuse them later within algorithms. ...more
INSERT ETL transformation patterns family
    by Ludek Bob Jankovsky, 28-Aug-2008 (ETL PATTERNS)
ETL Pattern means standard functional task defining what has to happen despite technical implementation.
Basic insert-wise patterns:
  • INSERT - just inserts records from source (simpliest)
  • DIFF_INSERT - inserts new records only, existing records (based on matching key) are ignored
  • SCD2_INSERT - inserts new and changed records. Changed records are inserted as duplicates. That way supports load of SCD2 (slowly changing dimensions).
 ...more
DIFF_MERGE - Differential merge ETL transformation pattern
    by Ludek Bob Jankovsky, 28-Aug-2008 (ETL PATTERNS)
ETL Pattern means standard functional task defining what has to happen despite technical implementation.
DIFF_MERGE pattern task:
  • inserts new elements
  • updates changed existing elements
  • ignores unchanged existing elements
 ...more
Ask Bob
    by Ludek Bob Jankovsky, 26-Aug-2008 (NEWS)
New feature on the site - see Ask Bob in menu. Ask anything, I'll answer you anything:) ...more
Simple ETL module generator (XMLMap)
    by Ludek Bob Jankovsky, 19-Aug-2008 (ORACLE STUFFS)
There are several complex ways of generating PL/SQL ETL modules based on metadata. Now we have to avoid all the rules such as:
  • Open metadata interface
  • Custom - dynamic ETL patterns (templates)
  • ...
Following stuff is dedicated to be used in places with the minimum impact into infrastructure requirement. ...more
Merge template for table based on data dictionary
    by Ludek Bob Jankovsky, 18-Aug-2008 (ORACLE STUFFS)
Usefull generator of merge statement for specified table. It is worth to use it to create initial data upsert script. Script gathers data dictionary metadata about table including primary or unique key information to propose matching criteria.  ...more
Sample script to read content of server directory
    by Ludek Bob Jankovsky, 18-Aug-2008 (ORACLE STUFFS)
 ...more
Sample script to run OS commands on server
    by Ludek Bob Jankovsky, 18-Aug-2008 (ORACLE STUFFS)
 ...more
String concatenation as aggregation function
    by Ludek Bob Jankovsky, 18-Aug-2008 (ORACLE STUFFS)
Create your own aggregation function. The sample returns all the column text as a concatenation of them.  ...more
Oracle dynamic code buffer
    by Ludek Bob Jankovsky, 18-Aug-2008 (ORACLE STUFFS)
To generate and execute dynamic SQL or PL/SQL code there is usefull buffer allowing compose code and execute it. Differently of standard usage of execute immediate :Varchar2 this one is not limited by 32767 bytes and allows compose really huge code. It could be implemented either as type or as package. Type form allows to use multiple instances simultaneously.  ...more
Ageing of data with band validity (SCD, History tables)
    by Ludek Bob Jankovsky, 10-Aug-2008 (DATA ARCHITECTURE)
Data in slowly changing dimension and history tables are usually organized as set of records with band of validity (VALID_FROM, VALID_THRU). All new records are then inserted with default - maximal date VALID_THRU. The date will be modified when new different values come.  ...more
Table, view or package description from Oracle into HTML
    by Ludek Bob Jankovsky, 10-Aug-2008 (ORACLE STUFFS)
Simple script allows to generate html fragment describing table(s) based on data dictionary. DBMS_OUTPUT will be used to return result html.  ...more
Domain analysis
    by Ludek Bob Jankovsky, 09-Aug-2008 (ORACLE STUFFS)
Domain analysis is a task of data profiling allowing gather automatically some kind of statistical metadata of either data in their original database schema or data samples loaded into an Oracle database. ...more
Rebuild unusable indexes and enable key constraints
    by Ludek Bob Jankovsky, 04-Aug-2008 (ORACLE STUFFS)
Simple useful script to rebuild unusable indexes (both global and local) in defined scope. ...more
Multiplication of partitions
    by Ludek Bob Jankovsky, 04-Aug-2008 (ORACLE STUFFS)
Several partitioning strategies use partitioning by time (one of partitioning columns) to allow ageing of data. On the way of regular ETL process new partitions shold be added automatically in some kind of pre-process, anyway task to multiply partition either before an initial load or migration of current data into partitioned state (repartitioning) remains. ...more
Exchange partition issues ORA-14096 and ORA-14097
    by Ludek Bob Jankovsky, 03-Aug-2008 (ORACLE STUFFS)
On the way of exchange partition there are two basic prerequisities you have to satisfy:
  • Equal number and data types of columns.
  • Each local index of partitioned table should have an equivalent as an index of exchanged table (valid for constraints).
Despite it sounds simply, it is often not as easy getting rid of troubles such as:
  • ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns.
  • ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION.
 ...more
Articles: 61 .. 79   first  prev  next 
All Right Reserved © 2007, Designed by Bob Jankovsky