DWM 2nd chapter

QB solution

1] List Application of OLAP.

1. Sales reporting.
2. Marketing.
3. Management reporting.
4. Process management.
5. Budgeting.
6. Forecasting.

2] Explain need of OLAP.

1. Finance departments use OLAP for applications such as budgeting, activity- based
costing (allocations), financial performance analysis, and financial modeling.

2. Sales analysis and forecasting are two of the OLAP applications found in sales
departments.

3. Marketing departments use OLAP for market research analysis, sales forecasting,
promotions analysis, and customer analysis and market/customer segmentation.

4. Manufacturing OLAP applications include production planning and defect analysis.

3] Define the term 1)OLAP 2)ROLAP 3)MOLAP 4)HOLAP

1. OLAP can be defined as the process of converting raw data in to business

information through multi dimension analysis. For a data warehouse application a
well-designed metadata layer will provide a multi-dimensional view of data.

OLAP is a key technology for successful management that describes a class of

applications that require multi-dimensional analysis of business data.

2. Multi-dimensional OLAP (MOLAP) is based on a multi-dimensional data base

architecture.
This stores data in a three-dimensional data cube that is already in the OLAP
multi- dimensional format for „slicing and dicing‟ into analysis views.
Multi-dimensional architectures provide performance benefits where the data
retrieval paths follow the pre-defined structure of the data cubes.

3. Relational OLAP (ROLAP) is the fastest growing area of OLAP technology, with
new vendors entering the market at an accelerating pace.
Relational OLAP products are designed to operate directly on a data
warehouse built on relational databases, through a comprehensive metadata
layer.

Dhrupesh Sir 9699692059

 4. HOLAP (Hybrid Online Analytical Processing) is a combination of ROLAP
(Relational OLAP) and MOLAP (Multidimensional OLAP).
HOLAP allows storing part of the data in a MOLAP store and another part of the data
in a ROLAP store. HOLAP can use varying combinations of ROLAP and OLAP
technology.

4] List OLAP guidelines.

1. Multidimensional Conceptual View

2. Transparency
3. Accessibility
4. Consistent Reporting Performance
5. Client/Server Architecture
6. Generic Dimensionality
7. Dynamic Sparse Matrix Handling
8. Multi-user Support
9. Unrestricted Cross-dimensional Operations
10. Intuitive Data Manipulation
11. Flexible Reporting
12. Unlimited Dimensions and Aggregation Levels

5] Draw and explain star schema, snowflake schema and fact constellation schema
(galaxy schema).

1] Star Schema:
 Each dimension in a star schema is represented with only one-dimension
table.
The simplest data warehouse schema is star schema because its structure
resembles a star. Star schema consists of data in the form of facts and
dimensions.
 In the Star schema, the center of the star can have one fact tables and
numbers of associated dimension tables.
 The fact table present in the center of star and points of the star are the
dimension tables.
 In star schema fact table contain a large amount of data, with no redundancy.
Each dimension table is joined with the fact table using a primary or foreign
key.
 It is also known as Star Join Schema and is optimized for querying large data
sets.
 This dimension table contains the set of attributes.
 The following diagram shows the sales data of a company with respect to the
four dimensions, namely time, item, branch, and location.
 There is a fact table at the centre. It contains the keys to each of four

Dhrupesh Sir 9699692059

 dimensions.
 The fact table also contains the attributes, namely dollars sold and units sold

2] Snowflake Schema:
 The snowflake schema is a more complex than star schema because
dimension tables of the snowflake are normalized.
 The snowflake schema is represented by centralized fact table which is
connected to multiple dimension table and this dimension table can be
normalized into additional dimension tables.

 The major difference between the snowflake and star schema models is that
the dimension tables of the snowflake model are normalized to reduce
redundancies
 Some dimension tables in the Snowflake schema are normalized.
 The normalization splits up the data into additional tables.
 Unlike Star schema, the dimensions table in a snowflake schema are
normalized. For example, the item dimension table in snowflake schema is
normalized and split into two dimension tables, namely item and supplier
table.
 Now the item dimension table contains the attributes item_key, item_name,
