Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

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

Modelling customer and a fact

Received Response
21
Views
2
Comments
Sunny86
Sunny86 Rank 6 - Analytics & AI Lead
edited Aug 13, 2024 5:57PM in Oracle Analytics Forum

Hello All,

We have a customer table and Loans fact table

customer table

custid custname country

Loans

custID ParentID GroupID loanamount.

Currently we have created 3 alias table for customer and joined with loans on the keys in loan table

customer.ID = Loans.custID

groupCustomer.ID=Loans.ParentID

ParentCustomer.ID=Loans.GroupID

Do we have a better approach than this? Can we manage it with hierarchy and a single customer table?

Thanking you in advance for any points.

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    Your customers doesn't have any hierarchy structure, it's a flat list with just an ID, a name and a country.

    And I guess a customer acting as "parent" for a loan can also be the customer acting as "group" or "customer" for another load.

    That's why you probably don't have an hierarchy in your customer table, because it isn't really one.

    You can't really have a single customer object but with 3 different joins, except if instead of 3 aliases for customers you create 3 aliases for loans and your model is a kind of 3 fact tables with 1 dimension.

    It all depends on what kind of analysis you want to do, but in a way or another the join between 2 objects is unique ....

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics & AI Strategist

    +1 to @Gianni Ceresa

    in essence you have the option (or both):

    Alias the dimension ...

    customer.ID = Loans.custID

    groupCustomer.ID=Loans.ParentID

    ParentCustomer.ID=Loans.GroupID

    Alias the fact ...

    CustomerLoans.custID = Customer.ID

    ParentLoans.ParentID = Customer.ID

    GroupLoans.GroupID = Customer.ID

    - with aliased dimension you can choose discreet loanID and see all the parties involved with it (Customer, Parent & Group)

    - with aliased fact you can chose a discreet customerID and see all the loans that they are involved with (Customer, Parent & Group)