Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting

The set of questions you may need your Business Intelligence system to answer will vary depending on your business and, of course, your corresponding functions. However, to give you a taste of what Business Intelligence can do for you, let's first look at some questions that are commonly brought up by business users:

If you run your business online, you may be also interested in hit counting and traffic analysis questions, such as the following:

Looking at the business analysis requests presented here, a set of questions related to your own business may flash into your mind.

In the preceding section, you looked at some common questions a business analyst is usually interested in asking. But bowing to the reality, you may have to answer more probing questions in your decision-making process, in order to determine changes in the business and find ways to improve it. Here are some probing analytical questions you might need to find answers to:

As you can see, each of these questions reflects a certain business problem. Looking through the previous list, though, you might notice that some of the questions shown here can be hard to formulate with the tools available in a computer application environment.

It's fairly clear that the question about finding the factors impacting sales needs to be rephrased to become understandable for a Business Intelligence application. How you would rephrase it depends on the specifics of your business, of course.

Often, it's good practice to break apart a problem into simpler questions. For example, the first question on the above list—the one about comparing quarter sales—might be logically divided into the following two questions:

Once you get these questions answered, you can compare the results, thus answering the original, more generically phrased question. It can also provide one definition or variation for drill down.

In the above example, it's fairly obvious what specific questions can be derived from the generic question. There may be probing questions, though, whose derived questions are not so obvious. For example, consider the following question: What motivates a customer to buy? This could perhaps be broken down into the following questions:

Of course, the above list does not seem to be complete—some other questions might be added.

As you might guess, although all these questions sound simple when formulated in plain English, they are more difficult to describe when using data-access tools. If you're somewhat familiar with SQL, you might notice that most of the analytical questions discussed here cannot be easily expressed with the help of SQL statements, even if the underlying data is relational.

For example, the problem of finding the top three salespersons for a year may require you to write a multi-line SQL request including several sub-queries. Here is what such a query might look like:

SELECT emp.ename salesperson, top_emp_orders.sales sales
FROM
(SELECT all_orders.sales_empno empno, all_orders.total_sales
FROM
(SELECT sales_empno, SUM(ord_total) total_sales, RANK() OVER (ORDER BY SUM(ord_total) DESC) sal_rank
FROM orders
WHERE EXTRACT(YEAR FROM ord_dt) = 2009
GROUP BY sales_empno
)all_orders
WHERE all_orders.sal_rank<=3
)top_emp_orders, employees emp
WHERE top_emp_orders.empno = emp.empno
ORDER BY sales DESC;

This might produce something like this:

If you're not an SQL guru of course, writing the above query and then debugging it could easily take a couple of hours. Determining profitability by customer, for example, might take you another couple of hours to write a proper SQL query. In other words, business questions are often somewhat tricky (if possible at all) to implement with SQL.

Now let's look at a simple example illustrating how you can get an analytical question answered with a Business Intelligence tool—Oracle BI Discoverer Plus in this particular example. Suppose you simply want to calculate the average salary sum over the organization. This example could use the records from the hr.employees demonstration table. Creating a worksheet representing the records of a database table in the Discoverer Plus will be discussed in detail later in Chapter 4, Analyzing Data and Creating Reports, which focuses on issues related to analyzing data, and creating reports with the tools available through the Oracle Business Intelligence suite. For now, look at the following screenshot to see what such a worksheet might look like:

As you can see in the previous screenshot, a Discoverer Plus worksheet is similar to one in MS Excel. As in Excel, there are toolbars and menus offering a lot of options for manipulating and analyzing data presented on the worksheet. In addition, Discoverer Plus offers Item Navigator, which enables you to add data to (or remove it from) the worksheet. The data structure you can see in Item Navigator is retrieved from the database.

When we return to our example, answering the question: "what is the average salary across the organization?"Similarly, in Excel, it is as simple as selecting the Salary SUM column on the worksheet, choosing an appropriate menu, and setting some parameters in the dialog shown next. After you click the OK button in this dialog box, the calculated average will be added to the worksheet in the position specified. So, the Total dialog shown in the following screenshot provides an efficient means for automating the process of creating a total on a specified data column:

