Types of Dimensions

Dimension

A dimension table typically has two types of columns, primary keys to fact tables and textual\descriptive data.

Eg: Time, Customer

Types of Dimensions

1. Slowly Changing Dimensions
2. Rapidly Changing Dimensions
3. Junk Dimensions
4. Inferred Dimensions
5. Conformed Dimensions
6. Degenerate Dimensions
7. Role Playing Dimensions
8. Shrunken Dimensions
9. Static Dimensions 

Slowly Changing Dimensions

Attributes of a dimension that would undergo changes over time. It depends on the business requirement whether particular attribute history of changes should be preserved in the data warehouse. This is called a slowly changing attribute and a dimension containing such an attribute is called a slowly changing dimension.

Types of Fact Table

The Types of Fact Table are

1. Snapshot
2. Cumulative
3. Factless Fact Table

Snapshot

This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table.

Eg: Daily balances fact can be summed up through the customers dimension but not through the time dimension.

Types of Facts

Fact

A fact table typically has two types of columns, foreign keys to dimension tables and measures those that contain numeric facts. A fact table can contain fact’s data on detail or aggregated level.

Eg: Sales, Cost, and Profit

Types of Facts

1. Non-Additive
2. Semi-Additive
3. Additive

Advantages of using Alias Table in OBIEE

1. OBIEE does not support Circular join in BMM layer, we can overcome this problem by creating alias table for one of the table in that circular join.
2. You can create a join relationship which is different than the actual foreign key relationship in the database. This helps in defining relationships to meet business model criteria.

Why are we creating Alias table?

The following points are the main reasons to create an alias table:

• To reuse an existing table more than once in your physical layer instead of importing it several times.
• To set up multiple tables, each with different keys, names, or joins, when a single data source table needs to serve in different semantic roles. Setting up alias tables in this case is a way or will help us to avoid triangular or circular joins.

What is an Alias Table in OBIEE?

It is a reference to a physical table. Aliases are created in the physical layer of the repository and are an important part of designing a physical layer. An alias table is a physical table that references a different physical table as its source (called the original table).

What is Online and Offline Extraction?

Online Extraction:

Here the data is extracted directly from the Source for processing in the staging area, that’s why it’s called online extraction. During Extraction we connect directly to the source system and then access the source tables. There is no need of any external staging area

Offline Extractions:

Here the data is not extracted directly from the source, but instead it’s taken from another external area which keeps the copy of source. The external area can be Flat files, or some dump files in a specific format. So when we need to process the data we can fetch the records from the external source instead of the actual source.

Audit Columns in Datawarehouse

In data warehouse and data marts we maintain timestamp columns like “Last_Modified_Date or Created_Date” to track the exact date and time when the records were updated or inserted.These columns are called Audit Columns. But audit columns won’t track the deletion of columns. Last modified date stores the timestamp for record when it was modified for the last time and Created date will store the timestamp for the date on which the record was created, mostly we give it as SYSDATE

After we load the data for a particular day we can easily identify these newly loaded records by using audit columns. Whenever a record is inserted or deleted it makes a fresh timestamp entry through the Audit columns. So make sure that you include the Audit columns.

Snapshot sources in Datawarehouse

There are some source systems or an application that lacks the interface needed to extract data for ETL Processing .In that case we cannot directly extract from the source.What we can  do is to take copy of the source data and dump into flat file. This file will give a snapshot of the source system and hence is called Snapshot Sources.

Snapshot Source provides the snapshot of the Data at the time of data extraction. We can track the change of data by comparing successive snapshots. This approach is also known as snapshot differential.

Pipeline Partitioning in Informatica

Pipeline Partitioning a mapping in Informatica actually means to Partition the pipeline inside the mapping. Hope you all are aware of the various Partitions available in Oracle, if not please check about Partitions in ORACLE.

A pipeline will contain the source, transformations and targets. Mapping will be subdivided into many pipelines, these are called stage pipeline .After enabling partitioning in stage pipeline when we run the Integration service it runs pretty faster and we can expect more performance. The Integration service will run the partition threads concurrently.

Now it’s about how to enable partition and where to enable partition

• Set the partition points in the mapping
• Set the number of partitions
• Set the partition types

Partition points marks the boundaries that divide the pipeline into stages. By default Integration Service will keep partition at various transformations. Partition points mark the points in the pipeline where the Integration Service can redistribute data across partitions.

When we give Partition points it actually creates various stage pipelines inside the mapping. Now we need to decide how many partitions we need to have inside one stage pipeline. When we add more partitions it increases the performance of Informatica. You can define up to 64 partitions at any partition point in a pipeline.Also note that if we define two Partitions at any partition point, then the remaining partition points will also have two partitions.

 

By default Integration service will create a default Partition type for each partition point. Also we have the option of explicitly giving the Partitioning Type. The type of partition decides how Integration service will distribute the data across components. Below are the various Partitions available:

• Database partitioning
• Hash user keys
• Hash auto-keys
• Key range
• Pass-through
• Round-robin

Regression Testing Phase in ETL Testing

The steps are,

• Ensure that current functionality stays intact whenever new code is released

Performance Testing Phase in ETL Testing

The steps are,

• Verify that data loads and queries are executed within anticipated time frames
• Verify that maximum anticipated volume of data is loaded within an acceptable time frame
• Verify load times with various amounts of data to predict scalability

User Acceptance Testing(UAT) Phase in ETL Testing

The steps are,

• Verify that the business rules have been met
• Confirm that the system is acceptable to the client

Report Testing Phase in ETL Testing

The steps are,

• Verify report data with the data source
• Create SQL queries to verify source/target data
• Verify field-level data