nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Issue federating oracle database and Hana database

Jean-Pierre Hoedenaeken

In OBIEE 11g, I'm facing an issue in federating data from an oracle database (budget figures) and an Hana database (actuals figures).

The issue is that with the employee dimension, I get one set of figures (actuals or budget) but not both.

This is not the case with other dimensions (period, cost center, ...).

There is one specific thing with that employee dimension : the source for that employee number in SAP Hana is not in the fact table (ACDOCA) but need a physical join to another table (BSEG), could that be the cause of the problem ?

I can explain the BMM in more detail if necessary.

Thx.

Please find in attachment the details of the issue with the physical schema and the BMM.

Message was edited by: user647947

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

Federation is nothing but an in-memory stitch join between two data sets. What is your precise issue?

Jean-Pierre Hoedenaeken

So I have the following foreign keys in both transactions table : period, cost center and account.

The results seems ok for those dimensions.

For the employee dimension, I have the foreign key in the budget transaction table (Oracle database) but I need to join to another table (called BSEG) for the Sap Hana transaction table (ACTUALS --> BSEG)

When I want the budget and actuals by employee, I get only the budget when there is no actuals for that period.

The BMM is :

Dim Time

2 LTS : Budget and Actuals

Column : Period

Dim Account

2 LTS : Budget and Actuals

Column : Account

Dim Cost Center

2 LTS : Budget and Actuals

Column : Cost Center

Dim Employee

2 LTS : Budget and BSEG

Column : Employee Number

Fact Amount

2 LTS : Budget and Actuals

Columns : Budget and Actual

[Deleted User]

user647947 wrote:When I want the budget and actuals by employee, I get only the budget when there is no actuals for that period.

So that rather points to content levels for the LTSs which aren't visible in your screenshots. What fact content levels are set for your fact LTSs and measures?

Jean-Pierre Hoedenaeken

Here are in attachment the two measures with the content level which are all set to the detail level

[Deleted User]

Ok but then it begs the question if the two queries (Orcl and Hana sides) actually bring back correct, valid dimension members along which the stitch join can merge the data sets. Are they REALLY conformed? No weird data difference in formatting, type, spelling, anything?

'221000' won't be nicely federated with neither 221000 nor '221000 ' for example

Jean-Pierre Hoedenaeken

The employee numbers are ok in both databases.

What is also strange, as you can see in the attachment here, is that I get different results when filtering or not filtering on an employee.

When a filter is applied on an employee (here employee 4316), the results are correct.

But when there is no filter applied, the results for the same employee are different and not correct !

This seems to mean there is another problem anywhere.

Many thanks for yous time and help.

[Deleted User]

The plot thickens. You seem to be experiencing the issues because of effective data set mismatches. Run both queries apart from each other and look at the result sets. Look at them very very carefully. Is all data represented in precisely the same way?

Robert Angel

Look also for what you cannot see.

i.e. check the length of the values, I have seen DB content that looked to the eye identical but was in fact padded with nulls to a fixed length!

If numeric is there any spurious decimals?

If text could it be character set issues?

[Deleted User]

Robert Angel wrote:Look also for what you cannot see.

That is so meta it's screaming for a Friday joke:

Image result for you can't see this

Jean-Pierre Hoedenaeken

But how could you explain that when I am applying a filter on employee number, I get the good results for the budget and the actuals ?

I am wondering if the clause "FETCH FIRST 20000001 ROWS ONLY" in the logical SQL don't cause the problem ?

Thx.