...

OLAP and Relational Reporting Tip or Technique Product(s): IBM Cognos 8

by user

on
Category: Documents
7

views

Report

Comments

Transcript

OLAP and Relational Reporting Tip or Technique Product(s): IBM Cognos 8
Tip or Technique
OLAP and Relational Reporting
Product(s): IBM Cognos 8
Area of Interest: Report Design
OLAP and Relational Reporting
2
Copyright
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated). Cognos ULC
is an IBM Company. While every attempt has been made to ensure that the
information in this document is accurate and complete, some typographical
errors or technical inaccuracies may exist. Cognos does not accept
responsibility for any kind of loss resulting from the use of information
contained in this document. This document shows the publication date. The
information contained in this document is subject to change without notice.
Any improvements or changes to the information contained in this document
will be documented in subsequent editions. This document contains
proprietary information of Cognos. All rights are reserved. No part of this
document may be copied, photocopied, reproduced, stored in a retrieval
system, transmitted in any form or by any means, or translated into another
language without the prior written consent of Cognos. Cognos and the
Cognos logo are trademarks of Cognos ULC (formerly Cognos Incorporated)
in the United States and/or other countries. IBM and the IBM logo are
trademarks of International Business Machines Corporation in the United
States, or other countries, or both. All other names are trademarks or
registered trademarks of their respective companies. Information about
Cognos products can be found at www.cognos.com
This document is maintained by the Best Practices, Product and Technology
team. You can send comments, suggestions, and additions to
[email protected] .
IBM Cognos Proprietary Information
OLAP and Relational Reporting
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
PURPOSE ............................................................................................................4
APPLICABILITY .....................................................................................................4
EXCLUSIONS AND EXCEPTIONS ..................................................................................4
2
OVERVIEW .................................................................................................... 4
3
JOINS ............................................................................................................ 5
4
SET OPERATIONS.......................................................................................... 9
4.1
4.2
USING REPORT STUDIO SET QUERIES .........................................................................9
DIMENSIONAL SET EXPRESSIONS ............................................................................. 11
5
MASTER-DETAIL ......................................................................................... 13
5.1
5.2
5.3
5.4
5.5
LINKING DATA ITEMS ........................................................................................... 14
USING PROMPTS TO PASS A MUN ............................................................................ 15
PASSING VALUES TO FUNCTIONS.............................................................................. 18
DETAIL FILTERS.................................................................................................. 19
VALID MASTER DETAIL COMBINATIONS...................................................................... 22
6
CONCLUSION .............................................................................................. 22
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated), an IBM Company. All rights reserved.
OLAP and Relational Reporting
4
1 Introduction
1.1
Purpose
There are many scenarios where reporting is required across multiple data
sources. For example:
A central data warehouse does not exist and data must be merged
from multiple repositories
The reporting solution operates on a mix of relational data sources for
transactional data and OLAP sources for fast aggregate queries
Multiple OLAP sources representing distinct business processes need
to be combined in a single report
Creating these reports from multiple data sources will leverage several key
concepts and techniques. This document outlines the basic approaches to
several scenarios that will make this multi-source reporting easier to
accomplish.
1.2
Applicability
The techniques listed below leverage functionality introduced within IBM
Cognos 8.
1.3
Exclusions and Exceptions
Listed where applicable below.
2 Overview
The following table outlines the matrix of available methods for combining
different data sources in Report Studio.
Joins
OLAP to
OLAP
OLAP to
RDBMS
RDBMS to
RDBMS
No
Set Operations
(unions, etc.)
Yes
MasterDetail
Yes
DrillThrough
Yes
No
Yes
Yes
Yes
Yes
Yes
Yes
Yes
This document focuses on the methods that will allow reporting from multiple
data sources within a single report: Joins, Set Operations, and Master-Detail.
Drill through is not covered within the scope of this document.
IBM Cognos Proprietary Information
OLAP and Relational Reporting
5
In the table above, RDBMS refers to relational data which may or may not be
modelled dimensionally. Dimensionally Modelled Relational, DMR, packages
have specific behaviours that bridge between OLAP and relational reporting.
The differences regarding DMR will be highlighted in the appropriate sections
below.
3 Joins
Joins in Report Studio are a graphical means to create joins between two
derived tables or query objects where those joins do not already exist in the
Framework Manager model. Typically the joins would be defined in the
Framework Manager model so that report authors would not need to identify
data cardinality and appropriate join keys. Framework Manager allows joins
to be defined between relational queries only.
However, it is difficult if not impractical to model every conceivable join
scenario in Framework Manager. Also, in some situations a report author may
need to generate and join custom data sets in Report Studio that should not
be incorporated into the model due to their single-application nature.
Joins in Report Studio can only be defined between relational queries; other
constructs exist for OLAP queries.
The join cardinality can be used to define outer or inner joins exactly as in
Framework Manager as outlined in the following table*:
Left Table
1..n or 1..1
1..n or 1..1
0..n or 0..1
0..n or 0..1
Right Table
1..n or 1..1
0..n or 0..1
1..n or 1..1
0..n or 0..1
ANSI Join Expression
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
The difference between 1..1 and 1..n is a matter of how many matching
records are in the opposite table, either a maximum of one or a maximum
that can be one or more respectively. The choice will depend on the data
being used. For example, if a report were created with the following data sets
*
Cardinality also plays a role in determining appropriate aggregate rules when querying
multiple fact tables but this topic is outside the scope of the current document. See the
Framework Manager documentation for additional information.
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated), an IBM Company. All rights reserved.
OLAP and Relational Reporting
6
The results are driven by the same fact tables to produce the Quantity values
so the summary total is identical but the results on the right are at a lower
level of granularity.
The natural join key for this data is on the Order year column. From the data
above we can see that each record in the table on the left will match to
multiple records on the right (1..n) while each record on the right will match
to only one record on the left (1..1). However, if the two queries are joined
and the cardinality is incorrectly specified as 1..1 on both sides of the join
then the query engine will not be able to detect the different levels of
granularity and aggregate the values to build the appropriate summary value
for the Quantity measure from the query on the left.
IBM Cognos Proprietary Information
OLAP and Relational Reporting
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated), an IBM Company. All rights reserved.
7
OLAP and Relational Reporting
8
We can see that the results are counting the higher level Quantity values
multiple times and the summary total is inflated from the expected results as
in the original data set. If the cardinality is specified according to the actual
data being used (1..n on the left and 1..1 on the right) then the query engine
can make the proper data associations and aggregate the values (with the
same minimum aggregate function applied to the projected data item) to
generate the correct summary for the higher grain data.
IBM Cognos Proprietary Information
OLAP and Relational Reporting
9
4 Set Operations
There are two ways to apply Set Operations. The first is with the Set Query
Operators available from the Toolbox of the Report Studio Query Explorer.
The second method is to use the Set Expressions available for dimensional
data sources.
4.1
Using Report Studio Set Queries
The second approach to combining data via Set Operations is to use the
Union, Intersect, and Except operators between query objects. These
constructs are available from the Toolbox when using the Query Explorer in
Report Studio.
When using dimensional data sources the set operators use the results of
each query and project multi-dimensional result sets into a set of columns
defined based on the data items which make up the query itself. This means
that if a custom MDX statement is being used then the relationship of rows
and columns will be lost when the data is projected onto a single axis.
The standard SQL rules for unions between queries will apply at this point so
the queries must meet the following criteria:
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated), an IBM Company. All rights reserved.
OLAP and Relational Reporting
10
Data items in each query must occur in the same order. i.e. The first
data item from Query1 will be merged into the column results of the
first data item from Query2.
Data items in each query must have matching data types. i.e. A
character data item cannot be used in a set operation with
corresponding data item which is numeric.
There must be the same number of data items in each query.
Additional rules or restrictions may apply when using an RDBMS
depending on the brand and version of the product. Refer to your
software documentation or vendor for additional information on these
topics.
When using OLAP data sources the union operation will be performed locally
rather than within the OLAP data source itself as there is no corresponding
operation that will match the above union, intersect, or except operators.
This will also be the case when using multiple queries against a single OLAP
source.
However, Relational and Dimensionally Modelled Relational (DMR) data
sources may be processed within the RDBMS if the queries are created using
the same data source connection (although schema and owner qualifications
may vary). Queries using DMR that do not use the same data source
connection will incur local processing as the combined query cannot be sent
to a single RDMBS for processing the union of results outside of it’s own data
scope.
As an example of the Report Studio set operations one query may be defined
to report Revenue for 2004 while a second query may report the Quantity
sold for each Product line over all years.
The two queries could be combined via a Union operator as follows:
IBM Cognos Proprietary Information
OLAP and Relational Reporting
11
The result of this union is the following output where the Year and Product
Line information are reported in the same resulting set along with the
respective Revenue or Quantity sold values:
Note that the projection of the result sets into a new query via a Union will
not preserve the formatting of the individual data items in the source queries.
Formatting must be applied to the appropriate layout objects to separate the
formatting properties for the unique data values in the merged results.
The use of a UNION in this manner is only really applicable for purely
relational models or when using different data sources.
Note that it is not recommended to use a union query when creating disjoint
crosstabs in IBM Cognos 8. When using DMR or a single data source it is
possible to generate disjoint crosstabs simply by dragging the relevant
member or set into the appropriate position of the crosstab.
4.2
Dimensional Set Expressions
The set functions are only available when using dimensional data sources
such as an OLAP data source or a Dimensionally Modelled Relational data
source. These functions are only applicable within a single hierarchy of a
single OLAP data source. The union, intersect, and except functions rely on
the OLAP query engine to process the results and return a single set object
within the results.
An example of the union function is as follows:
union([great_outdoors_company].[Years].[Years].[Year],[great_outdoors_
company].[Years].[Years].[Quarter])
The result of this function would appear as in the following image:
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated), an IBM Company. All rights reserved.
OLAP and Relational Reporting
12
When using these OLAP functions the hierarchy information and detail
member properties can be leveraged later because the processing is
performed within the OLAP data source query engine rather than locally by
the IBM Cognos 8 Report Server. When using the union operator between
individual query objects these properties would be lost when the result set is
projected onto a single axis (to generate a tabular result set).
as identifying the top selling products from this year which were also top
selling products last year (an intersect within the Product hierarchy).
Similarly, a union would serve to combine the detail sales figures at the store
level for a single state with the aggregated sales figures at the state level for
all other states in a specific country (although the same output can be more
easily generated using multiple member sets in IBM Cognos 8).
Other relevant functions using union sets are Hierarchize and Order.
Hierarchize will reorder the results of the union into the order of the members
from the original hierarchy and will display the highest level member first
followed by the associated descendants. The Order function can be used to
sort the union set according to the value of a Measure or text attribute. The
Order function can also leverage the hierarchy information when sorting the
individual members. See the Report Studio User Guide for additional details
or the function tip text in Report Studio for syntax examples.
IBM Cognos Proprietary Information
OLAP and Relational Reporting
13
5 Master-Detail
The concept behind master-detail queries is that one (master) query supplies
a value or member reference that is used to drive a second (detail) query.
The detail query is executed iteratively for each matching value from the
master query. The exception to this process occurs when using a single query
to populate a sectioned list. In this case the grouping of the single query can
be leveraged to section the output for Master-Detail reporting.
There is no interaction between the two queries within the relational or OLAP
data source itself. The interaction of the master-detail queries is handled by
IBM Cognos 8. This processing by IBM Cognos 8 provides the ability to
combine multiple different data sources, as in the table at the start of this
document, with master-detail queries.
There are a few items that need to be covered in order to properly leverage
master-detail queries between different data sources
A report is based on a single model package connection so all the data
sources (and required query subjects for relational sources) must be
defined in a single package.
There must be common data values to relate different queries or
different data sources. i.e. if an OLAP source has Country data that
uses uppercase country names then these will not match directly to
relational data that contains mixed case country names and additional
manipulation of the data will be required. Similar considerations apply
when matching the Business Key of an OLAP source to the Key
column of a relational source. If the key values differ then additional
work must be performed to ensure proper alignment of the data.
The selection of the common data values to relate the master and detail
queries may have many possible candidates. For example, the business key
of an OLAP data source may match an ID field in a relational data source or a
business key in a second OLAP data source. Similar relationships may be built
using other attributes such as a member caption, member description, or
other custom property/role defined within the data.
When planning the creation of new data sources (i.e. a new Data Warehouse
or OLAP data source) there should be some consideration for relating the
data to other existing data sources in the environment. With some foresight,
the appropriate keys can be built into the new data source and allow for easy
creation of master-detail queries within Report Studio.
There are several main methods for generating a link between queries for
your master-detail relationships.
Linking Data Items
Detail Filters
Passing Values to Functions
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated), an IBM Company. All rights reserved.
OLAP and Relational Reporting
5.1
Using Prompts to Generate MUNs
Linking Data Items
The dialog for managing master-detail relationships can be accessed by
selecting an item in your detail query on the layout and selecting “Master
Detail Relationships…” from the Tools menu.
IBM Cognos Proprietary Information
14
OLAP and Relational Reporting
15
The link between the data items is established by clicking the “New Link”
button and then selecting the appropriate matching data items in each query.
Care must be taken to match both data type and data values between the
master and detail queries.
5.2
Using Prompts to Pass a MUN
With the OLAP prompting capabilities we can also pass a Member Unique
Name reference from the master query to the detail query. To enable this
method the detail query needs to be designed with a prompted member data
item. The basic syntax examples of a member prompt are as follows:
Hierarchy Prompt
[Namespace/OLAP Source].[Dimension].[Hierarchy]->?Prompt?
Level Prompt
[Namespace/OLAP Source].[Dimension].[Hierarchy].[Level]->?Prompt?
The difference between a Hierarchy prompt and a Level prompt is that the
Level prompt will restrict the valid selections to the members of the defined
Level while a Hierarchy prompt will allow members to be chosen from any
level within the single hierarchy.
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated), an IBM Company. All rights reserved.
OLAP and Relational Reporting
16
If the Great Outdoors Company sample PowerCube is used to generate a
data item in the detail report called “Target Year” an expression such as the
following might be used:
[great_outdoors_company].[Years].[Years].[Year]->?Current Year?
The master query would also need to use the Year level in its own query so
that the link in the master-detail dialog could be established as below:
In the image above we can see the link from the master query to feed the
MUN reference to the parameter used within the Target Year data item
expression. Note that the link is not established to the Target Year data item
itself. Applying the link to Target Year would define a filter condition to
restrict the entire set of Year members to just one selection. By using the
prompted MUN expression and providing the link to the Current Year prompt
the query is only required to generate the required data set rather than
evaluate all the data and filter out the unwanted results.
IBM Cognos Proprietary Information
OLAP and Relational Reporting
17
Also in the example above we can see the Target Year used in the Rows of
the detail query crosstab. This is not explicitly necessary. The Target Year
data item can be used for any operation within the detail query and does not
necessarily need to be included on the layout at all. In fact, the most
common use for the Target Year would be to define a context filter or slicer
within the detail query. This would be accomplished within the Query view by
dragging the Target Year data item into the slicers area of the query.
This would constrain the data displayed within each instance of the detail
query without having to display Target Year
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated), an IBM Company. All rights reserved.
OLAP and Relational Reporting
5.3
18
Passing Values to Functions
Another method of using parameters is to pass individual values rather than a
MUN object reference. To pass a value the master query must first be able to
provide a value and the detail query must have a function which will accept
the value. Considerations for the master query will be addressed in later
sections of this document.
The most common operation for a master-detail relationship is to constrain
the data for the detail query based on the information from the master query.
With dimensional queries this can be accomplished by using the FILTER
function. Briefly described, the filter function syntax is as follows:
filter ( set_exp , boolean_exp )
Here the set_exp is the set of members that you want to filter and
boolean_exp is the set of criteria which will include or exclude members from
set_exp. The boolean_exp is the location where a value can be leveraged
within the detail query. Using the Great Outdoors Company sample
PowerCube an expression such as the following could be constructed to filter
the Year in the detail report according to the text label (the member caption)
of each year member.
FILTER( [great_outdoors_company].[Years].[Years].[Year] , caption(
[great_outdoors_company].[Years].[Years].[Year] ) =?Current Year?)
Here the Current Year prompt would be supplying a text string while in the
previous section the prompt would be supplying a member unique name
reference. The caption function appears in the expression so that the Boolean
comparison can proceed using arguments of the same data type. If caption
were not included then the comparison would fail as a MUN would appear on
the left of the expression and a text value on the right.
IBM Cognos Proprietary Information
OLAP and Relational Reporting
19
The creation of the master detail linkage is the same as we saw in previous
examples where the master data item is linked to the parameter reference in
the detail query.
The results are also the same as in the prior example when the Target Year is
used to satisfy a slicer member set definition.
Using a Filter function is by no means the only option. There are many other
functions and applications for which this technique will be applicable.
5.4
Detail Filters
Detail filters are primarily a relational query construct. The idea is that a
tabular data set (single dimension/axis) is parsed in a linear fashion and the
records are either included or excluded based on the Boolean filter condition.
As with prior examples, the Boolean condition must be a valid comparison
between the same data types. If a value is passed from the master query to
the detail query then the detail filter must compare another value to the
prompt. If a MUN is passed from the master query to the detail query then a
MUN must be used in the comparison to the prompt.
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated), an IBM Company. All rights reserved.
OLAP and Relational Reporting
20
When using detail filters for dimensional reporting this process must be
understood as the application of a detail filter to a multi-dimensional query
may result in a more complex query than is absolutely necessary. Often other
OLAP functions or techniques, such as the ones above, can be leveraged to
better effect than when using a detail filter.
Similar to prior techniques, a detail filter will also use a parameter to supply a
value to an expression.
Here we can see the Current Year prompt being used within the detail filter.
Again, the caption function is used to convert the member reference from
Target Year to a text value.
If this were a purely relational model without any dimensional modelling
applied then the use of the caption function would not be necessary. Also
note that applying the caption function to a relational query will also return
an error message regarding the application of an OLAP function to a
relational query. With a relational model all query item references will return
values implicitly as there are no member constructs in this scenario.
On the other hand, if both the master and detail queries were OLAP sources
then the caption could also be dispensed. This is because the data items will
be using dimensional objects such as levels and sets to perform the
comparison to the MUN provided by the prompt.
The master-detail relationship is established by linking the data item from the
master query to the Current Year parameter in the detail query. The results
will be the similar to prior techniques.
IBM Cognos Proprietary Information
OLAP and Relational Reporting
21
One caveat when using a dimensional data source is that a slicer member set
will not remove empty records resulting from the context filter while a detail
filter will remove these empty intersections. For example, when using a slicer
to restrict data to 2005 the following data set might result
However, if a detail filter were used to restrict data to 2005 then the results
would be
Notice that the empty columns are not returned when using the detail filter.
Also keep in mind that this same behaviour can be obtained by using a
prompted member unique name or supplying a value to a filter function to
define the columns. Using this prompted MUN or a filter function a user could
select 2005 and the results would be
This is the same result as with the detail filter although with a more direct
approach to processing the required result set.
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated), an IBM Company. All rights reserved.
OLAP and Relational Reporting
5.5
22
Valid Master Detail Combinations
Linked
Data
Items
OLAP Master –
OLAP Detail
OLAP Master –
Relational Detail
Relational Master
– Relational Detail
Relational Master
– OLAP Detail
Prompted
MUN
Passing
Values to
Functions
Detail Filter
Yes
Yes‡
Yes†
Yes‡
Yes†
No
Yes†§
Yes†
Yes
No
Yes§
Yes
Yes**
No
Yes
Yes**
6 Conclusion
The presence of different data sources is no longer a limitation to reporting.
The use of Joins, Set Operations, and Master Detail queries allow multiple
data sources to be integrated into a single report.
Care must be take with these techniques to ensure that conformed data is
being used. Conformed data simply means that the references or values from
one data source match up to the references or values from another data
source. This is particularly so when merging data from both OLAP and
relational data sources as. However, by leveraging appropriate functions
there are often ways to create or ensure that the data types and values will
match between the different portions of your report.
The techniques in this document will allow you to create very sophisticated
reports and will be highly applicable to many applications where data is not
consolidated in a single warehouse or OLAP repository. Such applications
include corporate dashboards, burst reporting, and reporting on both realtime transactional data and periodic summarized data, to name a few.
†
The master query must contain a data item which defines a value rather than a MUN. This
can be accomplished in many ways but the roleValue and caption functions are some of the
more common options.
‡
If using different OLAP sources then the MUN references will likely not match and the
relationship will not succeed. In such a case the detail filter or function should be redefined to
use values/attributes rather than MUNs.
§
The detail query is relational so the FILTER function cannot be used to restrict data.
However, the master detail relationship will still allow for the values to be used in other
applications.
**
The detail query must contain a data item which defines a value rather than a MUN. This
can be accomplished in many ways but the roleValue and caption functions are some of the
more common options.
IBM Cognos Proprietary Information
OLAP and Relational Reporting
Copyright © 2008 Cognos ULC (formerly Cognos Incorporated), an IBM Company. All rights reserved.
23
Fly UP