Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

Exclusive offer: get 50% off this eBook here
IBM Cognos 8 Planning

IBM Cognos 8 Planning — Save 50%

Engineer a clear-cut strategy for achieving best-in-class results using IBM Cognos 8 Planning with this book and eBook

$35.99    $18.00
by Jason Edwards Ned Riaz Rich Babaran | August 2009 | Architecture & Analysis

In this article by Ned Riaz, Jason Edwards, and Rich Babaran, we will explain how planning data that has been entered and saved in the Contributor Web Client can be made available for reporting purposes by using the publish process. We then explain the different options that can be used for accessing this data for reporting in real time and through the publish process. After reading this article, you should be able to:

  • Make the user-submitted plan data available, by using the Contributor Web Client, for reporting, analytics, and export this data to other databases.
  • Understand how to access this data for IBM Cognos BI reporting in real time or through the publish process

When your users save and submit plans on the Contributor Web Client, Contributor saves and stores this data in XML format in a relational database. The stored data needs to be translated into a format that is easily readable and accessible to other IBM Cognos tools and databases. The publishing feature in Contributor works like a translator and converts the XML format data into a readable format.

Accessing planning data

There are two elementary methods in which planning data can be accessed for reporting. The first method allows you to access real-time data either by creating a planning package during a GTP or through the Planning Data Service (PDS). The data is revealed by opening up a slice of the application. This process is slow and is better suited to ad hoc reporting rather than for full-scale reporting purposes. The second method involves moving the planning data to a separate star schema datastore by using the publish process and reporting off of this database. This option is far more suited to reporting and ETL purposes.

Publish

When your users save and submit plans on the Contributor Web Client, Contributor saves and stores this data in XML format in a relational database. The stored data needs to be translated into a format that is easily readable and accessible to other IBM Cognos tools and databases. The publishing feature in Contributor works like a translator and converts the XML format data into a readable format. Publishing is an administrative task, and it is executed by the Contributor job system. You, as an administrator, can either manually publish data or automate the publishing task by using a Contributor macro. The frequency of data publishing is dictated by the needs of the consumers' tools, such as IBM Cognos Report Studio, or as an enterprise data warehouse.

Although Contributor-published data can be used for various purposes, the following are the most common business uses of published data:

  • Reporting plan data using IBM Cognos reporting tools, such as Report Studio, Analysis Studio, and Query Studio
  • Performing additional analysis on submitted plan data by using IBM Cognos Planning Analyst
  • Loading plan data back into a general ledger or ERP system

Storing published data

Contributor stores published data in a separate datastore, which IBM Cognos documentation refers to as the 'publish container'. Unlike the Contributor application datastore, which is a transactional database, the publish container has a different life cycle and contains a significantly different storage and performance profile. There are two different types of publish containers available, and we will examine both types of containers in the next section. The two types of containers are:

  • The Table-only Layout Publish Container
  • The View Layout Publish Container

The following are the steps required to create a publish container. Note that the steps to create a publish container apply to both types of publish layouts.

  1. Select the application and click on the Production branch.
  2. Select either the Table-only Layout or the View Layout from the Publish branch.
  3. Click on the Configure button on the Option tab. You may or may not have the required rights to create the publish container. See the following section.
  4. Click on the Create New button on the Select Publish Datastore Container screen.

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  5. Click on the Star icon on the Configure Datastore Server Connection screen. This opens the Create a New Publish Container screen.

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  6. Type the name of the publish container and the location of the database files on the Create a New Publish Container screen. Click on the Create button.
  7. Click on the Test Connection button on the Configure Datastore Server Connection screen to test the configuration. Click on OK twice on the next two messages.

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  8. Add this new publish container to the Job system by opening the Job Server Cluster branch and selecting a cluster or job server. You are ready to publish data.

Who can publish

You need the following rights to successfully complete the administrative duties related to creating the publish container and publishing data.

  • Access Rights: This gives you rights to perform publishing tasks. You can configure these rights on the following Access Rights screen. Note that the default Access Rights should work in most cases.

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  • Database Rights: Published data is stored in a database (called Container). The account that creates the publish container needs database creation rights (DDL) to create and modify a publish container. If this account does not have DDL rights, you can ask the database administrator to create and modify a publish container. In the Contributor Administration Console, you can generate a script for the database administrators, so that they can create and modify a publish container.

Publishing using the Table-only layout

