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

0
160
11 min read

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.

 

 


Subscribe to the weekly Packt Hub newsletter

* indicates required

LEAVE A REPLY

Please enter your comment!
Please enter your name here