1 of 80

Data Warehouse Implementation and Data Mining

:

MODULE 1

1

4/19/2022

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

2 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

3 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

4 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

5 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

6 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

7 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

8 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

9 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

10 of 80

Data Mining

  • It is the process of discovering or mining knowledge from a large amount of data
  • KDD(Knowledge Discovery from data)
  • Attempts to extract hidden patterns and trends from large databases
  • Also support automatic exploration of data

​

​

​

​

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

11 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

12 of 80

Needs of Data Mining

  • Needs comes evolution in size of database

​

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

13 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

14 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

15 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

16 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

17 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

18 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

19 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

20 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

21 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

22 of 80

  • Data Warehousing involves data cleaning, data integration and data consolidations

Integrating Heterogeneous Databases

To integrate heterogeneous databases, we have two approaches −

​

  • Query-driven Approach

​

  • Update-driven Approach

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

23 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

24 of 80

Query-Driven Approach

This is the traditional approach to integrate heterogeneous databases. This approach was used to build wrappers and integrators on top of multiple heterogeneous databases. These integrators are also known as mediators.

​

Process of Query-Driven Approach

    • When a query is issued to a client side, a metadata dictionary translates the query into an appropriate form for individual heterogeneous sites involved.
    • Now these queries are mapped and sent to the local query processor.
    • The results from heterogeneous sites are integrated into a global answer set.

​

Disadvantages

    • Query-driven approach needs complex integration and filtering processes.
    • This approach is very inefficient.
    • It is very expensive for frequent queries.
    • This approach is also very expensive for queries that require aggregations.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

25 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

26 of 80

Update Driven Approach

In Update driven approach the information from multiple heterogeneous sources is integrated in advance and stored in a warehouse. This information is available for direct querying and analysis.

​

  • “Data warehouse” makes use of update driven approach

​

​

ADVANTAGES

This approach has the following advantages: -

  • This approach provide high performance.

​

  • The data are copied, processed, integrated, annotated, summarized and restructured in semantic data store in advance.

​

  • Query processing does not require interface with the processing at local sources

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

27 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

28 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

29 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

30 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

31 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

32 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

33 of 80

​

ROLAP

MOLAP

1.

ROLAP stands for Relational Online Analytical Processing.

While MOLAP stands for Multidimensional Online Analytical Processing.

2.

ROLAP is used for large data volumes.

While it is used for limited data volumes.

3.

The access of ROLAP is slow.

While the access of MOLAP is fast.

4.

In ROLAP, Data is stored in relation tables.

While in MOLAP, Data is stored in multidimensional array.

5.

In ROLAP, Data is fetched from data-warehouse.

While in MOLAP, Data is fetched from MDDBs database.

6.

In ROLAP, Complicated sql queries are used.

While in MOLAP, Sparse matrix is used.

7.

In ROLAP, Static multidimensional view of data is created.

While in MOLAP, Dynamic multidimensional view of data is created.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

34 of 80

34

4/19/2022

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

35 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

36 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

37 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

38 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

39 of 80

Data Cube: A multidimensional data model What is

39

Data Warehousing and On-line Analytical Processing

• Facts?

  • Data Cube?
  • Dimensions?
  • Dimension Table?
  • Fact Table?

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

40 of 80

Example

40

May 18, 2020

Data Mining: Concepts and Techniques

  • AllElectronics - create a sales data warehouse -to keep records of the store’s sales with respect to the dimensions time, item, branch, and location
  • These dimensions allow the store to keep track of things like monthly sales of items and the branches and locations at which the items were sold.
  • Each dimension may have a table associated with it, called a dimension table.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

41 of 80

Data Warehousing and On-line Analytical Processing

41

  • Data warehouses and OLAP tools are based on a multidimensional data model.
  • Multidimensional data model views data in the form of a data cube
  • Data cube- A data cube allows data to be modeled and viewed in multiple dimensions. It is defined by dimensions and facts.
  • Dimensions are the perspectives or entities with respect to which an organization wants to keep records.

May 18, 2020

Data Mining: Concepts and Techniques

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

42 of 80

Example

42

May 18, 2020

