ORA-01719: outer join operator (+) not allowed in operand of OR or IN after upgrading to ODI 12c

Hello All,

I'm writing here after a long gap. Hope you all are doing great, and will continue to read my posts!!

Recently, after upgrading one of our customers from Oracle Data Integrator (ODI) 11g to Oracle Data Integrator (ODI) 12c, we faced an issue with a few of the upgraded mappings. When we run the mapping/package/scenario, they fail with an error "ORA-01719: outer join operator (+) not allowed in operand of OR or IN".

When we dug into this further, we found that this issue was caused by the usage of an older type of JOIN syntax, also called Non ANSI join syntax (With a (+) symbol) in Oracle. For example, we have 3 tables A, B, and C with the following join conditions in ODI 11g.

A = B

A= C

B=C(+)

On top of these, we have a filter condition on the table A like A>1.

In ODI 11g, the filter used to work without issues. However, post upgrade, ODI 12c adds a (+) symbol in the filter, like A(+) > 1, and we are thrown the above error when we run the ODI mapping/package/scenario.

To resolve this issue, we need to edit the mapping that has this issue in ODI 12c designer, and then change all the joins to "Generate ANSI Syntax". You get this option, when you select the Join component in the mapping shown as below.

Once this is done for all the Join components in the mapping, you could regenerate the scenario and run to see that the issue is fixed.

If you have any questions on the solution, please post in the comments section. Happy to help.

Note: Oracle recommends that we switch to the ANSI syntax in place of the older Non ANSI syntax, if the Oracle Database version is above 9i.

Cheers,

Siva

Presentation Services Not Starting after OBIEE 12C Installation - Windows 7

Hello guys,

I have been trying to install OBIEE 12C on my Windows 7 laptop for about 3 weeks and was stuck with an issue. Basically the Presentation services won't come up and when I try to manually start them the BI Server also goes down automatically. After researching for a long time, I was able to get help from Guillaume Slee from his site. Though his installation is on Windows 2012, the issue is same.

Going into the details of the issue, I had OBIEE 11.1.1.7.0 already installed on my machine. On top of this I have installed OBIEE 12C. Installation went fine and the configuration was also successful except that the Presentation Services did not come up with the below error.

And when I try to bring the presentation services up manually, I got the below error and BI Server also goes down automatically.

The root cause for this issue is the co-existence of OBIEE 11g and 12C DLL files on the same machine. Since the PATH environment variable already had OBIEE 11g related paths in it, 12C was trying to refer the DLLs of 11g and failing to start the Presentation Services.

The fix for this issue (Thanks to Guillaume Slee) is just to create a batch file with the below commands and start OBIEE services using the batch file.

set PATH=C:\Java\jdk1.8.0_66\bin;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem
call C:\Middleware12\user_projects\domains\bi\bitools\bin\start.cmd

You just need to make sure you replace the Java and OBIEE home paths as per your environment.

Once I started the services using this batch file, all the services came up without any issues and I was able to login successfully.

 So, there you go. If you face this issue on your machine this fix should work.

Cheers,
Siva

ETL Error - TNS:listener could not find available handler for requested type of server

Hello All,

If your ETLs are failing with the error, "TNS:listener could not find available handler for requested type of server" you will have to increase the processes in the Database by using the following commands.

alter system set processes=300 scope=spfile;    

alter system set sessions=300 scope=spfile;

This change requires a DB bounce. So, please bounce your DB server once done.

Appicable for: Oracle Only

Cheers,

Siva

Difference Between Two Dates Excluding Saturday and Sunday At Report Level In OBIEE 11g

Hey Guys,

Recently we got a requirement to find out the difference between two dates excluding the weekends i.e. Saturday and Sunday. I'm giving the report level formula that can be used to achieve this. Here the Presentation table I'm using is "Time" and the respective date columns are "Start Date" and "End Date". You need to replace these column names according to your Presentation Layer.

-TIMESTAMPDIFF(SQL_TSI_DAY,TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK("Time"."End Date")-1),"Time"."End Date"),
TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date"))-1),TIMESTAMPADD(SQL_TSI_DAY,6,"Time"."Start Date")))/7*5+
MOD(7-DAYOFWEEK("Time"."Start Date"),6)+CASE WHEN DAYOFWEEK("Time"."End Date")-2>5 THEN 5 ELSE DAYOFWEEK("Time"."End Date")-2 END

