Diving into BI

The Data Warehouse Toolkit,3rd edition by Ralph Kimball and Margy Ross will serve as a reference to this blog.  

The Basics

Business Intelligence is a process which comprises of many steps and goals :

• Understanding various business processes within the system,this may include requirements from a wide variety of users.
• Once processes have been identified they need to be mapped to specific data sets or entities.
• Massive amounts of data are stored in a data warehouse,which is essentially the crux of BI.
• The BI environment must be sustained and any application accessing the data warehouse must be able to read the data without any "hiccups". High quality and accessibility are also some features that a BI solution must have.

Datawarehouses are de-normalized so that data access is fast and aggregates are easier to obtain.

Facts 

As the title suggests this is the very "grain" of information required by business users. Multiple fact tables may exist within a system.
For example a transaction you make while purchasing an item is recorded as a fact in the data warehouse. The fact row might contain attributes such as transaction#,customer info,item purchased,sales amount,quantity sold etc.
If this was to be stored in a normal OLTP system we would store the data based on different themes i.e customer info would ideally go into a table which has all the customer information and item would go into a an item table.
Lets take a step forward and see how we store it in a OLAP system.
We de-normalize the data and store it in "FACT" tables. A fact table represents a business process which contains measures (In this case sales amount,quantity) and dimensions (Date of transaction,customer information,Item purchased)
Facts contain foreign keys to different dimensions present

Dimensions
Dimensions are different ways in which the data can be viewed. These are tables which contain themes present in the data. Viewing a fact from different points of view are what a dimension essentially achieves.
In the example above customers,items are different dimensions that will be created in the system.

Star Schema
An architecture wherein a central fact table references multiple dimensions is referred to a star schema.

Next post we'll be digging in to the different types of fact tables. Stay Tuned !