DataWareHousing Tools

Health insurance Schema

2008-05-14T23:10:00.000-07:00

Health insurance database

This example illustrates a star schema used for claims analysis by a health care insurance company. This database records policy sales and claims and maintains records of customers, their policies, and claims against those policies.

Schema for health insurance company

A second example in the health care field illustrates a schema that tracks member claims and authorizations, with many dimensions that include patients and provider information, diagnoses, services performed, and other dimensions of the business.

This schema has two fact tables: Claim, a star, and Authorization, a multistar with a single-column primary key and multiple foreign keys that are not part of the primary key. Any combination of foreign key values can be present multiple times in the Authorization table. The primary key values uniquely identify each row. Both tables have many foreign keys that reference the numerous dimension tables.

In this figure, the primary keys for each table are in boldface, and only the primary keys and foreign keys are labeled. Other attributes within the tables are not shown. Because of the many dimension tables, the many-to-one lines that match the foreign keys in the fact table with the primary keys in the dimension tables are not drawn.

Multistar schema for health insurance company with multiple dimension tables

2008-04-11T23:56:00.000-07:00

OLAP - Examples:

Topmost executives of an organization are really interested in aggregated facts or numbers to take decisions rather than querying several databases (that are normalized) to get the data and do the comparison by themselves.

OLAP tools visualize the data in an understandable format, like in the form of Scorecards and Dashboards with Key Performance Indicators enabling managers to monitor and take immediate actions.

In todays business life, OLAP plays a vital role by assisting decision makers in the field of banking and finance, hospitals, insurance, manufacturing, pharmaceuticals etc., to measure facts across geography, demography, product, and sales.

OLAP can be performed in data warehouses that undergo frequent updates and that do not. Following are some of the examples to show how OLAP solves complex queries involving facts to be measured across company’s best-interested dimensions.

Comparison of sales (fact) of a product (dimension) over years (dimension) in the same region (dimension).

How may members (fact) have opened a savings account (dimension), in USA branch (dimension), over a period (dimension)?

How many mortgage loans (fact) have been approved in fixed mortgage (dimension) or Adjustable Rate Mortgage (dimension) in New York City (dimension), over a period (dimension)?

What is the total sales value (fact) of a particular product (dimension) in a particular grocery store (dimension), over a period (dimension)?

What is the amount spent (fact) for a particular product promotion (dimension) in a particular branch (dimension) or in a particular city (dimension), over a period (dimension)?

2008-04-11T23:52:00.000-07:00

What is OLAP?

OLAP, an acronym for 'Online Analytical Processing' is a technique by which the data sourced from a data warehouse or data mart is visualized and summarized to provide perspective multidimensional view across multiple dimensions. Generally OLAP refers to OLAP Tools(e.g Cognos, Business Objects etc.,) that help to accomplish these tasks.

Since data warehouse is designed using a dimensional data model, data is represented in the form of data cubes enabling us to aggregate facts, slice and dice across several dimensions. OLAP tools provide options to drill-down the data from one hierarchy to another hierarchy.

For example sales amount can be calculated for a particular year or it can be drilled down to its next hierarchies like month, week, day etc. In the same way, data can be rolled up for summarization from product to product group, product group to product sub-class then from product sub-class to product class.

Thus with this cube structure, data can be viewed from multiple points providing the data analysts, a greater insight into data.

There are many OLAP hybrids or variants like MOLAP(Multidimensional OLAP), HOLAP(Hybrid OLAP), ROLAP(Relational OLAP), DOLAP(Desktop OLAP or Database OLAP) available in the market and can be used depending on the needs and requirements of an organization.

STAR SCHEMA

2008-03-16T13:52:00.000-07:00

Star Schema Modelling

A Dimensional modelling technique in which a detail fact table is linked to dimension tables. The data in data warehouses and data marts is accessed by end-users.

The information contained in the data warehouse/data mart must be easy for the end-user to use and access. Denormalized designs are easier for end-users to use than highly normalized designs, however these designs are more difficult to design and maintain.

The Star Schema diagram graphically models the end-user's view (i.e., the denormalized view) of how the information is accessed.

Components of a Star Schema Diagram

The diagram has three main components:

· Fact Table and its contents: metric attributes and the foreign keys necessary to join to the dimension tables,

· Dimension Tables and their contents: reference attributes, hierarchical attributes, and metric attributes. The dimension tables are highly denormalized,

· The lines that link the Dimension Tables to the Fact Table.

Star Schema Model

This diagram is a model of a star schema diagram.

Star Schema Example

The following is an example of a star schema for sales items.

OLAP

2008-03-16T08:21:00.000-07:00

OLAP & its Hybrids

OLAP, an acronym for Online Analytical Processing is an approach that helps organization to take advantages of DATA. Popular OLAP tools are Cognos, Business Objects, Micro Strategy etc. OLAP cubes provide the insight into data and helps the topmost executives of an organization to take decisions in an efficient manner.

Technically, OLAP cube allows one to analyze data across multiple dimensions by providing multidimensional view of aggregated, grouped data. With OLAP reports, the major categories like fiscal periods, sales region, products, employee, promotion related to the product can be ANALYZED very efficiently, effectively and responsively. OLAP applications include sales and customer analysis, budgeting, marketing analysis, production analysis, profitability analysis and forecasting etc.

ROLAP

ROLAP stands for Relational Online Analytical Process that provides multidimensional analysis of data, stored in a Relational database(RDBMS).

MOLAP

MOLAP(Multidimensional OLAP), provides the analysis of data stored in a multi-dimensional data cube.

HOLAP

HOLAP(Hybrid OLAP) a combination of both ROLAP and MOLAP can provide multidimensional analysis simultaneously of data stored in a multidimensional database and in a relational database(RDBMS).

DOLAP

DOLAP(Desktop OLAP or Database OLAP)provide multidimensional analysis locally in the client machine on the data collected from relational or multidimensional database servers.

2008-01-27T10:59:00.000-08:00

Sharing the information

You can quickly and easily share the documents you have created with otherusers in your company, either by sending them directly to selected individuals or groups, or by publishing them as corporate documents. When you distribute documents in these different ways, you use the BUSINESSOBJECTS repository.

The repository stores the documents you send so that other users can retrieve and view them. It also stores information about the documents it stores, such as name of sender, date and time, and also which users in the company have the right to retrieve and view a document.

You can retrieve documents that other users have sent, including WEBINTELLIGENCE and BUSINESSQUERY documents which you can open and view in BUSINESSOBJECTS. You can also use BROADCAST AGENT to send documents for scheduled processing.

Note: Sending, Retrieving, Printing, and Publishing and Scheduling documents, can be done using InfoView an aditional tool provided by the Business Object.

2008-01-27T10:52:00.000-08:00

Presenting and analyzing data

Once you have the data you need, you can present it in a number of ways. You can present it in a simple table Or create sophisticated reports containing large amounts of data, organized and formatted to make it easy for people to go directly to the information that interests them.,,

You can add images and embedded objects and format your documents to high presentation standards for viewing on screen or for printing.

On-report analysis allows you to switch your business perspective by dragging and dropping data, insert on-report calculations or drill into a report for detailed information:

OLAP

2008-01-27T10:28:00.000-08:00

OLAP Database - Multidimensional

This is a type of database that is optimized for data warehouse, data mart and online analytical processing (OLAP) applications. The main advantage of this database is query performance.

Relational databases make it easy to work with individual records, whereas multidimensional databases are designed for analyzing large groups of records. Relational database is typically accessed using a Structured Query Language (SQL) query.

A multidimensional database allows a user to ask questions like "How many mortgages have been sold in New Jersey city" and "How many credit cards have been purchased in a particular county?".

Popular Multidimensional Databases

Database Name	Company Name
Crystal Holos	Business Objects
Hyperion Essbase	Hyperion
Oracle Express	Oracle Corporation
Oracle OLAP Option	Oracle Corporation
AWMicrosoft Analysis Services	Microsoft
PowerPlay Enterprise	Cognos

