Data Warehousing: Engr. Madeha Mushtaq Department of Computer Science Iqra National University

DATA WAREHOUSING
LECTURE 6
ENGR. MADEHA MUSHTAQ

DEPARTMENT OF COMPUTER SCIENCE
IQRA NATIONAL UNIVERSITY
ER MODELING
• We are familiar with data modeling for operational or OLTP systems.

• We adopt the Entity-Relationship (E-R) modeling technique to create the data
models for these systems.
• Figure below lists the characteristics of OLTP systems and shows why E-R
modeling is suitable for OLTP systems.
ER MODELING
• Entity relationship diagrams provide a visual starting point for database

design.
• The diagrams are used to design or analyze relational databases used in
business processes.
• Coupled with normalization drives out all the redundancy out of the
database.
• Change (add or delete) the data at just one point.
• Resulted in success of OLTP systems.
NEED FOR DIMENSIONAL MODELING
• Lets have a look at a typical ER data model first.
• Some Observations:
• All tables look-alike, as a consequence it is difficult to identify:
• Which table is more important ?
• Which is the largest?
• Which tables contain numerical measurements of the business?
• Which table contain nearly static descriptive attributes?
• Many topologies for the same ER diagram, all appearing different.

• Very hard to visualize and remember.
• A large number of possible connections to any two (or more) tables.
12
7 6
3 12 7
11 4 8
8
9
1
10 10 9 11
6 1
3 2 5
2 5 4
• ER and Normalization result in large number of tables which are:
• Hard to understand by the users (DB programmers)
• Hard to navigate optimally by DBMS software
• Real value of ER is in using tables individually or in pairs.

• Too complex for queries that span multiple tables with a large number of
records.
ER VS. DM
ER DM
Constituted to optimize DSS query
Constituted to optimize OLTP performance.
performance.
Models the macro relationships among

Models the micro relationships among data
data elements with an overall
elements.
deterministic strategy.
A wild variability of the structure of ER All dimensions serve as equal entry points
models. to the fact table.
Very vulnerable to changes in the user's Changes in users' querying habits can be
querying habits, because such schemas are accommodated by automatic SQL
asymmetrical. generators.
7
DIMENSIONAL MODELING
• A simpler logical model optimized for decision support.

• Inherently dimensional in nature, with a single central fact table and a set of
smaller dimensional tables.
• Multi-part key for the fact table.
• Dimensional tables with a single-part PK.
• Keys are usually system generated.
DIMENSIONAL NATURE OF BUSINESS DATA
• To get a good grasp of the dimensional nature of business data, let us look
at an example:
• Figure below shows the analysis of sales units along the three business
dimensions of product, time, and geography.
DIMENSIONAL NATURE OF BUSINESS DATA
• These three dimensions are plotted against three axes of coordinates.

• In this case, the business data of sales units is three dimensional
because there are just three dimensions used in this analysis.
• If there are more than three dimensions, we extend the concept to
multiple dimensions and visualize multidimensional cubes, also called
hyper cubes.
EXAMPLES OF BUSINESS DIMENSIONS
Examples of business dimensions

• For the supermarket chain, the measurements that are analyzed are the
sales units.
• These are analyzed along four business dimensions.
• For the insurance company, we would want to analyze the claims data by
agent, individual claim, time, insured party, individual policy, and status of
the claim.
• For the example of the airlines company the business dimensions are time,
customer, specific flight, fare class, airport, and frequent flyer status.
• The example analyzing shipments for a manufacturing company show some

other business dimensions.
• Here we see the dimensions of time, ship-to and ship-from locations,
shipping mode, product, and any special deals.
• What we find from these examples is that the business dimensions are
different and relevant to the industry and to the subject for analysis.
• We also find the time dimension to be a common dimension in all examples.
• Almost all business analyses are performed over time.
PROCESS OF DIMENSIONAL MODELING
• Five Step process of DM:
• Choosing the Business Process

• Choosing the Grain
• Choosing the Facts
• Choosing the Dimensions
• Choosing the duration of the database
• Choose the Business Process:

• A business process is a major operational process in an organization.
• Selecting the subjects from the information packages for the first set of
logical structures to be designed.
• Typically supported by a legacy system (database) or an OLTP.
• Examples: Orders, Invoices, Inventory etc.
• Choosing the Grain:

• Determining the level of detail for the data in the data structures.
• Grain is the fundamental, atomic level of data to be represented.
• Grain is also termed as the unit of analyses.
• Example grain statements:
• Typical grains
• Individual Transactions
• Daily aggregates
• Monthly aggregates
• Choosing the Facts:

• Selecting the metrics or units of measurements (such as product sale
units, dollar sales, dollar revenue, etc.) to be included in the first set of
structures.
• Choose the facts that will populate each fact table record.
• Remember that best Facts are Numeric, Continuously Valued and
Additive.
• Examples: Quantity Sold, Amount etc.
Facts
“We need monthly sales