Data Mining: Concepts and Techniques

  • A dimension table for item may contain the attributes item name, brand, and type.
  • A multidimensional data model is typically organized around a central theme, such as sales. This theme is represented by a fact table.
  • Facts are numeric measures. They are quantities by which we want to analyze relationships between dimensions.
  • Examples of facts for a sales data warehouse include dollars sold (sales amount in dollars), units sold (number of units sold), and amount budgeted.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

43 of 80

43

May 18, 2020

Data Mining: Concepts and Techniques

  • The fact table the names of the facts, or contains measures, as well as keys to each of the related dimension tables.
  • Cubes are usually 3-D geometric structures,but in data warehousing the data cube is n-dimensional.
  • Let’s look at a simple 2-D data cube that is, in fact, a table or spreadsheet for sales data from AllElectronics.
  • In particular, we will look at the AllElectronics sales data for items sold per quarter in the city of Vancouver.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

44 of 80

44

May 18, 2020

Data Mining: Concepts and Techniques

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

45 of 80

45

May 18, 2020

Data Mining: Concepts and Techniques

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

46 of 80

46

  • We can think of a 4-D cube as being a series of 3-D cubes.

May 18, 2020

Data Mining: Concepts and Techniques

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

47 of 80

From Tables and Spreadsheets to Data Cubes

47

  • In data warehousing literature, an n-D base cube is called a base cuboid. The top most 0-D cuboid, which holds the highest-level of summarization, is called the apex cuboid. The lattice of cuboids forms a data cube.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

48 of 80

Cube: A Lattice of Cuboids

48

time,item

time,item,location

time, item, location, supplier

all

time

item

location

supplier

time,supplier

time,location item,location

item,supplier

time,item,supplier

time,location,supplier

item,location,supplier

location,supplier

2-D cuboids

0-D (apex) cuboid

1-D cuboids

3-D cuboids

4-D (base) cuboid

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

49 of 80

Conceptual Modeling of Data Warehouses

49

  • The entity-relationship data model is commonly used in the design of relational databases, where a database schema consists of a set of entities and the relationships between them.
  • The most popular data model for a data warehouse is a multidimensional model, which can exist in the form of a star schema, a snowflake schema, or a fact constellation schema.

May 18, 2020

Data Mining: Concepts and Techniques

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

50 of 80

50

  • Star schema: The most common modeling paradigm is the star schema, in which the data warehouse contains
  • (1) a large central table (fact table) containing the bulk of the data, with no redundancy, and
  • (2) a set of smaller attendant tables (dimension tables), one for each dimension. The schema graph resembles a starburst, with the dimension tables displayed in a radial pattern around the central fact table

May 18, 2020

Data Mining: Concepts and Techniques

Star schema

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

51 of 80

51

4/19/2022

time

time_key day

day_of_the_week

month quarter year

branch

branch_key branch_name branch_type

time_key

item_key

branch_key

location_key

​

units_sold

dollars_sold

avg_sales

Measures

location_key street

city state_or_province country

item

item_key item_name brand

type supplier_type

location

Sales Fact Table

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

52 of 80

  • Snowflake schema: The snowflake schema is a variant of the star schema model, where some dimension tables are normalized, thereby further splitting the data into additional tables. The resulting schema graph forms a shape similar to a snowflake.

May 18, 2020

Data Mining: Concepts and Techniques

29

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

53 of 80

Example of Snowflake Schema

30

time_key day

day_of_the_week

month quarter year

time

location_key street city_key

location

Sales Fact Table

time_key

item_key

branch_key

location_key

units_sold

dollars_sold

avg_sales

Measures

item_key item_name brand

type

supplier_key

item

branch_key branch_name branch_type

branch

supplier

​

​

​

supplier_key

supplier_type

city_key city

state_or_province country

city

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

54 of 80

54

  • The major difference between the snowflake and star schema models is that the dimension tables of the snowflake model may be kept in normalized form to reduce redundancies.
  • Such a table is easy to maintain and saves storage space.
  • However, this space savings is negligible in comparison to the typical magnitude of the fact table.
  • Furthermore, the snowflake structure can reduce the effectiveness of browsing, since more joins will be needed to execute a query. Consequently, the system performance may be adversely impacted.
  • Hence, although the snowflake schema reduces redundancy, it is not as popular as the star schema in data warehouse design.

