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

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.

1 Answer
2
1.5kviews

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)

enter image description here

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.

enter image description here

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.

enter image description here

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.

enter image description here

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$

enter image description here

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

Please log in to add an answer.