OLAP

2008-01-27T10:23:00.000-08:00

OLAP Analysis

Imagine an organization that manufactures and sells goods in several States of USA which employs hundreds of employees in its manufacturing, sales and marketing division etc. In order to manufacture and sell this product in profitable manner, the executives need to analyse(OLAP analysis) the data on the product and think about various possibilities and causes for a particular event like loss in sales, less productivity or increase in sales over a particular period of the year.

During the OLAP analysis, the top executives may seek answers for the following:

1. Number of products manufactured.

2. Number of products manufactured in a location.

3. Number of products manufactured on time basis within a location.

4. Number of products manufactured in the current year when compared to the previous year.

5. Sales Dollar value for a particular product.

6. Sales Dollar value for a product in a location.

7. Sales Dollar value for a product in a year within a location.

8. Sales Dollar value for a product in a year within a location sold or serviced by an employee.
OLAP tools help executives in finding out the answers, not only to the above mentioned measures, even for the very complex queries by allowing them to slice and dice, drill down from higher level to lower level summarized data, rank, sort, etc.

Example of OLAP Analysis Report

Time Dimension Id	Location Dimension Id	Product Dimension Id	Organization Dimension Id	Sales Dollar	DateTimeStamp
1	1	100001	1	1000	1/1/2005 11:23:31 AM
3	1	100001	1	750	1/1/2005 11:23:31 AM
1	1	100001	2	1000	1/1/2005 11:23:31 AM
3	1	100001	2	750	1/1/2005 11:23:31 AM

In the above example of OLAP analysis, data can be sliced and diced, drilled up and drilled down for various hierarchies like time dimension, location dimension, product dimension, and organization dimension .

This would provide the topmost executives to take a decision about the product performance in a location/time/organization.

In OLAP reports, Trend analysis can be also made by comparing the sales value of a particular product over several years or quarters.

OLAP

2008-01-27T10:18:00.000-08:00

OLAP & its Hybrids

ROLAP

ROLAP stands for Relational Online Analytical Process that provides multidimensional analysis of data, stored in a Relational database(RDBMS).

MOLAP

MOLAP(Multidimensional OLAP), provides the analysis of data stored in a multi-dimensional data cube.

HOLAP

HOLAP(Hybrid OLAP) a combination of both ROLAP and MOLAP can provide multidimensional analysis simultaneously of data stored in a multidimensional database and in a relational database(RDBMS).

DOLAP

DOLAP(Desktop OLAP or Database OLAP)provide multidimensional analysis locally in the client machine on the data collected from relational or multidimensional database servers.

METADATA

2008-01-27T09:40:00.000-08:00

Metadata Tools

Metadata is data about data and Metadata tools are used for gathering, storing, updating, and for retrieving the business and technical metadata of an organization.

Data and Metadata

Data - The actual data that is stored in the database.

Business Metadata - The information of the data related with business and this data is used by functional team (business analysts, smart management experts, business managers etc).

Technical Metadata - The information about the data related with technology and this data is used by the technical team (Developers, DBAs) etc.

Popular Metadata Tools

Tool Name	Company Name
Rochade	ASG
Metatrieve	Metatrieval
Datamapper	Exeros
Metacenter	Data Advantage Group
Meta Data Integration Framework	Info Librarian
SuperGlue	Informatica
Metastage	Ascential

Frequently Asked Questions

2008-01-27T07:33:00.000-08:00

>>Previous>>

1) What are the types of lookup cache?

Persistent cache: U can save the look up cache files and reuse them the next time the informatica server processes a lookup transformation to use the cache.

Static cache: U can configure a static or read-only lookup table.By default informatica server creates a static cache.It caches the lookup table and lookup values in the cache for each row that comes into the transformation.When the lookup condition is true the inforamtica server does not update the cache while it processes the lookup transformation.

Dynamic cache: If you want to cache the target table and insert new rows into cache and the target you can create a look up transformation to use dynamic cache.The informatica server dynamically inserts data into the target table.

Shared cache: You can share the lookup cache between multiple transformations.You can share unnamed cache between transformation in the same mapping.

2) Difference between static cache and dynamic cache?

Static cache	Dynamic cache
You cannot insert or update the cache	You can insert rows into the cache as you pass rows to the target
The informatica server returns a value from the lookup table or cache when the condition is true,.When the condition is true the informatica server returns the default value for connected transformation	The informatica server inserts rows into the cache when the condition is false.This indicates that the row in the cache or target table.You can pass these rows to the target table.

>>>Next>>>

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13 FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

Frequently Asked Questions

2008-01-27T07:21:00.000-08:00

>>Previous>>

1) Difference between connected and unconnected transformations?

Connected transformation is connected to another transformation with in a mapping.

Unconnected transformation is not connected to any transformation with in a mapping.

2) In which conditions we cannot use joiner transformation(limitations of joiner transformation)?

Both pipelines begin with the same original data source.

Both input pipelines originate from the same source qualifier transformation.

Both input pipelines originate from the same normalizer transformation

Both input pipelines originate from the same joiner transformation.

Either input pipelines contains an update strategy transformation

Either input pipelines contains sequence generator transformation.

3) what are the settings that u use to configure the joiner transformation?

Master and detail source.

Type of join

Condition of the join

4) what is look up transformation?

look up transformation can be used in a table view based on condition by default lookup is left outer join.

5) why use the lookup transformation?

To perform the following tasks.

Get a related value.For example if your table includes employee ID,but you want to include such as gross sales per invoice or sales tax but not the calculated value(such as net sales).

Update slowly changing dimension tables. You can use a lookup transformation to determine whether records already exist in the target.

6) What are the types of lookup?

Connected and unconnected

7) Difference between connected and unconnected lookup?

Connected lookup	Unconnected lookup
Receives input values directly from the pipe line.	Receives input values from the result of a clkp expression in a another transformation.
U can use a dynamic or static Cache	U can use a static cache
Cache includes all lokkup columns used in the mapping(that is lookup table columns included in the lookup condition and lookup table columns linked as output ports to other transformations)	Cache includes all lookup/output ports in the lookup condition and the lookup/return port.
Can return multiple columns from the same row or insert into the dynamic lookup cache.	Designate one return port(R).Returns one column from each row.
If there is no match for the lookup condition, the informatica server returns the default value for all output ports.If u configure dynamic caching the informatica server inserts rows into the cache.	If there is no matching for the lookup condition the informatica server returns NULL
Pass multiple output values to another transformatnion.Link lookup/output ports to another transformation	Pass one output value to another transformation.The lookup/output/return port passes the same value to the ---------------------------------------------------------

Supports user-defined default values.

Does not support user-defined default values.

8)explain index cache and data cache?

The informatica server stores conditions values in the index cache and output values in the data cache.

>>>Next>>>

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13 FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

Frequently Asked Questions

2008-01-27T06:47:00.000-08:00

>>Previous>>

1) What is a constraint based loading?

When you select this option the power center server orders the target load on a row-by-row basis only.

Edit tasks->properties->select treat source rows as insert.

Edit tasks->config object tab->select constraint based.

If session is configured constraint absed loading when target table receive rows from different sources.The power center server revert the normal loading for those tables but loads all other targets in the session using constraint based loading when possible loading the primary key table first then the foreign key table.

Use the constraint based loading only when the session option treat rows as set to insert.

Constraint based load ordering functionality which allows developers to read the source once and populate parent and child tables in a single process.

2) Explain incremental aggregation?

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 power center server to update your target incrementally rather than forcing it to process the entire source and recalculate the same data each time you run the session.

You can capture new source data.use incremental aggregation when you can capture new source data much time you run the session.Use a stored procedure on filter transformation only new data.

Incremental changes do not significantly change the target.Use incremental aggregation when the changes do not significantly change the target.If processing the incrementally changed source alters more than half the existing target, the session may not benefit from using incremental aggregation. In this case drop the table and recreate the target with complete source data.

3) Processing of incremental aggregation?