As you can see, this approach doesn't require you to write an SQL query on your own. Instead, Discoverer Plus will do it for you implicitly, thus allowing you to concentrate on business issues rather than data access issues.

This previous example should have given you a taste of what Business Intelligence can do for you. In the next chapters, you'll learn how you can make use of Business Intelligence applications to get these, and similar business questions, answered with a minimum of effort.

As stated above, the quantity and quality of the information you can obtain directly depends on the quantity and quality of the data available at your disposal. Not surprisingly, the more information you can collect, the more probing analytical questions you can answer.

To start with, let's look at a simple example. Suppose you need to analyze the results of a survey you conducted online to get some vital feedback from your customers. It turned out, however, that only one-tenth of the overall number of your customers participated. Of course, the information you will extract from the results of this survey cannot be considered comprehensive, as the opinion of the majority remains unclear and, therefore, only a sparse dataset has been analyzed.

While in the above example, a factor that complicated performing a quality analysis was that there was not enough incoming information; you will most likely in practice experience another kind of problem—composing the 'right' dataset from the sea of data available at your disposal. In terms of Oracle Discoverer, such datasets are called workbooks and represent business areas—collections of related information. Each workbook contains worksheets displaying data from the perspectives you want to view that data in, to obtain answers to your business questions. So when creating a workbook, you should include all the data structures required for building the worksheets you want.

Diagrammatically, the scenario involving Oracle Discoverer workbooks, each of which can be used to answer a certain set of business questions might look like the following diagram:

As you can see, Oracle Discoverer workbooks can be built on a variety of data sources, representing the derived data so that it can answer your business questions. The diagram in the figure gives you a rough idea of the mechanism used by Oracle Discoverer to provide a business view of the underlying data.

But you're probably wondering "How would I know which data structures must be included in a workbook to give enough information for getting those business questions answered?" Well, in most cases, the answer to this question is intuitively clear: you add to the workbook only those data structures that contain the data you want to see displayed at that point on the workbook's worksheets.

If you're working with relational data, then the rule of thumb is to include not only those tables whose data you want to see on worksheets, but also their related tables. It's not a big issue though, since the Discoverer wizard used to create a new workbook includes related tables by default. As you might guess, related tables may become required when the time comes to drill down through data to detail.

For example, when creating a workbook to be used for sales analysis, you would include at least the orders, customers, and employee tables. This is correct because you often need to have the ability to integrate customer, employee, and sales information for analysis. Now imagine that you want to create a worksheet for the report showing the sales of a particular region. What this means simply is that the regions table must also have been included.

In terms of Oracle Discoverer, database tables included in a workbook are folders, each of which includes items representing columns of the corresponding table. It's interesting to note, however, that folders and items are not necessarily based on relational tables and their columns respectively—other data structure options, such as LDAP directories and entries are also possible. So, the Oracle Discoverer documentation defines a folder as a collection of closely related information, and an item can be thought of as a piece of information of a certain type, within a folder.

Now that you have a rough idea of how data can be organized for business analysis, it's time to move on and look at how you might use that data to your advantage, gaining a comprehensive view of your business.

As we have just seen, within a workbook consolidating a collection of related information, you can create a set of worksheets, each of which is to answer a certain business question. Continuing the discussion, this section touches briefly on issues related to comparing and analyzing data.

To view the data displayed on a worksheet from the perspective you need, Oracle Discoverer offers a number of components, including totals, percentages, exceptions, and calculations. With these tools in hand, you'll be able to analyze data more quickly and easily.

Schematically, this might look like the following figure:

The above diagram illustrates that you can add totals, percentages, calculations, and filters to a worksheet through the corresponding Discoverer components. All these Discoverer tools are easy to use, and allow you to rearrange the worksheet's data or drill into it, so that you can see it from another perspective, with just a few clicks. In the Asking business questions using data-access tools section earlier in this chapter, you saw an example of how easy it is to calculate a total for a column on a worksheet.

Although Discoverer tools open up a great way to approach the problem of analyzing data, you may sometimes have to rearrange data even before it is included into a workbook. For example, in the area of comparative analyses, you may need to perform data densification before you can proceed to data analysis.

