Data and Semantic Model Readiness

Introduction

Power BI visualizations are only as reliable as the data and semantic model beneath them. A visually attractive report can still mislead users if dates are stored as text, quantities are aggregated incorrectly, relationships are ambiguous, or the level of detail in a table is misunderstood. Data and semantic model readiness therefore precede effective visualization.

A semantic model is the business-oriented representation of data used by Power BI to support analysis. It defines tables, columns, measures, relationships, hierarchies, security rules, and calculation behavior. The model translates operational data into an analytical structure that report users can understand and trust.

This chapter explains the foundations required for accurate and usable Power BI reports: data types, dimensions, measures, granularity, relationships, hierarchies, star schemas, data quality, and aggregation behavior.

Learning Objectives

By the end of this chapter, you should be able to:

1. Distinguish common Power BI data types and select appropriate types for analysis.
2. Explain the difference between dimensions, measures, facts, and calculated measures.
3. Identify the granularity of a table and understand its effect on visual results.
4. Describe relationships, cardinality, filter direction, and common modeling risks.
5. Design and interpret a basic star schema and hierarchy.
6. Detect data-quality problems that affect visual accuracy.
7. Choose appropriate aggregation behavior for different fields.
8. Apply a readiness process to a practical reporting scenario.

1. From Source Data to a Semantic Model

Source systems are usually designed to record transactions, not to support analysis. A sales system may contain invoices, customer records, product codes, shipping events, and payment details in separate structures. A Power BI model reorganizes these data into a form suitable for questions such as:

• How much revenue was generated by region?
• Which products are declining in sales?
• How does current performance compare with the previous year?
• What is the average order value?

The transformation from source data to analysis normally involves four stages:

1. Ingestion: Connecting to files, databases, cloud services, or other sources.
2. Preparation: Cleaning, shaping, and combining data in Power Query.
3. Modeling: Defining tables, relationships, calculated columns, and measures.
4. Visualization: Placing fields and measures into charts, tables, cards, and other report objects.

Errors introduced in an earlier stage often appear later as visualization problems. For example, a product code imported as a number may lose leading zeros, while a duplicated customer table may produce inflated totals through an ambiguous relationship.

2. Data Types and Their Analytical Meaning

A data type is not merely a formatting choice. It determines which operations Power BI can perform and how fields behave in visuals, filters, sorting, and calculations.

2.1 Text, categories, and identifiers

Text fields are commonly used as categories on an axis, legend, slicer, or row header. They include names and descriptive attributes. Codes such as 001245 should generally remain text if the digits identify an entity rather than represent a quantity. Converting such a code to a number can remove leading zeros and imply that arithmetic is meaningful when it is not.

A category should also have consistent spelling and casing. “East,” “EAST,” and “East ” may be interpreted as different values unless cleaned.

2.2 Numeric types

Numeric fields require semantic interpretation. A quantity such as units sold can usually be summed. A unit price should usually not be summed across products or transactions. A percentage stored as a decimal, such as 0.25, should be formatted as 25%, not displayed as 0.25 unless that is the intended convention.

Currency precision matters. Rounding at the source, during transformation, or only at display level can produce different results. Financial policies should specify where rounding occurs.

2.3 Dates and times

Dates should be stored as date values rather than text. Date values enable chronological sorting, filtering, grouping, and time-intelligence calculations. A dedicated date table is normally preferable to relying only on the date column in a transaction table.

A date/time field can contain both the calendar date and the time of day. It may need to be separated into a date column and a time column when users require daily and intraday analysis. Time zones must also be considered: a timestamp recorded in UTC may display a different business date from one recorded in local time.

2.4 Data type readiness checks

Before creating visuals, verify that:

• identifiers have not been converted into quantities;
• dates are true dates and use the intended locale;
• currency and percentages have suitable formats;
• decimal precision is sufficient for the business decision;
• null values are distinguished from zero;
• Boolean fields contain consistent values;
• columns do not contain mixed types.

3. Facts, Dimensions, Measures, and Calculated Columns

3.1 Fact tables

A fact table records events or observations. Sales lines, support tickets, website visits, and inventory movements are examples. Fact tables generally contain numeric values and foreign keys pointing to descriptive dimensions.