The first time u run an incremental aggregation session the power center server process the entire source.At the end of the session the power center server stores aggregate data from the session runs in two files, the index file and the data file .The power center server creates the files in a local directory.

Transformations

Q. what is transformation?

Transformation is repository object that generates modifies or passes data.

Transformation is an object which is used for processing or trasforming the data or converting the data into the required business format.

4) what are the type of transformations?

The Transformation which has developed will be stored in the Repository. There are types of trasformations:

1) active
2) passive.

Active transformation can change the number of rows that pass through it.No of output rows less than or equal to no of input rows.

The Following are the Active Transformations--

1) source qualifier Transformation

2) Filter Transformation

3) Joiner Transformation

4) Rank Transformation

5) Router Transformation

6) Aggregator Transformation

7) Normalizer Transformation

8) UpdateStrategy Transformation

9) Sortor Transformation

10) Union Transformation

11) Transaction Control Transformation

Passive transformation does not change the number of rows.Always no of output rows equal to no of input rows.

The Following are the Passive Transformations---

1) Expression Transformation

2) Sequence Generator Transformation

3) Stored Procedure Transformation

4) XML Source Qualifier Transformation

5) LookUp Transformation

5) Difference filter and router transformation.

Filter transformation to filter the data only one condition and drop the rows don’t meet the condition.

Drop rows does not store anywhere like session log file..

Router transformation to filter the data based on multiple conditions and give yiou the option to route rows that don’t match to a default group.

6) what r the types of groups in router transformation?

Router transformation 2 groups ---

1. Input group

2. output groups.

Output groups in 2 types. ---

1. user defined group

2. default group.

7) Difference between expression and aggregator transformation?

Expression transformation calculate the single row values before writes the target.Expression transformation executed by row-by-row basis only.

Aggregator transformation allows you to perform aggregate calculations like max, min,avg…

Aggregate transformation perform calculation on groups.

8) How can u improve the session performance in aggregate transformation?

Use stored input.

9) what is aggregate cache in aggregate transformation?

The aggregate stores data in the aggregate cache until it completes aggregate calculations.When u run a session that uses an aggregate transformation , the informatica server creates index and data caches in memory is process the transformation. If the informatica server requires more space it seores overview values in cache files.

10) Explain joiner transformation?

Joiner transformation joins two related heterogeneous sources residing in different locations or files.

The types of joins in joiner in the joiner transformation--

Normal

Master outer

Detail outer

Full outer

>>>Next>>>

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13 FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

Frequently Asked Questions

2008-01-27T05:22:00.001-08:00

>>Previous>>

1) what is a server?

The power center server moves data from source to targets based on a workflow and mapping Metadata stored in a repository.

2) what is a work flow?

A workflow is a set of instructions that describe how and when to run tasks related to extracting,transformation and loading data.

3) what is session?

A session is a set of instructions that describes how to move data from source to target using a mapping.

4) What is workflow monitor?

Use the work flow monitor work flows and stop the power center server.

5) Explain a work flow process?

The power center server uses both process memory and system shared memory to perform these tasks.

Load manager process: stores and locks the workflow tasks and start the DTM run the sessions.

Data Transformation Process DTM: Perform session validations,create threads to initialize the session,read,write and transform data, and handle pre and post session operations.

The default memory allocation is 12,000,000 bytes.

6) What are types of threads in DTM?

The main DTM thread is called the master thread.

Mapping thread.

Transformation thread.

Reader thread.

Writer thread.

Pre-and-post session thread.

7) Explain work flow manager tools?

1) Task developer.

2) Work flow designer.

3) Worklet designer.

8) Explain work flow schedule?

You can sehedule a work flow to run continuously, repeat at given time or interval or you manually start a work flow.By default the workflow runs on demand.

9) Explain stopping or aborting a session task?

If the power center is executing a session task when you issue the stop the command the power center stop reading data. If continuous processing and writing data and committing data to targets.

If the power center can’t finish processing and committing data you issue the abort command.

You can also abort a session by using the Abort() function in the mapping logic.

10) What is a worklet?

A worklet is an object that represents a set of taske.It can contain any task available in the work flow manager. You can run worklets inside a workflow. You can also nest a worklet in another worklet.The worklet manager does not provide a parameter file for worklets.

The power center server writes information about worklet execution in the workflow log.

11) what is a commit interval and explain the types?

A commit interval is the interval at which power center server commits data to targets during a session. The commit interval the number of rows you want to use as a basis for the commit point.

Target Based commit: The power center server commits data based on the number of target rows and the key constraints on the target table. The commit point also depends on the buffer block size and the commit interval.

Source-based commit:---------------------------------------------

User-defined commit:----------------------------------------------

12) Explain bulk loading?

You can use bulk loading to improve performance of a session that inserts a large amount of data to a db2,sysbase,oracle or MS SQL server database.

When bulk loading the power center server by passes the database log,which speeds performance.

With out writing to the database log, however the target database can’t perform rollback.As a result you may not be perform recovery.

>>>Next>>>

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13 FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

Frequently Asked Questions

2008-01-27T05:07:00.000-08:00

>>Previous>>

1) can you connect more than one group to the same target or transformation?

NO

2) what is a reusable transformation?

Reusable transformation can be a single transformation.This transformation can be used in multiple mappings.when you need to incorporate this transformation into mapping you add an instance of it to mapping.Later if you change the definition of the transformation, all instances of it inherit the changes.Since the instance of reusable transformation is a pointer to that transformation.U can change the transformation in the transformation developer, its instance automatically reflect these changes. This feature can save U great deal of work.

The methods for creating reusable transformation

Two methods

1) Design it in the transformation developer.

2) Promote a standard transformation from the mapping designer.After you add a transformation to the mapping, you can promote it to status of reusable transformation.

Once you promote a standard transformation to reusable status, you can demote it to a standard transformation at any time.

If you change the properties of a reusable transformation in mapping , you can revert it to the original reusable transformation properties by clicking the revert.

3) what are mapping parameters and mapping variables?

Mapping parameter represents a constant value that you can define before running a session.A mapping parameter retains the same value throughout the entire session.

When you use the mapping parameter , you declare and use the parameter in a Mapping or Mapplet.Then define the value of parameter in a parameter file for the session.

Unlike a mapping parameter, a mapping variable represents a value that can change through out the session. The informatica server save the value of mapping variable to the repository at the end of session run and uses that value next time you run the session.

4) can you use the mapping parameters or variables created in one mapping into another mapping?

NO, we can use mapping parameters or variables in any transformation of the same mapping or mapplet in which have crated mapping parameters or variables.

5) Can you Use the mapping parameters or variables created in one mapping into any other result transformation?

Yes because the reusable transformation is not contained with any mapplet or mapping.

6) How the informatica server sorts the string values in rank transformation?

When the informatica server runs in the ASCII data movement mode it sorts session data using binary sort order.If you configures the session to use a binary sort order, the informatica server calculates the binary value of each string and returns the specified number of rows with the highest binary values for the string.

7) What is the rank index in rank transformation?

The designer automatically creates a RANKINDEX port for each Rank transformation. The informatica server uses the Rank Index port to store the ranking position for each record in a group.For example, if you create a Rank transformation that ranks the top 5 sales persons for each quarter, the rank index number the salespeople from 1 to 5.

8) what is the mapplet?

Mapplet is a set of transformation that you build in the mapplet designer and you can use in multiple mappings.

9) Difference between mapplet and reusable transformation?

Reusable transformation can be a single transformation.Where as mapplet use multiple transformations.

10) what is a parameter a file?

Paramater file defines the values for parameter and variables.

>>>Next>>>

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13 FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

Frequently Asked Questions

2008-01-27T04:44:00.001-08:00

>>Previous>>

1) what is the target load order?

You specify the target load order based on source qualifiers in a mapping.if u have the multiple source qualifiers connected to the multiple targets you can designate the order in which informatica server loads data into the targets.

2) what is default join that source qualifier provides?

Inner equi join.

3) what are the difference between joiner transformation and source qualifier transformation?

