The term Analytics mean a branch of logic dealing with analysis. So we can safely assume that Siebel Analytics means branch of Siebel dealing with Analysis. Siebel has always been transactional application and it is very difficult to do analysis of data that is residing in Siebel. Just to give you an example of what I mean.
Suppose a sales manager wants to know that:
How many opportunities in the last 3 months, from US Region for Product A, have a sales figure of over 3 million dollars?
I don’t think there is an easy way to get this kind of data in Siebel easily and this is just very small requirement that a sales manager might have it can get very complex easily.
This is where Siebel Analytics comes into picture. It is a wrapper over Siebel Application.
Siebel Analytics allow an enterprise to measure and evaluate business performance across customers. It helps in analyzing past, present and future opportunities with the help of Dashboard Reports to determine actions required to meet the sales targets. With the help of Dashboard reports we can determine which products and customers are generating most revenue. For understanding Siebel Analytics in more depth one has to know the basic difference between OLAP and OLTP. OLTP stands for On Line Transaction Processing:OLAP stands for On Line Analytical Processing
The data available at transaction side (Siebel Application) is OLTP and when that data is moved from transaction side for analyzing (Siebel Analytics) that becomes OLAP data.
OLAP brings into picture the concept of Data warehouse.
Data warehouse is a Relational /Multidimensional database that is designed for query and analysis rather for transaction processing. A data warehouse usually contains historical data that is derived from transaction data. Another important concept when we are talking about to Siebel Analytics is ETL.
ETL stands for Extract, Transform, and Load.
ETL is a concept that enables businesses to consolidate their disparate data while moving it from OLTP to OLAP and it doesn’t really matter that that data sources are in different forms or formats. The data can come from any source such as Oracle, SQL server, flat files, CSV etc One important function of ETL is “Cleansing” data. ETL consolidation protocols also include the elimination of duplicate or fragmentary data, so that what passes from the ‘E’ portion of the process to the ‘L’ portion is easier to assimilate and/or store.
Such cleansing operations can also include eliminating certain kinds of data from the process. If you don’t want to include certain information, you can customize your ETL to eliminate that kind of information from your transformation. The ‘T’ portion of the equation, of course, is the most powerful. ETL can transform data from different sources. For Example: - Data in an Oracle CRM could be transformed right along with data from an SAP Marketing application, with the result being a common data from both the application.
Tuesday, December 2, 2008
Informatica – How to create a Mapping.
Informatica:Informatica is a powerful ETL tool from Informatica Corporation, a leading provider of enterprise data integration software and ETL softwares. Some of the other popular ETL tools available in market are
Data Stage IBM
Oracle Warehouse Builder Oracle Corporation
Microsoft SQL Server Integration Microsoft
DT/Studio Embarcadero Technologies
Transformation Manager ETL Solutions
Why Informatica?Because it provides many Vanilla mappings taking siebel tables as source within the product
What is a “Mapping”?Mappings represent the data flow between sources and targets. When the Informatica Server executes a session, it uses the instructions configured in the mapping to read, transform, and write data.
Every mapping must contain the following components:
Source definition: It describes the characteristics of a source table or file.Transformation: How to modify data before writing it to targets. Use different transformation objects to on the source data and obtain different results.Target definition: It defines the target table or flat file.Connectors: Connect sources, targets, and transformations so the Informatica Server can move the data as it transforms it.
What is Transformation?Transformation is the core or the most important function of ETL process it determines what to do with the source data before sending to destination.
Now, we are done with the theory hence lets get our hands on practical. Step by step procedure given below has been tested on Informatica version 6.2.2
How to Create a Mapping?
Open the Mapping Designer.
Choose Mappings-Create, or drag a repository object into the workspace.
Enter a name for the new mapping and click OK
Select source tables from the Source Folder (We just need one Source Qualifier, delete other if taking multiple source tables)
Map column from source tables with Source Qualifier and put the SQL Query under Properties tab SQL Query Attribute. SQL query should contain all the fields, joins and table name required for getting the required data.
Under Ports tab the Port (fields) should be in the same order as in the SQL, and data type should be same as in the source table
Now create a transformation Expression
Go to Transformation (Under Tool Bar) Select –> Create Select –> Expression from Dropdown.
Map the ports from Source Qualifier to Expression Transformation.
Map the Ports with the Target Table
In next post we will discuss about different type of Transformation with an example.
Data Stage IBM
Oracle Warehouse Builder Oracle Corporation
Microsoft SQL Server Integration Microsoft
DT/Studio Embarcadero Technologies
Transformation Manager ETL Solutions
Why Informatica?Because it provides many Vanilla mappings taking siebel tables as source within the product
What is a “Mapping”?Mappings represent the data flow between sources and targets. When the Informatica Server executes a session, it uses the instructions configured in the mapping to read, transform, and write data.
Every mapping must contain the following components:
Source definition: It describes the characteristics of a source table or file.Transformation: How to modify data before writing it to targets. Use different transformation objects to on the source data and obtain different results.Target definition: It defines the target table or flat file.Connectors: Connect sources, targets, and transformations so the Informatica Server can move the data as it transforms it.
What is Transformation?Transformation is the core or the most important function of ETL process it determines what to do with the source data before sending to destination.
Now, we are done with the theory hence lets get our hands on practical. Step by step procedure given below has been tested on Informatica version 6.2.2
How to Create a Mapping?
Open the Mapping Designer.
Choose Mappings-Create, or drag a repository object into the workspace.
Enter a name for the new mapping and click OK
Select source tables from the Source Folder (We just need one Source Qualifier, delete other if taking multiple source tables)
Map column from source tables with Source Qualifier and put the SQL Query under Properties tab SQL Query Attribute. SQL query should contain all the fields, joins and table name required for getting the required data.
Under Ports tab the Port (fields) should be in the same order as in the SQL, and data type should be same as in the source table
Now create a transformation Expression
Go to Transformation (Under Tool Bar) Select –> Create Select –> Expression from Dropdown.
Map the ports from Source Qualifier to Expression Transformation.
Map the Ports with the Target Table
In next post we will discuss about different type of Transformation with an example.
Subscribe to:
Posts (Atom)
