OBIEE 11G Installation error during configuration steps with message "Distributing Repository" failed

Problem:

When attempting to install OBIEE11g (11.1.1.7) on Windows 64bit, the installation failed at the 
configuration steps with error message "Distributing Repository" failed.

Answer:

To avoid this issue, make sure that the locale en_US.UTF-8 exists on the installation computer before you install Oracle Business Intelligence.

Read More

Installing BI Applications 7.9.6.4 on BI EE 11.1.1.7.0

The Oracle Business Intelligence Application 7.9.6.4 installer fails on Oracle BI EE 11.1.1.7.0 with the error “NQSConfig.INI not found. Please make sure your environment is set up properly”. Also  7.9.6.4 JAZN file is incompatible with BI EE 11.1.1.7.0. This causes authentication failures when a user tries to log in to the Analytics page.

Issue 1: The Oracle Business Intelligence Application 7.9.6.4 installer fails on Oracle BI EE 11.1.1.7.0 with the error “NQSConfig.INI not found. Please make sure your environment is set up properly”.

Issue 2: The Oracle Business Intelligence Application 7.9.6.4 JAZN file is incompatible with BI EE 11.1.1.7.0. This causes authentication failures when a user tries to log in to the Analytics page.

Error Message

NQSConfig.INI not found. Please make sure your environment is set up properly

Please down the attached file Technote1561846.1_InstallSecurity.zip and apply the solution.ID 1561846.1
If required apply patch p16321623_7964_Generic

Patch Download from here

Patch Doc

Read More

Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

Error: 

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 17001] Oracle Error code: 932, message: ORA-00932: inconsistent datatypes: expected - got CLOB at OCI call OCIStmtExecute. [nQSError: 17010] SQL statement preparation failed. (HY000)

SQL Issued: SELECT 0 s_0, "SPM - Strategic Plan"."Res Statement"."Responsibility Statment Ar" s_1, "SPM - Strategic Plan"."Res Statement"."Responsibility Statment" s_2 FROM "SPM - Strategic Plan" 

Solutions:

Create a view in database by selecting all the columns  and use this DBMS_LOB.SUBSTR(<Clob column>) for clob column.
example: SELECT  rs.sp_id,  
        CAST(DBMS_LOB.substr(rs.RESPONSIBILITY_STATMENT,4000,1)AS VARCHAR2(4000)) RESPONSIBILITY_STATMENT,
        CAST(DBMS_LOB.substr(rs.RESPONSIBILITY_STATMENT_AR,2150,1) AS VARCHAR2(4000)) RESPONSIBILITY_STATMENT_AR,
       CAST(DBMS_LOB.substr(rs.RESPONSIBILITY_STATMENT_AR,4000,2151) AS VARCHAR2(4000))
FROM 
       xxspm_respon_statment rs

If it is English characeter it will accept up to 4000 char , If it is Arabic character it will allow you 2150 characters you can split the data in to two columns like in the example.

1) In the RPD physical layer change the data type to Longvarchar
2) In the report level add two arabic columns using concatinate symbal ( || ) in the expersion builder report will work

good luck.

Read More

OBIEE 11.1.1.6.11 - Patche Release Details

11.1.1.6.6

