Hi Sterling,
Thank you for giving us a very detailed question. I’ll try my best to answer some of your concern here.
Does Holistics support datasets with multiple facts that can be combined via a Kimball-style
drill-across query, when the facts are at different levels of granularity but share common dimensions?
Yes, as long as the common dimensions can connect to each others and your fact tables without ambiguity. Unfortunately, this is not the case for your use-case. In your case, you want an exploration like this:
explore {
dimensions {
dim_package.package_sn,
// fct_signal_package.bin_start_at or fct_signal_core.bin_start_at
}
measures {
energy_to_fuel: // you can put this in a biz cal
sum(fct_signal_package.energy_kwh) / sum(fct_signal_core.fuel_kbtu)
}
}
This would not work because as far as we concern fct_signal_package.bin_start_at and fct_signal_core.bin_start_at are not the same dimension. To overcome this, what we usually recommend is to introduce a date_dim model to connect the two models.
But this would not work for your case either because now the dataset contains ambiguous paths between
dim_package and date_dim
We know about this issue and ideally the experience we want you to have here is:
- Allow you disable the relationship in the dataset to remove ambiguity during normal exploration.
- But still allow you to explicitly enable/choose the correct path at measure definition time
This is what we’re actively working on, but a lot is still work in progress so I cannot share much with you now.
For now, the best work-around is either do what you said but separate them to different dataset or create a dim model that merge dim_package with date_dim and create a relationship on (package_sn, bin_start_at) to the fact tables.
Obviously, both of this is not ideal with different issues, We’ll get back to you when we think of any better work-arounds.