NewsCustom SearchContact
 Class:   Search:   for:      |<  <  > 
New time zone for Project managers :)
    by Ludek Bob Jankovsky, 29-Nov-2008 (NEWS)
According to recent experiences new time zone has been implemented. It is regularly used yet, but for better understanding and more clear communication with project managers we attach following transformation table. ...more
ETL process scheduling - failover power
    by Ludek Bob Jankovsky, 06-Nov-2008 (HEAP WORKFLOW)
There are various systems and methods to manage ETL processes. Following article describes failover capability of particular methods. ...more
ETL process scheduling - sample solution for Oracle SIMPLETASK
    by Ludek Bob Jankovsky, 22-Sep-2008 (HEAP WORKFLOW)
Implementing ETL or ELT processes based on PL/SQL modules there is usually one important task in the queue - Schedule execution of atomic modules following internal dependencies and another rules. Enterprise-wide schedulers usually do not fulfill requirements of fine grain dependencies and rules, so they are often used as trigger of ETL batch managed by local ETL scheduler. Most of schedulers built-in into standard ETL tools are too static and rigid, not scaleable. Following solution I call SIMPLETASK, because it is simple by use method despite of teh powers it covers. Following sample is the BASIC version of the solution. Basic, to allow understand better the principle and to be easily included into simplier solutions like small ETL or migration processes. ...more
Data interfaces - independence of particular systems within enterprise
    by Ludek Bob Jankovsky, 18-Sep-2008 (DATA ARCHITECTURE)
There are two basic ways to keep Information systems manageable:
  • Perfect enterprise metadata system (I dream about it but I have never seen it:)
  • Independent systems with defined services and data interfaces more realistic method
Most non success stories producing unmanageable bulks of systems come from attempts to reach the first one. So we will now talk about the second one better. ...more
Metadata driven ETL approach
    by Ludek Bob Jankovsky, 10-Sep-2008 (ETL PATTERNS)
It could be named "Dream about metadata driven ETL". The schema depicted all elements what should participate in the ETL generating process separately to keep the system high manageable. ...more
Unpivot ETL transformation pattern - 10g vs. 11g
    by Ludek Bob Jankovsky, 09-Sep-2008 (ETL PATTERNS)
Unpivot transformation (several columns into several rows) becomes much simplier with Oracle 11g. Lets see. ...more
Partitioning strategy for fact tables - 10g vs. 11g
    by Ludek Bob Jankovsky, 09-Sep-2008 (ORACLE STUFFS)
New features of Oracle 11g simplified management of range partitions allowing INTERVAL partitioning. It allows move part of functionality solved currently by ETL pre-wrappers to default processing of RDBMS defined in Data dictionary metadata. ...more
New features of Oracle 11g comforting me
    by Ludek Bob Jankovsky, 06-Sep-2008 (ORACLE STUFFS)
There are several new features in 11g. Various people would consider different features more important. So all the article has been written from my point of view.  ...more
Advanced query rewrite, as cool as dangerous
    by Ludek Bob Jankovsky, 05-Sep-2008 (ORACLE STUFFS)
There are three basic levels how to use materialized views functionality :
  • Complex functionality (fast refresh materialized views) - it is easiest to use, anyway it is too expensive because of performance requirements (update with MV log could be even 10 times longer than without it). This way is good for non-critical parts of systems, referential and metadata tables etc.
  • Materialized view on prebuilt tables - eliminates refresh functionality (it should be replaced by customized ETL processing). Anyway consistency check functionality (STALE) remains.
  • Advanced query rewrite - just a torso of materialized view functionality - query rewrite remains. It could be used alse for correction of used inefficient queries . This variant we will describe in detail.
  •  ...more
    Ageing of data - avoid flat consideration of data in time (and ORA-39726)
        by Ludek Bob Jankovsky, 04-Sep-2008 (DATA ARCHITECTURE)
    Once you realizes to store historical data, you have to solve their ageing. You should not do it at very start, anyway difficulties of the solution will increase aside amount of your data and complexity of your system. Therefore it is reasonable solve ageing from the very start. ...more
    Feel free to register yourself
        by Ludek Bob Jankovsky, 02-Sep-2008 (NEWS)
    there are following advantages:
  • You can use Ask Bob option
  • You can create personal links from the page
  • We will not bother you with any periodical messages, we know, no one read them
  • If there appears any technical problem during registration do not hesitate contact me on my e-mail address on Contact screen
  •  ...more
    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
    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).
    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
    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
    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
    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.
    Articles: 31 .. 55   first  prev  next 
    All Right Reserved © 2007, Designed by Bob Jankovsky