Business Intelligence Basics

BI Concept: 

Star Schema: The star schema has a center, represented by a fact table, and the points of the star, represented by the dimension tables.

Snowflake Schema: In a snowflake, the dimension tables are normalized.

ODS: ODS should be considered only when information retrieval requirements are to be real time and cannot access OLTP systems because of criticality of resources.

Dimension Types:

Changing Dimension:  Changing dimension is the dimension which has at least one attribute whose value would change over the time.

Slowly Changing Dimensions: Attributes of a dimension that would undergo changes over time. For example: Employee work location 

• Type I: The existing record is updated with change.

• Type II: History is preserved, a new record is added to dimension and old record is marked as inactive.  EndDate or IsLatest is generally used to represent latest record of a business key.

• Type III: Type III preserves a version of the history by adding it to the dimension record in a different attribute.  Typically, there are two states: current and previous. In some cases, you may have 4 or 5 states. 

Rapidly Changing Dimensions: A dimension attribute that changes frequently is a Rapidly Changing Attribute. For example, a CurrentLocation attribute

Junk Dimensions: A junk dimension is a single table with a combination of different and unrelated attributes to avoid having a large number of foreign keys in the fact table.

Inferred Dimensions: While loading fact records, a dimension record may not yet be ready. One solution is to generate an surrogate key with Null for all the other attributes.

Conformed Dimensions: A Dimension that is used in multiple locations is called a conformed dimension. A conformed dimension may be used with multiple fact tables in a single database, or across multiple data marts or data warehouses.

Degenerate Dimensions: A degenerate dimension is when the dimension attribute is stored as part of fact table, and not in a separate dimension table. Common examples include  Order Line Number or Transaction Number. 

Role Playing Dimensions: A role-playing dimension is one where the same dimension key—along with its associated attributes—can be joined to more than one foreign key in the fact table. For example, a fact table may include foreign keys for both Ship Date and Delivery Date.

Shrunken Dimensions: A shrunken dimension is a subset of another dimension. For example, the Orders fact table may include a foreign key for Product, but the Target fact table may include a foreign key only for ProductCategory, which is in the Product table, but much less granular.

Outtriggers: Sometimes a dimension attribute can have multiple values for a single dimension key member. For example, a customer may have multiple accounts. One solution is to create a separate table with Customer Account as its logical primary key. An outrigger dimension is the logical inverse of a shrunken dimension. 

Static Dimensions: Static dimensions are not extracted from the original data source, but are created within the context of the data warehouse. A static dimension can be loaded manually—for example with Status codes

Denormalization: This is the process of flattening the dimensions to form one single unit of all the related entities.  Normalization is generally performed on OLTP systems to reduce redundancy and reduce the risk of update anomalies while managing many transactions.  On the contrary, denormalization introduces redundancy and reduces joins, allowing for faster retrieval and more user-friendly structures

Types of Fact tables:

Transaction table: This is the transactions as it happens on the source.  Each transaction and its measure such as price is extracted, transformed and loaded to facts at regular intervals of time.

Snapshot table: This is not exactly a transaction on the source.  It is generally to get the snapshot of the system.  Example: Getting all customer account balance as of end of the day.

Factless Fact: This fact generally is not sourced from an OLTP transaction.  A factless fact table is a fact table that does not contain any facts (i.e., metrics). Factless fact tables are used to track events that did, or did not, happen. For example, attendance at a cultural event

Surrogate key: A surrogate key should be a simple running sequence number and should not have any encoded information.

Natural Key: The natural key (source key) may be modified in source system, having it referenced in fact table would lead to chaos as it needs to update entire fact table wherever the natural key is referenced.

Types Measures:

Additive measures: Allow summary information to be obtained by adding various quantities of the measure, such as the sales of a specific item at a group of stores for a particular time period.

Semi-additive measure: Is additive across all dimensions except time. The classic example of a semi-additive fact is a daily account balance.

Non-additive measures: Non additive measures are those which cannot be summarized like additive/semi additive measures. You can often do that by adding a weighting factor. For example, rather than store a rate, you can multiply the rate by a weight.

Covering Index:  Covering index will contain all the columns of the table, in this context it is for fact table.  The idea behind of creating covering index is boosting performance for any queries need not be part of any other index At the  cost of index size.