Book Image

Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting

By : Yuli Vasiliev
Book Image

Oracle Business Intelligence : The Condensed Guide to Analysis and Reporting

By: Yuli Vasiliev

Overview of this book

Business Intelligence (BI) is the process of obtaining business information from available data and today, most businesses use BI to control their affairs. With Business Analysis and Reporting in Oracle Business Intelligence, you can quickly learn how to put the power of the Oracle Business Intelligence solutions to work. To jump start with analysis and reporting of data on an Oracle Business Intelligence SE platform and to keep the process of learning simple and interesting requires numerous annotated examples.The examples in this introductory guide will make you immediately familiar with tools included in the Oracle Business Intelligence package. This book will teach you how to find answers to common business questions and make informed business decisions as well as helping you to use Oracle Business Intelligence SE platform and prepare database for analysis. This practical, example-rich guide starts by explaining concepts behind getting business information from data. We then move smoothly onto the tools included in the Oracle Business Intelligence SE and Oracle Business Intelligence Tools packages. Along the way, we will look at how to take advantage of Discoverer Administrator, Discoverer Plus, and Discoverer Viewer for analysis and reporting. You will also learn how to build, deploy and execute reports using Oracle Reports, and integrate data from different data sources with warehousing, employing Oracle Warehouse Builder software. Covering advanced Oracle Business Intelligence features, this book will teach you how to pivot data, drill it up and down, as well as display it visually in graphs.
Table of Contents (13 chapters)
Oracle Business Intelligence: The Condensed Guide to Analysis and Reporting
Credits
About the Author
About the Reviewers
Preface

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 first 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.

Note

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:

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.

Note

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. Chapter 3, Working with Database Data, will provide a closer look at advanced SQL features you can use to summarize data over multiple tables. 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 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.