- 1.0 Define Requirements – In this process you should understand the business needs by gathering information from the user. You should understand the data needed and if it is available. Resources should be identified for information or help with the process.
- Deliverables
- A logical description of how you will extract, transform, and load the data.
- Sign-off of the customer(s).
- Standards
- Document ETL business requirements specification using either the ETL Business Requirements Specification Template, your own team-specific business requirements template or system, or Oracle Designer.
- Templates
- ETL Business Requirements Specification Template
- 2.0 Create Physical Design – In this process you should define your inputs and outputs by documenting record layouts. You should also identify and define your location of source and target, file/table sizing information, volume information, and how the data will be transformed.
- Deliverables
- Input and output record layouts
- Location of source and target
- File/table sizing information
- File/table volume information
- Documentation on how the data will be transformed, if at all
- Standards
- Complete ETL Business Requirements Specification using one of the methods documented in the previous steps.
- Start ETL Mapping Specification
- Templates
- ETL Business Requirements Specification Template
- ETL Mapping Specification Template
- 3.0 Design Test Plan – Understand what the data combinations are and define what results are expected. Remember to include error checks. Decide how many test cases need to be built. Look at technical risk and include security. Test business requirements.
- Deliverables
- ETL Test Plan
- ETL Performance Test Plan
- Standards
- Document ETL test plan and performance plan using either the standard templates listed below or your own team-specific template(s).
- Templates
- ETL Test Plan Template
- ETL Performance Test Plan Template
- 4.0 Create ETL Process – Start creating the actual Informatica ETL process. The developer is actually doing some testing in this process.
- Deliverables
- Mapping Specification
- Mapping
- Workflow
- Session
- Standards
- Start the ETL Object Migration Form
- Start Database Object Migration Form (if applicable)
- Complete ETL Mapping Specification
- Complete cleanup process for log and bad files – Refer to Standard_ETL_File_Cleanup.doc
- Follow Informatica Naming Standards
- Templates
- ETL Object Migration Form
- ETL Mapping Specification Template
- Database Object Migration Form (if applicable)
- 5.0 Test Process – The developer does the following types of tests: unit, volume, and performance.
- Deliverables
- ETL Test Plan
- ETL Performance Test Plan
- Standards
- Complete ETL Test Plan
- Complete ETL Performance Test Plan
- Templates
- ETL Test Plan Template
- ETL Performance Test Plan
- 6.0 Walkthrough ETL Process – Within the walkthrough the following factors should be addressed: Identify common modules (reusable objects), efficiency of the ETL code, the business logic, accuracy, and standardization.
- Deliverables
- ETL process that has been reviewed
- Standards
- Conduct ETL Process Walkthrough
- Templates
- ETL Mapping Walkthrough Checklist Template
- 7.0 Coordinate Move to QA – The developer works with the ETL Administrator to organize ETL Process move to QA.
- Deliverables
- ETL process moved to QA
- Standards
- Complete ETL Object Migration Form
- Complete Unix Job Setup Request Form
- Complete Database Object Migration Form (if applicable)
- Templates
- ETL Object Migration Form
- Unix Job Setup Request Form
- Database Object Migration Form
- 8.0 Test Process – At this point, the developer once again tests the process after it has been moved to QA.
- Deliverables
- Tested ETL process
- Standards
- Developer validates ETL Test Plan and ETL Performance Test Plan
- Templates
- ETL Test Plan Template
- ETL Performance Test Plan Template
- 9.0 User Validates Data – The user validates the data and makes sure it satisfies the business requirements.
- Deliverables
- Validated ETL process
- Standards
- Validate Business Requirement Specifications with the data
- Templates
- ETL Business Requirement Specifications Template
- 10.0 Coordinate Move to Production - The developer works with the ETL Administrator to organize ETL Process move to Production.
- Deliverables
- Accurate and efficient ETL process moved to production
- Standards
- Complete ETL Object Migration Form
- Complete Unix Job Setup Request Form
- Complete Database Object Migration Form (if applicable)
- Templates
- ETL Object Migration Form
- Unix Job Setup Request Form
- Database Object Migration Form (if applicable)
- 11.0 Maintain ETL Process – There are a couple situations to consider when maintaining an ETL process. There is maintenance when an ETL process breaks and there is maintenance when and ETL process needs updated.
- Deliverables
- Accurate and efficient ETL process in production
- Standards
- Updated Business Requirements Specification (if needed)
- Updated Mapping Specification (if needed)
- Revised mapping in appropriate folder
- Updated ETL Object Migration Form
- Developer checks final results in production
- All monitoring (finding problems) of the ETL process is the responsibility of the project team
- Templates
- Business Requirements Specification Template
- Mapping Specification Template
- ETL Object Migration Form
- Unix Job Setup Request Form
- Database Object Migration Form (if applicable)
Monday, March 7, 2011
ETL Process Definitions and Deliverables
DBA Queries
--To Identify table names across schema which matches your parameter
SELECT TABLE_NAME FROM ALL_TABLES WHERE TABLE_NAME LIKE '%ACCT%' ORDER BY TABLE_NAME;
--To Identify list of table names which has common Column Name
select table_name, column_name from all_tab_columns where
column_name like '%ACCT_ATTR_CAN_RQS_CONV_CHK_IN%'
--To identify list schema name which has common Table Name
select * from all_objects where object_type in ('TABLE','VIEW')
and object_name = 'SHDW_ACCT_ATTR';
--To list all the owner, schema, table name, column name which matchs your columns.comments parameter
--and specific schema
desc ALL_COL_COMMENTS
select * from all_COL_COMMENTS where comments like '%Convenience%' and owner = 'PNET_RPTG'
--To list all the owner, schema, table name, column name which matchs your columns.comments parameter
--and specific table name
SELECT * FROM all_col_comments WHERE table_name='AUDT_RPT' and comments like '%name%'
SELECT 'comment on column '||table_name||'.'||column_name||' is '''||comments||''';'
FROM all_col_comments
WHERE comments is not null;
--To display Database Name & Current Session User
select sys_context('userenv','db_name'), sys_context('userenv','session_user') from dual
--To identify Owner, Object type and status
select owner,object_type, count(*),status from dba_objects GROUP BY OWNER, OBJECT_TYPE,status
ORDER BY 1,2,3
--To Display username of DBA objects
select username from dba_users minus select distinct owner from dba_objects ;
--=============================================================
desc pnet_rptg.audt_rpt
--Alternate Query for DESC a table--
SELECT
column_name "Name",
nullable "Null?",
concat(concat(concat(data_type,'('),data_length),')') "Type"
FROM all_tab_columns
WHERE table_name='AUDT_RPT';
--=============================================================
Oracle Partitioning Concepts
Partitioning in Oracle. What? Why? When? Who? Where? How? - Partitioning in Oracle
Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a "partition". From an "Application Development" perspective, there is no difference between a partitioned and a non-partitioned table. The application need not be modified to access a partitioned table if that application was initially written on a non partitioned tables.
So now you know partitioning in oracle now the only thing that yo u need to know is little bit of syntax and that’s it, and you are a partitioning guru.
Oracle introduced partitioning with 8.0. With this version only, " Range Partitioning" was supported. I will come to details later about what that means. Then with Oracle 8i " Hash and Composite Partitioning" was also introduced and with 9i " List Partitioning", it was introduced with lots of other features with each upgrade. Each method of partitioning has its own advantages and disadvantages and the decision which one to use will depend on the data and type of application. Also one can MODIFY , RENAME, MOVE, ADD, DROP, TRUNCATE, SPLIT partitions. We will go thru the details now.
Advantages of using Partition’s in Table
1. Smaller and more manageable pieces of data ( Partitions )
2. Reduced recovery time
3. Failure impact is less
4. import / export can be done at the " Partition Level".
5. Faster access of data
6. Partitions work independent of the other partitions.
7. Very easy to use
Types of Partitioning Methods
1. RANGE Partitioning
This type of partitioning creates partitions based on the " Range of Column" values. Each partition is defined by a " Partition Bound" (non inclusive ) that basically limits the scope of partition. Most commonly used values for " Range Partition" is the Date field in a table. Lets say we have a table SAMPLE_ORDERS and it has a field ORDER_DATE. Also, lets say we have 5 years of history in this table. Then, we can create partitions by date for, lets say, every quarter.
So Every Quarter Data becomes a partition in the SAMPLE_ORDER table. The first partition will be the one with the lowest bound and the last one will be the Partition with the highest bound. So if we have a query that want to look at the Data of first quarter of 1999 then instead of going through the complete data it will directly go to the Partition of first quarter 1999.
This is example of the syntax needed for creating a RANGE PARTITION.
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER)
PARTITION BY RANGE(ORDER_DATE)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
;
the above example basically created 8 partitions on the SAMPLE_ORDERS Table all these partitions correspond to one quarter. Partition SO99Q1 will contain the orders for only first quarter of 1999.
2. HASH Partitioning
Under this type of partitioning the records in a table, are partitions based of a Hash value found in the value of the column, that is used for partitioning. " Hash Partitioning" does not have any logical meaning to the partitions as do the range partitioning. Lets take one example.
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
ORDER_ZIP_CODE)
PARTITION BY HASH (ORDER_ZIP_CODE)
(PARTITION P1_ZIP TABLESPACE TS01,
PARTITION P2_ZIP TABLESPACE TS02,
PARTITION P3_ZIP TABLESPACE TS03,
PARTITION P4_ZIP TABLESPACE TS04)
ENABLE ROW MOVEMENT;
The above example creates four hash partitions based on the zip codes from where the orders were placed.
3. List Partitioning ( Only with 9i)
Under this type of partitioning the records in a table are partitioned based on the List of values for a table with say communities column as a defining key the partitions can be made based on that say in a table we have communities like ‘Government’ , ‘Asian’ , ‘Employees’ , ‘American’, ‘European’ then a List Partition can be created for individual or a group of communities lets say ‘American-partition’ will have all the records having the community as ‘American’
Lets take one example. In fact, we will modify the same example.
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
PARTITION BY LIST (SHIP_TO_STATE)
(PARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01,
PARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02,
PARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03,
PARTITION SHIP_TO_MASACHUSETTES VALUES (‘MA’) TABLESPACE TS04,
PARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
ENABLE ROW MOVEMENT;
The above example creates List partition based on the SHIP_TO_STATE each partition allocated to different table spaces.
4. Composite Range-Hash Partitioning
This is basically a combination of range and hash partitions. So basically, the first step is that the data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data.
Lets modify the above example again:
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
CUST_NAME VARCAHR2,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE)
SUBPARTITION BY HASH(CUST_NAME)
SUBPARTITION TEMPLATE(
(SUBPARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01,
SUBPARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02,
SUBPARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03,
SUBPARTITION SHIP_TO_NORTHEAST VALUES (‘MA’, ‘NY’, ‘NJ’) TABLESPACE TS04,
SUBPARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
ENABLE ROW MOVEMENT;
The above example shows that each range partition has been further sub-partitioned into smaller partitions based on the list value specified. SHIP_TO_ARIZONA is a sub-partition by a List value AZ. This partition will be present in the main partitions by range SO99Q1 etc.
5. Composite Range-List Partitioning ( Only with 9i)
This is also a combination of Range and List Partitions, basically first the data is divided using the Range partition and then each Range partitioned data is further subdivided into List partitions using List key values. Each sub partitions individually represents logical subset of the data not like composite Range-Hash Partition.
Index organized tables can be partitioned using Range or Hash Partitions
Lets modify the above partition once more.
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
CUST_NAME VARCAHR2,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE)
SUBPARTITION BY LIST(SHIP_TO_STATE)
SUBPARTITION TEMPLATE(
SUBPARTITION SP1 TABLESPACE TS01,
SUBPARTITION SP2 TABLESPACE TS02,
SUBPARTITION SP3 TABLESPACE TS03,
SUBPARTITION SP4 TABLESPACE TS04,
SUBPARTITION SP5 TABLESPACE TS05)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
ENABLE ROW MOVEMENT;
With Oracle 9i, there is also a feature to create indexes on the partitions. The indexes can be:
a. Local indexes
This is created the same manner as the index on existing partitioned table. Each partition of a local index corresponds to one partition only.
b. Global Partitioned Indexes
This can be created on a partitioned or a non-partitioned tables. But for now, they can be partitioned using the " Range Partitioning" only. For example, in above example, where I divided the table into partitions representing a quarter, a " Global Index" can be created by using a different " Partitioning Key" and can have different number of partitions.
c. Global Non- Partitioned Indexes
This is no different than the ordinary index created on a non-partitioned table. The index structure is not partitioned.
Oracle Index Concepts
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records. The disk space required to store the index is typically less than that required by the table (since indices usually contain only the key-fields according to which the table is to be arranged, and exclude all the other details in the table), yielding the possibility to store indices in memory for a table whose data is too large to store in memory.
data structure is a particular way of storing and organizing data in a computer so that it can be used efficie
Indices may be defined as unique or non-unique. A unique index acts as a constraint on the table by preventing duplicate entries in the index and thus the backing table.
Understand Index Architecture
Block
| Index | Data |
Non-clustered Index
The data is present in random order, but the logical ordering is specified by the index. The data rows may be randomly spread throughout the table. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the page and the row number in the data page. In non-clustered index:
The physical order of the rows is not the same as the index order.
Typically created on column used in JOIN, WHERE, and ORDER BY clauses.
Good for tables whose values may be modified frequently.
Clustered
Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually only where the data is accessed sequentially in the same or reverse order of the clustered index, or when a range of items is selected.
Since the physical records are in this sort order on disk, the next row item in the sequence is immediately before or after the last one, and so fewer data block reads are required. The primary feature of a clustered index is therefore the ordering of the physical data rows in accordance with the index blocks that point to them. Some databases separate the data and index blocks into separate files, others put two completely different data blocks within the same physical file(s). Create an object where the physical order of rows is same as the index order of the rows and the bottom(leaf) level of clustered index contains the actual data rows.
They are known as "index organized tables" under Oracle database.
Why Column order index is critical?
The order in which columns are listed in the index definition is important. It is possible to retrieve a set of row identifiers using only the first indexed column. However, it is not possible or efficient (on most databases) to retrieve the set of row identifiers using only the second or greater indexed column.
For example, imagine a phone book that is organized by city first, then by last name, and then by first name. If you are given the city, you can easily extract the list of all phone numbers for that city. However, in this phone book it would be very tedious to find all the phone numbers for a given last name. You would have to look within each city's section for the entries with that last name. Some databases can do this, others just won’t use the index.
Types of Index:
1. B-tree indexes: the default and the most common (It provides fast lookup of rows containing a desired key value. It is not suitable if the column(s) being indexed are of low cardinality (number of distinct values). They are simular construct to a binary tree, they provide fast access by key, to an individual row or range of rows, normally requiring very few reads to find the correct row. The B*Tree index has several subtypes.
2. B-tree cluster indexes: defined specifically for cluster . They are used to index the cluster keys
3. Hash cluster indexes: defined specifically for a hash cluster
4. Global and local indexes: relate to partitioned tables and indexes (an index on a partitioned table might be global or local)
5. Reverse key indexes: most useful for Oracle Real Application Clusters applications
6. Bitmap indexes: compact; work best for columns with a small set of values. bitmap indexes are most appropriate for columns having low distinct values—such as GENDER, MARITAL_STATUS, and RELATION. This assumption is not completely accurate, however. In reality, a bitmap index is always advisable for systems in which data is not frequently updated by many concurrent systems.
a. Ex: create bitmap index normal_empno_bmx on test_normal(empno);
7. Function-based indexes: contain the precomputed value of a function/expression Domain indexes: specific to an application or cartridge. (an index might be on a normal column, or on an expression)
a. Ex: create index emp_idx on emp (upper(ename))
b. alter index emp_idx disable
c. alter index emp_idx enable
Syntax:
CREATE INDEX
ix_emp_01
ON
emp (deptno)
TABLESPACE
index_tbs;
Concatenated Index:
create indexes on multiple columns in a table. Say, for example, we wanted an index on the EMP table columns EMPNO and DEPTNO. This is known as a concatenated index, and it’s created this way:
CREATE INDEX ix_emp_01 ON emp (empno, deptno) TABLESPACE index_tbs;
Altering Oracle Indexes
ALTER INDEX ix_emp_01 REBUILD TABLESPACE new_index;
In this example we use the alter index command to rebuild an index. The rebuild keyword is what tells Oracle to rebuild the index. When we use the tablespace keyword, followed by a tablespace name, we are telling Oracle which tablespace to recreate the rebuilt index in. By default Oracle will create the rebuilt index in the same tablespace.
Dropping Oracle Indexes
Sometimes what we create we must destroy. When it’s time to remove an index, the drop index command is what is needed. The drop index command is pretty straight forward as seen in this example:
DROP INDEX ix_emp_01_old;
Another Way to disable & rebuild Index:
1. To make your index unusable:
alter index your_index unusable;
2. To remark your index usable, you must rebuild the index.(This sucks)
alter index your_index rebuild [online];
3. Check following parameter. If your index is marked unusable and "skip_unusable_indexes" is false, you DML will fail.
show parameter skip_unusable_indexes;
alter session set skip_unusable_indexes = true;
Drawback on excessive use of Index:
1. Indexes consume disk space
2. Excessive use of indexes can pay serious performance penalty
3. And Queries can use wrong index plans, causing the queries to slow down
Difference Between INDEX and SORT:
Question:
What is an index and what is a sort?
Answer:
Here is a table that I will refer to during my answer:
RecNo cName nAge
----- ----- ----
1 Rick 34
2 Dan 30
3 Chris 33
An index is a logical reorganization of the data in a table. The record numbers do not change; the index just allows the table to be viewed in an order other than record number order. If I create an index on the cName field in the table above, here are the results:
RecNo cName nAge
----- ----- ----
3 Chris 33
2 Dan 30
1 Rick 34
Notice that even though Rick appears as the last record, it is still the first record in the table (Recno=1).
A sort is a physical reorganization of the records in a DBF. If I sort the table above by cName, I get the following table:
RecNo cName nAge
----- ----- ----
1 Chris 33
2 Dan 30
3 Rick 34
Notice that the records in the table have been reorganized: record 1 is no longer "Rick."
Comparison/Difference between Index & Partitions:
First rule is, there is no comparison, and you cannot compare them. They are different, apples and oranges.
· Index for a physical structure (b-tree) to help you query run faster.
· Table partition is a method of breaking a large table into smaller tables grouped by some logical separators.
· GLOBAL PARTITION INDEX CREATED OWN PARTITION RANGE OTHERS THEN TABLE PARTITION
Basics of ETL Testing Concepts
Source:
http://www.freewebs.com/testingtutorials/etltesting.htm
http://www.learn.geekinterview.com/data-warehouse/dw-basics/what-is-data-completeness.html
http://datawarehouse4u.info/index_en.html
What is ETL Testing?
ETL Testing:
During ETL application testing, we test for the following:
Data completeness. Ensures that all expected data is loaded.
Data transformation. Ensures that all data is transformed correctly according to business rules and/or design specifications.
Data quality. Ensures that the ETL application correctly rejects, substitutes default values, corrects or ignores and reports invalid data.
Performance and scalability. Ensures that data loads and queries perform within expected time frames and that the technical architecture is scalable.
Integration testing. Ensures that the ETL process functions well with other upstream and downstream processes.
User-acceptance testing. Ensures the solution meets users' current expectations and anticipates their future expectations.
Regression testing. Ensures existing functionality remains intact each time a new release of code is completed.
ETL testing validates that data is transformed correctly from OLTP to data
warehouse.
Validating the data transformed includes following main verification points:
1. Ensures that all expected data is loaded. Comparing record counts between source data loaded to the warehouse and rejected records.
2. Ensures that all data is transformed correctly according to design
specifications.
3. Ensures that the ETL application substitutes default values ignores
invalid data.
4. Validate correct processing of ETL-generated fields such as surrogate
keys.
5. Validate that data types in the warehouse are as specified in the design
and/or the data model.
6. Validate the referential integrity between tables.
Basics of Data warehousing:
In any data resource, it is essential to meet requirements of current as well as future demand for information. Data completeness assures that the above criterion is fulfilled.
Data completeness refers to an indication of whether or not all the data necessary to meet the current and future business information demand are available in the data resource.
It deals with determining the data needed to meet the business information demand and ensuring those data are captured and maintained in the data resource so they are available when needed.
A data warehouse has six main processes. These processes should be carefully carried out by the data warehouse administrator in order to achieve data completeness. The processes are as follows:
• Data Extraction – the data in the warehouse can come from many sources and of multiple data format and types with may be incompatible from system to system. The process of data extraction includes formatting the disparate data types into one type understood by the warehouse. The process also includes compressing the data and handling of encryptions whenever this applies.
• Data Transformation – This process include data integration, demoralization; surrogate key management, data cleansing, conversion, auditing and aggregation.
• Data Loading – After the first two process, the data will then be ready to be optimally stored in the data warehouse.
• Security Implementation – Data should be protected from prying eyes whenever applicable as in the case of bank records and credit card numbers. The data warehouse administrator implements access and data encryption policies.
• Job Control – This process is the constant job of the data warehouse administrator and his staff. This includes job definition, time and event job scheduling, logging, monitoring, error handling, exception handling and notification.
The measure of a data warehouse's performance depends on one of the factors pertaining to availability of useful data which is also an indication of the success of a business organization in reaching its own goals. All data can be imperfect in some fashion to some degree. It is the goal of the data warehouse manager to pursue perfect data which is consumed by the public resources without the need for creating appreciable value. The data warehouse manager and his staff should come up with strategies to be able to provide substantial accuracy and timeliness of data at a reasonable cost so as not to burden the company with extra expenses.
In most cases, data warehouses are available twenty four hours a day, seven days a week. So that comprehensive data is gathered, extracted, loaded and shared within the data warehouse, regular updates should be done. Parallel and distributed servers target for world wide availability of data so data completeness can be achieved with investing in high powered servers and robust software applications. Data warehouses are also designed for customer level analysis, aside from organizational level analysis and reporting. So flexible tools should be implemented in the data warehouse database to accommodate new data sources and support for metadata. Reliability can be achieved when all these are considered.
The success in achieving data completeness in a warehouse is not just dependent on the current status of the database and its physical set-up. At the planning stage, every detail about the data warehouse should be carefully scrutinized. All other frameworks of the data warehouse should also be carefully planned including the details of the business architecture, business data, business schema, business activities, data model, critical success factors, meta data, comprehensive data definition and other related aspects of organizational functions.
Having complete data can give an accurate guidance of the business organization's decision maker. With complete data, statistical reports will be generated with will reflect and accurate status of the company and how it is faring with the trends and patterns in the industry and how to make innovative moves to gain competitive advantages over the competitors.
Data warehouse Overview:
ETL process
ETL (Extract, Transform and Load) is a process in data warehousing responsible for pulling data out of the source systems and placing it into a data warehouse. ETL involves the following tasks:
- extracting the data from source systems (SAP, ERP, other oprational systems), data from different source systems is converted into one consolidated data warehouse format which is ready for transformation processing.
- transforming the data may involve the following tasks:
applying business rules (so-called derivations, e.g., calculating new measures and dimensions),
cleaning (e.g., mapping NULL to 0 or "Male" to "M" and "Female" to "F" etc.),
filtering (e.g., selecting only certain columns to load),
splitting a column into multiple columns and vice versa,
joining together data from multiple sources (e.g., lookup, merge),
transposing rows and columns,
applying any kind of simple or complex data validation (e.g., if the first 3 columns in a row are empty then reject the row from processing)
- loading the data into a data warehouse or data repository other reporting applications
Data Completeness
One of the most basic tests of data completeness is to verify that all expected data loads into the data warehouse. This includes validating that all records, all fields and the full contents of each field are loaded. Strategies to consider include:
Comparing record counts between source data, data loaded to the warehouse and rejected records.
Comparing unique values of key fields between source data and data loaded to the warehouse. This is a valuable technique that points out a variety of possible data errors without doing a full validation on all fields.
Utilizing a data profiling tool that shows the range and value distributions of fields in a data set. This can be used during testing and in production to compare source and target data sets and point out any data anomalies from source systems that may be missed even when the data movement is correct.
Populating the full contents of each field to validate that no truncation occurs at any step in the process. For example, if the source data field is a string (30) make sure to test it with 30 characters.
Testing the boundaries of each field to find any database limitations. For example, for a decimal (3) field include values of -99 and 999, and for date fields include the entire range of dates expected. Depending on the type of database and how it is indexed, it is possible that the range of values the database accepts is too small.
Data Transformation
Validating that data is transformed correctly based on business rules can be the most complex part of testing an ETL application with significant transformation logic. One typical method is to pick some sample records and "stare and compare" to validate data transformations manually. This can be useful but requires manual testing steps and testers who understand the ETL logic. A combination of automated data profiling and automated data movement validations is a better long-term strategy. Here are some simple automated data movement techniques:
Create a spreadsheet of scenarios of input data and expected results and validate these with the business customer. This is a good requirements elicitation exercise during design and can also be used during testing.
Create test data that includes all scenarios. Elicit the help of an ETL developer to automate the process of populating data sets with the scenario spreadsheet to allow for flexibility because scenarios will change.
Utilize data profiling results to compare range and distribution of values in each field between source and target data.
Validate correct processing of ETL-generated fields such as surrogate keys.
Validate that data types in the warehouse are as specified in the design and/or the data model.
Set up data scenarios that test referential integrity between tables. For example, what happens when the data contains foreign key values not in the parent table?
Validate parent-to-child relationships in the data. Set up data scenarios that test how orphaned child records are handled.
Data Quality
For the purposes of this discussion, data quality is defined as "how the ETL system handles data rejection, substitution, correction and notification without modifying data." To ensure success in testing data quality, include as many data scenarios as possible. Typically, data quality rules are defined during design, for example:
Reject the record if a certain decimal field has nonnumeric data.
Substitute null if a certain decimal field has nonnumeric data.
Validate and correct the state field if necessary based on the ZIP code.
Compare product code to values in a lookup table, and if there is no match load anyway but report to users.
Depending on the data quality rules of the application being tested, scenarios to test might include null key values, duplicate records in source data and invalid data types in fields (e.g., alphabetic characters in a decimal field). Review the detailed test scenarios with business users and technical designers to ensure that all are on the same page. Data quality rules applied to the data will usually be invisible to the users once the application is in production; users will only see what's loaded to the database. For this reason, it is important to ensure that what is done with invalid data is reported to the users. These data quality reports present valuable data that sometimes reveals systematic issues with source data. In some cases, it may be beneficial to populate the "before" data in the database for users to view.
Performance and Scalability
As the volume of data in a data warehouse grows, ETL load times can be expected to increase and performance of queries can be expected to degrade. This can be mitigated by having a solid technical architecture and good ETL design. The aim of the performance testing is to point out any potential weaknesses in the ETL design, such as reading a file multiple times or creating unnecessary intermediate files. The following strategies will help discover performance issues:
Load the database with peak expected production volumes to ensure that this volume of data can be loaded by the ETL process within the agreed-upon window.
Compare these ETL loading times to loads performed with a smaller amount of data to anticipate scalability issues. Compare the ETL processing times component by component to point out any areas of weakness.
Monitor the timing of the reject process and consider how large volumes of rejected data will be handled.
Perform simple and multiple join queries to validate query performance on large database volumes. Work with business users to develop sample queries and acceptable performance criteria for each query.
Integration Testing
Typically, system testing only includes testing within the ETL application. The endpoints for system testing are the input and output of the ETL code being tested. Integration testing shows how the application fits into the overall flow of all upstream and downstream applications. When creating integration test scenarios, consider how the overall process can break and focus on touch points between applications rather than within one application. Consider how process failures at each step would be handled and how data would be recovered or deleted if necessary.
Most issues found during integration testing are either data related to or resulting from false assumptions about the design of another application. Therefore, it is important to integration test with production-like data. Real production data is ideal, but depending on the contents of the data, there could be privacy or security concerns that require certain fields to be randomized before using it in a test environment. As always, don't forget the importance of good communication between the testing and design teams of all systems involved. To help bridge this communication gap, gather team members from all systems together to formulate test scenarios and discuss what could go wrong in production. Run the overall process from end to end in the same order and with the same dependencies as in production. Integration testing should be a combined effort and not the responsibility solely of the team testing the ETL application.
User-Acceptance Testing
The main reason for building a data warehouse application is to make data available to business users. Users know the data best, and their participation in the testing effort is a key component to the success of a data warehouse implementation. User-acceptance testing (UAT) typically focuses on data loaded to the data warehouse and any views that have been created on top of the tables, not the mechanics of how the ETL application works. Consider the following strategies:
Use data that is either from production or as near to production data as possible. Users typically find issues once they see the "real" data, sometimes leading to design changes.
Test database views comparing view contents to what is expected. It is important that users sign off and clearly understand how the views are created.
Plan for the system test team to support users during UAT. The users will likely have questions about how the data is populated and need to understand details of how the ETL works.
Consider how the users would require the data loaded during UAT and negotiate how often the data will be refreshed.
Regression Testing
Regression testing is revalidation of existing functionality with each new release of code. When building test cases, remember that they will likely be executed multiple times as new releases are created due to defect fixes, enhancements or upstream systems changes. Building automation during system testing will make the process of regression testing much smoother. Test cases should be prioritized by risk in order to help determine which need to be rerun for each new release. A simple but effective and efficient strategy to retest basic functionality is to store source data sets and results from successful runs of the code and compare new test results with previous runs. When doing a regression test, it is much quicker to compare results to a previous execution than to do an entire data validation again.
Taking these considerations into account during the design and testing portions of building a data warehouse will ensure that a quality product is produced and prevent costly mistakes from being discovered in production.
OLTP vs. OLAP
We can divide IT systems into transactional (OLTP) and analytical (OLAP). In general we can assume that OLTP systems provide source data to data warehouses, whereas OLAP systems help to analyze it.
- OLTP (On-line Transaction Processing) is characterized by a large number of short on-line transactions (INSERT, UPDATE, DELETE). The main emphasis for OLTP systems is put on very fast query processing, maintaining data integrity in multi-access environments and an effectiveness measured by number of transactions per second. In OLTP database there is detailed and current data, and schema used to store transactional databases is the entity model (usually 3NF).
- OLAP (On-line Analytical Processing) is characterized by relatively low volume of transactions. Queries are often very complex and involve aggregations. For OLAP systems a response time is an effectiveness measure. OLAP applications are widely used by Data Mining techniques. In OLAP database there is aggregated, historical data, stored in multi-dimensional schemas (usually star schema).
The following table summarizes the major differences between OLTP and OLAP system design.
| | OLTP System
Online Transaction Processing
(Operational System) | OLAP System
Online Analytical Processing
(Data Warehouse) |
| Source of data | Operational data; OLTPs are the original source of the data. | Consolidation data; OLAP data comes from the various OLTP Databases |
| Purpose of data | To control and run fundamental business tasks | To help with planning, problem solving, and decision support |
| What the data | Reveals a snapshot of ongoing business processes | Multi-dimensional views of various kinds of business activities |
| Inserts and Updates | Short and fast inserts and updates initiated by end users | Periodic long-running batch jobs refresh the data |
| Queries | Relatively standardized and simple queries Returning relatively few records | Often complex queries involving aggregations |
| Processing Speed | Typically very fast | Depends on the amount of data involved; batch data refreshes and complex queries may take many hours; query speed can be improved by creating indexes |
| Space Requirements | Can be relatively small if historical data is archived | Larger due to the existence of aggregation structures and history data; requires more indexes than OLTP |
| Database Design | Highly normalized with many tables | Typically de-normalized with fewer tables; use of star and/or snowflake schemas |
| Backup and Recovery | Backup religiously; operational data is critical to run the business, data loss is likely to entail significant monetary loss and legal liability | Instead of regular backups, some environments may consider simply reloading the OLTP data as a recovery method |
Data Mining:
Generally, data mining (sometimes called data or knowledge discovery) is the process of analyzing data from different perspectives and summarizing it into useful information - information that can be used to increase revenue, cuts costs, or both. Data mining software is one of a number of analytical tools for analyzing data. It allows users to analyze data from many different dimensions or angles, categorize it, and summarize the relationships identified. Technically, data mining is the process of finding correlations or patterns among dozens of fields in large relational databases.
http://www.anderson.ucla.edu/faculty/jason.frand/teacher/technologies/palace/datamining.htm
Subscribe to:
Posts (Atom)