Data Mining: Concepts and Techniques

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

55 of 80

55

  • Fact constellation: Sophisticated applications may require multiple fact tables to share dimension tables. This kind of schema can be viewed as a collection of stars, and hence is called a galaxy schema or a fact constellation.

​

  • Fact constellations: Multiple fact tables share dimension tables, viewed as a collection of stars, therefore called galaxy schema or fact constellation

May 18, 2020

Data Mining: Concepts and Techniques

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

56 of 80

Example of Fact Constellation

3

time_key day

day_of_the_week month

quarter

year

time

location_key street

city province_or_state country

location

Sales Fact Table

​

time_key

​

item_key

branch_key

​

location_key

​

​

units_sold

dollars_sold

​

avg_sales

​

Measures

item_key item_name brand

type supplier_type

item

branch_key branch_name branch_type

branch

Shipping Fact Table

time_key

item_key

shipper_key

from_location

to_location

dollars_cost

units_shipped

shipper_key shipper_name location_key shipper_type 3

shipper

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

57 of 80

May 18, 2020

Data Mining: Concepts and Techniques

34

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

58 of 80

Dimensions: The role of concept hierarchies

What is Concept Hierarchy? A concept hierarchy defines a sequence of mappings from a set of low-level concepts to higher-level, more general concepts.

59 of 80

Total Order : Street<city<province_or_state<country

Partial Order: day<{month<quarteer;week}<year

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

60 of 80

Set Grouping Hierarchy

61 of 80

A concept hierarchy that is a total or partial order among attributes in a database schema is called a schema hierarchy

May 18, 2020

Data Mining: Concepts and Techniques

38

Concept hierarchies may also be defined by discretizing or grouping values for a given dimension or attribute, resulting in a set-grouping hierarchy

There may be more than one concept hierarchy for a given attribute or dimension, based on different user viewpoints. For instance, a user may prefer to organize price by defining ranges for inexpensive, moderately priced, and expensive.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

62 of 80

Multidimensional Data

62

Product

  • Sales volume as a function of product, month, and region

Dimensions: Product, Location, Time

Hierarchical summarization paths

​

Industry Region Year

Category Country Quarter Product City Month Week

​

Office Day

​

​

Month

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

63 of 80

A Sample Data Cube

63

Total annual sales

of TVs in U.S.A.

Country

sum

TV

PC VCR

sum

1Qtr

2Qtr

Date

3Qtr

4Qtr

U.S.A

Canada

Mexico

​

sum

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

64 of 80

Cuboids Corresponding to the Cube

64

all

product

date

country

product,date

product,country

date, country

product, date, country

0-D (apex) cuboid

1-D cuboids

2-D cuboids

3-D (base) cuboid

65 of 80

Measures: Their Categorization and Computation

What is measure? Categories are:

  • Distributive
  • Algebraic
  • Holistic

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

66 of 80

What is Measure?

May 18, 2020

Data Mining: Concepts and Techniques

43

  • A multidimensional point in the data cube space can be defined by a set of dimension–value pairs; for example, <time = “Q1”, location = “Vancouver”, item = “computer”>
  • A data cube measure is a numeric function that can be evaluated at each point in the data cube space.
  • A measure value is computed for a given point by aggregating the data corresponding to the respective dimension–value pairs defining the given point.
  • Measures can be organized into three categories—distributive, algebraic, and holistic—based on the kind of aggregate functions used.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

67 of 80

Data Cube Measures: Three Categories

44

Distributive: An aggregate function is distributive if it can be computed in a distributed manner as follows:

  • Suppose the data are partitioned into n sets.
  • We apply the function to each partition, resulting in n aggregate values.
  • If the result derived by applying the function to the n aggregate values is the same as that derived by applying the function to the entire data set (without partitioning), the function can be computed in a distributed manner.
  • For example, sum() can be computed for a data cube by first partitioning the cube into a set of subcubes, computing sum() for each subcube, and then summing up the counts obtained for each subcube. Hence, sum() is a

distributive aggregate function.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