The recommended Table-only layout is an optimized publish layout for IBM Cognos BI reporting tools. As discussed later in this chapter, the Framework Manager (FM) model, which uses the Framework Manager Extension, requires this layout. You can also transmit data from the Table-only layout published tables to external databases, such as data mart or data warehouse.

Several tables are created when you run the Table-only publish layout. Three important types of tables are D-List items tables, hierarchy tables, and D-Cube export tables. The following is a description of these tables. You can create reporting models from these key tables to report on planning data.

  • D-List items tables (it_table): One table is created for each D-List in the planning model. Each item table describes the contents of a D-List. For example, you may find a month item table storing month details, such as Jan, Feb, Mar, and so on.
  • Hierarchy tables (sy_ and cy_table): The two most commonly used hierarchy tables are cy_ (Calculated hierarchy) and sy_ (Simple hierarchy). In most of the cases, the contents of these two tables will be very similar. Derived hierarchy lists found in sy_ table allow reporting tools to automatically generate summaries for each level of the hierarchy. The complete hierarchy lists found in cy_table are intended to be used when a D-List contains complex calculations between D-List items. You can use complete hierarchy lists, which are already in the Planning application, to avoid having to recreate calculations in your IBM Cognos 8 report.
  • Export tables (et_table): One table is created for each D-Cube in the planning model. For example, you may find the Sales cube export table when we publish the Sales cube of the ABC Company's model.

The following are the steps to publish the Table-only Layout:

  1. Select Production|Publish|Table-only Layout.
  2. Select the cube that is to be published, and then select a dimension to be published. The dimension for publish reduces the data volume to be published. It provides the measure dimension for the reporting environment. The measure dimension is typically referred to as a calculation D-List in the planning model, for example, the PL D-List in the ABC Company model. The following illustration identifies the differences in the table structures when you choose or do not choose a dimension for publish:

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  3. Select the e.List items to be published. As a minimum, you must have reconciled all e.List items in an application before you execute the publish job.
  4. Select the options to be used when publishing.
  5. Click on the Publish button. The program will create a publish job. You can monitor the publish job by using the Monitor Console branch.

Publishing data changes (incremental publish)

When you publish data using the Table-only Layout structure, the program takes a snapshot of the data entered in the Contributor Web Client, and then publishes and stores this in the published tables. Practically, you select all nodes to publish, even though you can choose to publish selected nodes. Depending on the number of e.List items being published, and the availability of job servers, a publish job can take anywhere from a few minutes to many hours. Because of the batch nature of the publish mechanism, a latency period exists between the time that users input plan numbers in the Web Client, and the time when the program populates the plan numbers in the publish container by using the publish feature. Because of this latency, it was impractical to produce a real time reporting solution in versions prior to 8.2, especially when IBM Cognos reporting studios relied on planning published containers.

The incremental publishing feature, also called trickled publishing, solves this real-time data publish and reporting problem. Instead of publishing all nodes, the incremental publish scans data changes and publishes only the changes.

For example, assume there are five e.List nodes in an application. When you publish the application using the Table-only Layout option, the program publishes all nodes. (We assume that you have selected all nodes to be published.) Now, assume that a user enters revised plan numbers in e.List node 4. Without the incremental publish feature turnedon, you must publish all nodes, as you, as an administrator, cannot tell who has entered or revised planning numbers on the Contributor Web Client, or when they did this. However, when you have incremental publish turnedon, the program would publish only e.List node 4.

It is important to note that a stabilized application will get the most benefit from the incremental publishing feature. If your application requires significant changes, such as e.List updates or model structural changes, you have to republish all nodes before you go back to using the incremental publish feature.

To accomplish real-time publishing and reporting, you have to configure the following items:

  • Configure the Table-only Layout publish container
  • Publish all nodes by using the Table-only Layout publish
  • Configure incremental publish
  • Configure macro incremental publish
  • Schedule the macro by using the Scheduling feature in the IBM Cognos Connection

Publishing using the View Layout

The View Layout was the only type of publishing available in IBM Cognos Planning version 7.2 and earlier. IBM Cognos kept this layout for its backward compatibility, as many applications and models are dependent on this feature. You can also transmit data from the View Layout published tables to external databases, such as data mart or data warehouse. Some differences between both layouts are noted in the following table:

Table Layout

View Layout

Greater flexibility in reporting on planning data

Intended for backward compatibility

