 |
|
|
|
 |
|
Business Intelligence Query |
|
|
Business intelligence can be defined as the retrieval and manipulation of data needed to produce
information required for business decisions. The retrieval method is through queries. Many such
queries are demonstrated in the SQL examples provided in this book. In this chapter, we describe
how leveraging certain database features and designs enables business intelligence queries to
perform optimally. The goals for design of such systems are often different from online transaction
processing (OLTP) systems, where extremely efficient updating is the primary goal. In OLTP
systems, a great deal of design work is spent ensuring that data is only stored once in a third
normal form (3NF). While such designs are great for transactions-oriented systems and operational
data stores where reporting requirements are usually known well in advance, such designs may
not be appropriate when data is primarily historical and for business analysts who have questions
of a more ad hoc nature.
Ad hoc queries often form the basis of gathering the data needed for business intelligence.
Business-oriented questions may be formulated such that a large transaction (fact) table surrounded
by multiple dimensions or lookup tables provides an ideal schema for ease of understanding and
optimization. This is called a star schema and is described in this chapter. We also provide examples
of queries that leverage this schema.
Another common business requirement is the need to drill to different levels of detail in a hierarchy.
Oracle provides this summary level capability through materialized views. This chapter describes
what materialized views are, how you determine where they are needed, and how you create and
maintain them.
Advanced Oracle features for business intelligence make use of the cost-based optimizer. As
optimization techniques could be either rules-based or cost-based prior to Oracle Database 10g,
remember to use the cost-based optimizer if using an older release of the database. As of Oracle
Database 10g, the cost-based optimizer is the only optimizer officially supported. In addition, much
of the business intelligence functionality described here is only available in the Enterprise Edition
of the database. Since Oracle features in the various editions can differ in different versions of the
database, if you are running an edition other than Enterprise Edition, you should consult the
Oracle documentation regarding supported features.
The focus of providing SQL examples in this chapter is relevant even though many business analysts
seeking business intelligence often use tools such as Business Objects, Cognos, Hyperion’s Brio,
MicroStrategy, Oracle Discoverer, and others instead of manually writing SQL. It is important to remember
that these business intelligence tools commonly access the Oracle database by generating SQL. In fact,
they usually provide the ability to see the SQL generated by the tool. Knowledge of how this SQL works
and leverages the database can be extremely useful to both programmers and analysts.Thus far, we haven’t discussed the manipulation of data in our business intelligence definition. In this
book, we’ll focus that discussion around the analytics, OLAP, and data mining capabilities in Oracle. That
material is covered in the next chapter (Chapter 27, “Business Intelligence Analysis”).
Performance management and tuning (optimization) is discussed in Chapter 28. However, maintaining
levels of performance proactively while handling business intelligence queries of widely differing
complexities is worth a mention now. This topic includes proper database design (touched upon here),
as well as optimal SQL and parallelization. Oracle can also be configured to prevent the popularly
phrased “query from hell” even when all is not optimal. So, we’ll briefly touch on using the Database
Resource Manager to do exactly that at the close of this chapter.
Schema for Business Intelligence
A variety of schema can be used in business intelligence applications. A 3NF is sometimes used where
standard reports are common and there are relatively few ad hoc queries that could introduce an extremely
large number of joins. Where 3NF is used and ad hoc queries take place, a common practice is to improve
performance by leveraging partition-wise joins through the introduction of composite range-list partitioning.
When both tables in the join are equi-partitioned on their join keys, much larger table joins can be broken
into a smaller joins involving pairs of relatively smaller partitions.
To illustrate a partition-wise join, we’ll use two partitioned tables, sales and costs, from the sh schema
provided as part of the Oracle Database 10g standard installation. Both are partitioned on time_id into
quarterly (and other) partitions. In this query, we are interested in the channels that sold items where the
unit price was $600 greater than unit cost in the first quarter of 1998.
SELECT costs.channel_id, costs.unit_price, costs.unit_cost
FROM sh.sales, sh.costs
WHERE sales.channel_id = costs.channel_id
AND sales.time_id BETWEEN TO_DATE (‘01-JAN-1998’, ‘DD-MON-YY’)
AND TO_DATE (‘01-APR-1998’, ‘DD-MON-YY’)
AND (costs.unit_price - costs.unit_cost) > 600
GROUP BY costs.channel_id, costs.unit_price, costs.unit_cost;
This query joins data that is only in the SALES_Q1_1998 partition of each table. The results returned are
shown in Figure 26-1 (as viewed in iSQL*Plus).
Larger numbers of joins are common in business intelligence queries, often leveraging common lookup
tables or dimensions. Astar schema frequently provides a more optimal and understandable solution than
3NF. The star schema consists of a single fact table surrounded by multiple dimensions and linked by a
foreign key relationship. Figure 26-2 illustrates a star schema that is part of the sh schema provided in an
Oracle Database 10g standard installation. This schema consists of the sales transaction or fact table
surrounded by dimensions for time, channels, customers, products, and promotions. Foreign keys in thesales fact table point to the primary keys in each of the dimensions. This schema easily enables queries such
as “How many products were sold by my channels during a specific promotion in the past three months?”SapereOnline |
|
|
|
|
|
|
|
ORACLE
|
|
|
|
|
| |
|
 |
|
|
|
|
