Oracle Application Express (APEX) is a web-based application development and deployment tool that comes with the Oracle Database. Developers can use it to create reliable, secure, and easy-to-maintain web applications. The built-in wizards and functionality make it possible to build applications rapidly using relational and Oracle OLAP data. Since the Oracle OLAP data can be represented as SQL views, it is now easier than ever to report on the Oracle OLAP data. By using conditions on the SQL queries, it is possible to return data without the need to use aggregation functions. This greatly increases the performance of querying and retrieving data.
How is this all done? The Oracle OLAP views represent a traditional star schema, where the fact table is represented by the cube view, and the dimension views represent the dimension tables. The data in the cube view represents the data at all levels for all dimensions. So by adding conditions to the WHERE
clause to include the level name of the required data, you can retrieve summary data without the time-consuming GROUP BY
clause.
APEX allows you to specify these WHERE conditions as drill parameters, so that as you drill down the dimensions, the correct summary data is retrieved from the cube view. This is all done by constructing a parameterized SQL statement. Figure 1 shows a query from the Oracle OLAP views that will be used in APEX.
FIGURE 1. SQL query to be used in APEX
This query is pasted into APEX and used as the primary query for a sales report. Figure 2 shows the main query entry window in APEX.
Now you can set up the parameters and construct the report based on the query. You can even define stoplight and alert formatting to enhance the report outputs. An example report using this data is shown in Figure 3.
Drilling up and down the dimensions is fully supported. APEX also supports graphing of the data. Each task requires little more than creating a simple SQL statement and following an APEX wizard to create a very usable Oracle OLAP- based report.
Figure 2. APEX report query definition window
The types of reports and dashboards that can be created from APEX are not as diverse as those produced by OBIEE, but they are still very comprehensive and plentiful. In addition, the skill level requirement is relatively low, and the price is considerably less.
Figure 3. Sample APEX report