Boston BI User Group

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Hello - new to the group!

    Posted Mar 24, 2022 03:23 PM
    Hi everyone! Brian here. I am an Excel expert looking to move into Power BI.

    Right now, I am working on a project for work and I am not sure I understand data modeling - for example, as an Excel user, I have always moved everything into one wide and long spreadsheet....and then gone from there.

    In my current project, I have a table with origination data for a portfolio of loans.

    Each loan has a unique loan_key and there are no duplicate Loan_Keys - so it is an origination table and it is stored in a SQL Server database.

    It has over 600,000 loans in it with over 300 kind of difficult to analyze in excel but I can make it work and I can easily import it into PowerBI.

    My question is as follows.

    I need to report weighted average metrics for the loan characteristics, for example, Weighted Average FICO Score, Weighted Average Debt to Income levels, Weighted Average Loan Terms, etc. The weightings are the loan amounts. The data table has loan level FICO scores, DTI levels, Terms, as well as loan amounts, so everything is there in the single table already.

    Is it best practice to create individual dimension tables for each metric?  For example, create a FICO dimension table with only 2 columns, namely, Loan_Key and FICO?  Similarly, then create a separate dimension table for Term with only Loan_Key and Term columns?  And then continue with other dimension tables for DTI, etc?

    Clearly, I can link them all with Loan_Key but I just don't know if this makes sense.

    Does it make more sense to just keep the data in one large table where it is already stored and report metrics by referencing the large table?

    Whichever direction is preferred, can anyone help me to understand why?



    Brian Field
    Analytics Leader