Business Intelligence Data Modelling Training Course

Participants can elicit measurable BI requirements and define a model scope.

• Stakeholder interviews structured around decisions, measures, filters and reporting frequency
• Business process matrices identify facts, dimensions and shared analytical subjects
• Metric definitions separate calculation logic, inclusions, exclusions and timing rules
• Event-based processes distinguished from snapshot and accumulating snapshot requirements
• Source ownership, refresh cadence and retention needs recorded during discovery
• Acceptance criteria written for data content, history, reconciliation and usability

Participants can create conceptual and logical models from business requirements.

• Conceptual models show business entities and relationships without implementation detail
• Logical entities defined with attributes, cardinality, optionality and natural identifiers
• Crow's Foot notation used to communicate relationship cardinality and participation
• Supertype and subtype patterns model shared attributes and distinct classifications
• Reference data, master data and transactional data separated by governance needs
• Business glossary terms aligned with logical model entities and attributes

Participants can specify physical structures and key strategies for BI data stores.

• Surrogate keys separate warehouse identity from unstable operational business keys
• Natural keys retained for matching, reconciliation and source-system traceability
• Primary, foreign and alternate keys specified for analytical table relationships
• Null handling rules protect joins, aggregations and unknown member reporting
• Data types selected for dates, money, quantities, codes and descriptive attributes
• Naming standards make schemas, columns and relationships understandable to delivery teams

Participants can design a star schema for a defined business process.

• Star schemas organise measures around a declared business process grain
• Fact tables store numeric events, foreign keys and additive measurements
• Dimension tables provide descriptive context for filtering, grouping and drill-down
• Bus architecture identifies conformed dimensions shared between subject-area marts
• Snowflake structures assessed against usability, maintenance and query performance
• Dimension role-playing handles order date, ship date and invoice date

Participants can select and document fact table patterns for reporting needs.

• Transaction facts record individual business events at lowest useful grain
• Periodic snapshots capture balances, inventory levels and status measures
• Accumulating snapshots track milestones, durations and process-cycle performance
• Factless fact tables record coverage, eligibility, attendance and event occurrence
• Additive, semi-additive and non-additive measures classified before aggregation
• Degenerate dimensions retain document numbers without separate dimension tables

Participants can design reusable dimensions and preserve required historical context.

• Slowly changing dimension Type 1 overwrites corrected descriptive attribute values
• Type 2 rows preserve history using effective dates and current indicators
• Type 3 attributes retain limited previous values for selected comparisons
• Mini-dimensions separate rapidly changing customer profiles from core attributes
• Junk dimensions combine low-cardinality flags into manageable reporting structures
• Hierarchies model organisational, geographic, product and account roll-up paths

Participants can model complex relationships without distorting analytical results.

• Bridge tables resolve many-to-many relationships between facts and dimensions
• Weighted bridges allocate shared transactions across multiple accountable entities
• Parent-child hierarchies support recursive organisational and account reporting structures
• Multivalued dimensions model customers, products or cases with multiple classifications
• Late-arriving dimensions handled through inferred members and controlled updates
• Late-arriving facts reconciled using event dates, load dates and key lookup

Participants can justify an architecture appropriate to reporting, integration and history needs.

• Inmon third normal form structures enterprise data before departmental presentation layers
• Kimball bus architecture coordinates dimensional marts through shared conformed dimensions
• Data Vault 2.0 separates hubs, links and satellites for historical integration
• Hubs model stable business keys from source-system entities and reference domains
• Links record relationships between hubs with load dates and record sources
• Satellites retain descriptive history, source context and changing attribute values

Participants can assess source data suitability and identify modelling risks.

• Column profiling measures completeness, uniqueness, validity and value distributions
• Relationship profiling tests candidate keys, referential integrity and orphan records
• Pattern analysis identifies malformed codes, inconsistent dates and mixed units
• Source-to-target mappings record field origins, transformations and target destinations
• Business keys assessed for reuse, change frequency, duplicates and source ownership
• Data profiling findings converted into model assumptions and remediation actions

Participants can specify transformations that populate BI models consistently.

• Staging layers retain extracted data before cleansing, matching and transformation
• Change data capture identifies inserts, updates and deletes between source extracts
• Surrogate key lookups manage historical dimension rows during fact loading
• Transformation specifications define derivations, defaults, rejects and exception handling
• Load sequencing protects dimension availability before dependent fact table processing
• Watermark controls record extraction boundaries, batch status and restart points

Participants can define quality controls and reconciliation tests for BI data.

• Data quality dimensions assess accuracy, completeness, consistency, timeliness and validity
• Validation rules test mandatory values, permitted codes, ranges and formats
• Reconciliation compares record counts, control totals and key financial balances
• Exception queues assign owners, severity, resolution dates and root-cause categories
• Thresholds distinguish warning conditions from load failures and reporting restrictions
• Quality scorecards communicate rule results, trends and unresolved data issues

Participants can document model metadata and assign accountability for BI data.

• Business glossaries define approved terms, metrics, owners and usage guidance
• Technical metadata records tables, columns, data types, keys and transformations
• Data lineage traces published measures through models, transformations and source fields
• RACI matrices assign ownership for definitions, quality, access and change approval
• DAMA-DMBOK principles frame stewardship, metadata management and data quality responsibilities
• Change impact analysis identifies affected reports, interfaces, tests and stakeholders

Participants can define a semantic layer that gives users consistent analytical measures.

• Semantic models separate user-facing measures from physical warehouse implementation details
• Measures defined with aggregation methods, filters, time logic and display formats
• Calculation dependencies documented to prevent circular references and conflicting formulas
• Row-level security rules restrict data visibility by approved organisational attributes
• Certified datasets distinguish governed reporting sources from exploratory data extracts
• Metric stores centralise reusable definitions for dashboards, reports and self-service analysis

Participants can improve analytical query performance through appropriate physical design.

• Partitioning strategies separate large fact data by date or business period
• Indexes selected according to joins, filters, cardinality and query execution plans
• Aggregate tables pre-calculate common summaries while preserving detail-level traceability
• Columnar storage assessed for scan-heavy analytical query workloads and compression
• Query plans examined for table scans, join methods and skewed estimates
• Performance tests define representative queries, volumes, response targets and concurrency

Participants can test BI models against business rules, source data and expected outputs.

• Test cases cover grain, keys, history, calculations and security requirements
• Reconciliation scenarios compare model outputs with approved operational control totals
• Boundary testing checks month-end, missing values, duplicates and late-arriving data
• Peer reviews challenge ambiguous relationships, hidden assumptions and aggregation risks
• Traceability matrices connect requirements to mappings, tests and acceptance evidence
• Defect triage classifies severity, business impact, ownership and release priority

Participants can plan controlled deployment and maintenance of a BI data model.

• Version control records schema changes, mapping revisions and approved metric definitions
• Migration plans sequence backfills, parallel runs, reconciliation and production release
• Backward compatibility assessed before changing dimensions, keys or published measures
• Release checklists cover security, refresh schedules, tests, documentation and support
• Operational monitoring tracks load duration, failures, volumes and data freshness
• Model retirement plans archive obsolete marts, reports and historical documentation

Participants can build, review and present a complete governed BI data model for a business scenario.

• Business scenario converted into scope, decisions, KPIs and reporting requirements
• Conceptual and logical models prepared with agreed entities and relationships
• Dimensional schema built with grain statements, facts, dimensions and surrogate keys
• Source-to-target mappings specify transformations, history rules and quality checks
• Semantic measures documented with calculation logic, ownership and usage constraints
• Peer review evaluates traceability, performance, governance and implementation readiness