Saturday, January 5, 2013

DAC - Installation and Configuration v10.1.3.4.1 on Windows

For instance, for Oracle :
CREATE USER DAC_REP IDENTIFIED BY DAC_REP 
DEFAULT tablespace users
TEMPORARY tablespace temp
quota unlimited ON users;
 
GRANT CONNECT, resource, CREATE VIEW TO DAC_REP;

DAC Configuration Handled by the DAC Installer

The DAC installer handles the following configuration:
  • In the config.bat file, the DAC installer configures the JAVA_HOME and DAC_HOME variables.
  • In the dac_env.bat file, the DAC installer creates an environment variable named INFA_DOMAINS_FILE and sets the value to the directory path of the domans.infa file.
  • The DAC installer adds the directory path to Informatica PowerCenter binaries to the PATH environment variable.
Procedure
  • Unzip the dac downloaded file
  • Start the installation with the dac\Disk1\setup.exe
  • Install location : C:\orahome\10gR3_1
  •  you have to choose 8.x
  • Informatica 9x location :
    • Informatica Powercenter : C:\Informatica\PowerCenter9.x.x\server
    • Informatica PowerCenter Domain File : C:\Informatica\PowerCenter8.x.x\domains.infa
  • Prerequisite check : Ok.
  • Summary
  • Click the Install button
  • Success !

Patch

Installation of the patch, Download latest patch from oracle site.
  • extract the content of the file p xxxxxxxxx_Generic.zip
  • Terminate all DAC clients and server(s).
  • Rename the jar file DAC_BASE\bifoundation\dac\DAWSystem.jar as DWASystem.jar.old
  • Rename the xml file DAC_BASE\bifoundation\dac\conf\infa_command.xml as infa_command.xml.old
  • Copy the patched up DAWSystem.jar file into DAC home directory (DAC_BASE\bifoundation\dac\)
  • Copy the patched up infa_command.xml file into DAC_BASE\bifoundation\dac\conf\

JDBC Driver

DAC requires JDBC drivers for database connectivity. To enable DAC database connectivity, you must install the appropriate JDBC driver in thedac\lib directory on the machines where the DAC Client and Server are installed.
To install JDBC drivers in the \dac\lib directory, copy the file corresponding to your database version from the directory oracle_database_home\jdbc\lib directory and paste it in the $ORACLE_DAC_HOME\bifoundation\dac\lib directory.
  • For Oracle database 11g, ojdbc6.jar
  • For Oracle database 9.x or 10.x, ojdbc14.jar

Creation of the Repository

Start the DAC Client by navigating to the $ORACLE_DAC_HOME\bifoundation\dac directory and double-clicking the startclient.bat file (of in the start menu)
  • The Login … dialog box appears.
  • Click Configure.
  • In the Configuring … dialog box, select Create Connection, and then click Next.
  • Enter the appropriate connection information:
FieldRequired Value
NameEnter a unique name for the connection to the DAC Repository
Connection typeSelect the type of database in which the DAC Repository will be stored.
Connection String, or Database name, or TNS Name, or InstanceSelect the database name or database account name of the DAC Repository. 
If you are using: 
* Oracle (OCI8), use the tnsnames entry. 
* Oracle (Thin), use the instance name. 
* SQL Server, use the database name. 
* DB2-UDB, use the connect string as defined in the DB2 configuration.
Database HostEnter the name of the machine where the DAC Repository will reside.
Database PortEnter the port number on which the database listens. For example, for an Oracle database the default port is 1521, or for a SQL Server database the default port is 1433.
Optional URLCan be used to override the standard URL for this connection.
Optional DriverCan be used to override the standard driver for this connection.
Authentication FileClick in this field to do one of the following: 
* Select an existing authentication file. 
* Create a new authentication file.
To create a new authentication file, do the following:
  • Click in the Authentication File field of the Configuring… dialog box.
  • In the Authentication File dialog box, select Create authentication file.
  • In the Create Authentication File dialog box, enter a unique name for the authentication file, and click OK. (Oracle.con for instance)
The file is located in the directory dac_home\bifoundation\dac\conf\connections
  • Enter the Table Owner Name and Password for the database where the repository will reside.
  • In the Configuring… dialog box, click Test Connection to confirm the connection works.
  • Click Apply, and then click Finish.
