Configure MS SQL database as a Physical Data Source in Oracle DAC

How to use/ configure MS SQL database as a physical datasource in Oracle DAC?

The general approach in Oracle DAC is to use Oracle databases are physical data sources but once in a while we come across datawarehouse environments which have MS SQL as sources/ targets. Configuring MS SQL physical data source is a little different than Oracle db.

Follow these steps

1. Download sqljdbc4.jar file (many web sources available) and place it in the ..bifoundation\dac\lib folder
2. Modify the file config.bat in folder ..bifoundation\dac . Set SQLSERVERLIB=.\lib\msbase.jar;.\lib\mssqlserver.jar;.\lib\msutil.jar;.\lib\sqljdbc4.jar
3. Open DAC Client and go to Setup -> Physical Data Sources. Create a new physical data source for MS SQL
4. Enter the following details. Name = <source name>, Type = <type of db> , Connection type = MSSQL, Database name = master (default), table owner = <db owner>, password = <password>, DB Host= <machine name\SQLEXPRESS> . URL =  jdbc:sqlserver://<hostname:port>;databaseName=<schema name> Eg: jdbc:sqlserver://client39:3617;databaseName=datawarehouse1

Mapplet parameters like $$ETL_PROC_WID are not getting intialized while running through DAC

Are your mappings failing because mapplet parameters like $$ETL_PROC_WID are not getting intialized while running through DAC?

Reason: 
DAC doesn't differentiate between a mapplet/ mapping parameter,
This means that while initializing mapplet parameters DAC does not prefix mapplet name in the parameter file. Because the mapplet name is not prefixed, informatica is not passing values to mapplet parameter like $$ETL_PROC_WID


Solution :
For this to work, we need to modify the custom property of Informatica Integration service.

