...

Designing EP cubes for best use in Powerplay Proven Practice

by user

on
Category: Documents
1

views

Report

Comments

Transcript

Designing EP cubes for best use in Powerplay Proven Practice
Proven Practice
Designing EP cubes for best use in
Powerplay
Product(s): IBM Cognos PowerPlay, IBM Cognos
Contributor
Area of Interest: Modeling
Designing EP cubes for best use in Powerplay
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] .
Cognos Proprietary Information
Designing EP cubes for best use in Powerplay
3
Contents
1
POWERPLAY AND PLANNING ....................................................................... 4
1.1
1.2
1.3
FROM CONTRIBUTOR ADMIN.....................................................................................4
FROM CONTRIBUTOR CLIENT ....................................................................................4
FROM BI ............................................................................................................4
2
HIERARCHIES / DIMENSIONS / D-LISTS..................................................... 5
2.1
2.2
2.2.1
2.2.2
2.2.3
2.3
2.4
2.5
2.6
2.7
DLIST NAMING .....................................................................................................5
FLATTENING CALCULATION HIERARCHIES ......................................................................6
Example: ..........................................................................................................6
Resolution 1:.....................................................................................................6
Resolution 2:.....................................................................................................7
NA'S BEING DISPLAYED ...........................................................................................7
EMPTY ‘MEASURES’ DIMENSION .................................................................................8
ALTERNATIVE DRILL PATHS ......................................................................................8
AVOIDING SPURIOUS DRILL PATHS ..............................................................................8
REPLICATING SPECIAL TIME CATEGORIES .....................................................................9
3
GENERAL NOTES ........................................................................................... 9
3.1
3.2
3.3
3.4
TESTING.............................................................................................................9
ROLLUPS ............................................................................................................9
FORMATS............................................................................................................9
PUBLISH TO POWERPLAY ENTERPRISE SERVER ............................................................. 10
Cognos Proprietary Information
Designing EP cubes for best use in Powerplay
1
4
PowerPlay and Planning
One thing you can be certain of in a Planning BI project is that the data will change
much more often than it does in a traditional project. In order to reduce the impact
this could have on your BI environment, Cognos EP includes several integration
points between PowerPlay and EP:
1.1
From Contributor Admin
1.2
Publish to PowerPlay Enterprise Server
Creates cube and Transformer model from Published1 data
Enables ‘traditional’ cube snapshots to be taken at points in time
From Contributor Client
View in PowerPlay web
Live access to the Contributor data in PowerPlay Web, launched directly
from the Contributor client (can also be launched manually using a URL)
Export to PowerPlay Client
1.3
Dynamically creates local PP Client cube. Requires PowerPlay on users
desktop
From BI
PowerPlay Connect
To Analyst or Contributor
Note: The PowerPlay Connect options for EP are turned off by
default. You can enable them in Configuration manager. Look
under the OLAP Data Access section.
Analyst is a Windows Client product so Analyst Connect can only
be used with PowerPlay for Windows
Build it yourself (not automated)
From the Publish tables, the traditional way using Transformer
(gives you full control, but you would have to change your Transformer
model when the EP model changes)
Cognos Planning dimensions contain certain planning-specific features which are
slightly different to standard PowerPlay functionality. So, when PowerPlay connects
to Planning cubes it has to make assumptions when mapping itself over the EP data.
This document explains how to interpret and make the most of these assumptions.
1
Publishing is the process that exports the multidimensional Planning data to a star schema
database
Cognos Proprietary Information
Designing EP cubes for best use in Powerplay
2
5
Hierarchies / Dimensions / D-Lists
For the purposes of this document, assume that the words D-List and Dimension
are interchangeable.
Cognos Planning dimensions contain certain features which optimise the environment
for data entry. PowerPlay is a read-only analysis environment, so does not contain
some of these features. As a result, when PowerPlay connects to Planning cubes it
has to make assumptions as to how to map itself over these EP structures.
Sometimes this results in your EP cubes looking a little odd when you first view them
in PowerPlay, but in most cases some subtle changes in your EP design will resolve
this.
Here are some of the more common issues and tips:
2.1
DList naming
Issue:
Unusual names on dimension bar
Why:
If EP hierarchies do not sum into a single top total, PP creates a virtual
top level item using the naming convention “All_” + D-List name
The D-lists in the above example were originally called ‘5 Version’ and ‘1
P&L’
Resolution:
If you create a top level subtotal in a D-list, the name of this subtotal will
be used to label the dimension in PowerPlay.
So, add your own top level. In the example above, we manually created a
top level in the time dimension using the formula:
Full Year = Jan + Feb + Mar + Apr + May + ….
You can take advantage of the resolution to this issue to make cubes easier to
understand. Use the same naming convention for all the top level subtotals in your
D-Lists (All Years, All Products, All Countries etc).
Note: It used to be common practice when developing EP models to prefix D-List item names
with a number to indicate the type of list (1 to 5). In the above example, Versions is a Type
5 D-list, so was named 5 Versions, resulting in the All_5 Versions naming seen above.
“All five versions? But I only have two…”
The practice of numbering dimensions is less popular these days. This is partly because
Cognos Planning has become more open, resulting in D-List names becoming more visible
than they used to be. For example, the Excel add-in for Contributor displays them in its slice
and dice panel.
Cognos Proprietary Information
Designing EP cubes for best use in Powerplay
2.2
6
Flattening Calculation hierarchies
Issue:
Unwanted hierarchies created for
d-lists containing calculations
Why:
When mapping onto EP, PowerPlay uses subtotals and calculations to
identify hierarchies
EP doesn’t separate calculation hierarchies from normal ones
The variance calc in this example is therefore being translated into a
hierarchy
2.2.1
Example:
Here is an example calculation D-List:
A
B
C
D = A/B * 100
E = TimeSum(C,D)
This is how the resulting hierarchy will be translated by PowerPlay if
you don’t use one of the techniques below.
E 2.2.2
C
D A
B
Resolution 1:
Only the first reference to an item in a calculation list is used in the
hierarchy. Use this to your advantage….
Add a new item at the start of the list with a formula that includes all of
the items in the list, for example: 0 * (<sum of all the fields>)
e.g. Versions = 0 * ({Current Version}
+ {LY Actual} + Variance + {Previous Version}))
This will creates a new flat dimension structure like this:
Cognos Proprietary Information
Designing EP cubes for best use in Powerplay
7
Note that this involves adding an entry into a list in the Planning model.
This may not always be desirable because, depending on the change, it
may make the model less intuitive to use and/or navigate. If this is the
case, Resolution 2 may be an option.
2.2.3
Resolution 2:
Create a separate Planning cube, specifically for reporting. This cube
would exclude the calculations so would not suffer from these issues
If immediate reporting is not required, this cube could potentially live in a
separate reporting-only model, refreshed by scheduled system links.
This option would also be appropriate when:
The calculation list contains interim calculations that you do not
want to display in PowerPlay
The additional item in your data entry model suggested by
Resolution 1 could be confusing for users in a data entry context
2.3
NA's being displayed
Issue:
You keep getting NA’s displayed in the PowerPlay grid
Why:
There is genuinely no data at that level
Common with Scenario dimensions or other hierarchies with no single top
level
It is functionally correct, but irritating when exploring a model
Resolution:
Use a variation of the Flatten Hierarchy technique
New formula: <Default Version> + (0 * <All the other versions>)
E.g. Versions = {Current Version}
+(0 * ({LY Actual} + Variance + {Previous Version}))
Cognos Proprietary Information
Designing EP cubes for best use in Powerplay
8
If top level of Versions is selected, it will now default to Current Version…
2.4
Empty ‘Measures’ Dimension
Issue:
Measures dimension is sometimes empty when reporting from EP cubes
Why:
PowerPlay cubes have a single Measures dimension,
EP cubes can have several.
Unless you tell it which one to use, PowerPlay won’t know which D-list is
the ‘official’ Measures dimension, so it leaves it empty
This doesn’t cause any data problems, it just looks a little odd
Resolution:
The PowerPlay extensions (even those that don’t report from the Publish
Tables) use the Dimension for Publish to identify which D-list to use as
the Measures dimension
Dimension for Publish is primarily used to arrange the layout of the Publish tables,
but is also used by the PowerPlay extensions to specify which D-List should be
placed into the Measures dimension. You can set the Dimension for Publish in the
Application Maintenance menu in Contributor Admin Console. Remember to GTP to
make your changes live.
NOTE: Changing Dimension to Publish has wide-ranging implications beyond this, so
do not change it without first checking with your reporting team!
2.5
Alternative Drill Paths
If any of your D-List items subtotal into more than one higher level, PowerPlay will
create alternative drilldown paths in the dimension viewer. However, these do not
work in quite the same way as their PowerPlay counterparts.
For example, in a D-List with a split hierarchy (resulting in more than one drill path),
the first hierarchy in the list is the only one taken to root level in PowerPlay. The
second only goes down to the level at which it stops becoming unique.
E.g. Time dimension: Hierarchy1: All Years Years Quarters Months
Hierarchy2: All Years by Months Years Months
If declared in this order, Hierarchy2 would stop at the Years level in PowerPlay.
If declared the other way round, Hierarchy 1 would stop at Quarters.
2.6
Avoiding spurious drill paths
To avoid spurious alternative drill paths as described above, you should be cautious
to keep your D-List hierarchies consistent. For example, a time dimension should
aggregate as follows:
Cognos Proprietary Information
Designing EP cubes for best use in Powerplay
9
Months into Quarters into Years
not
Months into Quarters and Years
This is best practice anyway (the second option would take longer to calculate), but
it is easy to get wrong, particularly if the Quarters level was added as an
afterthought. EP still returns the correct results, so it is not immediately obvious that
there is a problem.
One way of checking for these is to Publish the data, and then check for any items
whose leaf_code (in the SY table) are not set to zero. If you find any items in noncalculation hierarchies that you believe should be balanced, double-check the
subtotals in the Analyst model.
2.7
Replicating Special Time Categories
When building standard PowerPlay cubes, Transformer can automatically create
‘special’ alternative hierarchies within the time dimension, such as YTD and Current
Month. These are not created when PowerPlay is connected to Planning cubes
However, this functionality can be partly replicated by manually creating your own
Calculated items in Analyst. These will appear as alternative drills in PowerPlay.
3
General Notes
3.1
Testing
When making PowerPlay-driven changes to EP models, you don’t have to go through
a lengthy Synchronise/GTP/Publish process in order to test your changes. Just attach
PowerPlay Windows to your Analyst model using PowerPlay Connect. Once you are
happy with the impact of your changes, take them into Contributor and retest, as
you may find some minor differences between the Web and Windows environments.
3.2
Rollups
Cognos Planning supports a variety of hierarchy rollups, including weighted averages
and category-specific, manually defined calculations. Some of these are not standard
PowerPlay rollups.
As a result, all the automated PPEP connection methods use PowerPlays facility to
use ‘external rollups’ i.e. they load their rollups directly from the Planning data, so
that rollups that are not supported by PowerPlay will still return the correct values
when analysed.
3.3
Formats
Analyst formats are not carried through into PowerPlay.
Numeric values are displayed with two decimal places. Percentages are not displayed
with the percentage sign, so ensure that your item labels are explicit. Other Analyst
formatting properties such as Blank When Zero are not applied.
View in PowerPlay Web scales percentages differently to the other PowerPlay
extensions (it shows 4.5% as 0.04, rather than 4.50), so you may need to review the
scaling used by your calculations depending on your preferred reporting method.
PowerPlay only reports numeric data, so Text, Date and D-List formatted items are
ignored, except in the Publish to PowerPlay Enterprise Server extension, which
includes them with zero values.
Cognos Proprietary Information
Designing EP cubes for best use in Powerplay
10
If you have ‘layout’ items within your calculation list (such as blank lines), ensure
that these are formatted as Text so that they don’t end up as Zero items in
PowerPlay. Contributor techniques such as using vertical bars (|) to create blank
spaces are not valid in PowerPlay.
If you use Publish to PowerPlay Enterprise Server to create your base Transformer
model, you can override formats within the Transformer model but refer to the later
section on this extension for things to watch out for in this environment.
3.4
Publish to PowerPlay Enterprise Server
The ‘Published data’ option of this Contributor extension allows you to automatically
generate a transformer model containing the definition of selected cubes within the
model. If you wish, you can then choose to modify the generated model to apply
formats and remove unwanted items, prior to Publishing the cube to the web.
However, be wary when doing this. The model is updated with the latest metadata,
which is great, but it will be overwritten each time the extension is run, so any
changes that you make will not be saved. Also, the cubes will have been created
using external rollups, so you won’t easily be able to change or create alternative
hierarchies in Transformer.
Cognos Proprietary Information
Fly UP