As usual, this can be best understood by example. Say, when performing a period-to-period comparison, you may face the problem of sparse data. For example, you perform day-to-day comparisons to see the inventory figures for various products on a daily basis. The problem is that the inventory table usually stores a row for a product when its quantity of available units changes. So, the rows stored in the inventory table might look like the following:

PROD_id EVENT_DT QUANT
------- --------- -----
111 05-FEB-10 15
111 08-FEB-10 7
222 05-FEB-10 17
222 07-FEB-10 14
333 05-FEB-10 7
333 08-FEB-10 25

As you can see, there are days when no event happened, and therefore, what you have here is a sparse dataset. In reports that you might want to create based on this table, you will most likely need this data to be represented differently, as a dense dataset. So, before you can include it to a workbook, a densification is needed.

The simplest way to solve this problem is to create a view based on the inventory table, so that the view contains a dense dataset. The densification might be accomplished with the help of the LAST_VALUE Oracle SQL analytic function, included in the select list of the view's SELECT statement.

The above example illustrated that the process of converting data into information you need, may start even before that data is chosen to be processed by a Business Intelligence tool. In other words, you may need to make some preparations to rearrange an underlying dataset so that it's ready for analysis.

If you need the inventory table's original rows, you'll need to make a change to the set of underlying data objects. However, Oracle Discoverer provides a better option. Windowing functions, such as LAST_VALUE discussed here, can be used to compute aggregates with the help of calculations, a Discoverer analysis feature mentioned at the beginning of this section. Like other Discoverer analysis features, calculations can be activated or deactivated with a single click, thus giving you a greater degree of flexibility. The Oracle Discoverer features will be discussed in more detail later in this book.

It's often the case that transactional data is stored in a relational database. Relational tables represent relational entities, such as products, orders, details, and customers, storing information about current transactions. As mentioned, a Business Intelligence solution can be built directly upon such a transactional system, containing data that you can use for analysis and reporting.

It's important to understand, though, that a transactional system should remain mobile and highly responsive, enabling new transactions to be processed and stored quickly. So, a common problem with such systems is that they are not designed to store large amounts of data—old data should be removed from time to time.

While data set optimization is good for a transactional system as the performance increases, it's not so good for analysis and reporting purposes. This is why Business Intelligence solutions are often built upon a data warehouse, consolidating both old and new data that can actually be stored in different sources.

Like a transactional system, a data warehouse represents a relational database. Unlike a transactional system however, a warehouse accumulates data, which represents the business history of an organization, and is structured for reporting, analysis, and decision support. In warehouses, data is organized around business entities such as products, regions, and customers.

Data in a warehouse can be organized so that it concentrates on a certain subject matter, say, purchasing; that is, it's optimized to simplify the task of finding answers to questions about purchasing. So, aside from historical data, a warehouse often uses summaries containing pre-processed data to speed up access to frequently queried information.

Using historical data can provide a clearer picture of the status of your business, leading to improved predictive capabilities. As you might guess, historical data is derived from a transactional system that often cannot afford to keep large amounts of data due to performance requirements. In fact, Business Intelligence can utilize both transactional and historical data sources for reporting and analysis purposes.

Diagrammatically, this might look like the following diagram:

It's interesting to note that although transactional data is not stored in a warehouse, many Business Intelligence tools access that data through a warehouse, taking advantage of summaries and other warehouse features. Warehousing for analysis and reporting will be discussed in more detail in Chapter 5, Warehousing for Analysis and Reporting, later in this book.

A multidimensional data model is often used to perform complex analysis of historical data. Multidimensional data is named so because it is organized by dimensions, such as products, times, customers, regions, and departments. Although there may be more than three dimensions in such data structures, they are often referred to as cubes.

The following diagram illustrates a cube that contains sales figures for groups of products for each month of Q4 in different regions.

What you can see in the figure is a three-dimensional data structure, enabling you to concentrate on solving a particular business problem. Looking at this structure, you might wonder what operations can be applied to it. Operations often applied to multidimensional data to extract meaningful information include the following:

These techniques will be discussed in detail in later chapters of this book.