Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Issue federating oracle database and Hana database

Received Response
62
Views
12
Comments
Jean-Pierre Hoedenaeken
Jean-Pierre Hoedenaeken Rank 6 - Analytics Lead
edited Aug 13, 2024 6:00PM in Oracle Analytics Cloud and Server

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

«1

Answers

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

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

  • Jean-Pierre Hoedenaeken
    Jean-Pierre Hoedenaeken Rank 6 - Analytics Lead

    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]
    [Deleted User] Rank 2 - Community Beginner 
    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
    Jean-Pierre Hoedenaeken Rank 6 - Analytics Lead

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

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    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
    Jean-Pierre Hoedenaeken Rank 6 - Analytics Lead

    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]
    [Deleted User] Rank 2 - Community Beginner

    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
    Robert Angel Rank 8 - Analytics Strategist

    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]
    [Deleted User] Rank 2 - Community Beginner 
    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
    Jean-Pierre Hoedenaeken Rank 6 - Analytics Lead

    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.