Page 121 - DCAP606_BUSINESS_INTELLIGENCE

Business Intelligence

Notes Aggregate tables store pre-computed results, which are measures that have been aggregated
(typically summed) over a set of dimensional attributes. Using aggregate tables is a very popular
technique for speeding up query response times in decision support systems. This eliminates
the need for run-time calculations and delivers faster results to users. The calculations are done
ahead of time and the results are stored in the tables. Aggregate tables should have many fewer
rows than the non-aggregate tables, and therefore, processing should be quicker.
Often, a business wants to compare values of a measure and needs a calculation to express the
comparison. Oracle BI Server has a calculation engine to perform a multitude of calculations.
Calculation measures allow end users to ask business questions like “Show me the accounts
receivable balance as of Q3” or “Show me the difference between units ordered and units
shipped.”

Once the groundwork has been laid, MDX queries and the use of several MDX and SAS functions
within those queries will be demonstrated.

Notes The examples provided will allow you to customize the OLAP cube report data and
leverage the potential analytical insights made available through this medium.

Self Assessment

Fill in the blanks:
1. .......................... store pre-computed results, which are measures that have been aggregated
(typically summed) over a set of dimensional attributes.
2. ............................... Server has a calculation engine to perform a multitude of calculations.

8.2 Aggregate Functions

It is very common to sum measures when you aggregate values along dimension hierarchies,
but sometimes you need to apply a different aggregation method.

Example: If you want to calculate average sales per customer, you divide total sales by
number of customers. You can sum sales amount to get total sales, but to get the number of
customers, you need to count customers, making sure to count each customer only once, regardless
of how many purchases each customer has made.
Suppose you want to analyse the overall gross margin for every product in your data source.
One way to do this is to create a new calculated field called Margin that is equal to the profit
divided by the sales. Then you could place this measure on a shelf and use the predefined
summation aggregation. Here, Margin is defined as:
Margin = SUM([Profit]/ [Sales])
This formula calculates the ratio of profit and sales for every row in the data source, and then
sums the numbers.

!
Caution However, this is almost certainly not what you would have intended because
summing ratios is generally not useful.

116 LOVELY PROFESSIONAL UNIVERSITY