Version	Latest Patchset, patch Set Update or Maintenance Release	Patch Release Date	Comments
11.1.1.6.0		Available	This release is NOT recommended for customers to go-live on. If a customer is upgrading they should install 11.1.1.6.0, then apply the necessary patches via opatch to get them to 11.1.1.6.7 or newer If a customer is currently on 11.1.1.6.0, then apply the necessary patches via opatch to get them to 11.1.1.6.8 or above and plan an adjusted to-live accordingly to be successful. Customers can consider 11.1.1.6 PSE1 that is available for go-live, but the better option is to patch to 11.1.1.6.8 or above
11.1.1.6 PSE 1		Available	This version is recommended for upgrade customers over 11.1.1.6.2, because 6.2 does not have all of the 6.1 PSE fixes
11.1.1.6.2	Patch 13932572 Patch 13887566 Patch 13926409 Patch 13892934 Patch 13916045 Patch 13884769 Patch 13794002 as well as Patch 13952743	14th of May 2012	List of all patches 11.1.1.6.2 consists of: Patch 13932572 - 11.1.1.6.2 Oracle Business Intelligence Installer Patch 13887566 - 11.1.1.6.2 Oracle Real Time Decisions Patch 13926409 - 11.1.1.6.2 Oracle Business Intelligence Publisher Patch 13892934 - 11.1.1.6.2 Oracle Business Intelligence ADF Components Patch 13916045 - 11.1.1.6.2 Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x Patch 13884769 - 11.1.1.6.2 Oracle Business Intelligence Patch 13794002 - 11.1.1.6.2 Oracle Business Intelligence Platform Client Installer and JDeveloper Patch 13952743 mentioned in Readme files needs to be applied, too From the readme: "This patch is highly recommended for all the customers (except Exalytics customers) who are using Oracle Business Intelligence Enterprise Edition 11.1.1.6.0 and 11.1.1.6.1. Exalytics Customers must not apply this patch." This version is also NOT recommended for upgrade customers because 6.2 does not have all of the 6.1 PSE fixes.  Customers should either go to 11.1.1.6 PSE1 or patch to 11.1.1.6.8 or above Customers need to install 11.1.1.6.0, then apply the necessary patches using opatch to get to 6.2
11.1.1.6.2 BP1	Patch 14223977 Patch 14226980 Patch 13960955 Patch 14226993 Patch 14228505 Patch 13867143 Patch 14142868	Released on 29th June 2012 for all platforms, except HP-UX Itanium	List of Patches: Patch 14223977 - 11.1.1.6.2 BP1 (1 of 7) Oracle Business Intelligence Installer Patch 14226980 - 11.1.1.6.2 BP1 (2 of 7) Oracle Real Time Decisions Patch 13960955 - 11.1.1.6.2 BP1 (3 of 7) Oracle Business Intelligence Publisher Patch 14226993 - 11.1.1.6.2 BP1 (4 of 7) Oracle Business Intelligence ADF Components Patch 14228505 - 11.1.1.6.2 BP1 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x Patch 13867143 - 11.1.1.6.2 BP1 (6 of 7) Oracle Business Intelligence Patch 14142868 - 11.1.1.6.2 BP1 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer This is the first full cumulative patch bundle for 11.1.1.6, and will also synch up with fixes from 11.1.1.5. It can be installed on top of 11.1.1.6.0, 11.1.1.6 PSE1 or 11.1.1.6.2 The Readme files for the above patches describe the bugs fixed in each patch, and any known bugs with the patch. The instructions to apply the above patches are identical, and are contained in the Readme file for Patch 14223977. There are some new features: A new mobile app and features for mobile customers Improved charting and graphing, including Trellis charts Exalytics certification Note on the Mobile app: client app is available on iTunes AppStore now.  But, this app only works with the 11.1.1.6.2 BP1 server.  It is NOT backwards compatible.  So, you must use it with the 11.1.1.6.2 BP1 server.
11.1.1.6.3 - for Fusion Applications only		End of July - delayed	This version will contain OBIEE fixes specific for Fusion Applications only.  Thus, this version would not benefit standard OBIEE upgrade customers - they would even be advised to NOT install this version. Standard OBIEE customers should patch to 11.1.1.6.8 or above This version does not have all the 11.1.1.6.2 BP1 fixes.
11.1.1.6.4	Patch 14538078 Patch 14538128 Patch 14285344 Patch 14538164 Patch 14415773 Patch 14405222 Patch 14409674 as well as Patch 13952743	21st Sept 2012	This is the currently suggested Patch Set - even if there is a problem you encounter, and cannot find it in the list of fixes. List of Patches: Patch 14538078 Patch 11.1.1.6.4 (1 of 7) Oracle Business Intelligence Installer Patch 14538128 Patch 11.1.1.6.4 (2 of 7) Oracle Real Time Decisions Patch 14285344 Patch 11.1.1.6.4 (3 of 7) Oracle Business Intelligence Publisher Patch 14538164 Patch 11.1.1.6.4 (4 of 7) Oracle Business Intelligence ADF Components Patch 14415773 Patch 11.1.1.6.4 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x Patch 14405222 Patch 11.1.1.6.4 (6 of 7) Oracle Business Intelligence Patch 14409674 Patch 11.1.1.6.4 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer Note: The Readme files for the above patches describe the bugs fixed in each patch, and any known bugs with the patch. This patch is cumulative, and therefore, contains all of the fixes included in the earlier 11.1.1.6.2 patchsets. However, lists of fixes from included patchsets need to be looked up in the respective patches readme files, and are not included in the above patches' readme files The instructions to apply the above patches are identical, and are contained in the readme file for patch 14538078. Please bear in mind, that the readme states to apply a separate patch 13952743 to JDeveloper, too
11.1.1.6.5	Patch 14696072 Patch 14733356 Patch 14678543 Patch 14733390 Patch 14733413 Patch 14665284 Patch 14733370 as well as Patch 13952743	19th October 2012	This is a cumulative bundle patch to go on top of current 11.1.1.6.X releases  (excluding FA 11.1.1.6.3.X). Patch 14696072 (1 of 7) Oracle Business Intelligence Installer. Bug 14696072 Patch 14733356 (2 of 7) Oracle Real Time Decisions. Bug 14733356 Patch 14678543 (3 of 7) Oracle Business Intelligence Publisher Bug 14678543 Patch 14733390 (4 of 7) Oracle Business Intelligence ADF Components. Bug 14733390 Patch 14733413 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x. Bug 14733413 Patch 14665284 (6 of 7) Oracle Business Intelligence Bug 14665284 Patch 14733370 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer. Bug 14733370 Note: The Readme files for the above patches describe the bugs fixed in each patch, and any known bugs with the patch. This patch is cumulative, and therefore, contains all of the fixes included in the earlier 11.1.1.6.2 and 11.1.1.6.4 patchsets. However, lists of fixes from included patchsets need to be looked up in the respective patches' readme files, and are not included in the above patches' readme files. The instructions to apply the above patches are identical, and are contained in the readme file for patch 14696072. Please bear in mind, that the readme states to apply patch 13952743for JDeveloper, too. When downloading, please select the 11.1.1.6.0 release of this Patch rather than the 11.1.1.6.5 release.
11.1.1.6.6	Patch 15844023 Patch 15844066 Patch 14800665 Patch 15843961 Patch 15844096 Patch 14791926 Patch 15839347 as well as Patch 13952743	26th November 2012	This is a cumulative bundle patch to go on top of current 11.1.1.6.X releases  (excluding FA 11.1.1.6.3.X).     Patch 15844023 (1 of 7) Oracle Business Intelligence Installer.     Patch 15844066 (2 of 7) Oracle Real Time Decisions.     Patch 14800665 (3 of 7) Oracle Business Intelligence Publisher.     Patch 15843961 (4 of 7) Oracle Business Intelligence ADF Components.     Patch 15844096 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x.     Patch 14791926 (6 of 7) Oracle Business Intelligence.     Patch 15839347 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer Note: The Readme files for the above patches describe the bugs fixed in each patch, and any known bugs with the patch. This patch is cumulative, and therefore, contains all of the fixes included in the earlier 11.1.1.6.2, 11.1.1.6.4 and 11.1.1.6.5 patch sets. However, lists of fixes from included patch sets need to be looked up in the respective patches' readme files, and are not included in the above patches' readme files. The instructions to apply the above patches are identical, and are contained in the readme file for patch 15844023. Please bear in mind, that the readme states to apply patch 13952743for JDeveloper, too.
11.1.1.6.7	Patch 15959887 Patch 15959877 Patch 15929063 Patch 15959899 Patch 15959861 Patch 15894670 Patch 15959917	31st December 2012	This is a cumulative bundle patch to go on top of current 11.1.1.6.X releases  (excluding FA 11.1.1.6.3.X).     Patch 15959887 (1 of 7) Oracle Business Intelligence Installer.     Patch 15959877 (2 of 7) Oracle Real Time Decisions.     Patch 15929063 (3 of 7) Oracle Business Intelligence Publisher.     Patch 15959899 (4 of 7) Oracle Business Intelligence ADF Components.     Patch 15959861 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x.     Patch 15894670 (6 of 7) Oracle Business Intelligence.     Patch 15959917 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer Note: The Readme files for the above patches describe the bugs fixed in each patch, and any known bugs with the patch. This patch is cumulative, and therefore contains all of the fixes included in the earlier 11.1.1.6.2, 11.1.1.6.4, 11.1.1.6.5 and 11.1.1.6.6 patch sets. However, lists of fixes from included patch sets need to be looked up in the respective patches' readme files, and are not included in the above patches' readme files. The instructions to apply the above patches are identical, and are contained in the readme file for patch 15959887. Please bear in mind, that the readme states to apply patch 13952743for JDeveloper, too.
11.1.1.6.8	Patch 16094198 Patch 16094192 Patch 16080520 Patch 16094205 Patch 16094211 Patch 16067995 Patch 16097082	5th February 2013	This is a cumulative bundle patch to go on top of current 11.1.1.6.X releases  (excluding FA 11.1.1.6.3.X). Patch 16094198 (1 of 7) Oracle Business Intelligence Installer. (generic) Patch 16094192 (2 of 7) Oracle Real Time Decisions. (generic) Patch 16080520 (3 of 7) Oracle Business Intelligence Publisher. (generic) Patch 16094205 (4 of 7) Oracle Business Intelligence ADF Components. (generic) Patch 16094211 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x. (port-specific) Patch 16067995 (6 of 7) Oracle Business Intelligence. (port-specific) Patch 16097082 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer  (generic) Note: The Readme files for the above patches describe the bugs fixed in each patch, and any known bugs with the patch. This patch is cumulative, and therefore contains all of the fixes included in the earlier 11.1.1.6.2, 11.1.1.6.4, 11.1.1.6.5, 11.1.1.6.6 and 11.1.1.6.7 patch sets. However, lists of fixes from included patch sets need to be looked up in the respective patches' readme files, and are not included in the above patches' readme files. The instructions to apply the above patches are identical, and are contained in the readme file for patch 16094198. Please bear in mind, that the readme states to apply patch 13952743for JDeveloper, too.
11.1.1.6.9	Patch 16287811 Patch 16287778 Patch 16237960 Patch 16287840 Patch 16287854 Patch 16227549 Patch 16287884	8th March 2013	This is a cumulative bundle patch to go on top of current 11.1.1.6.X releases  (excluding FA 11.1.1.6.3.X). Patch 16287811 (1 of 7) Oracle Business Intelligence Installer. (generic) Patch 16287778 (2 of 7) Oracle Real Time Decisions. (generic) Patch 16237960 (3 of 7) Oracle Business Intelligence Publisher. (generic) Patch 16287840 (4 of 7) Oracle Business Intelligence ADF Components. (generic) Patch 16287854 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x. (port-specific) Patch 16227549 (6 of 7) Oracle Business Intelligence. (port-specific) Patch 16287884 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer  (generic) Note: The Readme files for the above patches describe the bugs fixed in each patch, and any known bugs with the patch. This patch is cumulative, and therefore, contains all of the fixes included in the earlier 11.1.1.6.2, 11.1.1.6.4, 11.1.1.6.5, 11.1.1.6.6, 11.1.1.6.7 and 11.1.1.6.8 patch sets. However, lists of fixes from included patch sets need to be looked up in the respective patches' readme files, and are not included in the above patches' readme files. The instructions to apply the above patches are identical, and are contained in the readme file for Patch 16287811 Please bear in mind, that the readme states to apply Patch 13952743for JDeveloper, too.
11.1.1.6.10	Patch 16504136 Patch 16504143 Patch 16504148 Patch 16504154 Patch 16504156 Patch 16427939 Patch 16504161	30th April 2013	This is a cumulative bundle patch to go on top of current 11.1.1.6.X releases  (excluding FA 11.1.1.6.3.X). Patch 16504136 (1 of 7) Patch (1 of 7) Oracle Business Intelligence Installer (BIINST) (generic) Patch 16504143 (2 of 7) Patch (2 of 7) Oracle Real Time Decisions (RTD) (generic) Patch 16504148 (3 of 7) Oracle Business Intelligence Publisher (BIP) (generic) Patch 16504154 (4 of 7) Oracle Business Intelligence ADF Components (BIADFCOMPS) (generic) Patch 16504156 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x (BIFNDNEPM) (port-specific) Patch 16427939 (6 of 7) Oracle Business Intelligence: (OBIEE) (port-specific) Patch 16504161 Patch (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer (generic) Note: The Readme files for the above patches describe the bugs fixed in each patch, and any known bugs with the patch. This patch is cumulative, and therefore, contains all of the fixes included in the earlier 11.1.1.6.2, 11.1.1.6.4, 11.1.1.6.5, 11.1.1.6.6, 11.1.1.6.7, 11.1.1.6.8 and 11.1.1.6.9 patch sets. However, lists of fixes from included patch sets need to be looked up in the respective patches' readme files, and are not included in the above patches' readme files. The instructions to apply the above patches are identical, and are contained in the readme file for Patch 16504136 Please bear in mind, that the readme states to apply Patch 13952743for JDeveloper, too.
11.1.1.6.11	Patch 16747681 Patch 16747684 Patch 16747692 Patch 16747699 Patch 16747703 Patch 16717325 Patch 16747708	June 28th, 2013	Patch 16747681 (1 of 7) Patch (1 of 7) Oracle Business Intelligence Installer (BIINST) (generic) Patch 16747684 (2 of 7) Patch (2 of 7) Oracle Real Time Decisions (RTD) (generic) Patch 16747692 (3 of 7) Oracle Business Intelligence Publisher (BIP) (generic) Patch 16747699 (4 of 7) Oracle Business Intelligence ADF Components (BIADFCOMPS) (generic) Patch 16747703 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x (BIFNDNEPM) (port-specific) Patch 16717325 (6 of 7) Oracle Business Intelligence: (OBIEE) (port-specific) Patch 16747708 Patch (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer (generic) Note: The Readme files for the above patches describe the bugs fixed in each patch, and any known bugs with the patch. This patch is cumulative, and therefore, contains all of the fixes included in the earlier 11.1.1.6.2, 11.1.1.6.4, 11.1.1.6.5, 11.1.1.6.6, 11.1.1.6.7, 11.1.1.6.8, 11.1.1.6.9 and 11.1.1.6.10 patch sets. However, lists of fixes from included patch sets need to be looked up in the respective patches' readme files, and are not included in the above patches' readme files. The instructions to apply the above patches are identical, and are contained in the readme file for Patch 16747681 Please bear in mind, that the readme states to apply Patch 13952743for JDeveloper, too.

Read More

OBIEE 11g - Application Roles Migration from Dev To Prod

1) To migrate security there is a WLST method called as MigrateSecurityStore which can move the roles from one system-jazn-data.xml to the other. To do that, we need to first copy the system-jazn-data.xml from Dev instance to the Prod instance (to a temporary directory). In the same way copy the system-jazn-data.xml from the Prod instance to the temporary directory.

2) Take a backup of all these files before doing the migration. Now, copy the jps-config.xml from any one of the instances (Dev or Prod) to the temporary Directory. And rename the file to jps-config-policy.xml.

