Conformed Dimensions and the Bus MatrixConcept Challengeadvanced25 min

Shrunken month dimension

Share one month-grain (shrunken) date dimension between a forecast fact and an actuals fact so variance lines up period for period.

How this preview works

Preview this problem before signing in. Sign in to save progress and submit work.

Concept

conformed-dimensions

The primary modeling idea this problem reinforces.

Requirements

Business needs the model must satisfy.

Scenario

Finance forecasts revenue monthly and records actual revenue monthly. They want plan-versus-actual variance by fiscal period. A month-grain date dimension already exists as a rollup of the daily calendar; the two monthly facts do not.

Why this matters

A shrunken conformed dimension is a rollup of a base dimension to a coarser grain. Both monthly facts must share it, or plan and actual land on mismatched periods and variance becomes meaningless.

Requirements

Add a monthly forecast fact and a monthly actuals fact at month grain.
Reference the existing month dimension from both facts.
Declare surrogate-key foreign keys and N:1 relationships from each fact to the month dimension.

Read-only starter workspace

Inspect the authored starting ERD, then open the workspace to edit and run the checks. Saving your progress needs a free account.

Template

Shrunken month dimension starter

The model users start from in the full workspace.

Entities

Seeded tables visible in the public preview.

Dialect

postgresql

Generated SQL target for this starter model.

UUIDNOT NULL
VARCHAR(7)NOT NULL
VARCHAR(8)NOT NULL

Grain lens

Hover, focus, or tap a column to inspect what its current keys and role imply.

This preview is read-only. Open the workspace to edit and verify; saving needs a free account.

Open workspace

In this set

Problem 5 of 5 in Conformed Dimensions and the Bus Matrix.

Your progress

Saved learner intelligence from this challenge loop.

Not started

Starter workspace

Launch the authored starter model, persist progress immediately, and continue the work in the full workspace.

Workspace template

Shrunken month dimension starter

The initial model users start from before making their own changes.

Seeded entities

How much structure the starter workspace provides on day one.

Open this workspace now and run the checks, no account needed. Sign up to save your progress and track what you solve.

Try the question first.

The discussion has other people's approaches and solutions. Give it a real attempt before you read them.