A fact row might represent one product on one order line. Another fact table might contain one daily inventory balance per product and warehouse. These tables may both be valid, but they have different granularities and should not be combined casually.

3.2 Dimension tables

A dimension provides descriptive context for facts. Common dimensions include Date, Customer, Product, Employee, Store, and Geography. Dimension attributes are used to group, filter, slice, and label measures.

For example, a Product dimension may contain ProductKey, ProductName, Brand, Category, and StandardCost. The ProductKey connects the dimension to a sales fact table, while the other columns support analysis.

3.3 Measures

A measure is a calculation evaluated at query time according to the current filter context. A basic revenue measure might be expressed in DAX as:

Total Revenue = SUM(Sales[Revenue])

Measures are generally preferable for aggregations because they respond dynamically to filters and provide one governed definition of a business metric.

A measure should have a clear name, a suitable format, and documented business meaning. “Margin” could mean gross margin amount, margin percentage, or contribution margin; the model should remove this ambiguity.

3.4 Calculated columns

A calculated column is computed row by row and stored in the model. It is appropriate when a persistent attribute is needed for grouping, filtering, sorting, or relationship support. For example, a Product column may classify products into price bands.

A calculated column is not automatically a better substitute for a measure. A row-level expression such as Quantity × Unit Price can produce a sales-line amount, but the total revenue should generally be aggregated through a measure.

3.5 Additive, semi-additive, and non-additive measures

Aggregation behavior depends on the meaning of a field.

A daily inventory balance may be summed across warehouses but should not normally be summed across dates. A percentage should be recalculated from its underlying numerator and denominator rather than averaged blindly.

For a weighted average price, the appropriate calculation is often:

Here, Revenue and Quantity must refer to the same filter context and compatible units. This is different from averaging transaction-level unit prices, which gives each transaction equal weight regardless of quantity.

4. Granularity: The Level of Detail

Granularity is the level at which each row represents an event, entity, or observation. It is one of the most important modeling concepts because every measure depends on the meaning of a row.

Examples include:

• one row per order;
• one row per order line;
• one row per product per store per day;
• one row per customer per month;
• one row per employee absence.

The granularity should be stated explicitly, preferably in the table documentation. “Sales table” is insufficient; “one row per invoiced product line” is meaningful.

4.1 Why granularity affects totals

Suppose an order table contains one row per order and an order total of £500. If the order is joined to a product-line table containing four rows for that order, the £500 may appear four times after expansion. Summing the repeated order total produces £2,000, which is incorrect.

This is a grain mismatch. It can occur when tables are merged, when relationships are incorrectly defined, or when fields from different fact tables are combined in one visual.

4.2 Grain and distinct counts

A transaction count can mean different things:

• count of rows;
• count of order numbers;
• count of distinct customers;
• count of completed orders.

These are not interchangeable. If a fact table is at order-line grain, counting rows counts lines, not orders. A distinct-count measure is needed when the business question concerns unique entities.

4.3 Grain declaration procedure

For each table, document:

1. What does one row represent?
2. Which column or combination uniquely identifies a row?
3. Which measures can be summed safely?
4. Which dimensions can filter the table?
5. What date does the row represent: order date, shipment date, or payment date?

5. Relationships and Filter Propagation

Relationships connect tables and allow filters to propagate through the model. A relationship commonly connects a unique key in a dimension to a repeated foreign key in a fact table.

5.1 Cardinality

Cardinality describes how many records on one side can relate to records on the other side.

• One-to-many (1:*): One product appears in many sales rows. This is the standard star-schema relationship.
• One-to-one (1:1): Each record has exactly one matching record. It may indicate a legitimate split table or an unnecessary separation.
• Many-to-many (:): Multiple records on each side can match. This requires careful design because it can create ambiguous filtering and unexpected totals.

The “one” side should contain unique values. Blank or duplicated keys can prevent the intended relationship behavior.

5.2 Filter direction

Single-direction filtering usually flows from dimension to fact. This makes the model easier to understand and reduces ambiguity. Bi-directional filtering may be justified in limited scenarios, but it can create circular paths or cause filters to affect unrelated calculations.

A model should use the simplest filter direction that correctly represents the business process.

5.3 Active and inactive relationships