Hope this helps!!!

Cheers,
Siva

DAC Tasks Fail With "Error: Java heap space" Fix

Hello Guys,

Today I will be discussing about a very common issue when running the DAC Execution Plans.

Many tasks in DAC EP will be failing at a time with "Error: Java heap space". There will not be any more details in the log too. To fix this issue we need to make some changes to the DAC server startup script on the DAC Unix or Windows server machine.


To resolve the issue:

1. Find out a file called startserver.sh/.bat under DAC server folder.

2. Back up this file.

3. Find an entry starting with java.... On this line, locate the -xms paramter and change it (i.e. to -Xms512m -Xmx1536m). You can increase these values according to the load on your server, though keeping huge values isn't recommended.

4. Save the file and close it.

5. Restart the DAC server and kickoff the load.

Cheers,
Siva

Filling in Sparsely Populated pivot tables in OBIEE

In this post, i will be explaining how to satisfy some complex requirements like calculating average with respect to all the records in dimension even though there are no relevant fact records.

Most of the reports like simple Order Quantity Report can be done easily with the functionality provided by the OBIEE Answers. Some complex requirements will require additional setup. For example, if instead of subtotaling Order Quantity by summing, we wish to subtotal by averaging Quantities by the number of Customers, whether or not they sold any products.

Here is the initial report that has subtotaling with a sum aggregation rule, [the default].

Here is the same report subtotaling with an average aggregation rule. 

Note that subtotals will be as desired when all cells in the column are populated. Now the FMRADIO against BR Country_Code, for example, does not have the desired results.  The subtotal displayed is 1100.   The desired value is 220 [1100/5] since there are 5 Customers. Similarly for CHARGER against BR, the desired result is 380 [1900/5].

The problem is caused by the absence of rows returned for the respective product lines. See screen shot.

No rows are returned as there are no rows in the fact table for that intersection of dimensions, (i.e. Customer [DARREN] [RAMESH] [STEVE] [DOUG], Country [BR] and products with subtype=FMRADIO).  To solve this problem, we need to join the dimensions in such a way that all values are returned, even if there is no Quantity.
To accomplish this, we will create a physical table alias based on the problematic dimension that is referenced in our report which is DIM_CUSTOMER and we will call this as DIM_CUSTOMER_ALLVALUES

NOTE: The next step is very critical.  The new alias we have created needs to be joined to the other dimension tables.  As there is usually no physical join that can be used, we will just use the clause 1=1, which results in a Cartesian join when executed by an RDBMS.  See screenhots.  Note that this join must be defined as a complex join.

Our physical diagram will look as in below screen shot once we are done creating joins.

Next step is to add this new alias to the logical fact table as one of the sources as shown in the screenshot below.

We will also create a new logical column called “All Values” in the Logical Fact Table and the formula will be just '1' for this new column as shown below. The aggregation rule will be SUM for this column in our case.

Add the All Values column to your presentation catalog.  We are ready to complete our report.

To the original request, add a filter based upon the All Values column. 

Modify the formula of Original Order Quantity column to IFNULL("Fact - Orders"."Original Order Quantity",0) in Edit Column Formula and change the aggregation rule on the column to average.

Now run the report:

We can see that even though there are no values in the fact table for some of the dimensional combinations, OBIEE is substituting the Original Order Quantity as "0" for those and then calculating the Sub Totals properly.

This approach can be used in all the scenarios where we face issues with the fact tables being sparsely populated (Normal in Data Warehouse environments).

Hope this helps.
Siva

Load Historical Data to any Table in Oracle BI Applications Data Warehouse

Hello All,

In this post, i would be explaining how to perform a historical load on any table in your Oracle BI Apps environment. This methodology is applicable for both facts and dimensions.

