Data is often stored in sparse form and business often wants to see data in a more organized and explicit fashion. Report developers working across reporting technologies face a requirement to show a record with a '0' in the measures columns if no data exists for a particular combination of dimension values. For such a requirement, we will have to densify the data and this article presents a way to densify and aggregate on the fly.
I wish to state that this article is borrowed from my book on Oracle APEX 4.2 Reporting. You can check the table of contents and a brief description along with screenshots.
Let me explain the use case now
Let us say that we have a report in Oracle APEX where we wish to show the income from 2 different sources ('Direct source' and 'Online source') for every quarter. Lets say Direct source did not produce any income in Q1/2013 quarter. In such a case, we wont have a row for Direct source and Q1/2013 combination. What if, we have a requirement where we have to show a record with '0' for Order Total if we do not have a record for a source-quarter combination and also show the aggregated result for various dimensions?
We will try to solve this problem here.
I have created an example based on the above use case @ https://apex.oracle.com/pls/apex/f?p=81782:3
The first report is with the data densifiation implementation and the second report is the a report without the implementation. The 3rd and the 4th reports show the underlying data responsible for the first 2 reports.
Let me share a screenshot of the data densified report
and the one without data densification
Both these implementations use a time dimension. Time dimension is one of the most widely used dimensions in BI systems. Time dimension is joined to fact tables to help the user see the trends over a period of time. Time dimension is also useful in comparing business performances of 2 different time periods.
Note that the report with data densification is packed with rows for every quarter-order_mode combination while the one without data densification is not.
Let me now share the query that does the trick.
SELECT decode(GROUPING(t.quarter_year) + GROUPING(o.order_mode),
0, t.quarter_year,
1, decode(GROUPING(t.quarter_year), 0, 'Total for ' || t.quarter_year, 'Total for ' || o.order_mode),
2, 'Grand total') "Quarter",
decode(GROUPING(t.quarter_year) + GROUPING(o.order_mode),0,o.order_mode,NULL) "Order mode",
nvl(SUM(order_total), 0) "Sum of order amount",
to_char(100 *nvl(ratio_to_report(SUM(order_total)) over(PARTITION BY(GROUPING(o.order_mode) || t.quarter_year)), 0), '990D0') "% of contribution in a Quarter"
FROM oehr_time_dim t LEFT
OUTER JOIN oehr_orders o PARTITION BY(o.order_mode) ON(o.time_dim_id = t.time_dim_id)
WHERE t.dat <= to_date('31-Dec-1998', 'dd-Mon-yyyy')
GROUP BY cube(quarter_year, order_mode)
ORDER BY to_number(SUBSTR(t.quarter_year, 4)),
to_number(SUBSTR(t.quarter_year, 2, 1)),
o.order_mode
The first column i.e. Quarter displays the Quarter string and also strings such as 'Total for Q1-1998', 'Total for direct' and 'Grand total'. The decode statements in this column helps to display these strings.
The second column i.e. Order mode displays the order mode and has null for rows that display the aggregations.
The third column i.e. Sum of order amount shows the sum of order_total and displays a '0' if it finds a null.
The fourth column i.e. % of contribution in a Quarter shows the percentage of contribution by both the order modes (online and direct) to the sum of order_total for every quarter. We find this percentage using ratio_to_report analytical function.
Now, the data densification is done by the partition by clause along with left outer join. This part of the query is highlighted in blue. This syntax enables us to use the join for every logical partition of order_mode.
We are also using the cube extension of the group by clause in order to get an aggregation for every quarter and every order_mode along with getting a grand total.
This and many more tricks in my book @ http://www.packtpub.com/oracle-apex-4-2-reporting/book
Table of contents: http://obiee-oracledb.blogspot.in/2013/07/my-upcoming-book-oracle-apex-42.html
Description of the book with screenshots: http://obiee-oracledb.blogspot.in/2013/08/description-of-my-book-on-oracle-apex.html
Cheers
No comments:
Post a Comment