A model can contain multiple relationships between two tables, but normally only one is active for a particular path. A sales table may have OrderDate, ShipDate, and DueDate, while the Date table can have only one active relationship to Sales at a time. Measures can deliberately use an inactive relationship when a different business question is required.

The report should make date meaning clear. A chart labelled “Sales by Date” is incomplete if users cannot tell whether the date means order, shipment, or invoice date.

5.4 Relationship readiness checks

Check that:

• dimension keys are unique;
• fact foreign keys match dimension keys;
• unmatched keys are investigated;
• relationship cardinality reflects the data;
• filter direction is intentional;
• inactive relationships are documented;
• many-to-many designs have an explicit bridge or mapping strategy where appropriate.

6. Star Schema and Hierarchies

A star schema places a central fact table around which dimension tables are arranged. It is widely recommended for Power BI because it separates numeric events from descriptive context and provides predictable filtering.

Figure: A typical star schema in which dimensions filter a central sales fact table.

6.1 Hierarchies

A hierarchy organizes levels from broad to specific. Examples include:

• Year → Quarter → Month → Day;
• Country → Region → City;
• Category → Subcategory → Product.

Hierarchies support drill-down and consistent navigation. Levels should have a logical order and compatible grain. A fiscal calendar may require a hierarchy different from the standard calendar year.

A hierarchy is not merely a visual convenience. It communicates how the organization understands its data. Poorly designed hierarchies can encourage inappropriate comparisons, such as drilling from a financial year into calendar months without explanation.

7. Data Quality and Visual Trust

Data quality is multidimensional. A dataset can be complete but inaccurate, or accurate but too delayed to support decisions.

7.1 Common cleansing actions

Useful preparation actions include trimming spaces, standardizing case, replacing invalid codes, splitting compound fields, removing exact duplicates, and creating a controlled mapping table for categories. These actions should be traceable and documented rather than performed invisibly.

Missing values require interpretation. A blank revenue value may mean not recorded, not applicable, or zero. These meanings should not be collapsed into one value without business agreement.

Outliers should not be deleted merely because they look unusual. A very large sale may be a valid wholesale transaction. Investigation should precede correction.

7.2 Reconciliation and validation

A report should be reconciled to a trusted source. Useful checks include:

• total sales in Power BI versus the source system;
• number of transactions by reporting period;
• distinct customer count;
• minimum and maximum transaction dates;
• number of unmatched foreign keys;
• totals before and after transformation.

Reconciliation should be performed at several levels, not only at the grand total. A total may match while one region is overstated and another understated.

8. Aggregation Behavior in Power BI

When a numeric column is placed in a visual, Power BI may apply a default aggregation such as Sum, Average, Minimum, Maximum, or Count. The default is not a substitute for semantic judgment.

8.1 Choosing aggregation

Use Sum for additive quantities such as revenue or units, provided the table grain does not duplicate them. Use Count for the number of rows only when each row represents the intended counted event. Use Distinct count for unique customers, orders, or products. Use Average only when an ordinary arithmetic mean answers the question.

For a set of n values x₁, x₂, …, xₙ, the arithmetic mean is:

where x̄ is the mean, xᵢ is an observation, and n is the number of observations. This formula gives every observation equal weight. It is not necessarily appropriate for prices or percentages.

8.2 Ratios and percentages

A ratio should normally be calculated after aggregating its components. For example, conversion rate is commonly:

The numerator and denominator must use the same population, period, and filter context. Averaging daily conversion rates can differ from calculating total conversions divided by total visits, especially when daily visit counts vary.

8.3 Totals are calculations, not always sums

A matrix total for a percentage may not equal the arithmetic sum or average of the displayed rows. The correct total should represent the measure evaluated in the total filter context. Users should be taught that a total is a result for the total population, not necessarily a roll-up of visible percentages.

9. An Illustrative Case: Regional Sales Dashboard

Background

A fictional distributor, Northstar Supplies, asks for a Power BI dashboard showing sales by region, product category, customer segment, and month.

Problem

The initial report shows sales totals 18% higher than the finance system. Some products appear under “Unknown,” and monthly charts sort alphabetically. The team also reports that average selling price changes dramatically when a region filter is applied.

Analysis

