All about Informatica and Datawarehousing

Mapping Optimization Techniques

2009-05-05T23:01:00.000-07:00

1. Reduce the number of transformations. There is always overhead involved in moving data between transformations.

2. Consider more shared memory for large number of transformations. Session shared memory between 12MB and 40MB should suffice.

3. Calculate once, use many times.

o Avoid calculating or testing the same value over and over.

o Calculate it once in an expression, and set a True/False flag.

o Within an expression, use variable ports to calculate a value than can be used multiple times within that transformation.

4. Only connect what is used.

o Delete unnecessary links between transformations to minimize the amount of data moved, particularly in the Source Qualifier.

o This is also helpful for maintenance. If a transformation needs to be reconnected, it is best to only have necessary ports set as input and output to reconnect.

o In lookup transformations, change unused ports to be neither input nor output. This makes the transformations cleaner looking. It also makes the generated SQL override as small as possible, which cuts down on the amount of cache necessary and thereby improves performance.

5. Watch the data types.

o The engine automatically converts compatible types.

o Sometimes data conversion is excessive. Data types are automatically converted when types are different between connected ports. Minimize data type changes between transformations by planning data flow prior to developing the mapping.

6. Facilitate reuse.

o Plan for reusable transformations upfront.

o Use variables. Use both mapping variables as well as ports that are variables. Variable ports are especially beneficial when they can be used to calculate a complex expression or perform a disconnected lookup call only once instead of multiple times

o Use mapplets to encapsulate multiple reusable transformations.

o Use mapplets to leverage the work of critical developers and minimize mistakes when performing similar functions.

7. Only manipulate data that needs to be moved and transformed.

o Reduce the number of non-essential records that are passed through the entire mapping.

o Use active transformations that reduce the number of records as early in the mapping as possible (i.e., placing filters, aggregators as close to source as possible).

o Select appropriate driving/master table while using joins. The table with the lesser number of rows should be the driving/master table for a faster join.

8. Utilize single-pass reads.

o Redesign mappings to utilize one Source Qualifier to populate multiple targets. This way the server reads this source only once. If you have different Source Qualifiers for the same source (e.g., one for delete and one for update/insert), the server reads the source for each Source Qualifier.

o Remove or reduce field-level stored procedures.

o If you use field-level stored procedures, the PowerCenter server has to make a call to that stored procedure for every row, slowing performance.

What are the types of Facts?

2009-05-05T14:48:00.000-07:00

Types of Facts

There are three types of facts:

Additive: Additive facts are facts that can be summed up through all of the dimensions in the fact table.
Semi-Additive: Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.

Let us use examples to illustrate each of the three types of facts. The first example assumes that we are a retailer, and we have a fact table with the following columns:

Date

Store

Product

Sales_Amount

The purpose of this table is to record the sales amount for each product in each store on a daily basis. Sales_Amount is the fact. In this case, Sales_Amount is an additive fact, because you can sum up this fact along any of the three dimensions present in the fact table -- date, store, and product. For example, the sum of Sales_Amount for all 7 days in a week represent the total sales amount for that week.

Say we are a bank with the following fact table:

Date

Account

Current_Balance

Profit_Margin