3) Open up the jps-config-policy.xml. You will notice that this file contains all the details about all the stores in the environment. Now, for policy migration, we need to basically use this file to point to the locations of the Dev and Prod (source & target) system-jazn-data.xml files. Also, since we are doing only policy migration, we do not need the other store related tags within this file. Remember that we are modifying only a copy of the jps-config.xml. This copy file will be used only for migration.

You will need the following lines for migration:

<!-- JPS XML Policy Store Service Instance -->


<serviceInstance name="policystore.xml" provider="policystore.xml.provider" location="c:/temp/dev/system-jazn-data.xml">
<description>File Based Policy Store Service Instance</description>
</serviceInstance>


<serviceInstance name="policystore.xml" provider="policystore.xml.provider" location="c:/temp/prod/system-jazn-data.xml">
<description>File Based Policy Store Service Instance</description>
</serviceInstance>

4) Now that we have our jps-config file ready, navigate to the temporary directory from command prompt and initialize the WLST using the following command.

In WLST, application role migration can be done even in offline mode. So, effectively there is no need for us to connect to the weblogic admin server. In offline interactive mode, fire the following command

migrateSecurityStore(type=”appPolicies”, srcApp=”obi”, configFile=”C:/SecurityMigration/jps-config-policy.xml”, src=”sourceFileStore”, dst=”targetFileStore”, overWrite=”false”) 

