Issue federating oracle database and Hana database
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
Answers
-
Federation is nothing but an in-memory stitch join between two data sets. What is your precise issue?
0 -
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
0 -
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?
0 -
Here are in attachment the two measures with the content level which are all set to the detail level
0 -
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
0 -
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.
0 -
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?
0 -
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?
0 -
Robert Angel wrote:Look also for what you cannot see.
That is so meta it's screaming for a Friday joke:
0 -
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.
0 -
I guess two actions from your comment, one remove the limit (for a single user) and see if it works. Two have a look at the defaults for your Hana database and see if they are all compatible with the defaults OBIEE is applying.
I know less than zero about Hana database, but if it works with filters is it in any way like Essbase where you have to fix on the dimensions to get a meaningful result?
My only other thought is data quality, is there some subtle difference between what returns and what doesn't?
Also if it works in a very specific case, you apply a filter, does it work in ALL cases where you apply a filter?
Finally could you have some kind of time out issue where unfiltered it is taking too long and so you get no result?
All I have is questions....
0 -
As I said choosing one single emp will probably force a very nice concordance of data. Seems your other dimensional rollups are messed up.
0