The purpose of this table is to record the current balance for each account at the end of each day, as well as the profit margin for each account for each day. Current_Balance and Profit_Margin are the facts. Current_Balance is a semi-additive fact, as it makes sense to add them up for all accounts (what's the total current balance for all accounts in the bank?), but it does not make sense to add them up through time (adding up all current balances for a given account for each day of the month does not give us any useful information). Profit_Margin is a non-additive fact, for it does not make sense to add them up for the account level or the day level.

Types of Fact Tables

Based on the above classifications, there are two types of fact tables:

Cumulative: This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative 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.

What is a Degenerated Dimension(DD)?

2009-05-05T14:47:00.001-07:00

An item that is in the fact table but is stripped off of its description, because the description belongs in dimension table, is referred to as Degenerated Dimension. Since it looks like dimension, but is really in fact table and has been degenerated of its description, hence is called degenerated dimension. Now coming to the slowly changing dimensions (SCD) and Slowly Growing Dimensions (SGD): I would like to classify them to be more of an attributes of dimensions its self. Although other might disagree to this view but Slowly Changing Dimensions are basically those dimensions whose key value will remain static but description might change over the period of time. For example, the product id in a companies, product line might remain the same, but the description might change from time to time, hence, product dimension is called slowly changing dimension. Lets consider a customer dimension, which will have a unique customer id but the customer name (company name) might change periodically due to buy out / acquisitions, Hence, slowly changing dimension, as customer number is static but customer name is changing, However, on the other hand the company will add more customers to its existing list of customers and it is highly unlikely that the company will acquire astronomical number of customer over night (wouldn’t the company CEO love that) hence, the customer dimension is both a Slowly changing as well as slowly growing dimension. Another definition of Degenerated Dimension: A fact or "degenerate" dimension has a one-to-one relationship with the facts, such as a transaction number. The degenerate dimension is not used for analysis as such, but rather for identification. For example, to locate a specific transaction or to identify the transactions that make up an aggregate cell.

What is a Junk Dimension(JD)?

2009-05-05T14:45:00.000-07:00

When you consolidate lots of small dimensions and instead of having 100s of small dimensions, that will have few records in them, cluttering your database with these mini ‘identifier’ tables, all records from all these small dimension tables are loaded into ONE dimension table and we call this dimension table Junk dimension table. (Since we are storing all the junk in this one table) For example: a company might have handful of manufacture plants, handful of order types, and so on, so forth, and we can consolidate them in one dimension table called junked dimension table

What is a Conformed Dimensions (CD)?

2009-05-05T14:29:00.000-07:00

These dimensions are something that is built once in your model and can be reused multiple times with different fact tables. For example, consider a model containing multiple fact tables, representing different data marts. Now look for a dimension that is common to these facts tables. In this example let’s consider that the product dimension is common and hence can be reused by creating short cuts and joining the different fact tables.Some of the examples are time dimension, customer dimensions, product dimension.

When do u we use dynamic cache and when do we use static cache in an connected and unconnected lookup transformation?

2009-05-05T14:28:00.000-07:00

We use dynamic cache only for connected lookup. We use dynamic cache to check whether the record already exists in the target table are not. And depending on that, we insert,update or delete the records using update strategy. Static cache is the default cache in both connected and unconnected. If u select static cache on lookup table in infa, it own't update the cache and the row in the cache remain constant. We use this to check the results and also to update slowly changing records

Why sorter transformation is an active transformation?

2009-05-05T14:22:00.000-07:00

This is type of active transformation which is responsible for sorting the data either in the ascending order or descending order according to the key specifier. the port on which the sorting takes place is called as sortkeyport. As we have a DISTINCT option to process the distinct records, we can call the Sorter Transformation as an ACTIVE transformation

Properties:

if u select distinct eliminate duplicates

case sensitive valid for strings to sort the data
null treated low null values are given least priority

What are the mappings that we use for slowly changing dimension table?

2009-05-05T14:21:00.000-07:00

Type1: Rows containing changes to existing dimensions are updated in the target by overwriting the existing dimension. In the Type 1 Dimension mapping, all rows contain
current dimension data.
Use the Type 1 Dimension mapping to update a slowly changing dimension table when you do not need to keep any previous versions of dimensions in the table.

Type 2: The Type 2 Dimension Data mapping inserts both new and changed dimensions into the target. Changes are tracked in the target table by versioning the primary
key and creating a version number for each dimension in the table.
Use the Type 2 Dimension/Version Data mapping to update a slowly changing dimension table when you want to keep a full history of dimension data in the table. Version numbers and versioned primary keys track the order of changes to each dimension.

Type 3: The Type 3 Dimension mapping filters source rows based on user-defined comparisons and inserts only those found to be new dimensions to the target. Rows
containing changes to existing dimensions are updated in the target. When updating an existing dimension, the Informatica Server saves existing data in different columns
of the same row and replaces the existing data with the updates

What are the different types of Type2 dimension mapping?

2009-05-05T14:15:00.000-07:00

Type2 Dimension/Version Data Mapping: In this mapping the updated dimension in the source will gets inserted in target along with a new version number. Newly added
dimension in source will inserted into target with a primary key.

Type2 Dimension/Flag current Mapping: This mapping is also used for slowly changing dimensions.In addition it creates a flag value for changed or new dimension.

Flag indiactes the dimension is new or newlyupdated.Recent dimensions will gets saved with cuurent flag value 1. And updated dimensions are saved with the value 0.

Type2 Dimension/Effective Date Range Mapping: This is also one flavour of Type2 mapping used for slowly changing dimensions.This maping also inserts both new and
changed dimensions in to the target. And changes are tracked by the effective date range for each version of each dimension.

How do we do unit testing in informatica?

2009-05-05T14:12:00.000-07:00

1. Quantitaive testing
2.Qualitative testing

Steps:

Quantitative testing.:
1.First validate the mapping
2.Create session on themapping and then run workflow.

Once the session is succeeded the right click on session and go for statistics tab.
There you can see how many number of source rows are applied and how many number of rows loaded in to targets and how many number of rows rejected.This is called Quantitative testing.

If once rows are successfully loaded then we will go for qualitative testing.

Qualitative testing:
Steps

1.Take the DATM(DATM means where all business rules are mentioned to the corresponding source columns) and check whether the data is loaded according to the DATM in to target table.If any data is not loaded according to the DATM then go and check in the code and rectify it.

This is called Qualitative testing. This is what a devloper will do in Unit Testing.

What is incremantal aggregation?

2009-05-05T14:11:00.001-07:00

When using incremental aggregation, you apply captured changes in the source to aggregate calculations in a session. If the source changes only incrementally and you
can capture changes, you can configure the session to process only those changes. This allows the Informatica Server to update your target incrementally, rather than
forcing it to process the entire source and recalculate the same calculations each time you run the session.

Staging Area and its Importance

2009-04-30T08:30:00.000-07:00

Staging Area: Most Important Component or Overkill? Designing the physical path of data movement from transaction databases into a data mart or warehouse can range from very simple to very complex. In an environment where there are one or two homogeneous sources and a single data mart, introducing an intermediate “staging area” (also called an “operational data store”) may add little value. However, as the complexity increases—by involving multiple database types or different platforms for different sources (e.g., ERP data on Unix, historical data on MVS, a data mart on Windows NT)—the need for a staging area to integrate all data types into one format becomes apparent, even critical.

For organizations with heterogeneous sources, or where multiple data marts are supported from a common set of data sources, the staging area becomes the first level data warehouse. This “atomic” data warehouse is a place to clean and synchronize all sources and organize the elements to support the downstream data warehouse or data mart environment. Here, data can automatically be loaded and undergo data type adjustment, freeing the database administrators from writing and maintaining a number of complicated programs.Tapestry by D2K, Inc.™ explicitly supports and leverages the staging area concept. Following are some key reasons why an organization might incorporate a relational staging area into its data warehouse architecture.

A staging area allows the process of converting data to be separate from transforming data.

For example, if sales data must be joined with human resources data, each will be extracted and moved independently to the intermediate relational database; the administrator performs the join there, before populating the data mart tables. In this way, differences between the data sources can be resolved in the staging area before applying the transformations in the data mart. The staging area facilitates building data marts with the least amount of complexity and the greatest amount of transparency for the user.

A staging area facilitates moving data from different sources on different schedules.

Different sources may need to be extracted at different times. For example, the sales data may be available (i.e., sales processing is finished and the data is in a stable and synchronized state) for extract only between 1:00 a.m. and 2:00 a.m., while the financial data is available only from 4:00 a.m. until 5:00 a.m. A staging area would allow the administrator to extract the data during its available times and hold it for integration after the fact. It facilitates scheduling extractions from their operational systems during off-peak periods.

A relational staging area provides a place to check data cleanliness and correctness.

The staging area is the only place to determine which values actually came out of the legacy system. The data warehouse administrator has the opportunity to investigate the staging area to determine where the error occurred (e.g., there was an error in the business rules, or the data was incorrect when it came from the legacy system). The staging area can be used for cleaning the dirty data before it goes into the transformation process—the sooner data is cleansed, the less chance there is for error.

Staging areas, as the first data warehouse tier, are one of the components that differentiate D2K’s solution from others in the marketplace. The Tapestry staging area is the hub in the “hub-and-spoke” model. As the hub, the spokes are able to have data integrated and transformed in various ways, all from one atomic level. The hub-and-spoke model delivers flexibility, scalability, integrity, and performance by extracting data once for customization at the user level.

Suggestions for Informatica Aspirants

2009-04-29T13:00:00.000-07:00

Informatica is a promising etl tool which is easy to understand but needs good amount of real time knowledge to work on. For a developer level, we shouldn't bother about the Informatica architectureto a large extent but we should have a good understanding of different options available in Mapping designer,Mappletdesigner,transformation developer and Repository manager,task developer,workflow manager.But for Admin level, we should be having a clear picture on how exactly the Informatica server communicates with the repository server and also the communication between Load Manager and DTM