Read More

Informatica - Aggregate Transformation

• Connected and Active Transformation
• The Aggregator transformation allows us to perform aggregate calculations, such as averages and sums.
• Aggregator transformation allows us to perform calculations on groups.

Components of the Aggregator Transformation 

1.   Aggregate expression 

2.   Group by port 

3.   Sorted Input 

4.   Aggregate cache

1) Aggregate Expressions

• Entered in an output port.
• Can include non-aggregate expressions and conditional clauses.

The transformation language includes the following aggregate functions:

• AVG, COUNT, MAX, MIN, SUM
• FIRST, LAST
• MEDIAN, PERCENTILE, STDDEV, VARIANCE

Single Level Aggregate Function: MAX(SAL) 

Nested Aggregate Function: MAX( COUNT( ITEM ))

Nested Aggregate Functions

• In Aggregator transformation, there can be multiple single level functions or multiple nested functions.
• An Aggregator transformation cannot have both types of functions together.
• MAX( COUNT( ITEM )) is correct.
• MIN(MAX( COUNT( ITEM ))) is not correct. It can also include one aggregate function nested within another aggregate function

Conditional Clauses 

We can use conditional clauses in the aggregate expression to reduce the number of rows used in the aggregation. The conditional clause can be any clause that evaluates to TRUE or FALSE.

