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 work for A) it will not work for B) and other way round…
Really interesting data modeling problem. I’d like to have a go at this one, but will need to do some substantial remodeling/reshaping in Power Query. Can you please provide the underlying data file?
Thanks! That gives me exactly what I need to start working on this.
One dumb question - what is PMV? I’ve googled it and while there are a bunch of definitions listed for that acronym, I’m not sure any of them fit here.
I should include that, sorry! PMV = Preventive Maintenance Visit.
Type of an agreement we have with the customer. Let’s say that I have installed a CCTV in your shop and we sign a contract that every 6 Months I will come (as a part of our contract) and check/maintain this CCTV. This visit will be called PMV.
Would it make sense?
Let me know if anything else comes up, thanks again!
I think I am making excellent headway on this, but have run into a problem in the reshaping of the data I need to do. In the Customer table there are over 1,000 records with a blank _PMVKey.
Okay, I removed the null records from the Customer table, but I didn’t get very far until I ran into a referential integrity problem between the PMV Skill Mapping table and the Customer table. I’m trying to merge SkillKey into the customer table using the PMVKey as the field on which to merge the two tables. However there are four values in the customer table for this field that are not in the skill mapping table – PMVkeys 8, 46, 52, 55. Because these are not present in the dimension table, when I merge the SkillKey field throws a blank – which I need it not to do to make this modeling strategy work.
Would you be able to update the mapping table to include these four missing IDs mapped to the appropriate SkillKeys?
Okay, this took some doing but I basically worked backwards from the conclusion that this would be built as a fully compliant star schema. Here’s the final data model I came up with:
Ratio Cust to Eng =
DIVIDE(
[Distinct Customers],
[Distinct Employees],
BLANK()
)
I replaced the normal slicers with SmartFilter Pro just because the Observer configuration made it much easier to test the model.
I also wasn’t sure what you wanted to do about the date table. I replaced the single column calendar table with the full Extended Date Table. To connect this to the Customer Visit table, you will need to use TREATAS or a monthly table filtering the date table to get to the proper granularity.
Here’s what it looks like all put together:
Per your requirements (1) shows the location of the selected skills; (2) shows the location of the selected PMVs; and (3) s shows the ratio of customers to engineers for each combination selected.
Glad to help. I really enjoy the puzzle and creative aspects of data modeling, and this was an interesting one to work through. The breakthrough for me was when I figured out to incorporate the the skill-to-PMV mapping table into the Customer Visits fact table. Everything fell into place pretty nicely after that.
I will say that I spun my wheels a bit on the Location mappings, which still don’t entirely make sense to me in that a city can be split between multiple regions, and multiple regions can encompass many cities. Thus, I tried to create a joint locations table incorporating region and city/town/site in order that the slicers for each would synchronize, but based on the many-to-many relationship between those two elements, I was not able to get the slicer syncing to work.