68 of 80

  • count(), min(), and max() are distributive aggregate functions.

​

  • A measure is distributive if it is obtained by applying a distributive aggregate function. Distributive measures can be computed efficiently because of the way the computation can be partitioned.

May 18, 2020

Data Mining: Concepts and Techniques

45

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

69 of 80

Data Cube Measures: Three Categories

69

Algebraic: An aggregate function is algebraic if it can be computed by an algebraic function with M arguments (where M is a bounded positive integer), each of which is obtained by applying a distributive aggregate function.

  • For example, avg() (average) can be computed by sum()/count(), where both sum() and count() are distributive aggregate functions.
  • Similarly, it can be shown that min N() and max N() (which find the N minimum and N maximum values, respectively, in a given set) and standard deviation() are algebraic aggregate functions.
  • A measure is algebraic if it is obtained by applying an algebraic aggregate function.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

70 of 80

Data Cube Measures: Three Categories

70

Holistic: An aggregate function is holistic if there is no constant bound on the storage size needed to describe a subaggregate.

  • That is, there does not exist an algebraic function with M arguments (where M is a constant) that characterizes the computation.
  • Common examples of holistic functions include median(), mode(), and rank().
  • A measure is holistic if it is obtained by applying a holistic aggregate function.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

71 of 80

Typical OLAP Operations

“How are concept hierarchies useful in OLAP?”

In the multidimensional model, data are organized into multiple dimensions, and each dimension contains multiple levels of abstraction defined by concept hierarchies.

  • This organization provides users with the flexibility to view data from different perspectives.
  • A number of OLAP data cube operations exist to materialize these different views, allowing interactive querying and analysis of the data at hand.
  • Hence, OLAP provides a user-friendly environment for interactive data analysis.

48

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

72 of 80

Typical OLAP Operations

72

  • Roll up (drill-up):The roll-up operation (also called the drill-up operation by some vendors) performs aggregation on a data cube, either by climbing up a concept hierarchy for a dimension or by dimension reduction.
  • Figure shows the result of a roll-up operation performed on the central cube by climbing up the concept hierarchy for location given in .

​

  • This hierarchy was defined as the total order “street < city < province or state < country.”

​

  • The roll-up operation shown aggregates the data by ascending the location hierarchy from the level of city to the level of country. In other words, rather than grouping the data by city, the resulting cube groups the data by country.

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

73 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

74 of 80

Typical OLAP Operations

74

  • Drill down (roll down): reverse of roll-up
    • It navigates from less detailed data to more detailed data.

​

    • Drill-down can be realized by either stepping down a concept hierarchy for a dimension or introducing additional dimensions.

​

    • Figure shows the result of a drill-down operation performed on the central cube by stepping down a concept hierarchy for time defined as “day<month< quarter < year.”

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

75 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

76 of 80

Typical OLAP Operations

76

  • Slice and dice: project and select
    • The slice operation performs a selection on one dimension of the given cube, resulting in a subcube. Figure shows a slice operation where the sales data are selected from the central cube for the dimension time using the criterion time = “Q1.”

​

    • The dice operation defines a subcube by performing a selection on two or more dimensions. Figure shows a dice operation on the central cube based on the following selection criteria that involve three dimensions: (location

= “Toronto” or “Vancouver”) and (time = “Q1” or “Q2”) and (item = “home entertainment” or “computer”).

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

77 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

78 of 80

Typical OLAP Operations

78

  • Pivot (rotate):
    • reorient the cube, visualization, 3D to series of 2D planes
    • provides an alternative data presentation.
    • Figure shows a pivot operation where the item and location axes in a 2-D slice are rotated. Other examples include rotating the axes in a 3-D cube, or transforming a 3-D cube into a series of 2-D planes.
  • Other operations
    • drill across: involving (across) more than one fact table
    • drill through: through the bottom level of the cube to its

back-end relational tables (using SQL)

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

/skit.org.in

79 of 80

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in

80 of 80

80

Fig. 3.10 Typical OLAP Operations

Sri Krishna Institute of Technology

(Approved by AICTE, Accredited by NAAC, Affiliated to VTU, Karnataka)

/skit.org.in