• SUM( COMMISSION, COMMISSION > QUOTA )

Non-Aggregate Functions 

We can also use non-aggregate functions in the aggregate expression.

• IIF( MAX( QUANTITY ) > 0, MAX( QUANTITY ), 0))

2) Group By Ports

• Indicates how to create groups.
• When grouping data, the Aggregator transformation outputs the last row of each group unless otherwise specified.

The Aggregator transformation allows us to define groups for aggregations, rather than performing the aggregation across all input data.

For example, we can find Maximum Salary for every Department.

• In Aggregator Transformation, Open Ports tab and select Group By as needed.

3) Using Sorted Input 

• Use to improve session performance.
• To use sorted input, we must pass data to the Aggregator transformation sorted by group by port, in ascending or descending order.
• When we use this option, we tell Aggregator that data coming to it is already sorted.
• We check the Sorted Input Option in Properties Tab of the transformation.
• If the option is checked but we are not passing sorted data to the transformation, then the session fails.

4) Aggregator Caches 

• The Power Center Server stores data in the aggregate cache until it completes Aggregate calculations.
• It stores group values in an index cache and row data in the data cache. If the Power Center Server requires more space, it stores overflow values in cache files.

Note: The Power Center Server uses memory to process an Aggregator transformation with sorted ports. It does not use cache memory. We do not need to configure cache memory for Aggregator transformations that use sorted ports.

