Data Modeling-

[Mkrishna]

Hi All,

I am new to Power Bi. I am working to create a data model. Had the data been static, it would have been simple for me to implement the data model as I learned but the data with the passing of date keeps changing and when I use the technique I learned I get many is to many relationship. I have presented the screenshot of the data and the simple file of the data. As the data that I am working on has sensitive information, I am unable to share the data as such I have created a dummy data. Here is the picture of the data.

The data has store number, store number, state, manager, rep and date. I want to create a data model such that I would be able to have slicers State, Manager, Rep.  As you can see from the data,

in Jan, John had store 001 and reported to Krishna

In Jan Moh had store 002 and reported to Krishna

In Jan Agu has store 003 and reported to Min

In Feb John looked after store (001 and 002) and reported to Krishna

In March, Mohn looked after store (001 and 002) and reported to Min

As you can see that the store's sales rep can change with time and the manager the report to can change with time as well.

How to make a data model in such scenario such that it accomodate the future changes as well.

I create a lookup table that comprises Manager and rep. Doing this there was many to many relatioship with the fact tabl.

Plz do help

[Mkrishna]

Hello @Idrissshatila 
I tried to create a star schema but I am getting many to many when I try in staff. I created two lookup tables

1. Store : State, Store number and store name

2. Staff which has Manager and Rep

When I try to connect Rep from Staff look up table to Rep of Fact Table, I get many to many relationship. How do I get one to one relationship between Staff and Fact Table.  

My question

I tried to create a separate two table for staff: Manager and Rep. Doing this I got one to one relationship with Fact table but I wanted to have manager and rep in a single lookup table

[Mkrishna]

@DataInsights Could you please help me. 

[gmsamborn]

Hi @Mkrishna 

Since your data shows a many-to-many relationship between Manager and Rep, I would replace your Staff dimension with 2 new dimensions: Manager and Rep.  

This way all of your relationships are one-to-many, creating a star schema.

Let me know if this helps.

[Idrissshatila]

@Mkrishna ,

An idea that i just had, is go to power query and add an index column for the staff table then merge the two tables to get the ID of the staff table into the fact table where rep & manager = rep & manager.

then do a relationship on the ID, it will be one to many.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡

[Idrissshatila]

Hello @Mkrishna ,

Build it as a star schema data model, check this out https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea💡