Modeling with sub-dimension and sub-facts

[DoctorYSG]

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 sesion (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 trhough 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 Comoon, 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.

[Anonymous · Accepted solution]

If following Kimball Dimension Modeling, the sub-facts - DNS, Data Throughput and OCSP, would have the Common dimension table surrogate key and all 3 would be separate facts with a relationship to Common dimension table.
There is not a need to relate to the 3 facts to the Common fact table because measures/aggregates created from those 3 facts can be place side-by-side in a visualization tool (Power BI). The Common Dimension table is what would be called a Conformed Dimension because it can 'conform' measures to slice and dice by attributes in a common dimension.