You can join heterogeneous data sources in joiner transformation, which we cannot achive in source qualifier transformation.

You need matching keys to join two relational sources in source qualifier transformation.where you doesn’t need matching keys to join two sources.

Two relational sources should come from same data source in source qualifier.You can join relational sources, which are coming from different sources in source qualifier.You can join relational sources which are coming from different sources also.

4) what is update strategy transformation?

Whenever you create the target table whether you are store the historical data or current transaction data in to target table.

5) Describe two levels in which update strategy transformation sets?
1) Using Update strategy Trasformation in The Mapping
2) Using Target Table options at the Session.

6) what is default source option for update strategy transformation?

Data driven.

7) What is data driven?

The information server follows instructions coded into update strategy transformations with in the session mapping determine how to flag records for insert,update,delete or reject if u do not choose data driven option setting , the informatica server ignores all update strategy transformations in the mapping.

8) what are the options in the trarget session of update strategy transformation?

Insert

Delete

Update

Update as update

Update as insert

Update else insert

Truncate table.

9) Difference between the source filter and filter?

Source filter is filtering the data only relational sources. Where as filter transformation filter the data any type of source.

10) what is a tracing level?

Amount of information sent to log file.

Types of tracing levels:

Normal,Terse,verbose data,verbose intitialization.

11) Expalin sequence generator transformation?

This is of type passive Transformation which is use to generate sequence Numbers that is treated as Primary Key.

This Transformation is created with two default OutPuts.

1) Next Val
2)Curr Val

Here We can not create the new ports or Edit the existing Ports.

12) can you connect multiple ports from one group to multiple transformations?

Yes

>>>Next>>>

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13
FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

Frequently Asked Questions

2008-01-27T04:34:00.001-08:00

>>Previous>>

1) How many types of approaches in DWH?

Two approaches: Top-down(Inmol approach), Bottom-up(Ralph Kimball)

2) Explain Star Schema?

Star Schema consists of one or more fact table and one or more dimension tables that are related to foreign keys.

Dimension tables are De-normalized, Fact table-normalized.

Advantages: Less database space & Simplify queries.

3) Explain Snowflake schema?

Snow flake schema is a normalize dimensions to eliminate the redundancy.The dimension data has been grouped into one large table. Both dimension and fact tables normalized.

4) What is confirm dimension?

If both data marts use same type of dimension that is called confirm dimension.If you have same type of dimension can be used in multiple fact that is called confirm dimension.

5) Explain the DWH architectural Components?

1. Source data Component

a. Production data
b. External Data
c. Internal Data
d. Archived data

2. Data Staging Component

a. Data Extraction
b. Data Transformation
c. Data Loading

3. Data storage component

The physical DB

4.Information Delivery Component

a.Querying tools
b.Analytic tools
c.DataMining tools

6) What is a slowly growing dimension?

Slowly growing dimensions are dimensional data,there dimensions increasing dimension data with out update existing dimensions.That means appending new data to existing dimensions.

7) What is a slowly changing dimension?

Slowly changing dimension are dimension data,these dimensions increasing dimensions data with update existing dimensions.

Type1: Rows containing changes to existing dimensional are update in the target by overwriting the existing dimension.In the Type1 Dimension mapping, all rows contain current dimension data.

Use the type1 dimension mapping to update a slowly changing dimension table when you do not need to keep any previous versions of dimensions in the table.

Type2: The Type2 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 Type2 Dimension/version data mapping to update a slowly changing dimension 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.

Type3: The type 3 dimension mapping filters source rows based on user-defined comparisions 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.

8) When you use for dynamic cache.

Your target table is also look up table then you go for dynamic cache .In dynamic cache multiple matches return an error.use only = operator.

9) what is lookup override?

Override the default SQL statement.You can join multiple sources use lookup override.By default informatica server add the order by clause.

10) we can pass the null value in lookup transformation?

Lookup transformation returns the null value or equal to null value.

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13 FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

Frequently Asked Questions

2008-01-27T03:51:00.000-08:00

>>PreviousFaq>>

1) What is Data warehouse?

Data warehouse is relational database used for query analysis and reporting. By definition data warehouse is Subject-oriented, Integrated, Non-volatile, Time variant.

Subject oriented: Data warehouse is maintained particular subject.

Integrated: Data collected from multiple sources integrated into a user readable unique format.

Non volatile : Maintain Historical date.

Time variant : data display the weekly, monthly, yearly.

2) What is Data mart?

A subset of data warehouse is called Data mart.

3) Difference between Data warehouse and Data mart?

Data warehouse is maintaining the total organization of data. Multiple data marts used in data warehouse. where as data mart is maintained only particular subject.

4) Difference between OLTP and OLAP?

OLTP is Online Transaction Processing. This is maintained current transactional data. That means insert, update and delete must be fast.

OLAP is Online Analytical Processing.This is Used to Read the data and is more useful for the analysis.

5) Explain ODS?

Operational data store is a part of data warehouse. This is maintained only current transactional data. ODS is subject oriented, integrated, volatile, current data.

6) Difference between Power Center and Power Mart?

Power center receive all product functionality including ability to multiple register servers and Metadata across the repository and partition data.

One repository multiple informatica servers. Power mart received all features except multiple register servers and partition data.

7) What is a staging area?

Staging area is a temporary storage area used for transaction, integrated and rather than transaction processing.

When ever your data put in data warehouse you need to clean and process your data.

8) Explain Additive, Semi-additive, Non-additive facts?

Additive fact: Additive Fact can be aggregated by simple arithmetical additions.

Semi-Additive fact: semi additive fact can be aggregated simple arithmetical additions along with some other dimensions.

Non-additive fact: Non-additive fact can’t be added at all.

9) What is a Fact less Fact and example?

Fact table which has no measures or A table with out Facts is said to be as Fact less Fact Table.

10) Explain Surrogate Key?

Surrogate Key is a series of sequential numbers assigned to be a primary key for the table.

A surrogate key is an arbitrary value (GUID and IDENTITY types are frequently used) that is used in place of a natural or intelligent key. The choice may be one of performance or one of convenience. A degenerate key is usually a surrogate key and is used to replace primary key values from the source OLTP system since these values are not likely unique across multiple systems in an enterprise.

A GUID is a "globally unique identifier" -- a big huge 16 bit random number that's fairly certain to be unique. An IDENTITY is a seeded, sequential number that's unique because it's always one bigger than the previous one.

A surrogate key is a substitution for the natural primary key.

It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table.

Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys. They can use Infa sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key.

How are Surrogate Keys useful in DataWare House:

1.It is useful because the natural primary key (eg.,Customer Number in Customer table) can change and this makes updates more difficult.

2. Another usage is to track the Slowly changing dimensions.

>>>NextFaq>>>

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13 FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

Transformation Manager(TM)

2008-01-02T23:32:00.000-08:00

ETL Solution's product "Transformation Manager(TM)" is a stand-alone Windows® or Linux® software suite of meta-data driven code generator programs that provides for the authoring, testing, and debugging of data transforms between virtually all types of data, whether XML, databases, flat files, Java™ classes or spreadsheets, with flexible deployment options in both J2EE™ and Microsoft®.NET architectures

Transformation Manager(TM) - Contents:

1. EXECUTIVE SUMMARY

2. WHAT SOLUTIONS ARE AVAILABLE?

2.1. HAND CODING

2.2. ENGINE BASED ETL DATA INTEGRATION SOLUTION

2.3. DATABASE EMBEDDED ETL

2.4. CODE GENERATORS

3. OVERVIEW OF TRANSFORMATION MANAGER

4. KEY FEATURES OF TRANSFORMATION MANAGER

4.1. META-DATA TEXT REPOSITORY

4.2. DATA ACCESS TO DATA OF ANY FORMAT

4.3. MODEL MANAGEMENT

4.4. DEVELOPMENT ENVIRONMENT

4.5. TRANSFORMATION FEATURES

4.6. DATA QUALITY

4.7. TEST AND DEBUGGER TOOLS

