I’ve created a model that seems to work, except I’m not sure my thinking is correct around the concept of breaking up the data into dimensions and fact tables when it comes to data that is not very quantitative, as you’ll see in the example file.
For example, I’ve broken the projects and application ids out into 2 dimensions but this results in the project and application names being always present in chart. They will filter the other tables but not vice-versa. My understanding is that quantitative data or data that will be used in calculations should be in the fact table, but I’m not always sure which and how many columns is best made into dimensions.
I love to improve my model so it is as efficient as can be?
Without knowing anything about your specific business processes, I can say that the data model you put together looks quite good. It adheres to the conditions of a star schema, which is optimal for DAX and Power BI. One small problem is that your Dates table is not marked as a date table, but that’s easily fixable.
In terms of what belongs in fact versus dimension tables, here’s a slide I put together for the solution session of Week #2 of the Power BI Accelerator initiative that summarizes some helpful guidelines for making that determination.
While there are a lot of objectively wrong ways to put together data model, there is not necessarily one “optimal” or “best” configuration. Two things to examine in evaluating whether the model you developed is optimal:
is it well structured to answer the key questions associated with the reports that are being developed from it? This is where I think developing an Analysis Plan before doing your data modeling is very helpful. That way you can tick through the key questions, looking at your draft data model structure and conceptually evaluate whether in Power Query or DAX it will be relatively straightforward to construct the analyses necessary to answer those questions. If for any of those questions the answer is “no” then you may want to look at restructuring your model.
a secondary question is whether the dimension tables are well structured to support the slicer configurations you’re going to want in your report, in terms of having one slicer filter the other properly. In some cases, in order for this to work you need to de-normalize (combine) dimension tables that would otherwise logically be separate.
Overall though, it looks like you’re off to a very good start here, having built a very solid foundation from which to evaluate those two questions.
