Oracle Business Intelligence Applications

Home› Oracle Analytics› Oracle Analytics Forums› More Solutions› Oracle Business Intelligence Applications

Categories

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

Joining 'attribute' table from DW to existing OBIA tables

Received Response
52
Views
9
Comments
user4472139
user4472139 Rank 1 - Community Starter
in Oracle Business Intelligence Applications

Hi all,

As part of a current project I am trying to incorporate an 'attribute' table into our existing OBIA subject area.

The table is like a lookup. Basically for each combination of Legal Entity (Balancing Segment), Nominal (Natural Account) and Source (GL Segment 6), there are 6 fields which define the accountant, process owner, reconciler etc.

I have created a table in the data warehouse, imported this into the Physical Layer, created an alias table and created joins from this to the existing alias tables for Legal Entity, Nominal and Source. I have then created a logical table in the BMM layer and joined this to the existing logical tables for the 3 dimensions. Then I've created a new Presentation table and dragged just the required fields across from the BMM layer.

In OBIA I can run an analysis based solely on the new Presentation table, but when I try to retrieve fields from other presentation tables the query fails with "None of the fact tables are compatible with the query request......"

I'm new to OBIEE and my background is in Discoverer. This sounds like a fan-trap sort of issue to me.

What I'd like to know is, is it actually possible to join this attribute table to 3 other dimension tables? If so what am I doing wrong and how can I achieve this please?

Sorry if the details are sketchy!

We are using OBIEE 11.1.1.7.

Thanks in advance for any help.

Steven

Answers

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

    From what you're explaining it looks like you have created a snowflake in the business model layer. I.e. your logical model goes Fact -> Dimension -> Dimension.

    In the OBI RPD th logica models have to be stars with snowflakes modeled in the logical dimension table sources. Meaning the "extension join" has to happen inside the dimension LTS and not as an own logical dimension table that's joined to another dimension.

    However you say "lookup" so maybe you literally just need the table to exist and use it via lookup functions.

  • user4472139
    user4472139 Rank 1 - Community Starter

    Hi Christian,

    Thanks for the suggestions and apologies for the late reply.

    We have tried creating a lookup table in the BMM layer, but when we try creating the LOOKUP function using the Expression Builder and click OK, the BI Admin Tool crashes! This happens every time, either when online or offline.

    I've raised an SR with Oracle as it doesn't seem to be anything I'm doing wrong. The formula I'm using is below:

    Lookup(SPARSE "Core"."FLC Database Lookup"."ACCOUNTANT_1" , 'Not Found', "Core"."Dim - Legal Entity Segment"."Legal Entity Code", "Core"."Dim - Nominal Segment Statutory"."Nominal Code", "Core"."Dim - GL Source"."Source Segment Code")

    Thanks

    Steven

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

    Uhhh nice? Not.

    Well any lookup you can also just model into your existing LTS as a joined table. Just put it outer (for your sparsity) and get the data.

  • user4472139
    user4472139 Rank 1 - Community Starter

    Thanks Christian. The problem I'm trying to get my head around (like I said it's all new to me) is that I have 3 dimensions as logical tables already in the BMM layer and my lookup table has 3 columns for those dimension values plus all the 'attribute' columns to the right.

    Do I need to add my lookup table as a new LTS in EACH of the 3 existing dimensions?

    Thanks

    Steven

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

    if you're doing an in-lts-extension, then yes

  • Naeem Akhtar
    Naeem Akhtar Rank 3 - Community Apprentice

    Dear,

    In addition to above responses.

    user4472139 wrote:In OBIA I can run an analysis based solely on the new Presentation table, but when I try to retrieve fields from other presentation tables the query fails with "None of the fact tables are compatible with the query request......"Steven

    You have to create hierarchies from new dimensions and set content levels in fact tables to fix "None of the fact table" Issue.

    Please mark if helpful.

    Regards

    Naeem Akhtar

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

    You haven't really read the OPs question and the other answers in the thread?

    LOOKUP was the keyword there and I already told him to alternatively model things into snowflaked dimension LTSs.

  • Naeem Akhtar
    Naeem Akhtar Rank 3 - Community Apprentice

    You are absolutely right on the approach.

    What I meant to say is totally different approach for the same requirements in star way.

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

    Then he'd have to change his fact because dimensional attribute lookups are by definition not tied to the fact but only the dimension.