BITS WILP Data Warehousing Mid-Sem Exam 2017-H2

BITS WILP Data Warehousing Mid-Sem Exam 2017-H2
Data Warehousing (SSZG515) EC2R SEM1 2017-18 (Closed Book)

…

Q. 4: Compare and contrast between ‘Application Orientation’ and ‘Subject Orientation’ with relevant examples. [6]

A. 4: Here, differences and similarities born out of using OLTP (application oriented) and OLAP (subject oriented) systems can be written.

Q. 5(B): What do you mean by degenerate dimension? What is its use in dimension modeling?

A. 5(B):

Degenerate Dimension

·         A degenerate dimension is represented by a dimension key attribute(s) with no corresponding dimension table

·         Occurs usually in line-item oriented fact table design

…

Question 5(D): Give four examples of OLAP queries.

Answer 5(D):

Oracle SQL queries for OLAP:

1. This example of ROLLUP uses the data in the video store database.

  SELECT Time, Region, Department,

   sum(Profit) AS Profit FROM sales

   GROUP BY ROLLUP(Time, Region, Dept)

Calculating Subtotals without ROLLUP

The result set in Table 20-1 could be generated by the UNION of four SELECT statements, as shown below. This is a subtotal across three dimensions. Notice that a complete set of ROLLUP-style subtotals in n dimensions would require n+1 SELECT statements linked with UNION ALL.

SELECT Time, Region, Department, SUM(Profit)

 FROM Sales

 GROUP BY Time, Region, Department

UNION  ALL

 SELECT Time, Region, '' , SUM(Profit)

 FROM Sales

 GROUP BY Time, Region

UNION ALL

 SELECT Time, '', '', SUM(Profits)

 FROM Sales

 GROUP BY Time

UNION ALL

 SELECT '', '', '', SUM(Profits)

 FROM Sales;

OUTPUT:

Table 20-2 ROLLUP Aggregation across Three Dimensions

Time Region Department Profit

-------------------------------------------------------------

1996       Central VideoRental       75,000

1996       Central VideoSales          74,000

1996       Central [NULL]                  149,000

1996       East        VideoRental       89,000

1996       East        VideoSales          115,000

1996       East        [NULL]                  204,000

1996       West     VideoRental       87,000

1996       West     VideoSales          86,000

1996       West     [NULL]                  173,000

1996       [NULL]  [NULL]                  526,000

1997       Central VideoRental       82,000

1997       Central VideoSales          85,000

1997       Central [NULL]                  167,000

1997       East        VideoRental       101,000

1997       East        VideoSales          137,000

1997       East        [NULL]                  238,000

1997       West     VideoRental       96,000

1997       West     VideoSales          97,000

1997       West     [NULL]                  193,000

1997       [NULL]  [NULL]                  598,000

[NULL]  [NULL]  [NULL]                  1,124,000

…………………………………………………………..

Optimized "Top-N" Analysis:

select ROWNUM AS Rank, Name, Region, Sales from 

  (select Name, Region,  sum(Sales) AS Sales 

      from Sales GROUP BY Name, Region

      order by sum(Sales) DESC)

WHERE ROWNUM <= 10

---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   ---   --- 

Tags: BITS Pilani, Work Integrated Learning Program (WILP), Previous Year Question Papers, Data Warehousing.