1) Aggregator Index Cache:

The index cache holds group information from the group by ports. If we are using Group By on DEPTNO, then this cache stores values 10, 20, 30 etc.

• All Group By Columns are in AGGREGATOR INDEX CACHE. Ex. DEPTNO

2) Aggregator Data Cache: 

DATA CACHE is generally larger than the AGGREGATOR INDEX CACHE.

Columns in Data Cache:

• Variable ports if any
• Non group by input/output ports.
• Non group by input ports used in non-aggregate output expression.
• Port containing aggregate function

1) Example: To calculate MAX, MIN, AVG and SUM of salary of EMP table. 

• EMP will be source table.
• Create a target table EMP_AGG_EXAMPLE in target designer. Table should contain DEPTNO, MAX_SAL, MIN_SAL, AVG_SAL and SUM_SAL
• Create the shortcuts in your folder.

Creating Mapping:

1. Open folder where we want to create the mapping. 

2. Click Tools -> Mapping Designer. 

3. Click Mapping-> Create-> Give mapping name. Ex: m_agg_example 

4. Drag EMP from source in mapping. 

5. Click Transformation -> Create -> Select AGGREGATOR from list. Give name and click Create. Now click done. 

6. Pass SAL and DEPTNO only from SQ_EMP to AGGREGATOR Transformation. 

