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

What about the education, participant, and employment tables? Shouldn't there primary keys be included in the fact table?

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

Hi jswartz_09,

 

No, their primary keys do not need to be included in the fact table. The fact table should only contain the foreign key that defines its grain.

  • The participant is already connected through the Case table (Case contains fk_participant_id). Through that relationship, power bi can reach the Participant, Education and Employment tables.

In a star schema, the fact table stores only the keys needed to define its grain. Other dimensions are reached through relationships, so their primary keys do not need to be duplicated in the fact table.

 

Thanks and regards,

Anjan Kumar Chippa

  • jswartz_09's avatar
    jswartz_09
    Helper I
    5 months ago

    I'm sorry but I don't think that is going to work. 
    Below is what I believe modeling wise you are suggesting?

    Because of the 1-* relationships off of Participant to Employment and Education when you try to put data from the Completion (FACT) table, Case table, Participant table, and Employment table into a table visual in PowerBI an error is thrown because it cannot establish the relationships. See below. 
    My goal is to be able to have all data from each table into one table visual together. 

  • v-achippa's avatar
    v-achippa
    Community Support
    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

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

    Hi @jswartz_09,

     

    As we haven’t heard back from you, 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