Summary :
  • In the Login… dialog box, do the following:
    • Select the Connection just created from the drop-down list.
    • Enter Administrator as the User Name.
    • Enter Administrator as the Password.
    • Click Login and follow the picture below

Configuration of the connection between the DAC Server and the DAC Repository

This step configures the Connection Between the DAC Server and DAC Repository.
This step must be performed on the dac server machine
When the DAC server is installed on UNIX OR Linux, use the serverSetupPrompt.sh to configure
the connection between the DAC server and the DAC repository




ODBC Database Connections for the DAC Client

The DAC Client uses an ODBC connection to create and alter tables in the Oracle Business Analytics Warehouse.
Create a DSN Odbc Connection to the Oracle Business Analytics Warehouse database on the machine that hosts the DAC Client with the following:
  • Data Source Name: Enter any meaningful name such as OBIA_DWH
  • Server Name: Enter the tnsname of the Oracle Business Analytics Warehouse.
  • Client Version: Select 10gR1 for 10g and 11g databases.
  • ODBC Driver: Oracle Merant ODBC Driver that is installed with the DAC platform installation.

Enable Communication Between Informatica PowerCenter and DAC (with pmcmd and pmrep)

DAC uses the Informatica pmrep and pmcmd command line programs to communicate with Informatica PowerCenter in the following ways:
  • DAC Server uses
    • pmrep to communicate with PowerCenter Repository Services.
    • pmcmd to communicate with PowerCenter Integration Services to run the Informatica workflows.
  • DAC Client uses
    • pmrep to synchronize tasks with Informatica workflows and to keep the DAC task source and target tables information up to date.
The pmrep/pmcmd program must be installed:
  • in the PowerCenter Client (\PowerCenter_home\client\bin)
  • and in the PowerCenter Services bin directories (\PowerCenter_home\server\bin).
It's why the two software (client as server) must co-located.
The pmrep is already installed with the default installations and the pmcmd program must be manually copied from the PowerCenter Services bin folder to the PowerCenter Client bin folder.

Copying the DAC Metadata Files

DAC metadata files are extracted by the Oracle BI Applications installer (in the directory OBIEE_HOME\OraclelBI1\biapps\dwrep\DAC_metadata\) and you need to copy these files to the machines hosting the DAC Client and Server.
DAC_Client in this table is the directory OBIEE_HOME/OraclelBI1\biapps\dwrep\DAC_metadata
DAC MachineFile/Folder NameSource File/FolderDestination File/Folder
ClientThe complete Export folderDAC_metadata\DAC_Client\exportDacHome\bifoundation\dac\export
ClientThe deletetriggers.list fileDAC_metadata\DAC_ClientDacHome\bifoundation\dac\conf
ServerThe parameterfileOLTP.txt fileDAC_metadata\DAC_ServerDacHome\bifoundation\dac\Informatica\parameters\input
ServerThe parameterfileDW.txt fileDAC_metadata\DAC_ServerDacHome\bifoundation\dac\Informatica\parameters\input

Importing Metadata into the DAC Repository

  • To avoid possible ETL errors in other applications (for example, missing tasks), you must import the Universal application.
  • Do not select the Data Warehouse option unless Oracle specifically instructs you to do so. This container is reserved for special purposes.
  • Make a note of the Application names that you select. When you create data warehouse tables later in the configuration process, you might need to type in the names exactly as they are displayed here
  • If you are importing DAC metadata for the first time, you should select the Truncate Repository Tables check box. If you are importing DAC metadata into a repository that already has metadata, do the following:
  • Select the Enable Batch Mode check box. If you are using an Oracle 9i Release 2 database, you cannot use batch mode because the Oracle 9i JDBC drivers do not handle array inserts or bulk inserts.
  • To confirm that you want to import the seed data selected, re-type the text in the text box and click Yes.
If your source or target database is a DB2/390 database, run DAC_DB2390_Source.sql or DAC_DB2390_Target.sql immediately after importing the seed data by connecting to the database where the DAC Repository resides. These files are stored in the \DAC directory.

