Page 217 - DCAP603_DATAWARE_HOUSING_AND_DATAMINING

Unit 11: Query Processing and Optimization

2. The column probed against. This is the point from which the filtered data flows through notes
the tree.
3. Whether the bitmap probe uses in-row optimization. When it is, the bitmap probe is
invoked with the IN ROW parameter. Otherwise, this parameter is missing.

11.2.2 optimized Bitmap filtering requirements

Optimized bitmap filtering has the following requirements:

1. Fact tables are expected to have at least 100 pages. The optimizer considers smaller tables
to be dimension tables.
2. Only inner joins between a fact table and a dimension table are considered.
3. The join predicate between the fact table and dimension table must be a single column
join, but does not need to be a primary-key-to-foreign-key relationship. An integer-based
column is preferred.

4. Joins with dimensions are only considered when the dimension input cardinalities are
smaller than the input cardinality from the fact table.

Task Describe the main activities associated with various design steps of data
warehouse?

11.3 Writing your own Queries

When writing your own query, you define the set of choices and conditions that you want to use
to retrieve data stored in the Warehouse. You determine such attributes as data elements to be
returned (e.g., last name, city, age), conditions for selecting records (e.g., equal to or less than),
and sort criteria (the order and priority in which results are to be sorted).
You may want to keep in mind some general questions to guide you in composing a query:
1. What information are you looking for? In what collection does this data reside, and which
Business Object universe would be best to use?
2. Bear in mind the level of detail data you need, the time periods concerned, and which
source system you would use to verify the data retrieved.
3. Once you have a basic idea of the results you need, consider how the query should be
contrained – by time period? account segment(s)? employee or organization names/
codes?
4. What will you do with your results? If you are presenting them to others, you may want to
include segment descriptions for those unfamiliar with codes. Also, if you plan to export
the data, you may want to include objects which you have used to constrain your query,
to better identify the data in the exported file. (For example, although you may have used
Accounting_Period as a constraint, it might help to have the period appear as a column in
your exported file, so you know what period that data represents.)

11.4 Query processing techniques

In this section, introduce to you four current query processing techniques that are used in Data
Warehousing queries. This is followed by a description of a cost model that I have adopted and
later to be used when I compare the performance of the different query processing techniques.

LoveLy professionaL university 211