volume and Rs. by
week, product and Zone”
Dimensions
Choosing fact statement

• Choosing the Dimensions:

• Choosing the business dimensions (such as product, market, time, etc.) to
be included in the first set of structures.
• Choose the dimensions that apply to each fact in the fact table.
• Typical dimensions: time, product, geography etc.
• Identify the descriptive attributes that explain each dimension.
• Determine hierarchies within each dimension.
• The single valued attributes during recording of a transaction are dimensions.
Fact Table
Calendar_Date
Time_of_Day
Dim Account _No
ATM_Location
Transaction_Rs
Transaction_Type
Time_of_day: Morning, Mid Morning, Lunch Break etc.
Transaction_Type: Withdrawal, Deposit, Check balance etc.

How to Identify a Dimension?
• Are dimensions ALWAYS single?

• Not really
• What are the problems? And how to handle them
 Calendar_Date (of inspection)

 Reg_No
 Technician
 Workshop
 Maintenance_Operation
Multivalued Dimensions
• Choosing the duration of the database:

• Determining how far back in time you should go for historical data.
• Let us look at an example for analyzing sales for a certain business as shown in
figure:
• The subject here is sales.

• The measured facts or the measurements that are of interest for analysis are
shown in the bottom section of the figure.
• The business dimensions along which these measurements are to be analyzed
are shown at the top of diagram as column headings.
• In our example, these dimensions are time, location, product, and
demographic age group.
• Each of these business dimensions can contain a hierarchy or levels.

• For example, the time dimension has the hierarchy going from year down to
the level of individual day.
• The other intermediary levels in the time dimension could be quarter,
month, and week.
• Let us now examine the business dimensions for an automobile

manufacturer.
• Let us say that the goal is to analyze sales.
• We want to build a data warehouse that will allow the user to analyze
automobile sales in a number of ways.
• In the automaker sales figure, we can see the facts at the bottom.
• All these facts will form the fact table as shown in the figure.
• The fact table gets its name from the subject for analysis; in this case, it is
automaker sales.
• Each fact item or measurement goes into the fact table as an attribute for
automaker sales.
Formation of the automaker sales fact table

• Next step is the formation of dimension tables.

• The business dimensions are shown as column heading.
• In the automaker sales example, the business dimensions are product, dealer,
customer demographics, payment method, and time.
• We can form dimension tables for each of these dimensions.
• The data items shown within each column would then be the attributes for
each corresponding dimension table.
Formation of the automaker dimension tables

• Let us take one more example. In this case, we want to come up with an
information package for a hotel chain.
• The subject in this case is hotel occupancy.
• We want to analyze occupancy of the rooms in the various branches of the
hotel chain.
• We want to analyze the occupancy by individual hotels and by room types.
• So hotel and room type are critical business dimensions for the analysis.
• As in the other case, we also need to include the time dimension.
• Here are some of the criteria for combining the tables into a dimensional
model:
• The model should provide the best data access.
• The whole model must be query-centric.
• It must be optimized for queries and analyses.
• The model must show that the dimension tables interact with the fact table.
• It should also be structured in such a way that every dimension can
interact equally with the fact table.
END OF SLIDES

Data Warehousing: Engr. Madeha Mushtaq Department of Computer Science Iqra National University

Uploaded by

Copyright:

Available Formats

Data Warehousing: Engr. Madeha Mushtaq Department of Computer Science Iqra National University

Uploaded by

Document Information

Original Description:

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Data Warehousing: Engr. Madeha Mushtaq Department of Computer Science Iqra National University

Uploaded by

Copyright:

Available Formats

DATA WAREHOUSING

ENGR. MADEHA MUSHTAQ

• We are familiar with data modeling for operational or OLTP systems.

• Entity relationship diagrams provide a visual starting point for database

• Lets have a look at a typical ER data model first.

• Many topologies for the same ER diagram, all appearing different.

• ER and Normalization result in large number of tables which are:

• Hard to understand by the users (DB programmers)

• Hard to navigate optimally by DBMS software

• Real value of ER is in using tables individually or in pairs.

Models the macro relationships among

• A simpler logical model optimized for decision support.

• These three dimensions are plotted against three axes of coordinates.

Examples of business dimensions

• The example analyzing shipments for a manufacturing company show some

• Five Step process of DM:

• Choosing the Business Process

• Choose the Business Process:

• Choosing the Grain:

• Choosing the Facts:

“We need monthly sales

Choosing fact statement

• Choosing the Dimensions:

Time_of_day: Morning, Mid Morning, Lunch Break etc.

Transaction_Type: Withdrawal, Deposit, Check balance etc.

• Are dimensions ALWAYS single?

 Calendar_Date (of inspection)

• Choosing the duration of the database:

• The subject here is sales.

• Each of these business dimensions can contain a hierarchy or levels.

• Let us now examine the business dimensions for an automobile

Formation of the automaker sales fact table

• Next step is the formation of dimension tables.

Formation of the automaker dimension tables

You might also like