Data Warehouses

1
????? ??????

• ????? 3 ????? ??????????

2
?????? ?????? ??????? ????? ???? ??????

• 1. ???? ?????? ???????? ???????.
• 2. ????? ?????? (Conceptual/Semantic Design) ??
  ???? ???????.
• ???? ?? ????? ?? ???? ??????.
• ???? ??? ?? ????? ?? ??????? ??????? ?????? ?????
  ?????? ?? ??? ???? ???????.
• ????? ??? ???? ?????? ??????? ?????? DBMS ??????,
  ????? ????? ?? ?? ????? ???????? ???.

3
?????? ?????? ??????? ????? ???? ??????

•  3. ????? ???? (Logical Design) ?? ???? ???????.
• ????? ????? ??????? ????? ??.
• ????? ????? ????? ?????? DBMS ??????.
• ????? ?????? ???? ?? ???? ????? ???? ???????
  Database Schema.
• 4. ???' ...
•  

4
?????? ?????? ???? ??????

• Conceptual Design Looks at the requirements
  what needs to be build to answer business
  requirements.
• Logical Design.
• Structural Design - How do the parts fits in the
  Data Warehouse architecture.
• Physical Design Optimized the design to the
  implementing environment.

5
?? ???????
Produce Data Model for each component in the data
warehouse environment Data Warehouse Data
Marts Staging areas.
6
Conceptual Design with Entity-Relation modeling

• Entity-relation modeling is often used to create
  a single complex model of all of the
  organization's processes.
• This approach has proven effective in creating
  efficient online transaction processing (OLTP)
  systems
• The diagram is very symmetric. Users cant
  navigate ER diagram.

7
ER Data Model Ingredients

• Entities
• Attributes
• Relationships
• One-to-one
• One-to-many
• Many-to-many

8
?????
9
Dimensional Data Modeling

• DDM is a conceptual design technique that seeks
  to present the data in a standard framework that
  often correspond to the way analysts want to
  query data warehouse and allows query performance.

10
Dimensional Data Model Ingredients
A dimension is a logical grouping of attributes
arranged according to business area
Examples Product, Location, and Time
Facts (business metrics) - points of dimensional
intersection
Example product sales in a particular location
during a given period of time
11
The Cube Example 1
12
DDM Example 1
Color
Sales
Year
Made
13
The Cube Example 2
14
DDM Example 2
location
Sales
Product
Sales Channel
15
Measures type

• Additive SUM, AVG, COUNT.
• Example Sales
• Semi-additive AVG, COUNT
• Example Temperature.
• Non-additive COUNT
• Example Description.

16
Mixed type

• Some measures are not additive along one or more
  dimensions, but additive on other.
• It is common that measures can be added along
  dimensions other than the time dimension.
• For example, Quantity-on-Hand can be added along
  the Warehouse dimension to achieve a meaningful
  total of the quantity of items on hand in all
  warehouses at a specific point in time. Along the
  time dimension, however, an aggregate function,
  such as Average, must be applied to provide
  meaningful information about the quantity of
  items on hand.

17
Calculated Measures
A calculated measure is a measure that results
from applying a function to one or more measures,
for example, the computed Total Sum value
resulting from multiplying Quantity times Price.
Calculated measures may be precomputed during
the load process and stored in the data
warehouse, or they may be computed on the fly as
they are used. Determination of which measures
should be precomputed is a design consideration
(the usual tradeoff between storage space and
computational time.)
18
Hierarchies in Dimension
19
Multiple Hierarchies

• A typical dimension contains one or more natural
  hierarchies and non-hierarchical attributes.

Category
Division
Subcategory
Department
Product
20
Ragged Hierarchies

• In a ragged hierarchy, branches of the hierarchy
  can descend to different depths. Another way to
  think about it is to say that a ragged hierarchy
  has empty positions in the member hierarchy.
• Lets consider the Vendor Location

21
ParentChild Dimensions

• In Parent-Child Dimensions the hierarchies
  structure is unbalanced and cant be predefined.

22
Date/Time Dimension
Probably will be in all data warehouse.
23
????? 1 ?????? ??????? ?????? ?? ????
24
????? 1

• ????? ???? ????? ???? ?????? ??? ????? ???? ??
  ??????? ???????? ????? ??????? ????, ??????
  ?????.
• ?????? ??????? ????? ?????? ??? ?????? ??????
  ???"? ?????? ????? ?? ?????? ?????? ???? ???? ???
  ?????? ??????????? ????? ?????.

25
????? 2 ?????? ??????? ?????? ?? ????
26
????? 2

• ????? ???? ???? ???? ?????? ?????? ???? ?? ????
  ??????? ???????? ????? ??????? ?????, ??????,
  ???? ?????? ??????.

27
????? 3
28
Private Shared Dimensions

• Dimension can be created for use in an individual
  cube or multiple cubes.
• A private dimension is a dimension created for
  an individual cube.
• A shared dimension is a dimension that can be
  used by multiple cubes.

29
?????? ??????? ??? ?????
30
???? ???? ???? ????? ???????????
2model.doc
31
??? ?? ????

• ???? ???? ??????? Northwind ??????
• ????? ???? ???? ???? ?????? ?????? ???? ?? ????
  ??????? ????????.

32
?? ???? ??? ???? ????????

• ????? 1 ?? 5.