OLTP, OLAP and star schemas

Separate transaction systems from analytical models.

UnderstandStudy an examplePractise

Understand the concept

OLTP supports many small consistent operations, such as inserting an order. OLAP aggregates larger datasets for analysis. The two workloads may need different models and indexes.

In a star schema, a fact table stores measures at a declared grain and dimension tables add product, customer or calendar context. If FactSales has order-line grain, COUNT(*) is not an order count. Slowly changing dimensions may be needed to preserve history: decide whether reports need the current customer city or the city at sale time.

WHEN TO USE IT

Use it when building a warehouse or dashboard model from a transactional system.

COMMON PITFALL

Building all measures with COUNT/SUM without defining fact grain.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

SELECT OrderId, ProductId, Quantity, UnitPrice FROM lab.OrderItem;

A source of line-grain measures.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Calculate units sold by category for paid orders.

Show the solution and explanation

Worked solution

SELECT p.Category, SUM(i.Quantity) AS Units FROM lab.OrderItem AS i JOIN lab.Product AS p ON p.ProductId = i.ProductId JOIN lab.SalesOrder AS o ON o.OrderId = i.OrderId WHERE o.Status = 'Paid' GROUP BY p.Category;

Units sold use SUM(Quantity); COUNT(*) measures lines, not units.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.