Creating the Data Warehouse Tables

  • The Oracle Business Analytics Warehouse tables are created by the DAC Client. The DAC Client uses ODBC connections to the Oracle Business Analytics Warehouse database for this procedure.
  • Make sure that the SSE role has been created for the Oracle Business Analytics Warehouse, and that the database user has been associated with the role
sys@obia_dwh>SELECT * FROM V$NLS_PARAMETERS WHERE parameter = 'NLS_CHARACTERSET';
 
PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8
If a 'Success' message is displayed, the data warehouse tables have been created. If you want to see log information about the process, use the following log files.
  • DAC_Home\bifoundation\log\config\generate_ctl.log - A log of the schema definition process, including details of any conflicts between containers.
  • DAC_Home\bifoundationlog\config\createtables.log - A log of the DDLIMP process.
If a 'Failure' message is displayed, the data warehouse tables have not been created. Use the log information in DAC_Home\bifoundation\log\config\generate_ctl.log to diagnose the error. The createtables.log is not generated.

Starting the DAC Server

On Windows, start the DAC Server by double-clicking the \DAC\startserver.bat script.

DAC Setup

DAC System Properties

Where:
  • DAC Server Host is the name or IP address of the machine that hosts the DAC Server.
  • DAC Server OS is the operating system of the machine that hosts the DAC Server. Possible values are Windows, AIX, Solaris, HP-UX, Linux (case sensitive).
  • InformaticaParameterFileLocation is the path to the Informatica source file directory (the $PMSourceFileDir parameter of Informatica PowerCenter), for example \Informatica\PowerCenter 9.1\ server\infa_shared\SrcFiles. You can find it the PowerCenter Administration Console > General Properties area of the Processes tab. It contains theParameterFile of the PowerCenter Mapping.

Informatica Servers

Be sure that you have the INFA_HOME\Server\bin and INFA_HOME\Client\bin in the PATH environment variable. Otherwise DAC Server and DAC client can't found the pmcmd and pmcrep programs and the connection test will fail


Physical Data Source

The Physical Data Sources tab displays a precreated record for the data warehouse with the name DataWarehouse, and one or more records for the OLTP sources. The records that are created by DAC for the OLTP sources depend on the business application source systems you selected when importing the DAC metadata.
You must set up as data source only :
  • the datawarehouse database (DataWarehouse)
  • and the source database (ORA_R1211)
    The file “Connection Type” doesn't need any configuration.


where:
  • Dependency Priority is the number used to generate dependencies when designing execution plans.
  • Data Source Number is an Unique number assigned to the data source category so that the data can be identified in the data warehouse. For example, the value '1' is used for Siebel data sources. Oracle recommends that you do not change the default value.  If you are specifying a data  source without using a pre defined template, you must use the correct value for the source catagory, for example, if your specify an Oracle EBS R12 data source, you must specify the  DATASOURCE_NUM_ID  for R12.1.1value '999' or R12.1.3 '1000' , The data source number is passed as a parameter to the Informatica workflows. If you are using multiple sources, each data source has a unique number. Typically, all source dependent extracts will use this parameter to populate the DATASOURCE_NUM_ID column, and the source independent workflows will carry these values to the final dimension and fact tables.

Email Recipient

Create the mails that you need on the setup > Email Recipient tab with the notification levels as follows:
  • 10 – Notifies recipient of success or failure of each task.
  • 5 – Notifies recipient of success of failure of the entire ETL process.
  • 1 – Notifies recipient that ETL completed successfully.
Inactive indicates whether the selected email activation is active or inactive.
To set up the SMTP parameters, you must go to Tools > DAC Server Setup > Email Configuration 

Creation of a container

You cannot make any changes to the preconfigured containers. You must make a copy of a container before you can make any changes to it

Source System Parameters

You set source system parameters (also known as DAC ETL Preferences) in DAC to specify how the ETL routines process data for a container. For example, if operating in the United States, you might set the $$DLFT_COUNTRY to 'USA' to identify your data.
The following preferences are applied to all tasks within a container,If extracting data from more than one source, these preferences will need to be re-applied to each associated container. They can be overridden at the task level by adding the parameter to the specific task and assigning a value there.
With the copy of the container, modify the parameters:

