How to model 1 conformed dimension and 2 facts (Facts are kind of snowflake) — Oracle Analytics

Oracle Analytics Cloud and Server

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

How to model 1 conformed dimension and 2 facts (Facts are kind of snowflake)

Received Response
71
Views
12
Comments
Sunny86
Sunny86 Rank 6 - Analytics Lead


Hello All,

We have 3 tables.

Customers

LoanDetails

RepaymentDetails

How i can model this . Suppose i have to generate a report for customer wise loan amount and repayment amount how can i calculate it?

Now for example

Customer Steve got two loans from bank

Home loan - 1000USD

Car loan -   200 USD

in repayment table he has entries for his repayment every month. For example in 2016 he paid 12 times every month.

So now i joined like below

Customer - > Loan Details

Customer ->RepaymentDetails

But when we use sum(loanamount) and sum(repaymentamount) for steve in report it is totalling the 1000USD + 500 USD 12 times ..

Hope my explanation is clear. any help will be highly appreciated.

Thanks

Stv

«1

Answers

  • EmmanuelMash
    EmmanuelMash Rank 4 - Community Specialist

    The proof is in the SQL that obiee is generating. Have a look at the nqquery.log after running your analysis to see what obiee is generating based on your model. Its probably creating some cross join that is why you are seeing the multiplication of records.

    regards

    Emmanuel

  • Sunny86
    Sunny86 Rank 6 - Analytics Lead

    Hello Emmanuel,

    didnt create the join in OBIEE yet. I was executing query only.

    I have filters such as payment date , loan date etc in fact tables.

  • EmmanuelMash
    EmmanuelMash Rank 4 - Community Specialist

    Can you post the sql query?

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    "Facts are kind of snowflake" ... oh no!

    "I have filters such as payment date, loan date, etc. in fact tables" ... nope!  these go in dimensions ...

    Given what you've said in your post your fact star model should look like:

    Dim - Date

         attributes related to dates: year, month, week, date, fiscal period, same date last year, etc.

    Dim - Customers

         attributes related to customers: name, phone, address, start date, credit rating etc

    Dim - Loans

         attributes related to loans: loan number, loan type, etc

    Dim - Payments

         attributes related to payments: payment number, payment type, tender type, etc

    Fact - Customer Loans

         GRAIN:  customer key, loan key, date key

         MEASURE:  loan amount

    Fact - Customer Loan Payments

         GRAIN: customer key, loan key, payment key, date key

         MEASURE: payment amount

    With a simple structure like the above coupled with logical hierarchy for dates -- you can do all you want and more.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner
    Thomas Dodds wrote:"Facts are kind of snowflake" ... oh no!"I have filters such as payment date, loan date, etc. in fact tables" ... nope! these go in dimensions ...

    That's why I didn't even bother replying ;-)

  • Sunny86
    Sunny86 Rank 6 - Analytics Lead

    .. If i make dimension out of fact it will become non conformed dimensions and will trouble us in reporting layer.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner
    3295595 wrote:.. If i make dimension out of fact it will become non conformed dimensions and will trouble us in reporting layer.

    Care to explain how and why?

  • Sunny86
    Sunny86 Rank 6 - Analytics Lead


    We have to set totals for the non conformed dimension in RPD and when we take sum for the repayment and loan loan value will add up based on number of records in the repayment table for same loan?

    can it happen?

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

    If the LTS content setting are correct then the results will be correct.

    Attributes in facts are just wrong and only ever acceptable if used to created/calculate facts which don't exist physically.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    If I have to I can create a logical star out of a single physical database table.

    A logical star is a must-do for OBIEE ...