Question

Summarize these pages in your own style and you have to include in your report some figures highlighting the relation between these operations.

Basic AGGREGATE functions (Revision) COUNT: returns the number of tuples, which meet the specified condition: SELECT COUNT(DISTINCT Dept) AS Num_Depts FROM subject: SUM: returns the sum of the values in a specified column (i.e. numeric column) SELECT COUNT(*) AS hi_sal, SUM(salary) FROM Lecturer WHERE Salary > 4500 MIN: returns the minimum value in a specified column (numeri or character). • MAX: returns the maximum value in a specified colin (numeric or character). AVG: returns the average of the values in a specified column (numeric or character).

Answer 1

1. OLAP is stands for online analytical processing approach, it is basically used for answering multi dimensional analytical queries.
2. The main application of AP model is for trend analysis Complex modelling and sophisticated data processing.
3. Following are two one of the main categories of OLAP:-

• Aggregate function

               1. Basic aggregate function

   2. Advanced aggregate function

• Analysis function

          4. following is the detailed explanation of basic aggregate functions used in OLAP model:-

     A. Basic Aggregate Function:-

• COUNT:-

1. IT returns the number of tuples which meet required condition.

• SUM:-

1. IT returns sum of the values specified column.

• MIN:-

1. Returns minimum value from specified column.

• MAX:-

1. IT returns maximum value from specified column.

• AVG:-

1. IT returns average value from specified column.

       B. Advanced Aggregate Function:-

MAX AVG MIN Basic Aggregate Function COUNT SUM Fig. Basic Aggregate Function

• CUBE:-

1. It is extension to the group by clause used for representing data in cross tabular format.
2. It uses attributes as a input, groups them and calculates the sub totals for the different combinations.

• ROLLUP:-

1. It is added advantage of group by clause, it can generate the aggregate from very small level to the most detailed versiot.
2. Using ROLL UP we can compute multiple category of sub totals with particular group.
3. the syntax of rollup is easy-to-use it is highly efficient adding minimal over head to the query.
4. for data for data warehouse administrators rollup can simplify the maintenance.
5. Rollup appears in the group by clause for select query its Syntax is:-
6. SELECT.......GROUP BY ROLLUP ( grouping_column_reference_list);

• Partial ROLL UP:-

1. For including only some of the subtotals we can use partial role up function.
2. Syntax:-
3. GROUP BY expr1, ROLL UP(expr2,expr3);

• GROUPING : function:-

1. It is important to differentiate aggregate null that appear in the output by using roll up or cube function.
2. GROUPING clause represent which outputs are subtotals for which aggregate functions.
3. GROUPING appears in select statement its Syntax is as following:-
4. SELECT....[GROUPING (DIMENSION 1,DIAMENSION2]....GRIUP BY {CUBE|ROLL UP| GROUPING SETS}....DIAMENSSION COLUMNS;

        B. SQL OLAP ADVANCED ANALYSIS FUNCTION:-

• following are some of the advanced analysis functions:-

GROUPING ROLL UP CUBE Aggregate function (Advanced) Fig. Basic Aggregate Function

1. RANK:-

• it compute rank of the records as compare to other recordsrecords, based on the values of set of measures.
• when we need to display ranking of multiple attributes we will need to partition the aggregate so that appropriate ranking can be displayed.
• For that we have one option of display one rank on one attribute partitioning only.

        2. Top N Ranking:-

• using the rank function we can display Top N ranking based on certain attributes values we can achieve this by simply in closing rank function in a subquery and filter condition outside the subquery

         3. CUMULATIVE DISTRIBUTION:-

• To compute CUME_DIST of the value of x in as set X of size N we using the following formula.
• CUME_DIST= ( number of values in S coming before and including X in rhe specified order)/ N
• Following is the syntax:-
• CUME_DIST() OVER([ query_partition_clause], order_by_clause);

MAX AVG MIN Basic Aggregate Function COUNT SUM Fig. Basic Aggregate Function

GROUPING ROLL UP CUBE Aggregate function (Advanced) Fig. Basic Aggregate Function