type, brand, and supplier-key.
 The supplier key is linked to the supplier dimension table. The supplier
dimension table contains the attributes supplier_key and supplier_type.

Dhrupesh Sir 9699692059

3] Fact Constellation Schema
 A fact constellation has multiple fact tables. It is also known as galaxy schema.
 The following diagram shows two fact tables, namely sales and shipping.
 The sales fact table is same as that in the star schema.
 The shipping fact table has the five dimensions, namely item_key, time_key,
shipper_key, from_location, to_location.
 The shipping fact table also contains two measures, namely dollars sold and
units sold.
 It is also possible to share dimension tables between fact tables. For example,
time, item, and location dimension tables are shared between the sales and
shipping fact table.

Dhrupesh Sir 9699692059

6] Compare ROLAP and MOLAP.

7] List operations of OLAP.

1. Roll-up
2. Drill-down
3. Slice
4. Dice
5. Pivot (rotate).

8] Explain Drill-down, Roll-up, Slice , Dice and Pivot(Rotate) operations of OLAP.

1. Drill down: In drill-down operation, the less detailed data is converted into highly
detailed data. It can be done by:
 Moving down in the concept hierarchy
 Adding a new dimension
In the cube given in overview section, the drill down operation is performed by
moving down in the concept hierarchy of Time dimension (Quarter -> Month).

Dhrupesh Sir 9699692059

2. Roll up: It is just opposite of the drill-down operation. It performs aggregation
on the OLAP cube. It can be done by:
 Climbing up in the concept hierarchy
 Reducing the dimensions
In the cube given in the overview section, the roll-up operation is performed by
climbing up in the concept hierarchy of Location dimension (City -> Country).

3. Dice: It selects a sub-cube from the OLAP cube by selecting two or more
dimensions. In the cube given in the overview section, a sub-cube is selected by
selecting following dimensions with criteria:
 Location = “Delhi” or “Kolkata”
 Time = “Q1” or “Q2”
 Item = “Car” or “Bus”

Dhrupesh Sir 9699692059

 4. Slice: It selects a single dimension from the OLAP cube which results in a new
sub-cube creation. In the cube given in the overview section, Slice is performed
on the dimension Time = “Q1”.

5. Pivot: It is also known as rotation operation as it rotates the current view to get a
new view of the representation. In the sub-cube obtained after the slice
operation, performing pivot operation gives a new view of it.

Dhrupesh Sir 9699692059

9] Write features and benefits of OLAP.

Features of OLAP
The key features of OLAP are:
• Multi-dimensional views of data
• Calculation-intensive capabilities
• Time intelligence

Benefits of OLAP
 Successful OLAP applications increase the productivity of business managers,
developers, and whole organizations.
 IT developers also benefit from using the right OLAP software
 OLAP reduces the applications backlog still further by making business users
self-sufficient enough to build their own models.
 OLAP enables the organization as a whole to respond more quickly to market
demands. Market responsiveness, in turn, often yields improved revenue and
profitability.

10] Write advantages and disadvantages of star, snowflake and fact constellation
schema.

1] Star schema:
Advantages:
1. Offers highly optimized performance.
2. Applicable to both small-scale data sources like data marts and large-scale data
sources such as databases.
3. The star schema is simple to implement and maintain.
Disadvantages:
1. May offer less accuracy and consistency.
2. Data may be de-normalized.
3. May have issues with data redundancy.

2] Snowflake:

Advantages:
1. Offers reduced data redundancy.
2. Uses normalized data.
3. Provides more accurate and consistent results compared to the star schema.
Disadvantages:
1. Can be slower than the star schema due to the use of joins.
2. More complex than the star schema.
3. May require more complex queries due to the use of joins.

3] Fact Constellation:

Advantages:
1. Offers high flexibility.
2. No data redundancy.
3. Requires low memory/space.

Dhrupesh Sir 9699692059

Disadvantages:
1. May have a complicated design.
2. Creating, implementing, and maintaining a galaxy schema can be a challenging
task.
3. May require more complex queries due to the higher number of joins used to
connect fact and dimension tables.
4. Data analysis may be difficult due to the complex structure

Dhrupesh Sir 9699692059