OBIEE

  • Configuring the Oracle BI Repository Connections
    • Especially the repository variables OLAP_DSN, OLAP_USER and OLAPTBO.
  • And don't forget to unzip the catalog file: OBIEE_HOME\Oracle_BI1\biapps\catalog\EnterpriseBusinessAnalytics.zip

    The Oracle BI Applications repository file named OracleBIAnalyticsApps.rpd in the OBIEE_HOME\Oracle_BI1\biapps\repository folder has as credential: Administrator/SADMIN or Administrator/Admin123

Post-task installation

Duplicate rows in per_all_people_f

Before starting a full load (Bi Apps: Problems With Index Creation During Full Load), you must be sure that you don't have any duplicate in the table per_all_people_f. You may find in the vision database (the sample database of EBS) a bad record.
SELECT person_id,
  effective_start_date,
  effective_end_date
FROM per_all_people_f
WHERE person_id = 6272;
person_ideffective_start_dateeffective_end_date
627204-JAN-9106-FEB-02
627204-JAN-9131-DEC-12
A person id can't be effective for the same date. This is technically possible because the database uniqueness depends on person_id, effective_start_date, effective_end_date, all three of them. However, this is functionally not possible. You must then correct the second line with the value 07-FEB-02 for the effective_start_date.

My Oracle Support

  • Database connection name PARAM_OLTP_ORA11I is not well defined
Following 2 mappings failing in 7.9.6.3 (SDE_ORA_Stage_ARTransactionFact_DiffManDerive, SDE_ORA_Stage_GLRevenueFact_DiffManDerive ) with this error :“Database connection name PARAM_OLTP_ORA11I for database connection variable $DBConnection_OLTP is not well defined.” See this solution: SDE_ORA_Stage_ARTransactionFact_DiffManDerive error
  • ORA-00001: unique constraint error for W_AR_XACT_F_U1 index
Don´t add the “SA Financial - Group Account Cleanup” subject area in the execution plan. See ORA-00001: unique constraint error for W_AR_XACT_F_U1 index

No gateway connectivity is provided for domain

In the DAC log file (located DAC_Home\10gR3_1\bifoundation\dac\log), you can find the log file of the execution (such as Financial_Analytics_Gerardnico.21484569.log) and found this error:
Request to start workflow : 'SIL_InsertRowInRunTable' has completed with error code 3
Error Message : An error occurred in starting or running or stopping the workflow or task.
Just grab the pmcmd command and test it in a DOS shell.
>pmcmd startworkflow -sv int_obia7961 -d Domain_OBIA7961 -u dac -p dac -wait -f SILOS -paramfile E:\Informatica\PowerCenter8.6.1\server\infa_shared\SrcFiles\FlatFileConnection.DataWarehouse.SILOS.SIL_InsertRowInRunTable.txt SIL_InsertRowInRunTable

Informatica(r) PMCMD, version [8.6.1 HotFix10], build [412.0123], Windows 32-bit
Copyright (c) Informatica Corporation 1994 - 2010
All Rights Reserved.

Invoked at Sat Nov 06 13:15:54 2010

[PCSF_46007] No gateway connectivity is provided for domain [Domain_OBIA7961].
ERROR: Cannot connect to Integration Service [int_obia7961].

Completed at Sat Nov 06 13:15:54 2010
This error comes from that pmcmd can't find the dd'efinition file of the domains. Just set up the INFA_DOMAINS_FILE environment variable to point out to the domains.infa file such as:
C:\>set INFA_DOMAINS_FILE=E:\Informatica\PowerCenter8.6.1\domains.infa
More log: E:\Informatica\PowerCenter8.6.1\server\infa_shared\SessLogs

W_PARAM_G - Lookup table not found in the database

I don't know why but it seems that the current schema is not the good one when integration service does a connection with the Oracle Database.
To resolve this problem, you can add this command in the connection environment SQL of the relational database parameters:
ALTER SESSION SET CURRENT_SCHEMA = "DWH_REP";

Running A Full Load ETL

