...

IBM Cognos 8 Framework Manager - Modeling for Combination Analysis - HR

by user

on
Category: Documents
4

views

Report

Comments

Transcript

IBM Cognos 8 Framework Manager - Modeling for Combination Analysis - HR
Tip or Technique
IBM Cognos 8 Framework
Manager - Modeling for
Combination Analysis - HR
Example
Product(s): IBM Cognos 8 BI - Framework Manager
Area of Interest: Modeling
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
2
Copyright and Trademarks
Licensed Materials - Property of IBM.
© Copyright IBM Corp. 2009
IBM, the IBM logo, and Cognos are trademarks or registered trademarks of
International Business Machines Corp., registered in many jurisdictions worldwide.
Other product and service names might be trademarks of IBM or other companies. A
current list of IBM trademarks is available on the Web at
http://www.ibm.com/legal/copytrade.shtml
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. IBM does not accept responsibility for any kind of loss resulting from the use
of information contained in this document. The information contained in this
document is subject to change without notice.
This document is maintained by the Best Practices, Product and Technology team.
You can send comments, suggestions, and additions to [email protected]
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
3
Contents
1
INTRODUCTION ............................................................................................ 4
1.1
1.2
1.3
1.4
PURPOSE ................................................................................................................ 4
APPLICABILITY ......................................................................................................... 4
ASSUMPTIONS.......................................................................................................... 4
EXCLUSIONS AND EXCEPTIONS ..................................................................................... 4
2
OVERVIEW .................................................................................................... 4
3
STITCH QUERIES EXPLAINED....................................................................... 5
4
HR DATA EXAMPLE........................................................................................ 7
5
FACT COMBINATION ANALYSIS SOLUTION ............................................... 10
6
CONCLUSION .............................................................................................. 14
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
4
1 Introduction
1.1 Purpose
This document will illustrate a technique to avoid full outer joins in cases where
multi-fact combinations rather than aggregations are desired. This type of reporting
commonly occurs with human resource (HR) data, but also occurs in various other
types of subject areas.
1.2 Applicability
This technique was written and tested using IBM Cognos 8 BI version 8.4.
1.3 Assumptions
This document assumes experience with IBM Cognos 8 Framework Manager and
relational database design, in particular star schemas (fact tables and related
dimension tables).
1.4 Exclusions and Exceptions
The technique described in this document applies to relational data sources and
affects aggregation results. It should only be used in unique instances where
required and report consumers must be educated regarding the aggregation results
they will see in the reports.
2 Overview
In certain situations, report consumers would like to analyze combinations of
disparate facts in the context of some common dimensional attributes rather than
treat each fact independently in a sub query and then merge the results together.
The latter is what IBM Cognos 8 does when performing a multi-fact query and is
referred to by IBM Cognos as a "stitch query". Stitch queries are explained in more
detail in the next section.
Some types of analysis can present scenarios where stitch queries are not desired.
This is sometimes the case with HR data. For example, rather than seeing how many
people speak Spanish as a secondary language in a division of the organization as
well as how many people are sales representatives in an organization, analysts may
want to see how many Spanish speaking sales representatives there are in a
division; a direct correlation between the two. Stitch queries don't always present a
one-to-one correlation between the facts returned. This will be illustrated in a
subsequent section.
This document will present a technique that will avoid stitch queries and allow for
combination analysis.
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
5
3 Stitch Queries Explained
When queries are performed in IBM Cognos 8 that request facts from multiple
underlying database tables, the query engine performs a stitch query. Stitch queries
consist of sub queries, one for each disparate fact, which are then merged together
on their common attributes.
Examine the report below:
Notice the Return quantity column has a null value in the last row. There are no nulls
in the Returned items table in the database. Why is there a null in the report?
Quantity and Return quantity are each facts taken from a separate query subject and
have Year from Time dimension and Product name from Products in common.
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
6
IBM Cognos 8 generates two subqueries which are then merged together to fulfil the
parent query which populates the report.
Pseudo SQL:
Select
coalesce(D2.Year1,D3.Year1) as Year1,
coalesce(D2.Product_Name,D3.Product_name) as Product_name,
D2.Quantity as Quantity,
D3.Return_quantity as Return_quantity
from
(Sub query 1) D2
full outer join
(Sub query 2) D3
on
((D2.Year = D3.Year) and (D2.Product_name = D3.Product_name))
In the pseudo SQL example above, the coalesce function is used to return the first
non-null record set from the sub queries. If both are null, then no record is returned.
If one is null and the other is not, then a record will be returned, but the sub query
that had no match will display a null value.
Examine the results of each sub query. First, look at the sub query results that
retrieve data for the Quantity fact.
There are four records. Now examine the sub query results for the Return quantity
fact.
Notice that there are only three records. There were no returns for 2007.
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
7
In a stitch query however, when there are more records in one sub query than the
other, nulls will be returned for the rows where there is no match as seen below:
IBM Cognos 8 uses cardinality defined in Framework Manager to identify which query
subjects are facts (fact detection). Query subjects that have only a 1..n or 0..n
cardinality attached to them in the context of a query will be considered facts. If two
or more facts are found in the context of a query, a stitch query will be performed
provided a shared/conformed dimension is also a part of the query.
In the majority of cases, these stitch queries are required to bring back all required
data and aggregate the results correctly. However, there may be instances when fact
detection and stitch queries are not desired.
4 HR Data Example
Examine the following HR model:
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
8
Staff members belong to a division. Each staff member speaks the company's
primary language, English, and may also speak a secondary language, which is
tracked in the StaffLanguageFact table. Each staff member also has one or more
skills, such as being able to work on the sales floor or at customer service. These
skills are tracked in the StaffSkillsFact table.
In this model scenario, Staff and Division are both shared/conformed dimensions
between the two fact tables, which would enable a stitch query should both facts be
requested in a single query. The Language and Skills tables are nonshared/conformed dimensions as they are only related to their respective fact tables
and not other fact tables.
The following is a report output when reporting which divisions have which skills and
which secondary spoken languages.
In this report output, it appears that the Furniture division has one person who
speaks Spanish as a secondary language and one or two people who can act as a
cashier or customer service representative. The Home Electronics division has one or
two people that can speak French and Spanish and also have customer service skills
and sales skills. It is possible that the same person can have more than one
language or skill as seen below when StaffName is added to the report.
Here you can see that indeed, the same person can speak more than one secondary
language and have more than one skill. So over all, the report, through a stitch
query, represents accurate summary totals where both divisions have a total of three
secondary languages spoken and a total of four skill sets.
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
9
Examine the SQL snippet below to see the stitch query (…… represents removed SQL
in order to shorten the example):
select
coalesce(D2.DivisionName,D3.DivisionName) as DivisionName,
coalesce(D2.LastName,D3.LastName) as LastName,
coalesce(D2.FirstName,D3.FirstName) as FirstName,
D2.LanguageName as LanguageName,
D2.LanguageCount as LanguageCount,
D3.SkillName as SkillName,
D3.SkillCount as SkillCount,
XMIN(D2.Total_LanguageCount_ ) as Total_LanguageCount_,
XMIN(D3.Total_SkillCount_ ) as Total_SkillCount_
from
(select
……
) D2
full outer join
(select
……
) D3
on
((((D2.DivisionName = D3.DivisionName) and
(D2.LastName = D3.LastName)) and (D2.FirstName = D3.FirstName))
and (D2.sc = D3.sc)) order by DivisionName asc
This SQL indicates, through the use of the coalesce function in combination with the
full outer join, that two sub queries (D2 and D3) are sent to the database and then
merged on the common items from the shared/conformed dimensions, in this case
DivisionName from Division and FirstName and LastName from Staff.
In this scenario however, each of the facts are not correlated to one another. For
example, the report tells us that Maria Valenzuela speaks Spanish and has two skills,
Cashier and Customer Service, but it does not indicate that there is a Cashier as well
as Customer Service representative that can speak Spanish in the Furniture division.
In other words, the report is not showing language and skill combinations.
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
10
5 Fact Combination Analysis Solution
Looking at the diagram again, it is clear that the IBM Cognos 8 query engine is using
fact detection through the cardinality settings on StaffLanguageFact and
StaffSkillsFact.
Both have only n cardinalities attached. In order to achieve combinations of facts
based on the dimension attributes included in the query, fact detection must be
removed. This is done by changing the n cardinalities to 1 on the fact side.
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
11
Before this is done however, it is important to examine the nature of the data. In this
example, Division and Staff are separate shared/conformed dimensions. Yet each
staff member must belong to a Division. In this case, a hierarchy of data can be
established rather than two separate shared/conformed dimensions. This can be
accomplished in two ways. The tables can be merged and flattened in the data
source to include the division and staff hierarchy in one table, or a relationship can
be established from Division to Staff (snowflake dimension), as shown below, instead
of Division to each of the facts:
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
12
The next step is to change the cardinality from Staff to the facts to one-to-one as
shown below:
Now the fact tables will not be perceived as facts by the IBM Cognos 8 query engine
and a stitch query will not be performed. Instead all record combinations will be
returned allowing for combination analysis as seen in the report output below:
The report now shows two entries for Spanish for Maria Valenzuela; one combination
for each skill she possesses. The same applies for John Smith. There are now four
records; one for each language and skill combination.
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
13
It is important to note the summary totals for this report. This number now
represents the total number of combinations and not the total number of languages
spoken or skills for the divisions as in the previous report. Authors and consumers
must be aware of this.
The SQL generated for this type of report appears below:
select
Division.DivisionName as DivisionName,
Staff.LastName as LastName,
Staff.FirstName as FirstName,
Language_.LanguageName as LanguageName,
XSUM(StaffLanguageFact.LanguageCount for
Division.DivisionName,Skills.SkillName,Language_.LanguageName,Staff.
LastName,Staff.FirstName ) as LanguageCount,
Skills.SkillName as SkillName,
XSUM(StaffSkillsFact.SkillCount for
Division.DivisionName,Skills.SkillName,Language_.LanguageName,Staff.
LastName,Staff.FirstName ) as SkillCount,
XSUM(XSUM(StaffLanguageFact.LanguageCount for
Division.DivisionName,Skills.SkillName,Language_.LanguageName,Staff.
LastName,Staff.FirstName ) at
Division.DivisionName,Skills.SkillName,Language_.LanguageName,Staff.
LastName,Staff.FirstName ) as Total_LanguageCount_,
XSUM(XSUM(StaffSkillsFact.SkillCount for
Division.DivisionName,Skills.SkillName,Language_.LanguageName,Staff.
LastName,Staff.FirstName ) at
Division.DivisionName,Skills.SkillName,Language_.LanguageName,Staff.
LastName,Staff.FirstName ) as Total_SkillCount_
from
( HRSample.HRSample.Conformed.Skills Skills join
HRSample.HRSample.Person.StaffSkillsFact StaffSkillsFact on
(Skills.SkillID = StaffSkillsFact.SkillsID) join
HRSample.HRSample.Conformed.Staff Staff on (Staff.StaffID =
StaffSkillsFact.StaffID) join HRSample.HRSample.Conformed.Division
Division on (Division.DivisionID = Staff.DivisionID) ) left outer
join ( HRSample.HRSample.Conformed."Language" Language_ join
HRSample.HRSample.Person.StaffLanguageFact StaffLanguageFact on
(Language_.LanguageID = StaffLanguageFact.LanguageID) ) on
(Staff.StaffID = StaffLanguageFact.StaffID)
group by
Division.DivisionName,
Staff.LastName,
Staff.FirstName,
Language_.LanguageName,
Skills.SkillName
order by
IBM Cognos 8 Framework Manager - Modeling for Combination Analysis HR Example
14
DivisionName asc
In this SQL, in the "from" clause, one can see all the tables are joined together,
thereby bringing back every record combination. This is quite different than the
stitch query where two sub queries were merged through a full outer join.
6 Conclusion
Using the technique described in this document, combination analysis can be
achieved. However, it is important to note that report summary footers reflect the
total of combinations, not the actual fact count per dimension. Understanding the
authors' and consumers' needs is imperative. It may be that some require
combination analysis while others require actual counts as seen in the example with
the stitch query. If this is the case, then presentation views should be provided to
satisfy both needs. One view will be based on underlying objects that produce a
stitch query while the other view is based on underlying objects that have one-toone cardinalities specified to achieve combination analysis. This can be achieved
through shortcuts or model query subjects with different relationship configurations.
Fly UP