Source to other data mart and source systems

Source to other data mart and source systems

Required by Generate Framework Manager Model Admin Extension

Slower publish performance and inefficient data storage

Employs better naming conventions

 

 

The following are the steps to publish the View Layout:

  1. Select Your Application | Development | Application Maintenance | Dimensions for Publish. The selection of dimension for publish is optional in the View Layout publish.
  2. Execute the GTP.
  3. Select Your Application | Production | Publish | View Layout.
  4. Create a new container, if one has not yet been created. Add this new publish container to the Job system by opening the Job Server Cluster branch and selecting a cluster or job server. Note that you cannot use the Table-only Layout publish container for the View Layout publishing. Read the section on Storing Published Data earlier in this article.
  5. Select the cubes to be published.
  6. Select the e.List items to be published.
  7. Select the options to be used when publishing.
  8. Click on the Publish button. The program will create a publish job. You can monitor the publish job by using the Monitor Console branch.

Automating publishing jobs

You can automate publishing jobs by using the following macros:

  • Publish—View Layout
  • Publish—Table Only Layout
  • Publish—Incremental Publish

Understanding the impact of changes

Changes to e.List, model, and dimension for publish may range from having no impact, to having a significant impact, on the publishing process, tables, and BI reports. Some of these changes, and their impacts, are noted below.

e.List changes

  • When you add e.List items, you have to reselect these added e.List items on the Publish screen. The Publish screen tries to select e.List items that were most recently used in situations where e.List items have been removed.
  • When you modify an e.List or D-List hierarchy, for example, adding/removing a hierarchical level, you have to adjust the BI (Framework Manager) models so that the reports do not break.

Model changes

Changes in the model structure, for example by changing D-List items, adding or removing a D-List from a cube, or reordering the dimensions in a cube, can range from having no impact, to having a significant impact. For example, when you delete dimensions in a cube, a reconfiguration or restructuring of publishing parameters and published tables is required. The Framework Manager model, if attached to the publish table, needs to be reconfigured so that the reports do not break.

Dimension for publish changes

When you change the dimension for publish, the program needs to restructure the published tables. The Framework Manager model, if attached to the publish table, needs to be reconfigured so that the reports do not break.

 

 

IBM Cognos 8 Planning Engineer a clear-cut strategy for achieving best-in-class results using IBM Cognos 8 Planning with this book and eBook
Published: July 2009
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Reporting Planning data using BI Tools

This section explains the different methods that can be used to access planning data for BI reporting purposes.

Real-time reporting options

There are two ways that you can access live data:

  • Publish the application as a package to IBM Cognos Connection
  • Use Planning Contributor as a data source in Framework Manager

Publish the application as a package to IBM Cognos Connection

You can create a planning package of the ABC application in IBM Cognos Connection by running a GTP and selecting the Create Planning Package option. You will then be able to report directly off the planning package by using any of the IBM Cognos Studios. You can also determine the name and location of the package in IBM Cognos Connection in the GTP options in the Application Maintenance folder.

Carry out the following steps to create a planning package using GTP:

  1. Select the ABC application in the Contributor Administration Console.
  2. Click on the Go to Production icon.
  3. The Go to Production wizard opens. Click on Next.
  4. Select the Create Planning Package option. Click on Next.

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  5. Click on Next again, and then click on Finish.

You can now open one of the IBM Cognos Studios to report directly off of the package. When you open the studio, you will be able to select the planning package for reporting off, as shown:

Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

Using IBM Cognos Planning Contributor as a data source in Framework Manager

You can create a Framework Manager model and choose IBM Cognos Planning Contributor as the data source to access the planning data held in the application database. This method is using the PDS server to access the ABC Contributor application database. The following steps show how to access the ABC Contributor application datastore using IBM Cognos Planning Contributor as a data source.

  1. Open Framework Manager.
  2. Create a new project by clicking on Create a new project.
  3. Select the language you want to use.
  4. The Metadata wizard will appear. Select Data Source as shown:

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  5. Select Cognos Planning – Contributor as shown here. Click on Next.

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  6. The wizard will then show a list of cubes that belong to the ABC application. Select one of the cubes to import and click on Next. The cube will be imported.

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

To add additional cubes in Framework Manager, go to the Project View, select the model, click on Actions, and then click on Run Metadata Wizard to run through the process again and import other cubes into the Framework Manager model.

Reporting from published data

