Dimensional Modeling

 how to identify facts from a business transaction table

Identifying facts from a business transaction table is crucial for building a useful data warehouse. Here's a systematic approach to pinpointing those key measurements:

1. Understand the Business Process:

What is the purpose of the transaction? Every transaction represents a business event. Understanding the event is the first step.

What are the key activities? What actions are being recorded?

What are the outcomes? What are the measurable results of the transaction?

2. Look for Numerical Values:

Facts are typically numeric and represent quantities, amounts, or measurements.   

Examples:

Sales: Quantity sold, sales amount, discount amount, tax amount.

Inventory: Quantity received, quantity shipped, quantity on hand.

Manufacturing: Units produced, units rejected, production time.

Financial: Transaction amount, interest paid, fees charged.

Shipping: weight, number of packages, shipping cost.

3. Consider the Granularity:

At what level of detail do you need to measure the business process?

For example, in a sales transaction:

Do you need to measure sales at the order level?

Or at the individual line item level?

The chosen granularity determines which numerical values become facts.   

4. Distinguish Facts from Dimensions:

Facts are measurements; dimensions provide context.   

Dimensions are descriptive attributes that qualify the facts.   

Examples:

Facts: Sales amount, quantity sold.   

Dimensions: Product, customer, date, location.   

To help distinguish, ask yourself, "Can I sum, average, or perform other calculations on this value?" If yes, it's likely a fact.

5. Look for Derived Facts:

Sometimes, you can derive facts by performing calculations on existing columns.

Examples:

Extended price = quantity * unit price.   

Profit = sales amount - cost of goods sold.

These derived facts can be valuable for analysis.

6. Consult with Business Users:

Business users are the experts in the business process.

Ask them:

"What metrics are important to you?"

"What questions do you need to answer?"

"What do you want to measure?"

Their input will help you identify the most relevant facts.

7. Common Fact Types:

Additive Facts: Can be summed across all dimensions (e.g., sales amount, quantity sold).   

Semi-Additive Facts: Can be summed across some dimensions but not others (e.g., inventory on hand).   

Non-Additive Facts: Cannot be summed across any dimensions (e.g., ratios, percentages).   

Example:

Consider a "Sales Transactions" table with the following columns:

OrderID

CustomerID

ProductID

OrderDate   

Quantity

UnitPrice

Discount

Facts:

Quantity

UnitPrice

Discount

Derived Fact:

Extended Price (Quantity * UnitPrice)   

Dimensions:

CustomerID

ProductID

OrderDate

By following these steps, you can effectively extract the facts from your business transaction tables and create a robust data warehouse for an999