Data Warehousing and OLAP for Business Insights

Data Warehousing and Data Mining 18MCA452 Module-1 Prepared by Dr. Shankaragowda B.B. Asst. Professor, Dept. of MCA Bapuji Institute of Engineering and Technology, Davangere-577 004.

Introduction Data Warehousing A data Warehousing is a technique for collecting and managing data from varied sources to provide meaningful business insights. It is a blend of technologies and components which allows the strategic use of data. It is electronic storage of a large amount of information by a business which is designed for query and analysis instead of transaction processing. It is a process of transforming data into information and making it available to users in a timely manner to make a difference.

Data Warehousing Modeling Data warehouse modeling includes: Top Down/Requirements Driven Approach Fact Tables and Dimension Tables Multidimensional Model/Star Schema Support Roll Up, Drill Down, and Pivot Analysis Time Phased/Temporal Data Operational Logical and Physical Data Models Normalization and Denormalization Model Granularity: Level of Detail

OLAP Online analytical processing(OLAP) is an approach to answer multi-dimensional analytical queries swiftly in computing. OLAP is part of the broader category of business intelligence., which also encompasses relational databases, report writing and data mining. Advantages: OLAP is a platform for all types of business includes planning, budgeting, reporting and Analysis. Information and calculations are consistent in an OLAP cube. This is a crucial benefit . Disadvantages: OLAP requires organizing data into a star schema. These schemas are complicated to implement and administer. Transactional data cannot be accessed with OLAP system.

The main characteristics of OLAP are as follows: Multidimensional conceptual view: OLAP systems let business users have a dimensional and logical view of the data in the data warehouse. It helps in carrying slice and dice operations. Multi-User Support: Since the OLAP techniques are shared, the OLAP operation should provide normal database operations, containing retrieval, update, adequacy control, integrity, and security. Accessibility: OLAP acts as a mediator between data warehouses and front-end. The OLAP operations should be sitting between data sources (e.g., data warehouses) and an OLAP front-end. Storing OLAP results: OLAP results are kept separate from data sources. Uniform documenting performance: Increasing the number of dimensions or database size should not significantly degrade the reporting performance of the OLAP system.

OLAP provides for distinguishing between zero values and missing values so that aggregates are computed correctly. OLAP system should ignore all missing values and compute correct aggregate values. OLAP facilitate interactive query and complex analysis for the users. OLAP allows users to drill down for greater details or roll up for aggregations of metrics along a single business dimension or across multiple dimension. OLAP provides the ability to perform intricate calculations and comparisons. OLAP presents results in a number of meaningful ways, including charts and graphs.

OLAP implementation steps: After preparation, we are moving to implementation. There are seven basic steps of it: Step one: dimensional modeling Step two: select the data required for removing into OLAP system Step three: data extraction for the OLAP system Step four: loading data to the OLAP server Step five: data aggregation and derived data computation Step six: implementation of OLAP application on desktop Step seven: user s training organization Online Analytical Processing Server (OLAP) is based on the multidimensional data model. It allows managers, and analysts to get an insight of the information through fast, consistent, and interactive access to information. This chapter cover the types of OLAP, operations on OLAP, difference between OLAP, and statistical databases and OLTP.

We have four types of OLAP servers : Relational OLAP (ROLAP) Multidimensional OLAP (MOLAP) Hybrid OLAP (HOLAP) Specialized SQL Servers OLAP Tools: IBM Cognos Micro Strategy Mondrian Jedox

OLAP vs OLTP Sr.No. Data Warehouse (OLAP) Operational Database (OLTP) 1 Involves historical processing of information. Involves day-to-day processing. 2 OLAP systems are used by knowledge workers such as executives, managers and analysts. OLTP systems are used by clerks, DBAs, or database professionals. 3 Useful in analyzing the business. Useful in running the business. 4 It focuses on Information out. It focuses on Data in. 5 Based on Star Schema, Snowflake, Schema and Fact Constellation Schema. Based on Entity Relationship Model.

6 Contains historical data. Contains current data. 7 Provides summarized and consolidated data. Provides primitive and highly detailed data. 8 Provides summarized and multidimensional view of data. Provides detailed and flat relational view of data. 9 Number or users is in hundreds. Number of users is in thousands. 10 Number of records accessed is in millions. Number of records accessed is in tens. 11 Database size is from 100 GB to 1 TB Database size is from 100 MB to 1 GB. 12 Highly flexible. Provides high performance.

Data cube Data cube us a structure that enable OLAP to achieves the multidimensional functionality. The data cube is used to represent data along some measure of interest. A data cube refers is a three-dimensional(3D) range of values that are generally used to explain the time sequence of an image s data. It is a data abstraction to evaluate aggregated data from a variety of viewpoints.or A data cube is a multi dimensional array of values. A cube is a way of storing data in a multidimensional form. Each cell(l,p,t) in this 3D data cube, we store the aggregate of sales of product(p) that sold to location(l) at time(t). Every time we needed the cube we had to compute these aggregate from raw data inside a data warehouse.

OLAP Operations in the Multidimensional Data Model In the multidimensional model, the records are organized into various dimensions, and each dimension includes multiple levels of abstraction described by concept hierarchies. This organization support users with the flexibility to view data from various perspectives. A number of OLAP data cube operation exist to demonstrate these different views, allowing interactive queries and search of the record at hand. Hence, OLAP supports a user-friendly environment for interactive data analysis. Consider the OLAP operations which are to be performed on multidimensional data. The figure shows data cubes for sales of a shop. The cube contains the dimensions, location, and time and item, where the location is aggregated with regard to city values, time is aggregated with respect to quarters, and an item is aggregated with respect to item types.

OLAP Cube operations: Roll-up, Drill-Down, Slice, Dice and Pivot

Text book Jiawei Han and Micheline Kamber: Data Mining Concepts and Techniques, 2nd Edition, MorganKaufmann Publisher, 2006. Reference book Arun K Pujari: Data Mining Techniques University press 2nd Edition, 2009.