After you have installed and configured Oracle BI Applications, your Oracle Business Analytics Warehouse (OLAP) database is empty. You need to perform a full load ETL to populate your Oracle Business Analytics Warehouse.
The ETL processes for Oracle BI Applications are created and managed in DAC.
This section uses an example to show you how to get started quickly with running a full load ETL. In this example, you have installed Oracle Financial Analytics with an Oracle EBS OLTP data source, and you want to load OLTP data for the subject area Receivables, as follows:
  • In the Design/Setup view, you may still display the Properties tab and verify them.
  • Then Display the Execute view, and display the Execution Plan tab.
  • Click New, display the Edit tab, and use the Name field to specify a name for the ETL process save.
  • Display the Subject Areas tab and click Add/Remove to display the Choose Subject Areas dialog box.
  • Select the new container that you created in step 4 from the container drop-down list at the top of the Choose Subject Areas dialog box.
  • Select Financials - Receivables, click Add, then click OK.
  • Display the Parameters tab, and click Generate.
  • On the Parameters tab, edit the parameters as follows:
    • Edit the value of DBConnection_OLAP and set it to the same value as the name of the OLAP database that you specified in the Physical Data Source dialog box (for example, DataWarehouse).
    • Edit the value of DBConnection_OLTP and set it to the same value as the name of the OLTP database that you specified in the Physical Data Source dialog box (for example, ORA_R12.1.1.3).
    • If there is a FlatFileConnection parameter, edit the value of FlatFileConnection and set it to the same value as the name of the flat file data source that is specified in the Physical Data Source dialog box (for example, ORA_R12.1.1.3_Flatfile).
  • On the Execution Plans tab, click Build. Answer the question.
  • On the Execution Plans tab, click Run Now. DAC will perform a full load for Financials - Receivables.
  • Use the Current Run tab to check the status of the ETL run.
If the ETL run:
  • Fails, you can see information in the status description and the error code column, otherwise you will find useful information in the Log files
  • Was successful, you will see 'Success' in the Run Status field, and the End Timestamp value will be set to the time and date when the ETL was completed. And if you have set a SMTP server, you must received this kind of mail:

    Notice the global log file. It's in the DAC log directory and have the process id number
If an ETL run fails, you cannot re-run the ETL until the failed ETL has been cleared from the Current Run tab. To clear an ETL from the Current Run tab, right click on the ETL and select Mark As Completed.

Problem during the load

For any DAC problem (also during the load), see the log files.

DAC_HOME\10gR3_1\bifoundation\dac\log

DAC

Metadata Import fails

If the process fails, use the \DAC\log\import.log file to diagnose errors.

Create Datawarehouse table fails

If a 'Failure' message is displayed, the data warehouse tables have not been created. Use the log information in DAC_Home\bifoundation\log\config\generate_ctl.log to diagnose the error. The createtables.log is not generated.

Server: SEVERE: Checking the localhost with the values defined in the DAC repository!

It's not an error. You have to continue the configuration process in order to set them after the first start of the DAC Server.

Log

The log files are located in DAC_HOME\dac\log. (such as C:\orahome\10gR3_1\bifoundation\dac\log)
Example of content of an error file:
ANOMALY INFO::: Error while executing pmcmd pingservice -sv obia7961int_obia7961 -d Domain_OBIA7961
MESSAGE:::Cannot run program "pmcmd": CreateProcess error=2, The system cannot find the file specified
For the tip, this error occurs when you forget to add the INFA_HOME\Server\bin or/and INFA_HOME\Client\bin in the PATH environment variable

How to ping an informatica domain ?

E:\Informatica\PowerCenter8.6.1\server\bin>infacmd.bat ping
[ICMD_10135] Command requires at least one option specified.
[ICMD_10004] Usage:
  ping [<-DomainName|-dn> domain_name]
       [<-ServiceName|-sn> service_name]
       [<-GatewayAddress|-dg> domain_gateway_host:port]
       [<-NodeName|-nn> node_name]
       [<-ResilienceTimeout|-re> timeout_period_in_seconds]
 
E:\Informatica\PowerCenter8.6.1\server\bin>infacmd.bat ping -dn "Domain_OBIA7961"
[ICMD_10052] Domain [Domain_OBIA7961] Host:Port [OBIA7961:6001] was successfully pinged.


Posted by at
Labels: