We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
  1. Prepare
  2. Databases
  3. OLAP
  4. OLAP Operation Types
  5. Discussions

OLAP Operation Types

  • yasarerd2000
     + 1 comment

    Q1: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3

    Q2: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH CUBE

    Q3: Select D1,D2,D3,Sum(x) From DataPoints Group By D1,D2,D3 WITH ROLLUP

    My view : The below will be my notes as I solved. Hope it will help!!

    Q1 : Normal Group by which will produce a * b * c .

    In our case

    (2, 2, 2, 6, 18, 8) => a=2 b=2 c=2 => 2*2*2 =8 which is not satisfied with d in this case. We can ignore this (2, 2, 2, 8, 64, 15) => 8 satisfied with dth place (5, 10, 10, 500, 1000, 550) => a=5 b=10 c=10 => 5*10*10 =500 satisfied with dth place (4, 7, 3, 84, 160, 117) => 4*7*3=84 satisfied with d th place.

    Q2 : group by with CUBE

    Explanation: Because of WITH CUBE, we need to consider all 3 dimensions as well and every possible cases.

    so the result would be a*b*c +((a*b) +(b*c) +(c*a)) + (a + b + c) + 1

    remaining options left , (2, 2, 2, 8, 64, 15) => 8 + (4 + 4 + 4 )+( 2 + 2 +2) +1 = 27 Not satisfied with eth place (5, 10, 10, 500, 1000, 550) => 500 + (50 + 100 + 50) +(5+10+10) + 1 = 726 satisfied with dth place (4, 7, 3, 84, 160, 117) => 84+ (28+21+12) + (4+7+3) +1 =160 satisfied with e th place.

    Q3 : Group by with ROLL UP

    Explanation : Roll up will Reduce the dimensions.

    The formula will be a*b*c + (a*b) + a + 1 remaining options left , (5, 10, 10, 500, 1000, 550) => 500 + (50 ) +(5) + 1 = 556 Not satisfied with fth place (4, 7, 3, 84, 160, 117) => 84+ (28) + (4) +1 =117 satisfied with f th place.

    ****So the ANSWER will be (4, 7, 3, 84, 160, 117) satisfied all the three queries based on the OLAP operations.