Question: Consider a data warehouse for a hospital where there are three dimensions:
0

a) Doctor b) Patient c) Time

Consider two measures

i) Count

ii) Charge where charge is the fee that the doctor charges a patient for a visit.

For the above example create a cube and illustrate the following OLAP operations.

1) Rollup 2 ) Drill down 3) Slice 4) Dice 5) Pivot.

Mumbai University > Computer Engineering > sem 8> data warehouse and mining

Marks: 10M

mumbai university • 217 views
 modified 7 weeks ago  • written 9 weeks ago by Ankit Pandey ★ 70
0

There are four tables, out of 3 dimension tables and 1 fact table.

Dimension tables:

1. Doctor (D I D , name, phone, location, pin, specialization)

2. Patient (PID, name, phone, state, city, location, pin)

3. Time ( TID, day, month, quarter, year)

Fact Table:

Fact, table (DID.PID, TID, count, charge)

1) Rollup: It gives summary based on concept hierarchies.Assuming there exists concept hierarchy in patient table as $State \rightarrow city \rightarrow location$.Then roll up will summarise the charges or count in terms of city or further roll up will give charges for a particular state etc.

2 ) Drill down: It is opposite to roll up that means if currently cube is summarised with respect to city then drill down will also show summarisation with respect to location.

3) Slice:

Slice: Slice on fact table with $\mathrm{DID}=2,$ this cuts the cube at $\mathrm{DD}=2$ along the time and patient axis thus it will display a slice of cube, in which time on $\mathrm{x}$ and patient on $\mathrm{y}$ axis.

4) Dice: It is a sub cube of main cube. Thus it cuts the cube with more than one predicate like dice on cube with $D I D=2,$ and $D D=01$ and $PID=01$ and $P I D=03$ and $T I D=02,03$

5) Pivot: It rotates the cube, sub cube or rolled -up or drilled -down cube, thus changing the view of the cube.