OLAP Video Tutorial

This is an exciting video I came across where OLAP concepts are explained in a easy to understand way using cartoons.

Dimensional Modeling

Dimensional Modeling refers to the technique used to design logical models for database which can support OLAP and Data Warehousing operations. It differs from the relational modeling or ER modeling in the sense that it can be used for data warehouse as well as relational databases.

Dimensional modeling is based on two concepts called facts and dimensions. Facts are usually numeric values or something that can be measured or aggregated. For example: monthly sales amount. Dimensions are groups of hierarchies and attributes that are used to define facts. For example product and its color. In a dimensional model a group of facts forms a fact table while a group of dimensions form a dimension table.

Market Basket Analysis

Market Basket Analysis is used to identify or gain interesting insights into the way customers buy items. It finds out the combination of items which are most likely to be bought together. For example buying milk and bread together can lead to buying butter. Identifying such patterns from the transaction data collected over the years provide new window of opportunity to explore and understand the purchasing behavior and thus can be used to increase sales through cross selling and targeted marketing. Market basket analysis is not limited to retail sales but are applied to a wide range of areas like analyzing credit card purchases, fraud detection for insurance claims and so on. Data Mining technique called association rule mining is usually used. Algorithms like FP-Growth and Apriori algorithm are usually used for mining the transaction data to get the desired patterns.

MDX - Multi-Dimensional Expressions

OLAP cubes stores data in multidimensional form whereas in the relational database data is stored in the 2 dimensions: rows and columns. OLAP cubes can store data in more than these 2 dimensions. The SQL or Structured Query Language enables us to access and manipulate the data resinding in the relational database. However SQL cannot be used for the same purposes for OLAP cubes. MDX is the language used for accessing and manipulating data in the cubes.