In this section, we explore the option of reporting from Contributor planning data that has been published to a star schema datastore. The publish process essentially collects the planning data stored in XML format in the application datastore and transfers it to a star schema datastore. This planning data is now ready to easily be accessed and reported on by using external reporting tools. There are two ways in which you can report off published planning data:

  1. Build a Framework Manager model by running the Generate Framework Manager Model Admin extension in the Contributor Administration Console.
  2. In Framework Manager, build a Framework Manager model that uses the publish datastore as a data source.

Creating a Framework Manager model using the Framework Manager extension

This method involves creating a Framework Manager model using the Framework Manager model extension in the Contributor Administration Console. When the extension is first run, it will create two models: a base model and a user model. The base model should always be an exact replica of the Contributor model. Whenever changes are made to the planning model, the extension should be run again in the Contributor Administration Console to ensure that the base model is up-to-date. You can synchronize the base and the user model in Framework Manager in order to keep both versions in sync. The user model is where additional adjustments are made in Framework Manager. An example of adjusting the user model may be to add a component to bring the Actuals data into the model.

Before running the extension, there are a number of considerations to address, such as the options to be selected when publishing the application. Additionally, there are considerations to be addressed when selecting the options while running the extension. These considerations are listed in the following section.

Publishing considerations

The Framework Manager extension only works with the Table-only publish Layout. You should consider the following publishing options prior to running the Framework Manager Extension:

  • Dimension for publish: This is the dimension that forms columns in the publish table that is creating the planning data. Ideally, you can choose a dimension that is static and does not change often. This will ensure that the columns in the publish table are minimally impacted by structural changes in the underlying model.
  • Rollups: To speed up the publishing process, you can avoid publishing aggregated data, as this data can be aggregated using a reporting tool.
  • e.List items: Selecting only planner nodes will speed up the process.
  • Zeros: To speed up the publishing process, zeros should not be published.

The publish process can be automated by using a Publish Table-only Layout contributor macro. The considerations above are all options that can be configured in the publish macro.

Framework Manager considerations

In addition to the publishing options mentioned above, you should also consider the following Framework Manager options that are configured while running the extension:

  • Framework Manager Model location: This is the location on the server where the model is stored.
  • Package Name: This is the name of the package in IBM Cognos Connection.
  • Package location: This is the location of the package in IBM Cognos Connection.
  • Cubes: The extension allows you to select the cubes that you want to include in the Framework Manager model.
  • Data source query subjects: This option allows you to select the list types that you want to include in your model. These lists are based on the e.List and D-Lists of the underlying planning model. Selecting the derived or complete hierarchy lists will create a dimensional model. The following list types are available:
    • Unformatted lists: These are the unformatted lists stored in the publish table datastore. These tables have an it_ prefix in the database.
    • Derived hierarchy lists: These are simple hierarchy lists stored in the publish table datastore. These lists will contain a simple parent-child relationship. These tables have a sy_ prefix in the database.
    • Complete hierarchy lists: These lists contain more complex calculations, and are stored in the publish table datastore. These tables have a prefix of cy_ in the database.

Creating the model

To create a Framework Manager model using the extension, perform the following steps:

  1. In the Contributor Administration Console, click on the ABC application.
  2. Navigate to the Admin Extensions folder.
  3. In the rightmost pane, under Extensions, click on Generate Framework Manager Model, and click on Run, as shown:

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  4. The Generate Framework Manager Model wizard appears. Click on Next.
  5. When creating a new model, select the option to Create a new Framework Manager Model.

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  6. On the next screen, enter the location of the Framework Manager model, the package name, and the location of the package in IBM Cognos Connection.

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  7. On the next screen, select the cubes that you want to report from.

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  8. On the next screen, select the list types.

    Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

  9. Click on Next twice, and then click on Finish to generate the model.

To generate a report from the package created by running the Framework Manager extension, open one of the reporting studios in IBM Cognos Connection and select the package as the source for reporting.

Exploring the Framework Manager model

Once the Framework Manager model has been created, you can make changes to it in Framework Manager. The changes are made to the user model and not the base model. If any changes are made to the underlying planning model, the base and user models should be synchronized in Framework Manager to keep the user model up-to-date. These changes, and any additional changes made to the model in Framework Manager, can subsequently be published to IBM Cognos Connection.

The Project Viewer in Framework Manager will display two views of the data structure of the model: the Physical view and the Business view. These two views are in the Model folder in the Project Viewer.