7. Edit AGGREGATOR Transformation. Go to Ports Tab

8. Create 4 output ports: OUT_MAX_SAL, OUT_MIN_SAL, OUT_AVG_SAL,

OUT_SUM_SAL 

9. Open Expression Editor one by one for all output ports and give the

calculations. Ex: MAX(SAL), MIN(SAL), AVG(SAL),SUM(SAL) 

10. Click Apply -> Ok. 

11. Drag target table now. 

12. Connect the output ports from Rank to target table. 

13. Click Mapping -> Validate 

14. Repository -> Save 

• Create Session and Workflow as described earlier. Run the Workflow and see the data in target table.
• Make sure to give connection information for all tables.

Read More

Informatica - Source Qualifier Transformation

• Active and Connected Transformation.

• The Source Qualifier transformation represents the rows that the Power Center Server reads when it runs a session.

• It is only transformation that is not reusable.

• Default transformation except in case of XML or COBOL files.

Tasks performed by Source Qualifier:

• Join data originating from the same source database: We can join two or more tables with primary key-foreign key relationships by linking the sources to one Source Qualifier transformation.

• Filter rows when the Power Center Server reads source data: If we Include a filter condition, the Power Center Server adds a WHERE clause to the Default query.

• Specify an outer join rather than the default inner join: If we include a User-defined join, the Power Center Server replaces the join information Specified by the metadata in the SQL query.

• Specify sorted ports: If we specify a number for sorted ports, the

• Power Center Server adds an ORDER BY clause to the default SQL query.

• Select only distinct values from the source: If we choose Select Distinct,the Power Center Server adds a SELECT DISTINCT statement to the default SQL query.

• Create a custom query to issue a special SELECT statement for the Power Center Server to read source data: For example, you might use a Custom query to perform aggregate calculations. The entire above are possible in Properties Tab of Source Qualifier t/f.

SAMPLE MAPPING TO BE MADE:   


 

• Source will be EMP and DEPT tables.

• Create target table as showed in Picture above.

• Create shortcuts in your folder as needed.

Creating Mapping:

1. Open folder where we want to create the mapping.

1. Click Tools -> Mapping Designer.

1. Click Mapping-> Create-> Give mapping name. Ex: m_SQ_example

1. Drag EMP, DEPT, Target.

1. Right Click SQ_EMP and Select Delete from the mapping.

1. Right Click SQ_DEPT and Select Delete from the mapping.

1. Click Transformation -> Create -> Select Source Qualifier from List -> Give Name -> Click Create

1. Select EMP and DEPT both. Click OK.

1. Link all as shown in above picture.

1. Edit SQ -> Properties Tab -> Open User defined Join -> Give Join condition EMP.DEPTNO=DEPT.DEPTNO. Click Apply -> OK

1. Mapping -> Validate

1. Repository -> Save

• Create Session and Workflow as described earlier. Run the Workflow and see the data in target table.

• Make sure to give connection information for all tables.

SQ PROPERTIES TAB 
1) SOURCE FILTER: 
We can enter a source filter to reduce the number of rows the Power Center Server queries. 
Note: When we enter a source filter in the session properties, we override the customized SQL query in the Source Qualifier transformation. 

Steps:

1. In the Mapping Designer, open a Source Qualifier transformation.

1. Select the Properties tab.

1. Click the Open button in the Source Filter field.

1. In the SQL Editor Dialog box, enter the filter. Example: EMP.SAL)2000

1. Click OK.

Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output. 


