Forum Discussion

jswartz_09's avatar
jswartz_09
Helper I
6 months ago
Solved

Understanding Dimensional Modeling

Hi everyone,    I'm beginning my journey on trying to create a dimensional model for our Power BI environment. I come from an SQL background in reporting of just writing SQL queries for the end use...
Need Help
  • v-achippa's avatar
    v-achippa
    5 months ago

    Hi jswartz_09,

     

    Here the error is happening because Education and Employment still have multiple rows per participant (1-* relationship). When you bring fields from all tables into a single visual, power bi cannot determine which Education or Employment record to use for each participant.

    • To make this work, you must first reduce Education and Employment to one row per participant, for example the latest record so the relationships become 1-to-1 with participant. Once those relationships become 1-to-1, the visual will work without errors.

     

    Thanks and regards,

    Anjan Kumar Chippa

jswartz_09's avatar
jswartz_09
Helper I
6 months ago

I need a little more information. How would the fact table look? What are the columns in the fact table? How would you model it with Dimensions?

v-achippa's avatar
v-achippa
Community Support
6 months ago

Hi jswartz_09,

 

Since your requirement is based on completed cases, the grain should be like one row per completed case. So the Completion table should be the fact table, by this we make sure that there is no duplication of cases.

  • The Case and Participant tables are dimension tables that describe that completed case.
  • The important part is Education and Employment. In sql, if a participant has multiple education or employment records, the query just duplicates rows. Power bi cannot do that cleanly because relationships need to return a single value, that is why education and employment need to be reduced to one row per participant like for example the latest record.

So instead of flattening the data like sql, we create a clean fact table and connect the respective tables around it. With this model it gives the correct results.

 

Thanks and regards,

Anjan Kumar Chippa

  • jswartz_09's avatar
    jswartz_09
    Helper I
    6 months ago

    What are the columns of the fact table?

    • v-achippa's avatar
      v-achippa
      Community Support
      6 months ago

      Hi jswartz_09,

       

      Since the grain is one row per completed case, the fact table should only contain columns related to that completed case. For example like below:

      • pk_completion_id
      • fk_case_id
      • completion_date
      • completion_reason

       

      Thanks and regards,

      Anjan Kumar Chippa

      • v-achippa's avatar
        v-achippa
        Community Support
        5 months ago

        Hi @jswartz_09,

         

        We wanted to kindly follow up to check if the solution I have provided for the issue worked? or let us know if you need any further assistance.

         

        Thanks and regards,

        Anjan Kumar Chippa