Monday, April 11, 2011

Different types of facts inside the fact table in SSAS.

The facts inside the fact table could be of several different types

1) Additive facts

-Additive facts are facts that can be summed up through all of the dimensions in the fact table.
-facts that can be added across all of the dimensions in the fact table, and are the most common type of fact.
-Also be called fully additive facts. They are identified here because these facts would be used across several dimensions for summation purposes.
-It is important to understand that since dimensional modeling involves hierarchies in dimensions, aggregation of information over different members in the hierarchy is a key element in the usefulness of the model.
-Since aggregation is an additive process, it is good if we have additive facts.

2) Semi-additive facts

-Semi-additive facts are facts that can be summed up for some of the dimensions in the fact table, but not the others.
-These are facts that can be added across some dimensions but not all.
-They are also sometimes referred to as partially-additive facts.

3) Non-additive facts

-Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.
-Non-additive Facts that cannot be added for any of the dimensions. That is, they cannot be logically added between records or fact rows.
-facts are usually the result of ratios or other mathematical calculations. The only calculation that can be made for such a fact is to get a count of the number of rows of such facts.

Based on the above classifications, there are two types of fact tables:

A)     Cumulative: This type of fact table describes what has happened over a period of time. For example, this fact table may describe the total sales by product by store by day. The facts for this type of fact tables are mostly additive facts. The first example presented here is a cumulative fact table.

B)      Snapshot: This type of fact table describes the state of things in a particular instance of time, and usually includes more semi-additive and non-additive facts. The second example presented here is a snapshot fact table.

4) Derived facts
-Derived fact is shown as a kind of fact, along with base fact.
-A derived fact is created by an inference or a mathematical calculation from terms, facts, other derivations, or even action assertions.
-A base fact is a fact that is a given in the world and is remembered (stored) in the system.
-Derived facts are created by performing a mathematical calculation on a number of other facts, and are sometimes referred to as calculated facts.
-Derived facts may or may not be stored inside the fact table.
-Base fact is generally defined, when a fact can be picked-up from the source system without any derivation. The derived fact is a data field which is derived from the base fact

5) Textual facts
-Textual fact consists of one or more characters (codes). They should be strictly avoided in the fact table. Textual codes such as flags and indicators should be stored in dimension tables so they can be included in queries.
- Textual facts are non-additive, but could be used for counting.

6) Pseudo fact
-Pseudo fact when summed, a pseudo fact gives no valid result. They typically result when you design event-based fact tables.
-In SQL Server 2005, you cannot perform a non-logged load directly into a partition. However, you can load into a separate table that we will call the pseudo-partition. Under certain conditions, you can switch the pseudo-partition into the partitioned table as a metadata operation that occurs extremely quickly.

7) Factless fact
-A fact table with only foreign keys and no facts is called a factless fact table.
-Is a table which is similar to Fact Table except for having any measure; I mean that this table just has the links to the dimensions.
-Use of Factless fact table is these tables enable you to track events; indeed they are for recording events.

Posted by - SUHAS R. KUDEKAR (MCTS - Microsoft Business Intelligence)
Learning Office 2010 + SharePoint 2010 + SQL Server 2008 R2

No comments:

Post a Comment