Modeling: Advanced OODB style modeling

[DoctorYSG]

We have a number of telemetry sources which have overlap (network traffic at layers 2, 4, and 7) If PowerBI was a OODB (object oriented DB) I would create a class hierarchy for the common Fact Table, as well as each of the (3) dimension tables. So that the common columns were in the superclass, and they linked to the superclass of the dimension (TimeTable, Location Table, Path Table).

But PowerBi is not an OODB. So one way to do this is just to create a Fact (time, value, etc.) table that is a superset of all the different sources, realizing that some columns from one or more sources will be null, And the same with the Dimension tables.

Is this the best we can do? Does it mean to we have to create DAX expressions that give the user a clean slice of the data for each individual layer  (3,4,7) and another set of DAX for when things are in common across all layers?

[TomMartens]

Not if you want/have to use Power BI.

Tom

[DoctorYSG]

@TomMartens Jah, and that might be the best that we can do. But don't you see that this results in combinatorial explosion in both the Fact and Dimension tables, as one pre-computes all possible powersets for each one?

Sounds expensive in storage, and it is also hard to a-priori decide what combinations of "classes" are going to be interesting. 

Is there nothing closer to a true OODB (object-oriented DB) approach?


Dr. Y. Gutfreund

[TomMartens · Accepted solution]

Hey @DoctorYSG ,

I recommend reading the "(The Complete Reference) Star Schema" by Christorpher Adamson. There are fact tables with different dimensionality and dfferent granularity. Different fact tables can share the same dimension.

It is not a good idea to have fact and dimension tables with empty columns if different types of events are measured or different types of business objects are "modeled."

Hopefully, this adds some additional information.

Regards,

Tom

Regards,

Tom