4.8. DEPLOYMENT

4.9. SCALABILITY AND PERFORMANCE

5. THE BENEFITS OF TRANSFORMATION MANAGER

5.1. HIGHER PRODUCTIVITY

5.2. HANDLE THE MOST COMPLEX REQUIREMENTS

5.3. DEPLOYMENT FLEXIBILITY TO SUIT PROJECT REQUIREMENTS

5.4. IMPROVED MANAGEMENT AND REPORTING

5.5. REDUCED PROJECT RISK AND FUTURE MAINTAINABILITY

6. USE-CASES EXAMPLES

6.1. FINANCE USE-CASES

6.2. GEOSCIENCE USE-CASES

6.3. ERP SECTOR USE-CASE

6.4. PUBLIC SECTOR USE-CASE

7. CONCLUSION

Informatica - Transformations

2007-12-30T11:57:00.000-08:00

Informatica - Transformations

In Informatica, Transformations help to transform the source data according to the requirements of target system and it ensures the quality of the data being loaded into target.

Transformations are of two types: Active and Passive.

Active Transformation

An active transformation can change the number of rows that pass through it from source to target i.e it eliminates rows that do not meet the condition in transformation.

Passive Transformation

A passive transformation does not change the number of rows that pass through it i.e it passes all rows through the transformation.

Transformations can be Connected or UnConnected.

Connected Transformation

Connected transformation is connected to other transformations or directly to target table in the mapping.

UnConnected Transformation

An unconnected transformation is not connected to other transformations in the mapping. It is called within another transformation, and returns a value to that transformation.

Following are the list of Transformations available in Informatica:

Aggregator Transformation

Expression Transformation

Filter Transformation

Joiner Transformation

Lookup Transformation

Normalizer Transformation

Rank Transformation

Router Transformation

Sequence Generator Transformation

Stored Procedure Transformation

Sorter Transformation

Update Strategy Transformation

XML Source Qualifier Transformation

Advanced External Procedure Transformation

External Transformation

In the following pages, we will explain all the above Informatica Transformations and their significances in the ETL process in detail.

INFORMATICA

2007-12-30T09:38:00.000-08:00

Informatica

Power Exchange:

Informatica Power Exchange as a stand alone service or along with Power Center, helps organizations leverage data by avoiding manual coding of data extraction programs. Power Exchange supports batch, real time and changed data capture options in main frame(DB2, VSAM, IMS etc.,), mid range (AS400 DB2 etc.,), and for relational databases (oracle, sql server, db2 etc) and flat files in unix, linux and windows systems.

Power Channel:

This helps to transfer large amount of encrypted and compressed data over LAN, WAN, through Firewalls, tranfer files over FTP, etc.

Meta Data Exchange:

Metadata Exchange enables organizations to take advantage of the time and effort already invested in defining data structures within their IT environment when used with Power Center. For example, an organization may be using data modeling tools, such as Erwin, Embarcadero, Oracle designer, Sybase Power Designer etc for developing data models. Functional and technical team should have spent much time and effort in creating the data model's data structures(tables, columns, data types, procedures, functions, triggers etc). By using meta deta exchange, these data structures can be imported into power center to identifiy source and target mappings which leverages time and effort. There is no need for informatica developer to create these data structures once again.

Power Analyzer:

Power Analyzer provides organizations with reporting facilities. PowerAnalyzer makes accessing, analyzing, and sharing enterprise data simple and easily available to decision makers. PowerAnalyzer enables to gain insight into business processes and develop business intelligence.

With PowerAnalyzer, an organization can extract, filter, format, and analyze corporate information from data stored in a data warehouse, data mart, operational data store, or otherdata storage models. PowerAnalyzer is best with a dimensional data warehouse in a relational database. It can also run reports on data in any table in a relational database that do not conform to the dimensional model.

Super Glue:

Superglue is used for loading metadata in a centralized place from several sources. Reports can be run against this superglue to analyze meta data.

Power Mart:

Power Mart is a departmental version of Informatica for building, deploying, and managing data warehouses and data marts. Power center is used for corporate enterprise data warehouse and power mart is used for departmental data warehouses like data marts. Power Center supports global repositories and networked repositories and it can be connected to several sources. Power Mart supports single repository and it can be connected to fewer sources when compared to Power Center. Power Mart can extensibily grow to an enterprise implementation and it is easy for developer productivity through a codeless environment.

What is INFORMATICA?

2007-12-30T09:31:00.000-08:00

Informatica

Informatica is a powerful ETL tool from Informatica Corporation, a leading provider of enterprise data integration software and ETL softwares.

The important Informatica Components are:

Power Exchange

Power Center

Power Center Connect

Power Exchange

Power Channel

Metadata Exchange

Power Analyzer

Super Glue

In Informatica, all the Metadata information about source systems, target systems and transformations are stored in the Informatica repository. Informatica's Power Center Client and Repository Server access this repository to store and retrieve metadata.

Source and Target:

Consider a Bank that has got many branches throughout the world. In each branch data may be stored in different source systems like oracle, sql server, terradata, etc.

When the Bank decides to integrate its data from several sources for its management decisions, it may choose one or more systems like oracle, sql server, terradata, etc. as its data warehouse target. Many organisations prefer Informatica to do that ETL process, because Informatica is more powerful in designing and building data warehouses. It can connect to several sources and targets to extract meta data from sources and targets, transform and load the data into target systems.

Guidelines to work with Informatica Power Center

Repository: This is where all the metadata information is stored in the Informatica suite. The Power Center Client and the Repository Server would access this repository to retrieve, store and manage metadata.

Power Center Client: Informatica client is used for managing users, identifiying source and target systems definitions, creating mapping and mapplets, creating sessions and run workflows etc.

Repository Server: This repository server takes care of all the connections between the repository and the Power Center Client.

Power Center Server: Power Center server does the extraction from source and then loading data into targets.

Designer: Source Analyzer, Mapping Designer and Warehouse Designer are tools reside within the Designer wizard. Source Analyzer is used for extracting metadata from source systems.
Mapping Designer is used to create mapping between sources and targets. Mapping is a pictorial representation about the flow of data from source to target.
Warehouse Designer is used for extracting metadata from target systems or metadata can be created in the Designer itself.

Data Cleansing: The PowerCenter's data cleansing technology improves data quality by validating, correctly naming and standardization of address data. A person's address may not be same in all source systems because of typos and postal code, city name may not match with address. These errors can be corrected by using data cleansing process and standardized data can be loaded in target systems (data warehouse).

Transformation: Transformations help to transform the source data according to the requirements of target system. Sorting, Filtering, Aggregation, Joining are some of the examples of transformation. Transformations ensure the quality of the data being loaded into target and this is done during the mapping process from source to target.

Workflow Manager: Workflow helps to load the data from source to target in a sequential manner. For example, if the fact tables are loaded before the lookup tables, then the target system will pop up an error message since the fact table is violating the foreign key validation. To avoid this, workflows can be created to ensure the correct flow of data from source to target.

Workflow Monitor: This monitor is helpful in monitoring and tracking the workflows created in each Power Center Server.

Power Center Connect: This component helps to extract data and metadata from ERP systems like IBM's MQSeries, Peoplesoft, SAP, Siebel etc. and other third party applications.

Power Center Exchange: This component helps to extract data and metadata from ERP systems like IBM's MQSeries, Peoplesoft, SAP, Siebel etc. and other third party applications.

ETL Concepts

2007-12-30T08:52:00.000-08:00

ETL Concepts

Extraction, transformation, and loading. ETL refers to the methods involved in accessing and manipulating source data and loading it into target database.

The first step in ETL process is mapping the data between source systems and target database(data warehouse or data mart). The second step is cleansing of source data in staging area. The third step is transforming cleansed source data and then loading into the target system.

Note that ETT (extraction, transformation, transportation) and ETM (extraction, transformation, move) are sometimes used instead of ETL.

Glossary of ETL

Source System

A database, application, file, or other storage facility from which the data in a data warehouse is derived.

Mapping

The definition of the relationship and data flow between source and target objects.

Metadata

Data that describes data and other structures, such as objects, business rules, and processes. For example, the schema design of a data warehouse is typically stored in a repository as metadata, which is used to generate scripts used to build and populate the data warehouse. A repository contains metadata.

Staging Area

A place where data is processed before entering the warehouse.

Cleansing

The process of resolving inconsistencies and fixing the anomalies in source data, typically as part of the ETL process.

Transformation

The process of manipulating data. Any manipulation beyond copying is a transformation. Examples include cleansing, aggregating, and integrating data from multiple sources.

Transportation

The process of moving copied or transformed data from a source to a data warehouse.

Target System

A database, application, file, or other storage facility to which the "transformed source data" is loaded in a data warehouse.

Figure 1.12 : Sample ETL Process Flow

ETL Tools: What to Learn?

2007-12-30T08:41:00.000-08:00

ETL Tools: What to Learn?

With the help of ETL tools, we can create powerful target Data Warehouses without much difficulty. Following are the various options that we have to know and learn in order to use ETL tools.

Software:

» How to install ETL tool on server/client?

Working with an ETL Tool:

» How to work with various options like designer, mapping, workflow, scheduling etc.,?

» How to work with sources like DBMS, relational source databases, files, ERPs etc., and
import the source definitions?

» How to import data from data modeling tools, applications etc.,?

» How to work with targets like DBMS, relational source databases, files, ERPs etc., and import the source definitions?

» How to create target definitions?

» How to create mappings between source definitions and target definitions?

» How to create transformations?

» How to cleanse the source data?

» How to create a dimension, slowly changing dimensions, cube etc.,?

» How to create and monitor workflows?

» How to configure, monitor and run debugger?

» How to view and generate metadata reports?

What are ETL Tools?

2007-12-30T08:32:00.000-08:00

What are ETL Tools?

ETL Tools are meant to extract, transform and load the data into Data Warehouse for decision making. Before the evolution of ETL Tools, the above mentioned ETL process was done manually by using SQL code created by programmers.

This task was tedious and cumbersome in many cases since it involved many resources, complex coding and more work hours. On top of it, maintaining the code placed a great challenge among the programmers.

These difficulties are eliminated by ETL Tools since they are very powerful and they offer many advantages in all stages of ETL process starting from extraction, data cleansing, data profiling, transformation, debuggging and loading into data warehouse when compared to the old method.

There are a number of ETL tools available in the market to do ETL process the data according to business/technical requirements. Following are some those.

Popular ETL Tools

Tool Name	Company Name
Informatica	Informatica Corporation
DT/Studio	Embarcadero Technologies
DataStage	IBM
Ab Initio	Ab Initio Software Corporation
Data Junction	Pervasive Software
Oracle Warehouse Builder	Oracle Corporation
Microsoft SQL Server Integration	Microsoft
TransformOnDemand	Solonde
Transformation Manager	ETL Solutions

New Emerging ETL TOOLS:

The matter of fact is that Talend and Pentaho Data Integration are sufficiently different in conception, architecture and implementation that they are in fact two distinct choices on the ETL market. While some people prefer one tool and some prefer another, the choice is there. Having the opportunity to try out both tools for Free and to have this choice is one of the most important differentiators with the traditional closed source ETL companies.

Database - RDBMS

2007-12-30T08:21:00.000-08:00

Database - RDBMS

There are a number of relational databases to store data. A relational database contains normalized data stored in tables.

Tables contain records and columns. RDBMS makes it easy to work with individual records. Each row contains a unique instance of data for the categories defined by the columns.

RDBMS are used in OLTP applications(e.g. ATM cards) very frequently and sometimes datawarehouse may also use relational databases.

Please refer to Relational data modeling for details to know how data from a source system is normalized and stored in RDBMS databases.

Popular RDBMS Databases

RDBMS Name	Company Name
Oracle	Oracle Corporation
IBM DB2 UDB	IBM Corporation
IBM Informix	IBM Corporation
Microsoft SQL Server	Microsoft
Sybase	Sybase Corporation
Terradata	NCR

What is Fact Table ?

2007-12-30T07:44:00.000-08:00

Fact Table

The centralized table in a star schema is called as FACT table. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys.

In the example fig 1.6 "Sales Dollar" is a fact(measure) and it can be added across several dimensions. Fact tables store different types of measures like additive, non additive and semi additive measures.

Measure Types

Additive - Measures that can be added across all dimensions.
Non Additive - Measures that cannot be added across all dimensions.
Semi Additive - Measures that can be added across few dimensions and not with others.

A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables).

In the real world, it is possible to have a fact table that contains no measures or facts. These tables are called as Factless Fact tables.

Steps in designing Fact Table

Identify a business process for analysis(like sales).
Identify measures or facts (sales dollar).
Identify dimensions for facts(product dimension, location dimension, time dimension, organization dimension).
List the columns that describe each dimension.(region name, branch name, region name).
Determine the lowest level of summary in a fact table(sales dollar).

Example of a Fact Table with an Additive Measure in Star Schema: Figure 1.6

In the example figure 1.6, sales fact table is connected to dimensions location, product, time and organization. Measure "Sales Dollar" in sales fact table can be added across all dimensions independently or in a combined manner which is explained below.

Sales Dollar value for a particular product
Sales Dollar value for a product in a location
Sales Dollar value for a product in a year within a location
Sales Dollar value for a product in a year within a location sold or serviced by an employee

What is Snowflake Schema ?

2007-12-30T07:34:00.000-08:00

Snowflake Schema

A snowflake schema is a term that describes a star schema structure normalized through the use of outrigger tables. i.e dimension table hierachies are broken into simpler tables. In star schema example we had 4 dimensions like location, product, time, organization and a fact table(sales).

In Snowflake schema, the example diagram shown below has 4 dimension tables, 4 lookup tables and 1 fact table. The reason is that hierarchies(category, branch, state, and month) are being broken out of the dimension tables(PRODUCT, ORGANIZATION, LOCATION, and TIME) respectively and shown separately.

In OLAP, this Snowflake schema approach increases the number of joins and poor performance in retrieval of data. In few organizations, they try to normalize the dimension tables to save space. Since dimension tables hold less space, Snowflake schema approach may be avoided.

Example of Snowflake Schema: Figure 1.7

What is Star Schema?

2007-12-30T07:14:00.000-08:00

General Information

In general, an organization is started to earn money by selling a product or by providing service to the product. An organization may be at one place or may have several branches.

When we consider an example of an organization selling products throughtout the world, the main four major dimensions are product, location, time and organization. Dimension tables have been explained in detail under the section Dimensions. With this example, we will try to provide detailed explanation about STAR SCHEMA.

What is Star Schema?

Star Schema is a relational database schema for representing multimensional data. It is the simplest form of data warehouse schema that contains one or more dimensions and fact tables. It is called a star schema because the entity-relationship diagram between dimensions and fact tables resembles a star where one fact table is connected to multiple dimensions. The center of the star schema consists of a large fact table and it points towards the dimension tables. The advantage of star schema are slicing down, performance increase and easy understanding of data.

Steps in designing Star Schema

Identify a business process for analysis(like sales).
Identify measures or facts (sales dollar).
Identify dimensions for facts(product dimension, location dimension, time dimension, organization dimension).
List the columns that describe each dimension.(region name, branch name, region name).
Determine the lowest level of summary in a fact table(sales dollar).

Important aspects of Star Schema & Snow Flake Schema

In a star schema every dimension will have a primary key.
In a star schema, a dimension table will not have any parent table.
Whereas in a snow flake schema, a dimension table will have one or more parent tables.
Hierarchies for the dimensions are stored in the dimensional table itself in star schema.
Whereas hierachies are broken into separate tables in snow flake schema. These hierachies helps to drill down the data from topmost hierachies to the lowermost hierarchies.

Glossary:

Hierarchy