2) NUMBER OF SORTED PORTS: 
When we use sorted ports, the Power Center Server adds the ports to the ORDER BY clause in the default query. 
By default it is 0. If we change it to 1, then the data will be sorted by column that is at the top in SQ. Example: DEPTNO in above figure.

• If we want to sort as per ENAME, move ENAME to top.

• If we change it to 2, then data will be sorted by top two columns.

Steps:

1. In the Mapping Designer, open a Source Qualifier transformation.

1. Select the Properties tab.

1. Enter any number instead of zero for Number of Sorted ports.

1. Click Apply -> Click OK.

Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output. 

3) SELECT DISTINCT: 
If we want the Power Center Server to select unique values from a source, we can use the Select Distinct option.

• Just check the option in Properties tab to enable it.

4) PRE and POST SQL Commands 

• The Power Center Server runs pre-session SQL commands against the source database before it reads the source.

• It runs post-session SQL commands against the source database after it writes to the target.

• Use a semi-colon (;) to separate multiple statements. 

5) USER DEFINED JOINS
Entering a user-defined join is similar to entering a custom SQL query. However, we only enter the contents of the WHERE clause, not the entire query.

• We can specify equi join, left outer join and right outer join only. We Cannot specify full outer join. To use full outer join, we need to write SQL Query.

Steps:

1. Open the Source Qualifier transformation, and click the Properties tab.

1. Click the Open button in the User Defined Join field. The SQL Editor Dialog Box appears.

1. Enter the syntax for the join.

1. Click OK -> Again Ok.

Validate the mapping. Save it. Now refresh session and save the changes. Now run the workflow and see output. 

Join Type	Syntax
Equi Join	DEPT.DEPTNO=EMP.DEPTNO
Left Outer Join	{EMP LEFT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}
Right Outer Join	{EMP RIGHT OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO}

6) SQL QUERY 
For relational sources, the Power Center Server generates a query for each Source Qualifier transformation when it runs a session. The default query is a SELECT statement for each source column used in the mapping. In other words, the Power Center Server reads only the columns that are connected to another Transformation. 
In mapping above, we are passing only SAL and DEPTNO from SQ_EMP to Aggregator transformation. Default query generated will be:

• SELECT EMP.SAL, EMP.DEPTNO FROM EMP

Viewing the Default Query

1. Open the Source Qualifier transformation, and click the Properties tab.

1. Open SQL Query. The SQL Editor displays.

1. Click Generate SQL.

1. The SQL Editor displays the default query the Power Center Server uses to Select source data.

1. Click Cancel to exit.

Note: If we do not cancel the SQL query, the Power Center Server overrides the default query with the custom SQL query. 
We can enter an SQL statement supported by our source database. Before entering the query, connect all the input and output ports we want to use in the mapping.
Example: As in our case, we can’t use full outer join in user defined join, we can write SQL query for FULL OUTER JOIN: 

SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, EMP.EMPNO, EMP.ENAME, EMP.JOB, EMP.SAL, EMP.COMM, EMP.DEPTNO FROM EMP FULL OUTER JOIN DEPT ON DEPT.DEPTNO=EMP.DEPTNO WHERE SAL>2000 

• We also added WHERE clause. We can enter more conditions and write More complex SQL.

We can write any query. We can join as many tables in one query as Required if all are in same database. It is very handy and used in most of the projects.

Important Points:

• When creating a custom SQL query, the SELECT statement must list the port names in the order in which they appear in the transformation.

Example: DEPTNO is top column; DNAME is second in our SQ   mapping.
So when we write SQL Query, SELECT statement have name DNAME first, DNAME second and so on. SELECT DEPT.DEPTNO, DEPT.DNAME 

• Once we have written a custom query like above, then this query will Always be used to fetch data from database. In our example, we used WHERE SAL>2000. Now if we use Source Filter and give condition SAL) 1000 or any other, then it will not work. Informatica will always use the custom query only.

• Make sure to test the query in database first before using it in SQL Query. If query is not running in database, then it won’t work in Informatica too.

• Also always connect to the database and validate the SQL in SQL query editor

Read More