microsoft powerpoint - dw_bl_sept2013_07(1) [compatibility mode]

8/11/2019 Microsoft PowerPoint - DW_BL_Sept2013_07(1) [Compatibility Mode]

1/41

Course: 0604M- Testing & ImplementationCourse: Datawarehouse

An Overview of Testing and ImplementatiOLAP


2/41

OL A P (OLAP)

2

OLAP O

2D

Datawarehouse_09/2013


3/41

MD D M

10 15

Prod Code Time Code Store Code Sales Qty

Store InfoNumerical MeasuresKey columns joining fact tableto dimension tables

Product Info

Time Info

. . .

Fact table formeasures

Dimension tables


4/41

OLAP & D

Data

Warehouse

Time

Product

Category e.g Electrical Appliance

Sub Category e.g Kitchen

Product e.g Toaster

Category e.g Electrical Appliance

Sub Category e.g Kitchen

Product e.g Toaster

Drill downRoll up

4Datawarehouse_09/2013


5/41

OLAP &

DataWarehouse

Time

Product

Product=ToasterProduct

PivotSlicing

Time Region



6/41

A OLAP

OLAP

(1) OLAP (OLAP) DBM

OLAP

(2) M OLAP (MOLAP)

A

D

(3) H OLAP (HOLAP) DBM

MDBM

MOLAP



7/41

P OLAP

D ,

A .

C .

OLAP , ,

.B!!!

QL .



8/41

OLAP: D M

DBM

: ,

: ,

P ( , DBM),

MDDB

P

IBM DB2, O, IQ, B, I


9/41

OLAP

sale prodId storeId date amt

p1 s1 1 12

p2 s1 1 11

p1 s3 1 50

p2 s2 1 8

p1 s1 2 44

p1 s2 2 4

Fact table view:

81


Select sum(amt)From saleWhere date=1

Select date, sum(amt)

From saleGroup by date

date amt1 81

2 48


10/41

MOLAP: D M

M D M

MDDB:

F

D DB

P

P, A E, G



11/41

MOLAP C : 2

sale prodId storeId amt

p1 s1 12p2 s1 11

1 s3 50

s1 s2 s3p1 12 50p2 11 8

Fact table view:

Multi-dimensional cube:

p2 s2 8


Select prodId, storeId,sum(amt)

From saleGroup by prodId,storeId

prodId storeId amt

p1 s1 12

p1 s3 50p2 s1 11

p2 s2 8


12/41

3D C : 3

Multi-dimensional cube:Fact table view:

sale prodId storeId date amtp1 s1 1 12p2 s1 1 11p1 s3 1 50p2 s2 1 8

1 s1 2 44

day 2 s1 s2 s3p1 44 4p2 s1 s2 s3day 1

p1 s2 2 4 p2 11 8


Select date, prodId, storeId,sum(amt)From saleGroup by date, prodId,storeId

date prodId storeId amt

1 p1 s1 12

1 p1 s3 50

1 p2 s1 11

1 p2 s2 8

2 p1 s1 44

2 p1 s2 4


13/41



14/41

Data cubeData cube

A two-dimensional,

three-dimensional, orhigher-dimensionalob ect in which each

dimension of the datarepresents a measureof interest

- Grain

- Drill-down- Slicing



15/41



16/41