A logical structure that uses ordered levels as a means of organizing data. A hierarchy can be used to define data aggregation; for example, in a time dimension, a hierarchy might be used to aggregate data from the Month level to the Quarter level, from the Quarter level to the Year level. A hierarchy can also be used to define a navigational drill path, regardless of whether the levels in the hierarchy represent aggregated totals or not.

Level

A position in a hierarchy. For example, a time dimension might have a hierarchy that represents data at the Month, Quarter, and Year levels.

Fact Table

A Table in a star schema that contains facts and connected to dimensions. A fact table typically has two types of columns: those that contain facts and those that are foreign keys to dimension tables. The primary key of a fact table is usually a composite key that is made up of all of its foreign keys.

A fact table might contain either detail level facts or facts that have been aggregated (fact tables that contain aggregated facts are often instead called summary tables). A fact table usually contains facts with the same level of aggregation.

Example of Star Schema: Figure 1.6

In the example figure 1.6, sales fact table is connected to dimensions location, product, time and organization. It shows that data can be sliced across all dimensions and again it is possible for the data to be aggregated across multiple dimensions. "Sales Dollar" in sales fact table can be calculated across all dimensions independently or in a combined manner which is explained below.

Sales Dollar value for a particular product
Sales Dollar value for a product in a location
Sales Dollar value for a product in a year within a location
Sales Dollar value for a product in a year within a location sold or serviced by an employee.

Data Warehouse n Data Mart

2007-12-30T07:06:00.000-08:00

Data Warehouse & Data Mart

A data warehouse is a relational/multidimensional database that is designed for query and analysis rather than transaction processing. A data warehouse usually contains historical data that is derived from transaction data. It separates analysis workload from transaction workload and enables a business to consolidate data from several sources.

In addition to a relational/multidimensional database, a data warehouse environment often consists of an ETL solution, an OLAP engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.

There are three types of data warehouses:

1. Enterprise Data Warehouse - An enterprise data warehouse provides a central database for decision support throughout the enterprise.

2. ODS(Operational Data Store) - This has a broad enterprise wide scope, but unlike the real entertprise data warehouse, data is refreshed in near real time and used for routine business activity.

3. Data Mart - Datamart is a subset of data warehouse and it supports a particular region, business unit or business function.

Data warehouses and data marts are built on dimensional data modeling where fact tables are connected with dimension tables. This is most useful for users to access data since a database can be visualized as a cube of several dimensions. A data warehouse provides an opportunity for slicing and dicing that cube along each of its dimensions.

Data Mart: A data mart is a subset of data warehouse that is designed for a particular line of business, such as sales, marketing, or finance. In a dependent data mart, data can be derived from an enterprise-wide data warehouse. In an independent data mart, data can be collected directly from sources

Data Warehouse and Datamarts

Slowly Changing Dimensions

2007-12-30T06:42:00.001-08:00

Slowly Changing Dimensions

Dimensions that change over time are called Slowly Changing Dimensions. For instance, a product price changes over time; People change their names for some reason; Country and State names may change over time. These are a few examples of Slowly Changing Dimensions since some changes are happening to them over a period of time.

Slowly Changing Dimensions are often categorized into three types namely Type1, Type2 and Type3. The following section deals with how to capture and handling these changes over time.

The "Product" table mentioned below contains a product named, Product1 with Product ID being the primary key. In the year 2004, the price of Product1 was $150 and over the time, Product1's price changes from $150 to $350. With this information, let us explain the three types of Slowly Changing Dimensions.

Product Price in 2004:

Product ID (PK)	Year	Product Name	Product Price
1	2004	Product1	$150

Type 1: Overwriting the old values.

In the year 2005, if the price of the product changes to $250, then the old values of the columns "Year" and "Product Price" have to be updated and replaced with the new values. In this Type 1, there is no way to find out the old value of the product "Product1" in year 2004 since the table now contains only the new price and year information.

Product

Product ID(PK)	Year	Product Name	Product Price
1	2005	Product1	$250

Type 2: Creating an another additional record.

In this Type 2, the old values will not be replaced but a new row containing the new values will be added to the product table. So at any point of time, the difference between the old values and new values can be retrieved and easily be compared. This would be very useful for reporting purposes.

Product

Product ID(PK)	Year	Product Name	Product Price
1	2004	Product1	$150
1	2005	Product1	$250

The problem with the above mentioned data structure is "Product ID" cannot store duplicate values of "Product1" since "Product ID" is the primary key. Also, the current data structure doesn't clearly specify the effective date and expiry date of Product1 like when the change to its price happened. So, it would be better to change the current data structure to overcome the above primary key violation.

Product

Product ID (PK)	Effective DateTime(PK)	Year	Product Name	Product Price	Expiry DateTime
1	01-01-2004 12.00AM	2004	Product1	$150	12-31-2004 11.59PM
1	01-01-2005 12.00AM	2005	Product1	$250

In the changed Product table's Data structure, "Product ID" and "Effective DateTime" are composite primary keys. So there would be no violation of primary key constraint. Addition of new columns, "Effective DateTime" and "Expiry DateTime" provides the information about the product's effective date and expiry date which adds more clarity and enhances the scope of this table.

Type2 approach may need additional space in the data base, since for every changed record, an additional row has to be stored. Since dimensions are not that big in the real world, additional space is negligible.

Type 3: Creating new fields.

In this Type 3, the latest update to the changed values can be seen. Example mentioned below illustrates how to add new columns and keep track of the changes. From that, we are able to see the current price and the previous price of the product, Product1.

Product

Product ID(PK)	Current Year	Product Name	Current Product Price	Old Product Price	Old Year
1	2005	Product1	$250	$150	2004

The problem with the Type 3 approach, is over years, if the product price continuously changes, then the complete history may not be stored, only the latest change will be stored. For example, in year 2006, if the product1's price changes to $350, then we would not be able to see the complete history of 2004 prices, since the old values would have been updated with 2005 product information.

Product

Product ID(PK)	Year	Product Name	Product Price	Old Product Price	Old Year
1	2006	Product1	$350	$250	2005

Interview Questions

2007-12-30T02:56:00.000-08:00

1) can you connect more than one group to the same target or transformation?

NO

2) what is a reusable transformation?

Reusable transformation can be a single transformation.This Transformation can be used in multiple mappings.when you need to incorporate this transformation into mapping you add an instance of it to mapping.Later if you change the definition of the transformation, all instances of it inherit the changes.Since the instance of reusable transformation is a pointer to that transformation.U can change the transformation in the transformation developer, its instance automatically reflect these changes. This feature can save U great deal of work.--

what are the methods for creating reusable transformation?

Two methods

a) Design it in the transformation developer.

b) Promote a standard transformation from the mapping designer.After you add a transformation to the mapping, you can promote it to status of reusable transformation.Once you promote a standard transformation to reusable status, you can demote it to a standard transformation at any time.If u change the properties of a reusable transformation in mapping , you can revert it to the original reusable transformation properties by clicking the revert.

3) what are mapping parameters and mapping variables?

Mapping parameter represents a constant value that you can define before running a session.A mapping parameter retains the same value throughout the entire session.When you use the mapping parameter , you declare and use the parameter in a mapping or mapplet.Then define the value of parameter in a parameter file for the session.

Unlike a mapping parameter, a Mapping variable represents a value that can change through out the session. The informatica server save the value of mapping variable to the repository at the end of session run and uses that value next time you run the session.

4) can you use the mapping parameters or variables created in one mapping into
another mapping?

NO, we can use mapping parameters or variables in any transformation of the same mapping or mapplet in which have crated mapping parameters or variables.

5) Can you are the mapping parameters or variables created in one mapping into any other result transformation?

Yes because the reusable transformation is not contained with any mapplet or mapping.

6) How the informatica server sorts the string values in rank transformation?

When the informatica server runs in the ASCII data movement mode it sorts session data using binary sort order.If you configures the session to use a binary sort order, the informatica server calculates the binary value of each string and returns the specified number of rows with the highest binary values for the string.

7) What is the rank index in rank transformation?

