Can you advise with it? — Oracle Analytics

Oracle Analytics Cloud and Server

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

Can you advise with it?

Received Response
252
Views
47
Comments
Alex Sharkov
Alex Sharkov Rank 5 - Community Champion

Hi all I have 3 datamart tables:

Client(every month i gather all data about client)

Account (every month i gather all data about account)

Payments (every day i gather data about payment)

Calendar(Montly) -cause business have to analyze monthly

I have this schema

pastedImage_0.png

Client , Account - are ok . All formula work and attribute good. But formula from Payment(F) - is empty.

In logs [nQSError: 14081] You may be able to evaluate this query if you remove one of the following column references:

I cannot understand how OBIEE generate SQL. Can you help me

«1345

Answers

  • Joel
    Joel Rank 8 - Analytics Strategist

    Do you have logical dimensions with hierarchies defined? Your Client & Account dimensions are non conformed dimensions with the Payment fact and you need to set the logical level of these 2 dimensions accordingly in the Payment fact.

  • Just what Joel said

    I see you are playing with the multi facts model as you asked some weeks ago. It generally take a bit longer the first time to get the content levels idea for the dimensions which aren't conformed (as pointed out by Joel your Client and Account not being linked to Payment F) but once done it will work nicely.

  • Alex Sharkov
    Alex Sharkov Rank 5 - Community Champion

    So one Client have one or more Account from one Account client may do one or many Payments. I look about bridge table - but there is no need to do bridge table. Seems..

  • Alex Sharkov
    Alex Sharkov Rank 5 - Community Champion

    That's blow up my mind :( I will try understand it.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    What Gianni is telling you is that for the dimensions NOT joined to the facts in the table you need to use their content level to set to TOTAL level against the fact table that they are not joined to.

    Hope that helps with the cerebral explosion rather than making it worse.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner
  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Nice article!

    Amazing how many production systems, typically implemented by major consultancies, where when you come to look in the rpd and the content levels and number of elements on dimension hierarchies have not been set.

    10 minutes work later and there is a massive performance improvement and things stop erroring.

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

    It's so typical and symptomatic. The usual attitude and reply is "It works, so f* off and don't you dare voice any criticism, this project was a success for YudgeIntegratorXYZ".

    Yeah...right...

  • Alex Sharkov
    Alex Sharkov Rank 5 - Community Champion

    Ok Thank a lot Joel, Gianni, Christian, Robert for your  advise!

    Now i make calendar with level-based hierarchy.

    H-Calendar(month,year)

    I  set content on LTS:

    Client H-Calendar to TOTAL

    Account H-Calendar to TOTAL

    when i save RPD i get:

    [14031] The content filter of a source for logical table: Client (D) references multiple dimensions.

    [15001] Could not load navigation space for subject area MY-Subj-Area .

  • Alex Sharkov
    Alex Sharkov Rank 5 - Community Champion

    Did you mean  i have to do make hierarchies based on  Client  and Account too?