Investigation identifies five issues:

1. The sales table contains one row per order line, but the order total is repeated on every line.
2. Product codes contain leading zeros, but they were imported as whole numbers.
3. The month name is stored as text and sorted alphabetically.
4. Several product keys do not match the Product dimension.
5. Average unit prices are calculated as a simple average of line prices, even though quantities differ substantially.

Approach and solution

The model is redesigned as a star schema with:

• a Sales fact table at order-line grain;
• Product, Customer, Store, and Date dimensions;
• a controlled ProductKey mapping process;
• a Date hierarchy using fiscal year, fiscal quarter, fiscal month, and date;
• a measure for revenue based on line-level revenue;
• a weighted average price based on total revenue divided by total quantity.

The repeated order total is removed from the line-level aggregation or retained only in a separate order-grain table. Product identifiers are stored as text. A numeric month-order column is created for chronological sorting. Unmatched product keys are assigned to an explicitly monitored “Unknown Product” member while the source problem is investigated.

Outcome

The Power BI total reconciles with finance. Regional comparisons become reliable, month labels appear in chronological order, and the weighted price measure behaves consistently across filters. The dashboard also includes a data-quality page showing unmatched products, missing dates, and refresh time.

Learning points

The case demonstrates that visualization errors often originate in data structure rather than chart selection. Grain, key integrity, data types, and measure definitions must be resolved before visual design can be trusted.

10. Governance, Ethics, and Contemporary Practice

A semantic model influences organizational decisions, so it should be governed. Important practices include ownership of metric definitions, documented data sources, controlled access, refresh monitoring, and versioned changes.

Row-level security may restrict users to authorized regions or business units. Security filters must be tested carefully; an incorrect relationship can expose data or hide legitimate results. Sensitive personal information should be minimized, protected, and displayed only when necessary for a defined purpose.

AI-assisted features can help users generate summaries or suggest visuals, but they do not remove the need for model validation. AI may reproduce incorrect measures, misunderstand business terminology, or generate plausible explanations from incomplete data. Certified measures, clear metadata, and human review remain essential.

Contemporary Power BI practice increasingly emphasizes semantic models as reusable analytical products. A well-designed model can support multiple reports, self-service analysis, governed metrics, and natural-language interaction. This increases the value of readiness work because an error in a shared model can affect many reports simultaneously.

11. A Practical Readiness Checklist

Before publishing a report, confirm the following:

1. Every table has a documented grain.
2. Data types match business meaning.
3. Dates are valid, complete, and assigned to an appropriate date table.
4. Dimension keys are unique.
5. Foreign-key mismatches are measured and investigated.
6. Relationships have appropriate cardinality and filter direction.
7. Measures have clear definitions and formats.
8. Additive, semi-additive, and non-additive behavior is understood.
9. Text categories are standardized and sorted correctly.
10. Totals and subtotals have been tested under relevant filters.
11. Results reconcile with a trusted source.
12. Security, refresh, documentation, and ownership are defined.

Chapter Summary

Data and semantic model readiness is the foundation of accurate Power BI visualization. Data types determine how fields can be interpreted and used. Facts record events, dimensions provide descriptive context, measures calculate results dynamically, and calculated columns provide row-level attributes. Granularity defines what each row means and must be known before aggregation or relationships are designed.

Star schemas generally provide a clear structure in which dimensions filter a central fact table. Relationships require correct keys, cardinality, and filter direction. Hierarchies support meaningful drill-down, while data-quality controls protect accuracy and trust. Aggregation must reflect whether a value is additive, semi-additive, or non-additive; ratios and weighted measures should be calculated from appropriate components rather than averaged or summed blindly.

A professional Power BI practitioner validates the model before designing visuals, reconciles results with trusted sources, documents assumptions, and considers governance, security, and responsible use.

Key Terms

Data type: The technical and semantic classification of a field, such as text, number, date, or Boolean.

Semantic model: A business-oriented model defining tables, relationships, measures, and analytical behavior.

Fact table: A table recording events, transactions, or observations.

Dimension: A descriptive table used to group, filter, or label facts.

Measure: A calculation evaluated dynamically in the current filter context.

Calculated column: A value calculated and stored for each row of a table.

Granularity: The level of detail represented by one row.

