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:


      MS SQL Table with dimensions:






















      Also a MS SQL table with measures




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









      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:


      # of Clients
      # of Accounts
      # of Accounts Retail Transactional NIR
      Ave Debit Balance
      Ave Credit Balance


      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!