Struggling with Database Model Best Practice

[exzpz]

Hello,

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.

My issue

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.

Question

1. 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?)
2. 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
  • Oil prices
  • 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

HISTORICAL PRICING

Price Type: Oil, NGL, Condensate

• Benchmark 1
  • Engineer 2
    • CAD
  • Engineer 3
    • CAD
• Benchmark 2
  • Engineer 2
    • CAD
  • Engineer 3
    • USD
• Benchmark 3
  • Engineer 2
    • CAD
  • Engineer 3
    • CAD
• Benchmark 4
  • Engineer 2
    • CAD
  • Engineer 3
    • CAD
• Benchmark 8
  • Engineer 2
    • CAD
  • Engineer 3
    • USD

Price Type: Gas

• Benchmark 5
  • Engineer 2
    • CAD
  • Engineer 3
    • CAD
• Benchmark 6
  • Engineer 2
    • CAD
  • Engineer 3
    • USD
• Benchmark 7
  • Engineer 2
    • CAD
  • Engineer 3
    • CAD

FORECASTED PRICING

Price Type: Oil, NGL, Condensate

• Benchmark 1
  • Engineer 1-4: CAD
• Benchmark 2
  • Engineer 1-4: USD
• Benchmark 3
  • Engineer 1-4: CAD
• Benchmark 4
  • Engineer 1-4: CAD

• Price Type: Gas
  • Benchmark 5
    • Engineer 1-4: CAD
  • Benchmark 6
    • Engineer 1-4: USD
  • Benchmark 7
    • Engineer 1-4: CAD