Data Modeling for BI

Deciding the shape the reporting layer needs and building the pipelines that produce it — the grain of each table, which fields are authoritative when systems disagree, how measures are defined, and how the result should be laid out for the tool consuming it. The modelling decisions come first; the pipelines implement them.

Yes. The output is tables in the destination you nominate, which any BI tool can read. Join and Union consolidate the sources, Aggregate rolls rows up to the required grain, and Pivot produces the column layout the dashboard expects — so the tool receives a prepared table rather than a modelling problem.

You can, and for a single dashboard it is often the right choice. It stops being the right choice when several reports need the same definition: the logic is then duplicated per dashboard, drifts between them, and cannot be reused by anything that is not that BI tool. Modelling in the pipeline puts the definition in one place that every consumer shares.

Profile the output before it is published. The profiling report gives per column the data type, distinct and null counts, minimum, mean, maximum, and sum — and checking a sum against a figure you already trust is the fastest way to catch a join that duplicated rows or a filter that removed too many.

Scheduled pipelines producing the modelled tables, the definitions expressed in those pipelines, and a team that can change them. The intent is that a new report starts from an existing table rather than from a new modelling exercise.