Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
DoctorYSG
Helper III
Helper III

Modeling with sub-dimension and sub-facts

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)

NetScout.jpg

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.

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

1 REPLY 1
Anonymous
Not applicable

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.

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors