Dimensional Modeling for Business Intelligence

Translate a set of analytical questions into a defined business process, candidate measures, and candidate dimensions.

Declare the grain of a fact table in a precise business sentence and test whether it is atomic.

Select a transaction, periodic snapshot, accumulating snapshot, or factless fact table for a given analytical process.

Classify each measure as additive, semi-additive, or non-additive and specify its valid aggregation rules.

Detect measurements at incompatible grains and separate them into coherent fact tables.

Design a dimension table with a surrogate key, retained source key, descriptive attributes, and explicit hierarchy levels.

Model balanced, ragged, and parent-child hierarchies without introducing unnecessary snowflaking.

Design a date dimension and expose multiple business dates through role-playing views.

Identify degenerate and junk dimensions in a business scenario and place their attributes correctly.

Choose an SCD Type 1, Type 2, or Type 3 policy for each changing dimensional attribute.

Construct a bus matrix and identify the conformed dimensions and facts needed across business processes.

Design a periodic snapshot fact table for inventory or account balances while preserving semi-additive behavior.

Design an accumulating snapshot fact table with milestone dates for a process pipeline.

Design a factless fact table that distinguishes observed events from coverage or eligibility.

Model a multi-valued dimension with a bridge table and weighting rules that prevent double counting.

Separate rapidly changing attributes into an appropriate mini-dimension without losing analytical history.

Transform a normalized PostgreSQL source model into a star schema while preserving source-to-target key mappings.

Create PostgreSQL DDL for a star schema with surrogate keys, foreign keys, and explicit unknown-member rows.

Implement an SCD Type 2 dimension load in PostgreSQL using effective dates and a current-row indicator.

Load an atomic fact table by resolving source keys to dimension surrogate keys and handling late-arriving references.

Write a grain-safe drill-across query over two fact tables that share conformed dimensions.

Use EXPLAIN ANALYZE to choose a suitable indexing, partitioning, or pre-aggregation optimization for a slow star-schema query.

Review a draft dimensional model and correct errors involving grain, keys, hierarchies, additivity, and history handling.