2 Replies Latest reply: Mar 14, 2018 5:37 PM by Bill Markham RSS

    How to structure my data to avoid loops

    Scott Davies

      Hi there, new to the community and trying to build my first model. 

      I keep getting a problem where I get a data loop.  I have read up about this and understand the issue but cannot seem to get my head around how to solve the problem.

       

      I have 3 tables:

      TABLE 1: [CUSTOMER_HIGHLANDER]

      MS SQL Table with dimensions:

             [CUSTOMER_KEY]

            ,[ACCOUNT_NUMBER]

            ,[CUSTOMER_NAME]

            ,[CUSTOMER_ACTIVITY_DESC]

            ,[STATUS_DESC]

            ,[SUB_STATUS_DESC]

            ,[PRODUCT_DESC]

            ,[PRODUCT_MIDDLE_DESC]

            ,[SUB_PRODUCT_DESC]

            ,[CUSTOMER_SEGMENT_LEVEL_1]

            ,[CUSTOMER_SEGMENT_LEVEL_2]

            ,[CUSTOMER_SEGMENT_LEVEL_3]

            ,[RELATIONSHIP_DESC]

            ,[TOWN]

            ,[SUBURB_NAME]

            ,[SUPER_REGION]

            ,[BRANCH_NAME]

            ,[ABSA_SUPER_REGION]

            ,[ABSA_REWARDS_DESC]

       

      TABLE 2: [HIGHLANDER_FINANCE]

      Also a MS SQL table with measures

             [CUSTOMER_KEY]

            ,[ACCOUNT_NUMBER]

            ,[COST_CENTRE_CODE_1]

            ,[INFORMATION_DATE]  (This is monthly data so as at month end)

            ,[NON_INTEREST_INCOME]

            ,[NET_INT_INCOME_ON_ADV_AND_DEP]

            ,[TOP_LINE_INCOME]

            ,[TAXATION_EXPENSE]

            ,[PROFIT_LOSS_AFTER_TAX]

            ,[ACCOUNT_BALANCE]

            ,[ORIGINAL_CAPITAL_AMT]

       

      I created a concatenated field on CUSTOMER_KEY and ACCOUNT_NUMBER (CUST_ACCNT_NUMBER) and joined these 2 tables.  That was no problem.

       

      These SQL tables show the situation as is.  The part I am struggling with is to bring in the 3rd table (BUDGET_DATA) which shows the budgetted figures in XLSX format.  Eg I want to be able to show number of accounts budgeted vs actual.  Or Non-interest-income budgeted vs actual.

      The fields in this data are:

       

      INFORMATION_DATE
      Product
      Sub-Product
      Product_Mapping
      BB/Retail/Non-segment
      Classification
      Segment_Mapping
      PRODUCT_SEGMENT_DATE
      # of Clients
      # of Accounts
      # of Accounts Retail Transactional NIR
      Ave Debit Balance
      Ave Credit Balance
      NII
      NIR

       

      This data is aggregated on a high level by month, product and customer_segment.  Unfortunately the product and segment don't match up with any fields on the existing SQL tables. 

       

      So what I elected to do was to create a PRODUCT_MAPPING and SEGMENT_MAPPING column on the [CUSTOMER_HIGHLANDER] data.  These columns create a PRODUCT that matches up with the PRODUCT_MAPPING on BUDGET_DATA and SEGMENT that matches up with the SEGMENT_MAPPING on BUDGET_DATA.  But this data is also aggregated by MONTH. 

       

      When I try to bring this data into the model, I get an error saying I have a loop. 

       

      I am really struggling to figure out how to structure my data so I can show for example by date by product showing number of customers.  I'd appreciate any tips or suggestions!