My name is Peter and I am an accountant by trade but am keen on helping our organization transition to data-driven decisions. I am trying to set up a database for historical and forecasted oil and gas pricing. I want to try to use best practices as much as I can however I am having troubles identifying what that would be in this case.
All the records in dimension tables are used between the 2 fact tables (historic and pricing), however there are instances where 1 or more records in a dimension table will not appear in one of the fact tables.
For example, only 7 of the 8 benchmark prices are in the forecasted table, only 2 of the 4 engineers are tracked in the historic fact table, etc.
I am new to database modeling so forgive me if I am misunderstanding but I think a model with these characteristics would not be best practice because I am going to have records in my dimension tables that are not used in the fact tables.
Does anyone have any recommendations or guidance for how to best structure this model (should I have a separate model for forecasted and historic pricing?)
Ultimately I would like to be able to put this model into something like PowerBI and have the my dimension tables as filters that propagate to the other filters (i.e. if user selects Currency = USD, then only
Any help/guidance to point me in the right direction would be much appreciated, thank you!
Current Model Overview (no relationship to fact tables yet)
More insights into the model (if useful)
There are 2 types of oil and gas prices
Each price has multiple benchmarks (WTI, WCS, Brent, etc.)
There are 4 different third party Engineers that provide these benchmark prices (1, 2, 3, 4)
All 4 Engineers provider forecasted pricing but only on 7 of the 8 benchmarks
Only Engineer 2 and 3 provide Historical Pricing
Engineer 2 provides pricing on 7 of 8 benchmarks
Engineer 3 provides prices on 8 of the 8 benchmarks
Benchmark prices can be reported in CAD or USD
For Forecasted Pricing, each Engineer reports the same 4 benchmarks in CAD and 3 in USD)
For Historical pricing Engineer 2 supplies pricing for all their benchmarks in CAD while Engineer 3 provides pricing for 2 of their benchmarks in USD
Ultimately the relationship structure looks like this