The Logical Data Model » Facts: Business data and measurements

Facts: Business data and measurements

One of the first things you do when you create a logical data model is to determine the facts. Conceptually, you can think of facts as business measurements, data, or variables that are typically numeric and suitable for aggregation. Sales, Inventory, and Account Balance are some examples of facts you can use as business measurements.

Facts allow you to access data stored in a data warehouse and they form the basis for the majority of users’ analysis and report requirements. In MicroStrategy, facts are schema objects that relate data values (typically numeric data) from the data warehouse to the MicroStrategy reporting environment.

Facts are the building blocks used to create business measurements or metrics from which to derive insight into your data. The rest of data modeling consists mostly of providing context for the data that facts provide access to.

In a data warehouse, facts exist as columns within the fact tables. They can come from different source systems and they can have different levels of detail. For example, you can capture sales data in one system and track it daily, while you capture stock and inventory data in another system and track it weekly.

To those familiar with SQL, facts generally represent the numeric columns in database tables on which you perform SQL aggregations, such as SUM and AVG.

For example, in the following SQL statement, the ORDER_AMT column in the warehouse may correspond to the Order Amount fact in the MicroStrategy environment:

SELECT sum(a21.ORDER_AMT) EMP_NAME
FROM ORDER_FACT a21
JOIN LU_EMPLOYEE a22
			ON (a21.EMP_ID = a22.EMP_ID)
WHERE   a22.CALL_CTR_ID in (5, 9, 12)

In addition, while ORDER_AMT is the fact, sum(a21.ORDER_AMT) represents a metric. Metrics, which are business calculations often built using facts, are discussed in the Basic Reporting Guide.

For a more complete discussion about facts, see The Building Blocks of Business Data: Facts.