(

Stores : New York

Hats Coats Jackets Total

Product : Hats

Jan Feb Mar Total

Jan 200 550 350 1100

Feb 210 480 390 1080

Mar 190 480 380 1050

Total 600 1510 1120 3230

New York 200 210 190 600Boston 20 175 125 320

San Jose 110 210 125 445

Total 330 595 440 1365

Months January

New York Boston San Jose Total

Hats 200 20 110 330

Coats 550 435 275 1260

Jackets 350 220 125 695

Total 1100 675 510 2285 16Datawarehouse_09/2013


17/41

E

ct

Juice

Milk

NY

SF

LA

10

34

Dimensions:

Time, Product, StoreAttributes:

Product (upc, price, )

Store

roll-up to brand

roll-up to region

Pro

d

Time

M T W Th F S S

CokeCream

Soap

Bread

5632

12

56

56 units of bread sold in LA on M

Hierarchies:

Product Brand

DayWeek Quarter

Store Region Countryroll-up to week



18/41

C A:

day 2 s1 s2 s3p1 44 4p2 s1 s2 s3

p1 12 50p2 11 8

day 1

. . .

Example: computing sums

s1 s2 s3

p1 56 4 50p2 11 8

s1 s2 s3sum 67 12 50

sum

p1 110

p2 19

129

drill-down

rollup



19/41

C O

day 2 s1 s2 s3p1 44 4p2 s1 s2 s3

p1 12 50p2 11 8

day 1

. . .

sale(s1,*,*)

s1 s2 s3

p1 56 4 50p2 11 8

s1 s2 s3sum 67 12 50

sum

p1 110

p2 19

129

sale(*,*,*)sale(s2,p2,*)



20/41

s1 s2 s3 *p1 56 4 50 110p2 11 8 19* 67 12 50 129

E C

day 2 s1 s2 s3 *

*

p2* 44 4 48s1 s2 s3 *

p1 12 50 62p2 11 8 19* 23 8 50 81

day 1 sale(*,p2,*)



21/41

A H

storeday 2 s1 s2 s3p1 44 4p2 s1 s2 s3

p1 12 50day 1

region A region B

p1 56 54p2 11 8

country

(store s1 in Region A;stores s2, s3 in Region B)

p



22/41

P MOLAP P

.

BF , MDD . A 200MB 5GB

MDD 50GB .

D .

MDD, , DBM .



23/41

H OLAP (HOLAP)

HOLAP = H OLAP:

B

DBM

MDBM

MOLAP



24/41

Multi-dimensionalaccess

Multidimensional

ClientMDBMS Server

SQL-Read

RDBMS Server

D F HOLAP

Viewer

Relational

Viewer

Multi-

dimensionaldata

data Meta data

Deriveddata

SQL-ReachThrough

SQL-Read



25/41

, :1) P:

H ?

MDD .

2) D :

MDD 50GB , DBM

.



26/41

An experiment with Relational and theMultidimensional models on a data set

The analysis of the authors example illustrates the following differences betweenthe best Relational alternative and the Multidimensional approach.

relationalrelational MultiMulti--

dimensionaldimensional

ImprovementImprovement

Disk space requirementDisk space requirement

(Gigabytes)(Gigabytes)

1717 1010 1.71.7

* This may include the calculation of many other derived data without anyadditional I/O.

Reference: ://../599/F2002//I2P064.

Retrieve the corporate measuresRetrieve the corporate measuresActual Vs Budget, by month (I/Os)Actual Vs Budget, by month (I/Os)

240240 11 240240

Calculation of VarianceCalculation of VarianceBudget/Actual for the wholeBudget/Actual for the whole

database (I/O time in hours)database (I/O time in hours)

237237 2*2* 110*110*


27/41

IF A.

B. 50 GBC. 6090

D. L

E. D

F.

HEN C

IF A. 100 GB

" ".

C. H D. D ,

E. D

HEN C .

IF A. OLAP B. D

C. E

HEN C



28/41

E

OLAP : (CD)

EC

C C C

A

HOLAP

B F P



29/41

OLAP: OACLE 8

OACLE ; OACLE D

OACLE B

A E

MOLAP: OACLE E

M D P P IB

HOLAP:

OACLE 8 OACLE E

OACLE A M

OACLE E C (C/ )



30/41

C

OLAP: DBM > /

MOLAP: MDD > C

OLAP MOLAP: D

MOLAP: (1050GB)

OLAP:

B

, DBM/OLAP.



31/41


32/41

D M D

D

E

32

z Data Mining providesthe Enterprise withintelligence



33/41

...

G 100,000 , ?

?

I I . 2, OI?

33

I I 2,500 5,000, ?

I I , ?

?

Data Mining helps extract such informationDatawarehouse_09/2013


34/41


35/41

D M

G D M

A B

35

C

H G C

B C



36/41

?

A

:

(.., , , )

36

. G G



37/41

D ?

O & & .

C OLAP . .

, & & .

z Function

37

y Missing data: Decision support requires historical data, whichop dbs do not typically maintain.

y Data consolidation: Decision support requires consolidation(aggregation, summarization) of data from manyheterogeneous sources: op dbs, external sources.

y Data quality: Different sources typically use inconsistent datarepresentations, codes, and formats which have to bereconciled.



38/41

*M C

F

O M C

*M: 2000+

38

AM' C 100+

F C (NC )

D



39/41

O P

*M

I M M A

P

A O P P

P

39

P P:

N, , L

I

M

I

B C

E

D



40/41

N (JI) P

N

P (PO A)

O P

ACAL

*M M

40

D I

( D) *M

P

L I

D

D C

*M



41/41

*M

NC 5100M 96 N;

N :

H D:

24 00 1000

5

41

N D :

N :

N Q:

5

100 +

0,000


microsoft powerpoint - dw_bl_sept2013_07(1) [compatibility mode]

Documents