Learning how to setup a Star Schema

[Anonymous]

Hello everyone,

Prior to this I have only used flat tables so the world of data modeling is still really new to me and I hope to learn how to utilize it as it looks to be way more performant in the long run.

My company is asking me to create a report that showcases their sales reps' number of calls, if they attend the "Monday Morning Strategy Meeting" and if they've submitted their "Month-End" reviews.

I've pulled the data from our Dynamics server and I'm trying to figure out how to connect these all together so that I can utilize a date slicer and another slicer to filter it down to one single sales rep.

They've also asked me to link the "User Location Authority" table which will provide dynamic row-level security based on their UserPrincipalName of who's viewing the report.

Can someone explain or point me in the right direction here?

Every single table has an "ownerid" so I thought that would be a good way to connect them, but it doesn't behave how I thought it would.

Thanks in advance - you guys rock!

[Greg_Deckler · Accepted solution]

@Anonymous Well, in "proper" star schema design, your fact table really only contains columns that are used to relate to dimension tables and the actual fact (numbers) you wish to report on. The dimensional columns in this table repeat values. The dimension tables should only contain a single row for each dimensional identifier and thus these tables are the 1 side of the relationship and the fact table is the many (*) side of the relationship. So your dimension tables filter your fact table but not vice-versa. 

The AdventureWorks database is a good example of a database setup to provide a star schema data model. Understand star schema and the importance for Power BI - Power BI | Microsoft Learn

The issue with this overall though is that rarely, if ever, in the real world that data can perfectly conform to a star schema. It's tough to tell from just an image of your data model, but it would appear to me that you have multiple fact tables involved and you are going to need to merge those into a single fact table for starters.