The Physical view shows the database query subjects that make up the model, as shown below. The Cube Tables folders consist of the query subjects sourced from the dimensions selected for publish. The other folders represent the list types selected for inclusion in the model when the Framework Manager Extension was run.

Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

The Business view displays the dimensions and the star shema groupings, as shown:

Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

The remaining folders in the Project Viewer show the data sources and packages created when the extension was run, as shown:

Reporting Planning Data in IBM Cognos 8: Publish and BI Integration

Summary

In this article, you have learned how to create publish containers, how the two different publish layouts—the Table-only Layout and the View Layout—work, and the impact on publishing and reporting of changing e.Lists, models, and the dimension for publish. You have also learned how to produce real-time reporting by publishing the application as a package, and by using IBM Cognos Planning Contributor as a data source in Framework Manager. Lastly, you have discovered the process for creating a Framework Manager model by using the Contributor's Framework Manager Extension.

 

 

If you have read this article you may be interested to view :

IBM Cognos 8 Planning Engineer a clear-cut strategy for achieving best-in-class results using IBM Cognos 8 Planning with this book and eBook
Published: July 2009
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

About the Author :


Jason Edwards

Jason Edwards is an IBM Cognos Planning solutions expert and partner of Agile Strategic Business Consulting. He has extensive experience in application design and development and has effectively implemented highly successful IBM Cognos Planning solutions for a variety of companies. Jason is certified in IBM Cognos Planning and holds a Bachelor's degree in accounting and finance. Jason can be contacted at jason.edwards@agilestrategic.com.

Ned Riaz

Ned Riaz is a Certified IBM Cognos Planning expert and principal partner at Agile Strategic Business Consulting, a consulting company that specializes in IBM Cognos Planning and Business Intelligence implementations.

Ned has obtained a B.S. degree in Accounting and Management Information Systems, and he passed the CPA (Certified Public Accountant) exam after finishing his degree. After finishing his education, Ned worked as an auditor, accountant, and finance director in many industries, such as banks, software reselling, and entertainment. He became involved in system development work in the late nineties, and deployed various medium-sized accounting and general ledger systems.

Ned joined Adaytum Software, the original manufacturer of Planning products, in late 1999 when Adaytum had less than 50 employees. He has been working with Planning products since Contributor 1.1 and Analyst 2.2 were released in early 2000. While working with Adaytum, Ned designed and built many Planning models for a wide range of customers.

During his days in Adaytum and Cognos, Ned designed and deployed models and systems for many large fortune 500 companies in various industries, such as pharmaceutical, hospital, aircraft operations, and retailers.

As a partner/employee of Agile Strategic Business Consulting, Ned has been involved in designing and deploying various models and systems at a large information delivery corporation since 2006.

Ned and his wife live in Central Minnesota. He enjoys cycling, badminton, and volunteering with rescued rabbits. He can be contacted at ned.riaz@agilestrategic.com and on the Web at www.agilestrategic.com.

Rich Babaran

Rich Babaran has over 20 years of experience in financial modeling and analysis, planning and forecasting, performance measurement development, workflow modelling, and process improvement. He has spent the last 9 years helping organizations improve their planning processes using IBM Cognos Planning. Rich has a Bachelor's degree in Economics and an MBA in Finance. Rich is a partner at Agile Strategic Business Consulting.

The authors and Packt Publishing would like to extend their thanks to Janosys Inc. for their gracious support in the development of this book.

Books From Packt

SOA Cookbook
SOA Cookbook

SOA Governance
SOA Governance

RESTful PHP Web Services
RESTful PHP Web Services

SAP Business ONE Implementation
SAP Business ONE Implementation

Oracle Modernization Solutions
Oracle Modernization Solutions

Oracle SOA Suite Developer's Guide
Oracle SOA Suite Developer's Guide

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

Building SOA-Based Composite Applications Using NetBeans IDE 6
Building SOA-Based Composite Applications Using NetBeans IDE 6

 

 

 

 

Your rating: None Average: 4.7 (3 votes)
Try article by
I really liked the article, and the very cool blog
Printing is a problem by
Great article, it's just hard to print.
Accesing Planning Data by
Dear Jason, This article is good and it is even more appropriate if you mention about the automation of the Publishing part from the Cognos Contributor Console. Any way thanks a lot for your article.

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
6
i
v
w
G
1
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software