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

    Modelling customer and a fact

    Sunny86

      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.

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

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

          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

            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)

            1 person found this helpful