Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Multi dimensional schema

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 

2 Replies

  • You're spot on. Power BI is not OLAP.   You can emulate some of the features with inactive relationships, USERELATIONSHIP() and/or CROSSFILTER()  but you may also want to explore using separate data models for these separate business questions.  Or go back to SSAS 🙂

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Anonymous ,

    Please provide some data and expected output.

     

    Best Regards

    Community Support Team _ Polly

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.