| Wiki Markup |
|---|
{scrollbar}
----
In the context of Data Warehousing there are tools such as *IdeaSoft O3* geared towards information analysis, also called OLAP tools. These tools usually seek to facilitate analysis through the use of the multidimensional model and generally summarize greats amounts of information in a meaningful set of values that describe reality in the best possible way.
It is precisely this summary of information that lets us face the problem of analyzing large quantities of data in an effective and practical way. However, certain situations require the analysis of some details of the summarized data after identifying one problem area.
OLAP technology in general, and O3 as an implementation of such technology is not geared to handling these levels of detail within the information used for analysis. Among other reasons, because not all the set of available information deserves being analyzed in depth, so that queries made to obtain this information are associated to OLTP technology.
The latter fosters the idea for detailed information to remain in the databases and only be accessed when necessary. That is how functionalities such as *Drill Through* have appeared, to allow OLAP tool users to perform this type of queries in a simple way.
Drill Through comes from the fact that the typical operations associated to OLAP tools are: _Drill Up, Drill Down, Drill To_, which stand for going up, down, or to a specific place in one dimension. "Through", in this case means we are plunging into information, idealizing what was mentioned above.
!O3Query.png|align=center,vspace=10,hspace=10!
h1. What is "Defining the Drill Through"?
Defining the Drill Through means defining an SQL query that must be performed to obtain specific detailed information. IdeaSoft O3 lets you associate a set of queries to each cube, so as to access data not included in the multidimensional model.
For instance, let's consider the relationships shown in Picture 1 and the multidimensional model shown in Picture 2, a classical sales example. (Obviously very much simplified) where sales are made on a certain date and to a certain customer. A cube generated for the proposed multidimensional model has no individual detail for each sale, as in order to achieve this, a dimension Sales(id_sale) should be present.
The fact of not including the details of sales facilitates the analysis of a great amount of data. However, the need to identify, for instance through the invoice number, what the sales of any given day were may arise. Here is where the functionality known as Drill Through comes in, allowing us to state that a level of detailed data, called "Sales Details" for instance, and resulting from performing the following query on the database, exists.
{code} |
...
In the context of Data Warehousing there are tools such as IdeaSoft O3 geared towards information analysis, also called OLAP tools. These tools usually seek to facilitate analysis through the use of the multidimensional model and generally summarize greats amounts of information in a meaningful set of values that describe reality in the best possible way.
It is precisely this summary of information that lets us face the problem of analyzing large quantities of data in an effective and practical way. However, certain situations require the analysis of some details of the summarized data after identifying one problem area.
OLAP technology in general, and O3 as an implementation of such technology is not geared to handling these levels of detail within the information used for analysis. Among other reasons, because not all the set of available information deserves being analyzed in depth, so that queries made to obtain this information are associated to OLTP technology.
The latter fosters the idea for detailed information to remain in the databases and only be accessed when necessary. That is how functionalities such as Drill Through have appeared, to allow OLAP tool users to perform this type of queries in a simple way.
Drill Through comes from the fact that the typical operations associated to OLAP tools are: Drill Up, Drill Down, Drill To, which stand for going up, down, or to a specific place in one dimension. "Through", in this case means we are plunging into information, idealizing what was mentioned above.
What is "Defining the Drill Through"?
Defining the Drill Through means defining an SQL query that must be performed to obtain specific detailed information. IdeaSoft O3 lets you associate a set of queries to each cube, so as to access data not included in the multidimensional model.
For instance, let's consider the relationships shown in Picture 1 and the multidimensional model shown in Picture 2, a classical sales example. (Obviously very much simplified) where sales are made on a certain date and to a certain customer. A cube generated for the proposed multidimensional model has no individual detail for each sale, as in order to achieve this, a dimension Sales(id_sale) should be present.
The fact of not including the details of sales facilitates the analysis of a great amount of data. However, the need to identify, for instance through the invoice number, what the sales of any given day were may arise. Here is where the functionality known as Drill Through comes in, allowing us to state that a level of detailed data, called "Sales Details" for instance, and resulting from performing the following query on the database, exists.
| Code Block |
|---|
SELECT id_sale, date, id_customer, amount_gross, amount_net
FROM sales
{code}
|
This
...
query
...
returns
...
the
...
total
...
set
...
of
...
sales
...
as
...
a
...
result,
...
so
...
we
...
must
...
establish
...
restrictions
...
to
...
it,
...
in
...
order
...
to
...
obtain
...
a
...
sub-set
...
of
...
the
...
sales.
...
(for
...
instance,
...
for
...
a
...
day
...
and
...
customer
...
in
...
particular)
...
If,
...
for
...
instance,
...
we
...
want
...
to
...
analyze
...
the
...
sales
...
to
...
a
...
customer
...
identified
...
as
...
"1",
...
for
...
the
...
month
...
of
...
"January
...
2003",
...
something
...
we
...
get
...
to
...
by
...
drilling
...
the
...
cube
...
down
...
to
...
year,
...
month
...
and
...
customer,
...
the
...
query
...
we
...
need
...
to
...
make
...
would
...
look
...
like
...
the
...
following
...
one:
...
| Code Block |
|---|
SELECT id_sale, date, id_customer, amount_gross, amount_net
FROM sales
WHERE Year(date) = 2003 AND Month(date) = 1 AND id_customer = 1
{code}
|
To
...
state
...
this
...
from
...
O3,
...
we
...
must
...
indicate
...
the
...
values
...
that
...
are
...
selected
...
in
...
the
...
cube
...
to
...
be
...
introduced
...
to
...
filter
...
conditions
...
in
...
the
...
query.
...
To
...
do
...
so,
...
we
...
add
...
the
...
following
...
expressions
...
to
...
the
...
definition:
...
- In
...
- connection
...
- with
...
- the
...
- "date"
...
- dimension
...
- of
...
- the
...
- multidimensional
...
- model,
...
- and
...
- in
...
- particular
...
- with
...
- the
...
- first
...
- level
...
- (year),
...
- we
...
- find
...
- that
...
- "
...
- Year(date)
...
- =
...
- ?"
...
- is
...
- the
...
- condition
...
- that
...
- must
...
- be
...
- added
...
- to
...
- filter
...
- the
...
- year
...
- of
...
- the
...
- date.
...
- The
...
- "?"
...
- symbol
...
- indicates
...
- where
...
- the
...
- value
...
- selected
...
- in
...
- the
...
- cube
...
- must
...
- be
...
- substituted,
...
- in
...
- this
...
- case
...
- the
...
- year
...
- 2003
...
- As
...
- to
...
- the
...
- "date"
...
- dimension
...
- but
...
- in
...
- this
...
- case
...
- connected
...
- with
...
- the
...
- second
...
- level,
...
- we
...
- find
...
- the
...
- condition
...
- "Month(date)
...
- =
...
- ?"
...
- And
...
- as
...
- to
...
- the
...
- "customers"
...
- dimension
...
- in
...
- the
...
- first
...
- level,
...
- the
...
- related
...
- condition
...
- is
...
- "
...
- id_customer
...
- =
...
- ?
...
- ".
...
This
...
mechanism
...
lets
...
you
...
create
...
extremely
...
powerful
...
queries
...
that
...
are
...
linked
...
to
...
the
...
O3
...
cube
...
enabling
...
you
...
to
...
parameterize
...
the
...
conditions
...
that
...
must
...
be
...
added
...
in
...
each
...
case.
...
It
...
is
...
important
...
to
...
highlight
...
that
...
it
...
is
...
necessary
...
to
...
specifically
...
indicate
...
at
...
which
...
levels
...
of
...
the
...
dimension
...
the
...
values
...
to
...
filter
...
the
...
query
...
will
...
be
...
given.
...
As
...
a
...
result,
...
O3
...
will
...
add
...
the
...
necessary
...
conditions
...
to
...
the
...
query
...
depending
...
on
...
whether
...
the
...
user
...
has
...
filtered
...
certain
...
elements
...
at
...
the
...
time
...
the
...
Drill
...
Through
...
query
...
is
...
made
...
or
...
not.
...
Following
...
you
...
will
...
find
...
the
...
necessary
...
definition
...
details
...
to
...
implement
...
this
...
functionality.
...
...
| unmigrated-inline-wiki- |
|---|
| markup |
{scrollbar}
{ |
| Child pages (Children Display) |
|---|
...