Google Business Intelligence cheat sheet

BI definition: the collection, integration, analysis, and presentation of business data so stakeholders can make evidence-based decisions; it is a process plus tooling, not a single report.

Data hierarchy: raw data → information (processed data) → insight (interpreted pattern) → decision (action taken). Each layer adds interpretation and reduces ambiguity.

Six BI process stages: collect, process, analyze, present, act, and monitor; the loop returns to collect after outcomes are measured.

Data types: structured (rows/columns, relational), semi-structured (JSON, XML, key-value), unstructured (text, images, logs). BI tooling handles structured best.

Data quality dimensions: accuracy, completeness, consistency, timeliness, validity, uniqueness. A dataset can be complete yet inaccurate, so dimensions are scored separately.

Role split: data analyst answers defined questions; data scientist builds predictive or inferential models; BI developer builds semantic layers and dashboards; business stakeholder owns the decision.

Key performance indicator rules: tied to a strategic objective, measurable, has an owner, has a target and a time window, and is reviewed on a stated cadence.

Descriptive analytics reports what happened, diagnostic explains why, predictive estimates what will happen, prescriptive recommends an action. Dashboard type should match the question type.

Metric vs. dimension: metrics are aggregatable numbers (revenue, count); dimensions are descriptive attributes used to slice (region, channel, date). Mixing them in one field causes double counting.

Normalization: 1NF removes repeating groups; 2NF removes partial dependencies on a composite key; 3NF removes transitive dependencies on non-key attributes.

Star schema: one central fact table (measures plus foreign keys) surrounded by denormalized dimension tables. Optimized for read/aggregate speed.

Snowflake schema normalizes dimensions into sub-dimensions, saving storage but adding joins; star is usually preferred for BI query performance.

Fact table grain is the level of detail of one row; declare it explicitly before adding measures, because grain determines what aggregations are valid.

ETL extracts, transforms in a staging area, then loads; ELT loads raw first and transforms inside the warehouse. Cloud warehouses favor ELT because compute scales separately from storage.

Pipeline stages: ingestion → staging → transformation → validation/testing → serving layer → orchestration and monitoring. Failures at validation must block downstream loads.

Idempotency: re-running a pipeline must produce the same result. Implement with merge/upsert on a key or by truncating and reloading a partition.

Slowly changing dimensions: Type 1 overwrites the old value, Type 2 adds a new row with start/end dates and a current flag, Type 3 keeps a previous-value column. Type 2 preserves history.

Primary key uniquely identifies a row and cannot be null; foreign key references a primary key and enforces referential integrity. Surrogate keys are system-generated integers.

Dashboard purpose split: operational dashboards monitor daily activity, strategic dashboards track KPIs against targets, analytical dashboards support exploration of why a metric moved.

Chart selection: bar for comparison across categories, line for trend over time, scatter for correlation between two measures, pie only for parts of a single whole with few slices, histogram for distribution.

Preattentive attributes (color, size, position) are processed pre-cognitively; use them to highlight the one number that matters, not to decorate every series.

Data-ink ratio: maximize ink devoted to data, remove gridlines, 3D effects, redundant legends, and backgrounds that do not encode information.

Aggregation trap: averaging an already-averaged column produces a different result than averaging the raw rows; recompute from base grain or use weighted averages.

Filters and parameters: filters restrict the underlying data; parameters pass a single value into calculations or queries. Dashboard-level filters override page-level filters in most BI tools.

Accessibility: minimum contrast ratio 4.5:1 for normal text, never encode meaning by color alone, label axes with units, and provide table alternatives for charts.

Dashboard QA checklist: verify totals reconcile to source, test every filter combination, check null and zero handling, confirm date ranges, and validate mobile or narrow-screen layout.

BI definition: the collection, integration, analysis, and presentation of business data so stakeholders can make evidence-based decisions; it is a process plus tooling, not a single report.

Data hierarchy: raw data → information (processed data) → insight (interpreted pattern) → decision (action taken). Each layer adds interpretation and reduces ambiguity.

Six BI process stages: collect, process, analyze, present, act, and monitor; the loop returns to collect after outcomes are measured.

Data types: structured (rows/columns, relational), semi-structured (JSON, XML, key-value), unstructured (text, images, logs). BI tooling handles structured best.

Data quality dimensions: accuracy, completeness, consistency, timeliness, validity, uniqueness. A dataset can be complete yet inaccurate, so dimensions are scored separately.

Role split: data analyst answers defined questions; data scientist builds predictive or inferential models; BI developer builds semantic layers and dashboards; business stakeholder owns the decision.

Key performance indicator rules: tied to a strategic objective, measurable, has an owner, has a target and a time window, and is reviewed on a stated cadence.

Descriptive analytics reports what happened, diagnostic explains why, predictive estimates what will happen, prescriptive recommends an action. Dashboard type should match the question type.

Metric vs. dimension: metrics are aggregatable numbers (revenue, count); dimensions are descriptive attributes used to slice (region, channel, date). Mixing them in one field causes double counting.

Normalization: 1NF removes repeating groups; 2NF removes partial dependencies on a composite key; 3NF removes transitive dependencies on non-key attributes.

Star schema: one central fact table (measures plus foreign keys) surrounded by denormalized dimension tables. Optimized for read/aggregate speed.

Snowflake schema normalizes dimensions into sub-dimensions, saving storage but adding joins; star is usually preferred for BI query performance.

Fact table grain is the level of detail of one row; declare it explicitly before adding measures, because grain determines what aggregations are valid.

ETL extracts, transforms in a staging area, then loads; ELT loads raw first and transforms inside the warehouse. Cloud warehouses favor ELT because compute scales separately from storage.

Pipeline stages: ingestion → staging → transformation → validation/testing → serving layer → orchestration and monitoring. Failures at validation must block downstream loads.

Idempotency: re-running a pipeline must produce the same result. Implement with merge/upsert on a key or by truncating and reloading a partition.

Slowly changing dimensions: Type 1 overwrites the old value, Type 2 adds a new row with start/end dates and a current flag, Type 3 keeps a previous-value column. Type 2 preserves history.

Primary key uniquely identifies a row and cannot be null; foreign key references a primary key and enforces referential integrity. Surrogate keys are system-generated integers.

Dashboard purpose split: operational dashboards monitor daily activity, strategic dashboards track KPIs against targets, analytical dashboards support exploration of why a metric moved.

Chart selection: bar for comparison across categories, line for trend over time, scatter for correlation between two measures, pie only for parts of a single whole with few slices, histogram for distribution.

Preattentive attributes (color, size, position) are processed pre-cognitively; use them to highlight the one number that matters, not to decorate every series.

Data-ink ratio: maximize ink devoted to data, remove gridlines, 3D effects, redundant legends, and backgrounds that do not encode information.

Aggregation trap: averaging an already-averaged column produces a different result than averaging the raw rows; recompute from base grain or use weighted averages.

Filters and parameters: filters restrict the underlying data; parameters pass a single value into calculations or queries. Dashboard-level filters override page-level filters in most BI tools.

Accessibility: minimum contrast ratio 4.5:1 for normal text, never encode meaning by color alone, label axes with units, and provide table alternatives for charts.

Dashboard QA checklist: verify totals reconcile to source, test every filter combination, check null and zero handling, confirm date ranges, and validate mobile or narrow-screen layout.