1. Go to your integration service properties in Informatica Admin Console. 
2. In Custom properties box, Add a Variable name - overrideMpltVarWithMapVar and set its value to Yes. (This variable enables the integration service to accept values for mapplet parameters even though the mapping name is not prefixed to the parameter name.
3. Restart all informatica servers 

How to turn on or off Auto fill feature in Excel 2007?

Auto fill in excel is quite handy feature. Using this you can fill series of cells based on the entries of other excel cells. This feature can be turned on or off using the Excel Advanced Options.

Click on the Office button –> Excel Options

Under Excel Options select Advanced section

By checking or un checking “Enable fill handle and cell drag-and-drop”, you can turn on or off the Auto fill feature in Excel 2007.

Source:
http://blogmines.com/blog/2010/02/18/how-to-turn-on-or-off-auto-fill-feature-in-excel-2007/

Oracle BI Apps - Sales Backlog

Sales Backlog Logic implemented in Oracle BI Apps

What is a Backlog? What is the ETL logic implemented in Oracle BI Apps? These questions will be answered in this blog.

What is a Backlog?

In Simple terms any Sales Order Line that has not been Processed/ Closed within the expected Time is called as a Backlog Sales Order Line.

Description from Oracle BAW DMR 796

For every order line item, there could be a maximum of 2 backlog line items.

Financial: Occurs between the time order is created and time it is invoiced

Operational: Occur between the time order is created and time it is picked.

If picked quantity and invoiced quantity are greater than scheduled quantity, then the item is not backlogged. The above two backlogs could be further classified as scheduled or unscheduled. A backlog is scheduled if Ship date has been set, It is unscheduled if Ship date has not been scheduled. It is delinquent if Order is delinquent in relation to customer’s requested ship date, it is blocked if some detail in order process prevents order from being shipped or invoiced.

What is the ETL implementation logic in Oracle BI Apps?

There are 2 fact tables that store the backlog information.

W_SALES_BACKLOG_LINE_F – This stores backlog line items on a particular date (last ETL run date).

ETL Logic - This table is truncated every time, before a data load is done into the table. The latest backlogs for the current date are fetched from Oracle E biz and stored into the table. The column BACKLOG_PERIOD_DK stores the last date of a calendar month. BACKLOG_DK is the date when the ETL was last run to refresh the backlog tables.

Example- If the last ETL was run on 1^st Jan 2011 and Backlog period is Monthly. Then BACKLOG_DK = 01-JAN-2011 and BACKLOG_PERIOD_DK =31-JAN-2011.

W_SALES_BACKLOG_HISTORY_F- This fact stores the historical monthly snapshots of backlog lines and the snapshot of the current month till date (last ETL run date).

ETL Logic – The Backlog History Fact is loaded from the Backlog Line table and thus Backlog lines fact should be loaded before loading History table. Before the load starts, the BACKLOG_PERIOD_DK value from Backlog lines is fetched and all records that match this value in the history fact are removed. In the next step all records from backlog lines fact are appended into the history fact.

Example – If current ETL run date is 2^nd Jan 2011, then in Backlog Line table BACKLOG_DK = 02-JAN-2011 and BACKLOG_PERIOD_DK =31-JAN-2011. Before loading the Backlog History Fact, all records with BACKLOG_PERIOD_DK=31-JAN-2011 will be deleted, i.e records inserted during any ETL run in the same month (1^st jan) will be deleted. Next, all the Current ETL run date (2^nd Jan) records from Backlog line table will be inserted.

Similarly if next ETL is run on 10^th Jan then, all records inserted during ETL run in the current month (2^nd Jan) will be deleted and Current ETL run records (10^th Jan) will be inserted.

This goes on until the next period/ next month in this case, when the value of BACKLOG_PERIOD_DK changes. Example – If the next ETL run is on 2^nd Feb 2011 then in Backlog Line table BACKLOG_DK = 01-FEB-2011 and BACKLOG_PERIOD_DK =28-FEB-2011. In history fact all records in current period (28-FEB-2011) will be deleted. Since this is the first ETL run in the current month, no records will be deleted. All 1^st Feb records from backlog lines fact will be loaded into backlog history fact.

Case study

Let’s assume the ETL runs have occurred on the below dates

ETL Run Number	ETL Run Date
1	01-JAN-2011
2	02-JAN-2011
3	25-JAN-2011
4	01-FEB-2011
5	05-FEB-2011

The sample data in W_SALES_BACKLOG_LINE_F and W_SALES_BACKLOG_HISTORY_F are shown below.

W_SALES_BACKLOG_LINE_F

BACKLOG_DK	BACKLOG_PERIOD_DK	ORDER_LINE_NUMBER
05-FEB-2011	28-FEB-2011	100025
05-FEB-2011	28-FEB-2011	100030

2nd Column - Period End Date

1st Column shows Latest ETL Run Date

W_SALES_BACKLOG_HISTORY_F (Before ETL Load on 5^th Feb)

BACKLOG_DK	BACKLOG_PERIOD_DK	ORDER_LINE_NUMBER
25-JAN-2011	31-JAN-2011	100001
5-JAN-2011	31-JAN-2011	100005
25-JAN-2011	31-JAN-2011	100006
01-FEB-2011	28-FEB-2011	100001
01-FEB-2011	28-FEB-2011	100008

	Backlogs in the current period will be deleted (4th and 5th rows)

Monthly Snapshot (1st, 2nd & 3rd rows) – Backlogs as on last ETL run date for every month is captured. New backlogs for current period are inserted

W_SALES_BACKLOG_HISTORY_F (After ETL Load on 5^th Feb)

BACKLOG_DK	BACKLOG_PERIOD_DK	ORDER_LINE_NUMBER
10-JAN-2011	31-JAN-2011	100001
10-JAN-2011	31-JAN-2011	100005
10-JAN-2011	31-JAN-2011	100006
05-FEB-2011	28-FEB-2011	100025
05-FEB-2011	28-FEB-2011	100030

Latest backlogs from backlog lines fact are inserted (4th and 5th rows)

By observing the above diagrams we can infer that Backlog History fact stores the historical monthly snapshots of backlog lines and the snapshot of the current month till date (last ETL run date).

Error: Cannot connect to the integration service [Integration_Service] No gateway connectivity is provided for the domain [Domain_Name]

Error: Cannot connect to the integration service [Integration_Service] No gateway connectivity is provided for the domain [Domain_Name]

Reason: The workflow is not assigned to the correct integration service.

Solution: Assign the workflow to the integration service that is connected or associated to the informatica repository where you are executing the workflow.

Steps: Once you have identified the associated integration service, follow these steps to assign integration service.

Steps

Step 1 – Disconnect from all open folders and Click on Service -> Assign Integration Service

Step2: Choose the Integration Service - Integration_service from the drop down list.

Step 3 – Choose the folder which contains the workflow.

Step 4 – Select the Workflow and click on Assign.

Connecting to Oracle DB via command prompt using ldap

How do I connect to the Oracle db through command prompt via ldap?
Once we have the sqlnet.ora and ldap.ora files in place as explained in blog 'Sql developer connection to oracle db', we just have to use the below syntax to connect.

Syntax - sqlplus username/password@dbservice

Here dbservice is the same service name in sqldeveloper that we select as the last step in creating a connection.

SQL Developer connection to Oracle db via LDAP

How can we create a SQL Developer connection to Oracle db via LDAP ?

Let me first cover how to create a SQL Developer connection to Oracle db via LDAP.
We need to have ldap.ora and sqlnet.ora files for this connection to work.

sqlnet.ora file

Note : Please take a backup of the existing sqlnet.ora file, we'll need it to connect to other db which do not use LDAP.

Set the following parameters
NAMES.DIRECTORY_PATH= (LDAP,ONAMES,TNSNAMES)
NAMES.DEFAULT_DOMAIN = xxx.xxx.com
NAMES.PREFERRED_SERVERS = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = hostname1)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = hostname2)(PORT = 1521)) (CONNECT_TIMEOUT = 0) )
NAMES.REQUEST_RETRIES=2
NAMES.INITIAL_RETRY_TIMEOUT=10

For Preferred servers, hostname2 is optional. Also make sure that the port numbers are correct.
You can also add the following connection timeout parameter so that you don't have to reconnect every time, especially if you are connecting from VPN.

SQLNET.INBOUND_CONNECT_TIMEOUT=600

ldap.ora file
Set the following parameters, the values show are just an example.

DEFAULT_ADMIN_CONTEXT = “ou=oracledatabases,dc=mycompany,dc=com”
DIRECTORY_SERVERS = (ldap_server1.mycompany.com:389:636, ldap_server2.mycompany.com:389:636, ...)
DIRECTORY_SERVER_TYPE = OID

Place these 2 files in your $ORACLE_HOME/network/admin folder.

SQL Developer
Open a new SQL Developer connection and do the following
Select Connection Type = LDAP
Select LDAP Server from the drop down list
Select the appropriate context -
example - cn=OracleContext,ou=oracledatabases,dc=mycompany,dc=com
DBService - Click on Load Button then select the dbservice.

Now, Click on Test then Save and Connect.

Here you are! Successfully connected.