The designer automatically creates a RANKINDEX port for each Rank transformation. The informatica server uses the Rank Index port to store the ranking position for each record in a group.For example, if you create a Rank transformation that ranks the top 5 sales persons for each quarter, the rank index number the salespeople from 1 to 5.

8) what is the Mapplet?

Mapplet is a set of transformation that you build in the mapplet designer and you can use in multiple mappings.

9) Difference between mapplet and reusable transformation?

Reusable transformation can be a single transformation.Where as mapplet use multiple transformations.

10) what is a parameter a file?

Paramater file defines the values for parameter and variables.

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13 FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

Interview Questions

2007-12-30T02:39:00.000-08:00

1) what is the target load order?

You specify the target load order based on source qualifiers in a mapping.if u have the multiple source qualifiers connected to the multiple targets you can designate the order in which informatica server loads data into the targets.

2) what is default join that source qualifier provides?

Inner equi join.

3) what are the difference between joiner transformation and source qualifier transformation?

You can join heterogeneous data sources in joiner transformation, which we cannot achive in source qualifier transformation.You need matching keys to join two relational sources in source qualifier transformation.where you doesn’t need matching keys to join two sources.Two relational sources should come from same data source in source qualifier.You can join relational sources, which are coming from different sources in source qualifier.You can join relational sources which are coming from different sources also.

4) what is update strategy transformation?

Whenever you create the target table whether you are store the historical data or current transaction data in to target table.

5) Describe two levels in which update strategy transformation sets?

1) using the update strategy Transformation In the Mapping.

2) Using the Target table option at the session.

6) what is default source option for update strategy transformation?

Data driven.

7) What is data driven?

The information server follows instructions coded into update strategy transformations with in the session mapping determine how to flag records for insert,update,delete or reject if u do not choose data driven option setting , the informatica server ignores all update strategy transformations in the mapping.

8) what are the options in the trarget session of update strategy transformation?

Insert

Delete

Update

Update as update

Update as insert

Update else insert

Truncate table.

9) Difference between the source filter and filter?

Source filter is filtering the data only relational sources. Where as filter transformation filter the data any type of source.

10) what is a tracing level?

Amount of information sent to log file.

-- What are the types of tracing levels?

Normal,Terse,verbose data,verbose intitialization.

--Expalin sequence generator transformation?

-- can you connect multiple ports from one group to multiple transformations?

Yes

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13 FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

Interview Questions

2007-12-30T02:18:00.000-08:00

1) How many types of approaches in DHW?

Two approaches: Top-down(Inmol approach), Bottom-up(Ralph Kimball)

2) Explain Star Schema?

Star Schema consists of one or more fact table and one or more dimension tables that are related to foreign keys. Dimension tables are De-normalized, Fact table-normalizedAdvantages: Less database space & Simplify queries.

3) Explain Snowflake schema?

Snow flake schema is a normalize dimensions to eliminate the redundancy.The dimension data has been grouped into one large table. Both dimension and fact tables normalized.

4) What is confirm dimension?

If both data marts use same type of dimension that is called confirm dimension.If you have same type of dimension can be used in multiple fact that is called confirm dimension.

5) Explain the DWH architecture?

It expalins about the System Arrangement of the Datawarehouse.

6) What is a slowly growing dimension?

Slowly growing dimensions are dimensional data,there dimensions increasing dimension data with out update existing dimensions.That means appending new data to existing dimensions.

7) What is a slowly changing dimension?

Slowly changing dimension are dimension data,these dimensions increasing dimensions data with update existing dimensions.

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

Type2: The Type2 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 Type2 Dimension/version data mapping to update a slowly changing dimension 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.

Type3: The type 3 dimension mapping filters source rows based on user-defined comparisions 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.

8) When you use for dynamic cache?

Your target table is also look up table then you go for dynamic cache .In dynamic cache multiple matches return an error.use only = operator.

9) what is lookup override?

Override the default SQL statement.You can join multiple sources use lookup override.By default informatica server add the order by clause.

10) we can pass the null value in lookup transformation?

Lookup transformation returns the null value or equal to null value.

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13 FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

2007-12-30T01:12:00.000-08:00

1) What is Data warehouse?

Data warehouse is relational database used for query analysis and reporting.

By definition data warehouse is Subject-oriented, Integrated, Non-volatile, Time variant.Subject oriented : Data warehouse is maintained particular subject.

Integrated : Data collected from multiple sources integrated into auser readable unique format.

Non volatile : Maintain Historical date.Time variant : data display the weekly, monthly, yearly.

2) What is Data mart?

A subset of data warehouse is called Data mart.

3) Difference between Data warehouse and Data mart?

Data warehouse is maintaining the total organization of data. Multiple data marts used in data warehouse. where as data mart is maintained only particular subject.

4) Difference between OLTP and OLAP?

OLTP is Online Transaction Processing. This is maintained current transactional data. That means insert, update and delete must be fast.

5) Explain ODS?

Operational data store is a part of data warehouse. This is maintained only current transactional data. ODS is subject oriented, integrated, volatile, current data.

6) Difference between Power Center and Power Mart?

Power center receive all product functionality including ability to multiple register servers and metadata across the repository and partition data.One repository multiple informatica servers. Power mart received all features except multiple register servers and partition data.

7) What is a staging area?

Staging area is a temporary storage area used for transaction, integrated and rather than transaction processing.When ever your data put in data warehouse you need to clean and process your data.

8) Explain Additive, Semi-additive, Non-additive facts?

Additive fact: Additive Fact can be aggregated by simple arithmetical additions.Semi-Additive fact: semi additive fact can be aggregated simple arithmeticaladditions along with some other dimensions.Non-additive fact: Non-additive fact can’t be added at all.

9) What is a Fact less Fact and example?

Fact table which has no measures.

10) Explain Surrogate Key?

Surrogate Key is a series of sequential numbers assigned to be a primary key for the table.

Freqently Asked Questions:

FaQ1 FaQ2 FaQ3 FaQ4 FaQ5 FaQ6 FaQ7 FaQ8 FaQ9 FaQ10 FaQ11 FaQ12 FaQ13 FaQ14 FaQ15 FaQ16 FaQ17 FaQ18 FaQ19 FaQ20 FaQ21

Data Warehouse Concepts

2007-12-29T07:08:00.000-08:00

Data Warehouse Concepts

What is a Data Warehouse?

According to Inmon, famous author for several data warehouse books, "A data warehouse is a subject oriented, integrated, time variant, non volatile collection of data in support of management's decision making process".

Example: In order to store data, over the years, many application designers in each branch have made their individual decisions as to how an application and database should be built. So source systems will be different in naming conventions, variable measurements, encoding structures, and physical attributes of data. Consider a bank that has got several branches in several countries, has millions of customers and the lines of business of the enterprise are savings, and loans. The following example explains how the data is integrated from source systems to target systems.

Example of Source Data

System Name	Attribute Name	Column Name	Datatype	Values
Source System 1	Customer Application Date	CUSTOMER_APPLICATION_DATE	NUMERIC(8,0)	11012005
Source System 2	Customer Application Date	CUST_APPLICATION_DATE	DATE	11012005
Source System 3	Application Date	APPLICATION_DATE	DATE	01NOV2005

In the aforementioned example, attribute name, column name, datatype and values are entirely different from one source system to another. This inconsistency in data can be avoided by integrating the data into a data warehouse with good standards.

Example of Target Data(Data Warehouse)

Target System	Attribute Name	Column Name	Datatype	Values
Record #1	Customer Application Date	CUSTOMER_APPLICATION_DATE	DATE	01112005
Record #2	Customer Application Date	CUSTOMER_APPLICATION_DATE	DATE	01112005
Record #3	Customer Application Date	CUSTOMER_APPLICATION_DATE	DATE	01112005

In the above example of target data, attribute names, column names, and datatypes are consistent throughout the target system. This is how data from various source systems is integrated and accurately stored into the data warehouse.

Data Warehouse Architecture Diagram