Primary key: A field or combination of fields that uniquely identifies a row.

Foreign key: A field that references a key in another table.

Cardinality: The number-to-number relationship between matching rows in two tables.

Filter direction: The direction in which filtering travels through a relationship.

Star schema: A model with a central fact table connected to surrounding dimensions.

Hierarchy: An ordered set of analytical levels, such as Year, Month, and Day.

Additive measure: A measure that can be summed across relevant dimensions.

Semi-additive measure: A measure that can be summed across some dimensions but not others.

Non-additive measure: A measure for which summation is not meaningful.

Data quality: The degree to which data is accurate, complete, consistent, valid, unique, timely, and integrated.

Grain mismatch: A conflict caused by combining data at different levels of detail.

Filter context: The set of filters determining the result of a measure.

Reconciliation: Comparison of model results with a trusted source to verify accuracy.

Review Questions

Short Answer Questions

1. Why should an identifier such as 001245 generally be stored as text?
2. What is the difference between a fact table and a dimension table?
3. Define granularity and explain why it matters for aggregation.
4. What is the typical cardinality between a Product dimension and a Sales fact table?
5. Why can a percentage not usually be summed across categories?

Long Answer Questions

1. Explain how a star schema supports accurate and usable Power BI visualizations.
2. Compare measures and calculated columns, including an appropriate use for each.
3. Discuss the major data-quality dimensions and their effects on report interpretation.
4. Explain relationship cardinality, filter direction, and inactive relationships.
5. Describe how date tables and hierarchies support time-based analysis.

Analytical / Application Questions

1. A sales table contains one row per order line and repeats the order total on every line. Explain the resulting risk and propose a modeling solution.
2. A report shows an average product price of £12, although total revenue is £120,000 and total quantity is 8,000 units. Calculate the weighted average price and explain why it may differ from the displayed average.
3. A regional conversion-rate chart averages four daily rates equally, even though daily visitor counts vary greatly. Explain why this may be misleading and specify the components required for a more appropriate calculation.

Multiple Choice Questions with Answers

Question 1

Which field is most appropriately stored as text?

A. Units sold

B. Order identifier with leading zeros

C. Revenue

D. Transaction date

Correct Answer: B. Order identifier with leading zeros

Question 2

In a typical star schema, which table usually contains foreign keys and transactional numeric values?

A. Date dimension

B. Product dimension

C. Fact table

D. Hierarchy table

Correct Answer: C. Fact table

Question 3

A sales table has one row per order line. What does counting rows measure?

A. Unique customers

B. Orders only

C. Order lines

D. Product categories

Correct Answer: C. Order lines

Question 4

Which relationship is most typical between a Product dimension and a Sales fact table?

A. Many-to-many

B. One-to-many

C. One-to-one only

D. No relationship

Correct Answer: B. One-to-many

Question 5

Which measure is usually semi-additive?

A. Units sold

B. Sales revenue

C. Inventory balance

D. Number of invoices

Correct Answer: C. Inventory balance

Question 6

What is the main purpose of a hierarchy?

A. To remove duplicate fact rows

B. To provide ordered levels for analysis and drill-down

C. To convert text into numbers

D. To replace all relationships

Correct Answer: B. To provide ordered levels for analysis and drill-down

Question 7

Revenue is £50,000 and quantity is 2,500 units. What is the weighted average price?

A. £10 per unit

B. £20 per unit

C. £25 per unit

D. £125 per unit

Correct Answer: B. £20 per unit

Question 8

Which condition is essential on the one side of a one-to-many relationship?

A. The key must be unique

B. The key must contain only decimals

C. Every value must be negative

D. The table must contain no dates

Correct Answer: A. The key must be unique

Question 9

Why can averaging daily conversion rates be misleading?

A. Dates cannot be used in Power BI

B. Daily rates may have different numbers of visits

C. Conversion rates are always additive

D. Measures cannot respond to filters

Correct Answer: B. Daily rates may have different numbers of visits

Question 10

Which action best supports model validation?

A. Using the most colorful chart

B. Hiding all blank values

C. Reconciling totals and key counts with a trusted source

D. Converting all columns to text

Correct Answer: C. Reconciling totals and key counts with a trusted source