We collect test data from network tests. There is common dimensions and facts for all the different tests, But there are also some facets of the dimensions and facts that are unique.
If this was an OODB I would created a super-class with all the common properites, and sub-classes of the unique ones. But PowerBI is a traditional relational model.
I hae the following notional model that I want feedback on. The key words are:
DNS = DNS lookup tests OCSP = This is protocol to get an SSL certificate Data Throughput = This is a full session (does DNS lookup to IP address, gets OCSP, and does data transfer)
This looks nice and simple, but I wonder if the sub-dimension such as OCSP should be linked through the common dimension table or point directly to the Common Facts. The link between the OCSP to the COMMON dimension is 1-1. But I can see this as doing two JOINS to slice the OCSP facet to get to the Fact in Common, and three Joins to get to the OCSP facts. (so maybe I should flatten this model. It would not look as nice, but it might be more performant.
BTW, there is already about 150M rows in the fact table (the dimension tables are about 1K, so that is not a big concern.