First of all we need to understand the change capture logic used in the Informatica ETL Load mappings for any table. In any of the Data Warehouse table we should see columns like CHANGED_ON_DT, AUX1_CHANGED_ON_DT, AUX2_CHANGED_ON_DT, AUX3_CHANGED_ON_DT, AUX4_CHANGED_ON_DT. These columns would have directly been mapped to the LAST_UPDATE_DATE column in the source table. For example, in W_SALES_ORDER_LINE_F, the CHANGED_ON_DT column would be getting loaded from OE_ORDER_LINES_ALL.LAST_UPDATE_DATE. And if there are some other significant tables being used in the extract mapping, there LAST_UPDATE_DATE would be loaded to the AUX1, AUX2, AUX3 and AUX4.

The ETL logic would compare the CHANGED_ON_DT and other Auxiliary dates between the warehouse table and the stage table to decide whether to update the records or not the record already exists. Hence, if we need to do a complete historical load on any of the tables, first step would be to make one of these dates to NULL for all the records.

For example, if we need to do this on W_SALES_ORDER_LINE_F, we need to run below statement on the Warehouse Schema.

UPDATE W_SALES_ORDER_LINE_F SET CHANGED_ON_DT=NULL;

If we need to do this on a dimension table which is non SCD Type2, we need to run below statement

UPDATE W_PARTY_D SET CHANGED_ON_DT=NULL WHERE ROW_WID>0;

In case of SCD Type 2 dimension we need to make sure that we are including the CURRENT_FLG='Y' condition in the WHERE clause of the UPDATE query. For example if we need to perform historical load on W_PRODUCT_D the statement would look like

UPDATE W_PRODUCT_D SET CHANGED_ON_DT=NULL WHERE ROW_WID>0 AND CURRENT_FLG='Y';

Next step would be to set the dates from which we need to extract the data from source in DAC.

Here we need to understand how DAC behaves in different scenarios with respect to refresh dates.

As we know, DAC maintains the refresh dates for each of the table for all the connections under
Setup-Physical Data Source->Refresh Dates.

Note: If you have defined Micro ETL execution plans, DAC will track the refresh dates for all the tables in that EP separately. You should be able to see the EP name under the refresh dates tab for all those tables. So, if a table is used in regular EP as well as in micro EP, it will appear twice under the refresh dates tab one with the EP name and one without the EP name.

Below are the different scenarios

1. If the refresh date is not available for any of the primary source tables of a task, DAC will trigger a FULL command.

2. If the refresh date is NULL for any of the source tables of a task, DAC will use the refresh date whichever is minimum among the primary tables.

3. If same table is present in multiple EPs and the refresh date is not available in any of the EPs, DAC will trigger a FULL command.

4. If same table is present in multiple EPs and the refresh date is present in only one of the EPs, DAC will use that date for the other EP also where the refresh date is not available.

5. If same table is present in multiple EPs where one of them is Micro EP and the refresh date is latest in the non micro EP, then DAC will use the refresh date from non micro EP for the micro EP as well.

6. If the target table refresh date is less than that of the source table refresh date, DAC will use the target table refresh date and extract the data from source from that date.

Now, to do a historical load on W_SALES_ORDER_LINE_F from let's say 01/01/2000. We should execute the SQL statement given above in the Data Warehouse schema to set the CHANGED_ON_DT to NULL for all the records (Assumption here is the data that is present in W_SALES_ORDER_LINE_F is not older than 01/01/200).

After that, go to DAC Setup->Physical Data Sources->ORA_R1211(Connection that is pointing to your relational source)->Refresh Dates

Query for OE_ORDER_LINES_ALL

We have two options now. One is to set the refresh date of OE_ORDER_LINES_ALL to 01/01/2000, another is to delete that table from the refresh dates tab. If this table is present in multiple EPs repeat the step for all of the refresh dates.

Once this is done, start the Execution Plan and you should see that DAC uses the refresh date that you have set for the SDE task to extract the data from 01/01/2000 and subsequently since the CHANGED_ON_DT is NULL in the Warehouse table all the records will get updated in W_SALES_ORDER_LINE_F. If you have deleted the refresh dates, DAC will trigger FULL command any way for the SDE task to extract entire data and update the Warehouse table.

This process should be fairly straight forward if you understand the DAC Refresh Dates concept and the Informatica Update Logic in SILOS mapping. If you have any questions, you can post. I'll reply as soon as i can. Thanks.

Cheers,
Siva