field service data model optimisation

[jhowe1]

Hi all,

We're using d 365 field service and i'm interested in producing a field service report in power bi. i've been looking at the microsoft template for this on github https://github.com/microsoft/BusinessPlatformApps/tree/dev/Source/Apps/Microsoft/Released/Microsoft-... but found the data model to be quite large and complex which goes against all my DWH design knowledge around simple star schema design. Was wondering if anyone had any ideas to simplify this? Or am I basically stuck with this model due to complexity around multiple facts relating to each other i.e. work orders, bookings, product costs, service costs etc.? Any ideas/help would be greatly appreciated to simplify/optimise this.

[Greg_Deckler · Accepted solution]

@jhowe1 - You're likely stuck with it. Dynamics database schemas are quite complex. The above looks VERY much simplified down from the underlying tables. It's really, for the most part, two star schemas (bit of snowflake in bottom right) built around Work Orders and Booked Resources from what I can tell with a bridge table between them. Not the end of the world in my opinion.

[jhowe1]

Great thanks @Greg_Deckler just wanted to get another opinion before using this as an inspiration/starting point for our model.