– 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
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:
SELECT a11.MONTH_ID, sum(a11.TOT_DOLLAR_SALES) FROM MNTH_CATEGORY_SLS a11 JOIN LU_MONTH a12 ON (a11.MONTH_ID = a12.MONTH_ID) WHERE a11.MONTH_ID in (200801, 200802, 200803) GROUP BY a11.MONTH_ID
Note:
– Attribute forms are not explicitly included in the logical data model
– Attribute elements are not explicitly included in the logical data model
– Attribute relationships are essential to the structure of a logical data model as they are the key to joining data from different attributes and aggregating fact data to different levels. Attribute relationships describe logical associations between attributes that exist based on business rules.
Hierarchies
Hierarchies are groupings of directly related attributes ordered to reflect their relationships. In a logical data model, hierarchies are also sometimes referred to as dimensions. You generally organize attributes into hierarchies based on logical business areas.
For example, you can group attributes such as Year, Quarter, Month, and Date to form a Time hierarchy, or you can group attributes such as Category, Subcategory, and Item to form a Products hierarchy.
Hierarchies contain only attributes that are directly related to each other. Attributes in one hierarchy are indirectly related to attributes in other hierarchies.
Directly Related Hierarchies
For example, Month and Date are attributes that are usually directly related to each other. You do not need any additional information to establish the relationship between these two attributes.
Indirectly Related Hierarchies
Month and Customer are attributes that are usually indirectly related to each other. Therefore, they would generally not be part of the same hierarchy. You have to know some sort of additional information to establish a relationship between these attributes. They are related through the existence of one or more facts, such as when a customer purchased an item or a customer’s average account balance for a particular month.Hierarchies do not explicitly exist in the data warehouse, but they often parallel the logical dimensions you use to describe and organize your business data.
In MicroStrategy projects, the composite of all the hierarchies you include in the logical data model forms the system hierarchy. User hierarchies may or may not follow the structure of individual hierarchies from the logical data model.
Hope this says clearly about the components of Logical data model!!
Have a good day !!