2 Replies Latest reply on Aug 28, 2017 12:31 PM by Thomas Dodds

    Modelling customer and a fact


      Hello All,



      We have a customer table and Loans fact table



      customer table


      custid custname country





      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





      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.

        • 1. Re: Modelling customer and a fact
          Gianni Ceresa


          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 ....

          1 person found this helpful
          • 2. Re: Modelling customer and a fact
            Thomas Dodds

            +1 to Gianni Ceresa


            in essence you have the option (or both):


            Alias the dimension ...

            customer.ID = Loans.custID




            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)

            1 person found this helpful