HomeArticlesSlideshowsAsk BobLogin or Register
NewsCustom SearchContactLinks
 Class:   Search:   for:      |<  <  > 
Quick check of index condition
    by Ludek Bob Jankovsky, 11-Jan-2011 (ORACLE STUFFS)
One of task when checking Data Warehouse schema condition could be following quick index check. We look for:
  • Global indexes (suspicious in general, tere must be good reason to use them in data warehouse)
  • B-TREE indexes with low cardinality
  • B-TREE with high number of AVG_DATA_BLOCKS_PER_KEY (over 300) - such are reasonable to be made unusable before load process and rebuilt after
Failover technics - validation during the load phase
    by Ludek Bob Jankovsky, 09-May-2010 (ETL PATTERNS)
There are several places within the ETL process to catch wrong and low quality data. Generally there are four basic moments:
  • Validation during the load phase
  • Validation during the transformation phase
  • Limited validation during transformation phase just defaulting invalid values
  • Ex-post validation - data quality management

Following article is about the first one. ...more
SQL*Loader-500: Unable to open file
    by Ludek Bob Jankovsky, 02-Mar-2010 (ORACLE STUFFS)
There is a misleading error message in Oracle SQL*LOADER - following chain of messages could appear despite the problem is in another place:  ...more
Rigid Exchange Partition Data Acquisition approach
    by Ludek Bob Jankovsky, 05-Feb-2010 (ETL PATTERNS)
There are several ways to support consistency of data warehouses and data marts within business hoursperiod. In standard simple approaches it usually suppresses loading time window to really short period between readiness of source data and required readiness of target data. Following approach is just another way allowing keep all data in the mart consistent at the moment, anyway I consider it as an interesting stuff. ...more
Oracle MERGE to remote DB - ORA-01008
    by Ludek Bob Jankovsky, 24-Sep-2009 (ORACLE STUFFS)
Oracle Merge statement has some additional requirements when we process it thru DB-link. The first one is Oracle error "ORA-01008: not all variables bound" appearing despite it seems no variables are bound in the statement. ...more
Simple script to evaluete average VSize from tables in etalon schema (sizing issue)
    by Ludek Bob Jankovsky, 03-Jul-2009 (ORACLE STUFFS)
It is obviously possible get average size of each column from ALL_TAB_COLUMNS when statistics are up to date. Following script calculates it from data. It is useful when statistics are not reliable in the etalon. ...more
Unpivot in the Oracle 10g
    by Ludek Bob Jankovsky, 20-Apr-2009 (ORACLE STUFFS)
Unpivot operation available in Oracle 11g raised wave of interest about the same functionality in the 10g version of Oracle. Unfortunately there is just a workaround available. ...more
Search views for used columns - simple script
    by Ludek Bob Jankovsky, 17-Mar-2009 (ORACLE STUFFS)
Sometimes it is useful to search all views using certain column.
Dependencies in the Oracle data dictionary do not allow analysis on column level (10g). Definition of view is stored in a LONG datatype column in data dictionary. Following simple script helps to find views with a column (table, function) within its definition. ...more
Learning data architecture
    by avinash, 17-Mar-2009 (ASK BOB READY)
I want to learn data architecture. Which is the method I should follow? Any books I can refer with.
Thanks and Regards,
Avinash Prabhu  ...more
TBD STATUS indexing in Oracle
    by Ludek Bob Jankovsky, 08-Mar-2009 (ORACLE STUFFS)
It is not about a special type of index. That is just a technique of indexing columns indicating status TO BE DONE. What is common for such cases:
  • One or several statuses marking few TO BE PROCESSED records.
  • Majority of completed records.
  • Update of field is supposed.
What type of indexing of the field is the best?  ...more
Adding new mandatory columns with specified default 11g vs. 10g
    by Ludek Bob Jankovsky, 28-Feb-2009 (ORACLE STUFFS)
I am sure you have met the issue sometimes. You are adding new mandatory column into very large table. Oracle starts update all rows with default value of the column - like it would never finish. It takes huge amount of time in the data definition phase of migration scripts - not much agreeable issue. Lets see how it has been solved in the 10g (the same as 9i) version of Oracle and what is different in Oracle 11g.  ...more
How to avoid wasting sequences in differential merge pattern
    by Ludek Bob Jankovsky, 25-Feb-2009 (ETL PATTERNS)
In Oracle 10g, 11g getting values from sequences during MERGE behaves a bit strange. Despite you define getting value of sequence directly in a WHEN NOT MATCHED clause, sequence is acquired and wasted even if record is matched. According to docomented behaviour sequence will be acguired for every source record. It is a bad message because of offer usage of mentioned construction in DIFF_MERGE pattern.
Lets see what to do with it. ...more
Tidy your automatically maintained time partition names in 11g
    by Ludek Bob Jankovsky, 25-Feb-2009 (ORACLE STUFFS)
Oracle 11g have introduced new functionality of time interval partitions management. One of disadvantages of new approach are messy ssytem generated names of partitions. It would be pity not to use the new sofisticated approach just because of such issue. Better include simple solition to rename generated partitions based on its metadata definition. ...more
Intra-field list pivot and unpivot
    by Ludek Bob Jankovsky, 17-Feb-2009 (ORACLE STUFFS)
Aside standard unpivot tasks (we create n rows using values from n columns) following unpivot task appears:
One record with coma separated values in a column causes creating several rows, one for each part of the coma separated field.  ...more
Null-ability vs. rigid not null strategies in BI solutions
    by Ludek Bob Jankovsky, 19-Jan-2009 (DATA ARCHITECTURE)
Once several Data warehousing methodologists proposed rigid not-null approach to store data. All the null values has been substituted by not null substitution values despite it is reasonable or not. Like all rigid rules there are both advantages and disadvantages of such approach. Lets see them.  ...more
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
Useful queries for running processes check
    by Ludek Bob Jankovsky, 14-Oct-2008 (ORACLE STUFFS)
SQL tools like TOAD or PL/SQL developer contain enough functionality to check, anyway often you get into situation when it is impossible to use them. In that case I used to use a few useful queries to do despite no proper tools are present. Just a SQL+. ...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
    Third dimension for Zachman's matrix
        by Ludek Bob Jankovsky, 05-Sep-2008 (DATA ARCHITECTURE)
    Architctural-process approach comming from Zachman architecture framework matrix and specification of functional areas for an incremental development allows consider the third dimension to Zachman matrix specifying business areas.  ...more
    BI scope schema
        by Ludek Bob Jankovsky, 05-Sep-2008 (DATA ARCHITECTURE)
    Just a schema showing basic areas of the Business Intelligence. It is usefull to define real scope of particular project.  ...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
    SELF_UPDATE ETL Transformation pattern
        by Ludek Bob Jankovsky, 02-Sep-2008 (ETL PATTERNS)
    Self update patterrn is one of simpliests patterns. It performs updates based just on record data, such as closing record fulfilling specified conditions etc. ...more
    Articles: 31 .. 60   first  prev  next 
    All Right Reserved © 2007, Designed by Bob Jankovsky