As discussed earlier, the schema objects acts as the components for the logical data model. A logical data model includes the following three schema objects :
- Facts
- Attributes
- Hierarchies
Facts
Facts are measures that you use to analyze your business. Fact data is typically numeric, and it is generally aggregatable. Revenue, unit sales, inventory, and account balance are just a few examples of facts that you may use in your business.
In the data warehouse, facts exist as columns in fact tables. They can come from different source systems, and they may be stored at different levels of detail. For example, you may capture revenue data in one system and inventory data in another system. You may also track revenue data by quarter in one table and by day in another table. So, it depends on the DWH that we design and the level in which the fact resides.
Facts ( Key measures ) are critical to much of the analysis that users perform on your business data. In MicroStrategy projects, they map to fact schema objects, which form the basis for all the metrics you use in reports. The other components of a logical data model provide context for the facts.
If you are familiar with SQL, facts generally represent the numeric columns in tables on which you perform SQL aggregations like SUM, AVG, and so forth. For example, in the following SQL statement,the
ORDER_AMT column is a fact:
SELECT a22.EMP_ID, sum(a21.ORDER_AMT) FROM ORDER_FACT a21 JOIN LU_EMPLOYEE a22 ON (a21.EMP_ID = a22.EMP_ID) WHERE a22.CALL_CTR_ID in (5, 9, 12)
Attributes are descriptive data that provide context for analyzing facts. They enable you to answer questions about facts and report on various aspects. Without this context, facts are meaningless.
In the data warehouse, attributes exist as columns in lookup, relationship, and fact tables. They can come from different source systems, and you can use them to analyze facts at various levels of detail.
If you are familiar with SQL, attributes generally represent the non-aggregatable columns in tables that you use to qualify and group fact data. For example, in the following SQL statement, the MONTH_ID column is an attribute: