Chapter 3. Working with Database Data

As mentioned earlier, Oracle Business Intelligence is not limited to only the Oracle Application Server solutions discussed in the preceding chapter. Oracle Database itself has a lot to offer when it comes to Business Intelligence. To better understand which Oracle Database features can be regarded as Business Intelligence features, it would be wise to recall what Business Intelligence is all about. Returning to the discussion about the Business Intelligence basics in Chapter 1, you might recall that Business Intelligence is first of all about answering analytical business questions.

So, if you've got used to visual tools, this chapter encourages you to branch out of your comfort zone, and gives you a chance to understand manually written SQL code. You might be surprised as to just how much SQL can offer when it comes to answering analytical business questions based on the data you have stored in the database, as well as in external sources.

In this chapter...

Using analytic SQL functions

Analytic SQL is one of those Oracle Database native features that you can start using immediately after installing the database. Of course, you will also need some data stored in the database to play with. However, the data available in the demonstration database schemas can be quite enough to begin with.

Answering simple questions

As stated earlier, there are business questions that can be answered with a simple SQL query issued against the database. Questions starting with "how many" are a good example. Often, to answer such questions, you can use the COUNT SQL function. For example, you might need to know how many employees in your organization have been working for the company for 15 years or more. In this example, you might query the employees table located in the hr/hr demonstration schema, issuing the following statement:

SELECT count(*) FROM employees WHERE (EXTRACT(YEAR FROM (SYSDATE)) - EXTRACT(YEAR FROM (hire_date))) >= 15;

The output should look...

Relational implementation of the dimensional model

You can regard this section as a prelude to covering the data warehousing Oracle Database feature, which will be discussed in detail later in this book.

Database structures behind an EUL

In the preceding section, you looked at the SQL code behind a Discoverer item. While that code represented the SELECT statement issued against a regular database table, there are some objects within Discoverer that require a set of associated objects in the underlying database to hold metadata.

Let's, for example, explore the database structures, which are implicitly defined upon creation of the HR EUL discussed in the "Post-installation tasks" section of the preceding chapter. For this, let's examine the hr/hr database schema upon which the HR EUL was created. To begin with, you can simply count the tables in the schema. To do this, connect to the database /as sysdba and then issue the following query:

SELECT count(*) FROM all_tables WHERE owner = 'HR...

Summary

While the preceding chapters discussed Business Intelligence through the prism of visual Oracle Business Intelligence tools such as Discoverer, this chapter stepped aside and looked at how to use SQL, a native tool of Oracle Database, to get answers to business questions based on the database data. In particular, you looked at the advanced SQL features designed to summarize data, such as ROLLUP and CUBE extensions of the GROUP BY clause in SELECT statements.

Turning back to the Business Intelligence components from the Oracle Business Intelligence suite discussed in the preceding chapters, this chapter shed some light on how the metadata used by Discoverer is organized in the underlying Oracle database. In particular, you looked at the database structures behind EULs, which are needed in order to work with Discoverer.

The next chapter will come back to the discussion of the Oracle Business Intelligence suite, providing a detailed look at its components. Aside from Discoverer Plus...