Hello,
I am getting more and more confused after more than one week of research on what would be the best schema for the attached report. This doesn't seem like a typical star schema with fact and dim tables (even though I named them like that).
Background:
I want to understand which employees can work with which customers. This will allow me to better understand where I have skill gaps from the skills and regions perspective.
Each employee has a skill assigned and each customer has a PMV type assigned. There is also a mapping table showing which PMVs can be covered by which skills.
Data:
fact Employees - list of employees, their skills and cities where they are based
fact Customers - list of customers, their pmvs and cities where they are based
dim Skills - mapping of _SkillKey with the Skill name
dim Regions - mapping of _RegionKey with Region name
dim PMV Skill Mapping - mapping allowing to understand which skills can cover which pmvs (and other way round)
dim PMV - mapping of _PMVKey and PMV Type
dim MasterMap - table appended in PQ to get an ultimate view of all the locations (customers & cities)
Ideally I would like to understand things like:
A) where are based customers/employees with certain skill/pmv (selected from the filter)
B) what is the ratio (# of customers per engineer) for each skill/pmv
My biggest struggle is how to design a data model that will help to answer these questions... when I get it to work for A) it will not work for B) and another way round..
Any feedback is much appreciated!
thanks!
PBIX
