Oracle Business Intelligence : Getting Business Information from Data

0
123
11 min read

Oracle Business Intelligence: The Condensed Guide to Analysis and Reporting

Oracle Business Intelligence: The Condensed Guide to Analysis and Reporting

A fast track guide to uncovering the analytical power of Oracle Business Intelligence: Analytic SQL, Oracle Discoverer, Oracle Reports, and Oracle Warehouse Builder

 
  • Install, configure, and deploy the components included in Oracle Business Intelligence Suite (SE)
  • Gain a comprehensive overview of components and features of the Oracle Business Intelligence package
  • A fast paced, practical book that provides you with quick steps to answer common business questions and help you make informed business decisions
  • Leverage the computational power of Oracle Database
        Read more about this book      

(For more resources on Oracle, see here.)

Most businesses today use Business Intelligence (BI), the process of obtaining business information from available data, to control their affairs. If you’re new to Business Intelligence, then this definition may leave you with the following questions:

  • What is data?
  • What is the information obtained from it?
  • What is the difference between data and the information obtained from it?

You may be confused even more if you learn that data represents groups of information related to an object or a set of objects. Depending on your needs, though, such groups of information may or may not be immediately useful, and often require additional processing such as filtering, formatting, and/or calculating to take on a meaning.

For example, information about your customers may be organized in a way that is stored in several database tables related to each other. For security purposes, some pieces of information stored in this way may be encoded, or just represented in binary, and therefore not immediately readable. It’s fairly obvious that some processing must be applied before you can make use of such information.

So, data can be thought of as the lowest level of abstraction from which meaningful information is derived. But what is information anyway? Well, a piece of information normally represents an answer to a certain question. For example, you want to know how many new customers have registered on your site this year. An answer to this question can be obtained with a certain query issued against the table containing customer registration dates, giving you the information you asked for.

Data, information, and Business Intelligence

Although the terms data and information refer to similar things, they aren’t really interchangeable as there is some difference in their meaning and spirit. Talking about data, as a rule, involves its structure, format, storage, as well as ways in which you can access and manipulate it. In contrast, when talking about information, you mean food for your decision-making process. So, data can be viewed as low-level information structures, where the internal representation matters. Therefore, the ways in which you can extract useful information from data entirely depend on the structure and storage of that data.

The following diagram gives a conceptual view of delivering information from different data sets:

Oracle Business Intelligence: Getting Business Information from Data

As you can see from the figure, information can be derived from different data sources, and by different means. Once it’s derived, though, it doesn’t matter where it has come from, letting its consumers concentrate on the business aspects rather than on the specifics of the internal structure. For example, you might derive some pieces of data from the Web, using the Oracle Database’s XQuery feature, and then process it as native database data.

To produce meaningful information from your data, you will most likely need to perform several processing steps, load new data, and summarize the data. This is why the Business Intelligence layer usually sits on top of many data sources, consolidating information from various business systems and heterogeneous platforms.

The following figure gives a graphical depiction of a Business Intelligence system. In particular, it shows you that the Business Intelligence layer consumes information derived from various sources and heterogeneous platforms.

Oracle Business Intelligence: Getting Business Information from Data

It is intuitively clear that the ability to solve problems is greatly enhanced if you can effectively handle all the information you’re getting. On the other hand, extracting information from data coming in from different sources may become a nightmare if you try to do it on your own, with only the help of miscellaneous tools. Business Intelligence comes to the rescue here, ensuring that the extraction, transformation, and consolidation of data from disparate sources becomes totally transparent to you.

For example, when using a Business Intelligence application for reporting, you may never figure out exactly what happens behind the scenes when you instruct the system to prepare another report. The information you need for such a report may be collected from many different sources, hiding the complexities associated with handling heterogeneous data. But, without Business Intelligence, that would be a whole different story, of course. Imagine for a moment that you have to issue several queries against different systems, using different tools, and you then have to consolidate the results somehow—all just to answer a single business question such as: what are the top three customers for the preceding quarter?

As you have no doubt realized, the software at the Business Intelligence layer is used to provide a business-centric view of data, eliminating as much of the technology-specific logic as possible. What this means in practice is that information consumers working at the Business Intelligence layer may not even know that, say, customer records are stored in a Lightweight Directory Access Protocol (LDAP) database, but purchase orders are kept in a relational database.

The kind of business questions you may need to answer

As you just learned, Business Intelligence is here to consolidate information from disparate sources so that you need not concern yourself with it. Okay, but why might you need to gather and process heterogeneous data? The answer is clear. You might need it in order to answer analytical questions that allow you to understand and run your business better.

In the following two sections, you’ll look at some common questions that Business Intelligence can help you answer. Then, you’ll see how you can ask those questions with the help of Business Intelligence tools.

Answering basic business questions

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 firrst look at some questions that are commonly brought up by business users:

  • What is the average salary throughout the entire organization?
  • Which customers produce the most revenue?
  • What is the amount of revenue each salesman brought in over the preceding quarter?
  • What is the profitability of each product?

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

  • How much traffic does a certain account generate over a month?
  • What pages in your site are most visited?
  • What are the profits made online?

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

Answering probing analytical questions

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:

  • How do sales for this quarter compare to sales for the preceding quarter?
  • What factors impact our sales?
  • Which products are sold better together?
  • What are ten top-selling products in this region?
  • What are the factors influencing the likelihood of purchase?

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.

There’s nothing to be done here; computers like specific questions. Unlike humans, machines can give you exactly what you ask for, not what you actually mean. So, even an advanced Business Intelligence application will require you to be as specific as possible when it comes to putting a question to it.

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:

  • What are the sales figures for this quarter?
  • What are the sales figures for the last quarter?

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:

  • Where did visitors come from?
  • Which pages did they visit before reaching the product page?

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

Asking business questions using data-access tools

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:

Oracle Business Intelligence: Getting Business Information from Data

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.

All this does not mean that SQL is not used in the area of Business Intelligence. Quite the contrary, SQL is still indispensable here. In fact, SQL has a lot to offer when it comes to data analysis. As you just saw, though, composing complex queries assumes solid SQL skills. Thankfully, most Business Intelligence tools use SQL behind the scenes totally transparently to users.

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 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:

Oracle Business Intelligence: Getting Business Information from Data

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:

Oracle Business Intelligence: Getting Business Information from Data

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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here