Tag: grain

Data Modeling include:

• Conceptual Modeling
• Logical Modeling
• Physical Modeling
• Operational Data Modeling
• Relational Modeling
• Data Vault
• Dimensional Modeling
  e.g. Kimball dimensional modeling
• Other Modeling Approaches

Dimensional Modeling is a data modeling technique designed primarily for analytical workloads.

It organizes data around:

• Facts — measurable business events 可以度量的业务事件
• Dimensions — descriptive business context 描述业务上下文的信息

Kimball Dimensional Modeling

Kimball Dimensional Modeling is an approach to designing analytical data warehouses using dimensional models, centered around business processes, facts, dimensions, conformed dimensions, and a bus architecture.

Kimball Dimensional Modeling belongs to Dimmensional Modeling.

The Core Philosophy of Kimball:
Start with the business process, not the database tables.

Important concept of Kimball’s dimensional modeling

Business Process

A Business Process is a business activity that an organization performs and wants to measure, analyze, and improve.
企业正在执行的一类业务活动，而且企业希望对这类活动进行度量、分析和改进. e.g.

• 销售 Sales
• 订单履行 Order Fulfillment
• 库存 Inventory
• 支付 Payments
• 退货 Returns
• 保险理赔 Claims
• 医院入院 Admissions
• 贷款申请 Loan Applications
• 客户服务 Customer Service

Kimball dimensional modeling starts with the business process.

Typical examples of Business Processes

Retail:

• Sales
• Inventory
• Returns
• Payments
• Shipping

Banking:

• Account Opening
• Transactions
• Payments
• Loans
• Credit Card Transactions
• Fraud Detection

Government

• Application
• Case Processing
• Permit Issuance
• Payment
• Inspection
• Appeal

Kimball Dimensional Design — 4 Steps

The four critical steps of the Kimball lifecycle / dimensional modeling methodology are:

1. Select the Business Process
2. Declare the Grain
3. Identify the Dimensions
4. Identify the Facts

An Example: “We want to analyze credit card transactions.”

Do not create tables now.

Step 1: Select the Business Process

What is the Business Process? Credit Card Transaction

What is the Business Event? A cardholder makes a transaction
at a merchant.

Step 2: Declare the Grain

What is the Grain? One row = one credit card transaction

Step 3: Identify the Dimensions

What Dimensions?

• Customer
• Card
• Merchant
• Date
• Time
• Location
• Transaction Type

Step 4: Identify the Facts

What Facts?

• Transaction Amount
• Fee Amount
• Tax Amount
• Reward Points

Finally:

                  Dim Customer
                       |
                       |
Dim Card ------- Fact Transaction ------ Dim Merchant
                       |
                       |
                    Dim Date
                       |
                       |
                    Dim Time

This is Kimball’s thought.

Implementation – Initially Physical Model

Assuming, I have decided:
Grain = one row per sales order line

#  Fact
CREATE TABLE fact_sales (
    sales_date_key       INT,
    customer_key         BIGINT,
    product_key          BIGINT,
    store_key            BIGINT,

    order_number         STRING,

    quantity             DECIMAL(18,2),
    unit_price            DECIMAL(18,2),
    discount_amount       DECIMAL(18,2),
    sales_amount          DECIMAL(18,2),
    cost_amount           DECIMAL(18,2)
);



#  Dimensions：
CREATE TABLE dim_customer (
    customer_key BIGINT,
    customer_id  STRING,
    customer_name STRING,
    province STRING,
    customer_segment STRING
);

CREATE TABLE dim_product (
    product_key BIGINT,
    product_id STRING,
    product_name STRING,
    category STRING,
    brand STRING
);

CREATE TABLE dim_date (
    date_key INT,
    calendar_date DATE,
    year INT,
    quarter INT,
    month INT,
    month_name STRING,
    day_of_week STRING
);

Grain：Fact Table

The grain defines exactly what one row in a fact table represents.
Grain 定义 Fact Table 中“一行数据到底代表什么业务事件”。

One row = one sales transaction
一行 = 一笔销售交易

One row = one sales transaction
一行 = 一笔销售交易

One row = one order line
一行 = 一个订单明细

Grain is the semantic contract of the fact table.

Three common fact grains in Kimball

In data warehousing, these three grains are formally known as:

1. Transaction Fact grain (事务粒度)
2. Periodic snapshot Fact grain (周期快照粒度)
3. Accumulating snapshot Fact grain (累积快照粒度)

Fact Type	Grain	核心问题
Transaction Fact	One business event 一次业务事件	What happened? 发生了什么？
Periodic Snapshot	One entity/process at a periodic point 某个周期时点的状态	What was the state? 当时状态如何？
Accumulating Snapshot	One process lifecycle 一个业务流程生命周期	How did the process progress? 流程进行到哪里？

Transaction Fact Grain

One row represents one business transaction/event.

e.g. One row = one order line

Date	Customer	Product	Store	Qty	Sales
Sep 13	C001	P001	S001	1	1200
Sep 13	C001	P002	S001	2	500
Sep 13	C001	P003	S001	1	50

Periodic snapshot Fact grain

A row represents the state of a business process at a regular point in time.
一行代表某个业务对象/流程在一个固定周期时间点的状态。

e.g. Account Balance Snapshot
One row = Customer Account + Day

Date	Account	Balance
Sep 11	A001	10,000
Sep 12	A001	9,500
Sep 13	A001	11,000

Accumulating snapshot Fact grain

A single row represents the entire lifecycle of a process or a specific entity that moves through multiple, predictable milestones.

e.g. Order Fulfillment

Order Created
↓
Payment Approved
↓
Warehouse Processing
↓
Shipped
↓
Delivered

We can represent the entire lifecycle of an order in one row.

Order	Order Date	Payment Date	Ship Date	Delivery Date
10001	Sep 10	Sep 10	Sep 11	Sep 13

Grain Determines Whether a Fact Is Valid

A fact is only meaningful if it is measured at the same grain as the fact table.
一个 Fact 只有在与 Fact Table 的 Grain 相匹配时，才是有意义、可以正确分析的。

Suppose we define:

Grain: One row represents one Order Line.

That means every row in the fact table represents exactly:

One product on one order

Order	Product	Quantity	Sales Amount
O1001	iPhone	1	$1,200
O1001	AirPods	2	$500
O1002	MacBook	1	$2,000

A error fact example.

Order	Product	Sales Amount	Order Total
O1001	iPhone	1,200	1,700
O1001	AirPods	500	1,700

there is issue on the column “Order Total”
把 $1,700 放进一个 Order-Line Grain 的 Fact Table，并让它看起来像每一行的可加 Fact，是错误的设计。 我们的 Fact Grain 是 “One row represents one Order Line.” 一行一个order 的 “行” – order 上的一行。

Order Total O1001 这份order的 合计金额， 是 “Order Grain” – 一份order的. 放在目前的Fact 上就不对。

如果一定要使用 “Order Total ”， 那就再叫另外一个Fact_order,

Grain：One row per Order

Order	Customer	Order Total	Shipping Cost
O1001	C001	1700	20
O1002	C002	2000	30

Grain Determines Dimension Context

Kimball-style terminology: The grain defines not only what the fact represents, but also which dimensions can validly describe the row.

Kimball Principle: Dimensions should be understandable to business users.