Home › Category Archives › Cognos

PHP sample for Extracting Cognos report Query information From the xml report specification

Here is a sample PHP file that can be used to extract the Query information of a cognos report from the Cognos Content Store.

It will ask for the report xml specification file. The script can be extended to connect directly to the content store database and have a list of the reports as a master navigation list with this script as a detail.

<html>
<head>
<link type=”text/css” rel=”stylesheet” href=”mgawad.css”  />
</head>
<body>
<?php

function Selection_atr($selection,$offset)
{
echo ” <table ><tr><td>Type</td><td>name</td> <td>aggregate</td> <td>rollupAggregate</td><td>sort</td><td>expression</td> </tr> “;

foreach($selection->children() as $x)
{
echo ” <tr> “;
$att[‘Type’] = $x->getname();
foreach($x->attributes() as $a => $b)
{
switch ($a)
{
case ‘name’:
$att[‘name’] = $b;
break;
case ‘aggregate’:
$att[‘aggregate’] = $b;
break;
case ‘rollupAggregate’:
$att[‘rollupAggregate’] = $b;
break;
case ‘expression’:
$att[‘expression’] = $b;
break;
case ‘sort’:
$att[‘sort’] = $b;
break;
default:
echo “Error” . $b;
}
}
foreach($x->children() as $child)
{
if ($child->getName() == ‘expression’)
{
$att[‘expression’] = $child;
}
}
echo $offset . ”  <td>”.$att[‘Type’] .”</td>  ” .”  <td>”.$att[‘name’] .”</td>  ” . ”  <td>”.$att[‘aggregate’] .”</td>  ” . ”  <td>”.$att[‘rollupAggregate’] .”</td>  ” . ”  <td>”.$att[‘sort’] .”</td>  ” . ”  <td>”.$att[‘expression’] .”</td>  ” .”</tr>”;
}
echo ” </tr> </table>  “;
}

function detailFilters_atr($detailFilters,$offset)
{
echo ” <table >  <tr><td>use</td> <td>filterExpression</td> </tr> “;

foreach($detailFilters->children() as $x)
{
echo ” <tr> “;
foreach($x->attributes() as $a => $b)
{
switch ($a)
{
case ‘use’:
$att[‘use’] = $b;
break;
default:
echo “Error” . $b;
}
}
foreach($x->children() as $child)
{
if ($child->getName() == ‘filterExpression’)
{
$att[‘filterExpression’] = $child;
}
}
echo $offset . ”  <td>”.$att[‘use’] .”</td>  ” . ”  <td>”.$att[‘filterExpression’] .”</td>  ” .”</tr>”;
}
echo ” </tr> </table>  “;
}

function atr($x,$offset)
{
//if ($x->getName() == ‘selection’ ) { Selection_atr($x,$offset);}
//else {
echo ” <table >  <tr> <td>”.$x->getName().”</td>”;
foreach($x->attributes() as $a => $b)
{
echo $offset . ”  <td>”.$a,’=’,$b .”</td>  “;
}
echo ” </tr> </table>  “. “<br />”;;
//  }
}

function el($x,$offset)
{
$j=1;
switch ($x->getName())
{
/* case ‘query’:
echo ” <table >  <tr><td>Query</td> </tr>  <tr>”;
foreach($x->children() as $child)
{
atr($x,$offset);
el($x, $offset);
echo  “<br />”;
}
echo ” </tr> </table>  “. “<br />”;
break;
*/
case ‘selection’:
Selection_atr($x,$offset);
break;
case ‘detailFilters’:
echo $offset . $x->getName() . “: ” . $x . “<br />”;
detailFilters_atr($x,$offset);
break;
default:
foreach($x->children() as $child)
{
//echo $offset . $child->getName() . “: ” . $child . “<br />”;
atr($child,$offset);
el($child, $offset);
echo  “<br />”;
}
}
}
// main

$xml = simplexml_load_file($_GET[“report_file”]);

echo $xml->getName() .  “<br />”;
$I = 1;

foreach($xml->children() as $child)
{

if ($child->getName() == ‘queries’) {
echo $child->getName() . “: ” . $child .  “<br />”;
atr($child,”);
el($child,”);
}
}
?>
</body>

Cognos TM1 Services and Directory Structure

Directory Structure

TM1 Directory Structure

TM1 Directory Structure

Registry

TM1 Registry

TM1 Registry

Services

TM1 Services

TM1 Services

Start Menu

TM1 Start Menu

TM1 Start Menu

Cognos TM1 Installation Overview

Cognos TM1 Installation

Start the installation

TM1 Installation

TM1 Installation

Licence Agreement

Tm1 license agreement

Tm1 license agreement

Product to install

TM1 Choose Product

Installation Options

TM1 Installation Options

TM1 Installation Options

Use Custom installation Type to

–          Select which component to install.

–           Specify the required security model.

Component Selection

TM1 Component Selection

TM1 Component Selection

TM1 Clients TM1 Perspectives –          Excel Add-Ins.

–          Create and maintain Objects  and data

–          Local and remote servers

TM1 Client –          Excel Add-In.

–          Provide Limited access to Data on remote servers

TM1 Web Component –          Web Server component to access TM1 server and data through the web.

–          Installed as part of TM1 Web and Contributor  installation

Developer Environment TM1 Architect –          Stand alone TM1 client for creating and maintain data and metadata on local and remote servers
TM1 API Used to  create

–          C++ and VB Applications.

–          Java Applications.

–          .Net Applications

Servers TM1 Admin Server –          It is a process Run on the Admin Host that track all TM1 servers running on the network.
TM1 Server –          Handle Client Requests

–          Load the shared cubes into the memory

–          Manager Security

–          Send data to spreadsheets

–          update cubes on request

–          Maintain a log of changes to the database.

–          Default name is SData

TM1 Top –          Run on Windows only to monitor all running threads in a instance of TM1 server (windows on Unix)
Contributor –          Design deploy and run managed planning application based on TM1 data

Configure TCP/IP Ports the TM1 Services use

TM1 Ports

TM1 Ports

Default Ports
TM1 Admin Host 5495
TM1 SSL Admin server 5498
TM1 Client Message 12346

Username and Password for TM1 S

Username and Password for TM1 Services

TM1 Usename

TM1 Usename

On Windows, all TM1 services must run under a single domain account, local system account is not recommended as Windows restrictions on Local accounts may cause the TM1 to operate incorrectly.

For the Username, In case of using Local account, it must have the following privileges

  1. Act as part of the operating system
  2. Bypass traverse checking
  3. Increase quotas
  4. Replace a process-level token
  5. Log on as a service

In all cases use this format: domain\username or localmachine\username.

Authentication modes and security Settings:

once configured, it can not be changed, it need to reinstall TM1

Security Settings

TM1 Security Settings

TM1 Security Settings

Integrated Login Automatically log in using the Windows login.

It has 2 modes

–          Mixed mode, the default one.

–          Integrated login only

Tm1s.cfg to control the mode

TM1 Authentication
LDAP Authentication LDAP settings

LDAP Port: Default is 389

LDAP Host

LDAP Search Base

LDAP Search Field: default is cn

TM1 Server Configuration

TM1 Server Configuration

TM1 Server Configuration

During the installation session, up to 3 servers can be installed, to install more than 1 server, we need to use the Server configuration to assign different values for each

Choose Database

Server Name

Admin Server Host Machine Name

Port Number

Change Data Directory

Configure Another TM1 Server

TM1 Client Configuration

TM1 Client Configuration

TM1 Client Configuration

Each client has an Tm1p.ini file that contains

Admin Server Host Machine Name

Disable Excel Edit in Cell Capability

Set TM1 to Autoload in Excel

Use Integrated Login


Cognos Content Store: Retrieving Information

Here is a  sample on getting package and report information directly from the content store.

cmobjects Croatians a parent child relationship of all objects, to obtain the full  path of an object we need a Recursive function to resolve this, let us say getpath(Parent_cmID),         a sample exist at the end of this Port.

To get Information about Published packages

SELECT cls.NAME class_name, n.NAME object_name, o.pcmid, o.cmid,
getpath (o.pcmid)
FROM cmobjnames n, cmobjects o, cmobjprops36 c, cmclasses cls
WHERE o.cmid = n.cmid
AND o.cmid = c.cmid
AND o.classid = cls.classid
AND isdefault = 1;

To get information about Reports and their specifications

SELECT cls.NAME class_name, n.NAME object_name, o.pcmid, o.cmid,
getpath (o.pcmid), c.spec
FROM cmobjnames n, cmobjects o, cmobjprops7 c, cmclasses cls
WHERE o.cmid = n.cmid
AND o.cmid = c.cmid
AND o.classid = cls.classid
AND isdefault = 1
AND UPPER (cls.NAME) = ‘REPORT’;

Sample Recursive function

CREATE OR REPLACE FUNCTION getpath (p_pcmid IN NUMBER)
RETURN NVARCHAR2
IS
l_parentname   NVARCHAR2 (100);
l_pcmid        Number;
BEGIN
IF p_pcmid = 0
THEN
RETURN ‘root’;
ELSE
SELECT n.NAME object_name, o.PCMID
INTO l_parentname, l_pcmid –, cls.NAME class_name, o.CMID,
FROM cmobjnames n, cmobjects o
WHERE o.cmid = n.cmid AND o.cmid = p_pcmid and isdefault=1;
END IF;

RETURN getpath (l_pcmid)||’\’||l_parentname;
EXCEPTION
WHEN OTHERS
THEN
RETURN sqlerrm;
END getpath;

Cognos Planning Contributor

It consists of

  • Tabs (each tab display data from a cube)
  • Dimension bar
  • Dimensions
  • Grid
  • Simple cube help
  • Help

Quick commands

Commands entered into the cell and executed by pressing the keyboard Enter Key.

There are 2 types of Commands:

  1. Copy Commands
  2. Data entry command
Copy commands Data Entry commands
Appears at the end Appear at the beginning, not case sensitive
> Copy right Add, + Inc
< Copy left Sub, – Dec
^ Up Mul, * Hold, Hol
| Down Div, / Release, Rel
: Copy stoper Power
Per, %

Workflow states:

Not Started
Work in progress All sub items have been started, at least 1 sub item not yet submitted.
Incomplete At least 1 sub item not yet started
Ready All sub items have been submitted and locked.
Locked Submitted

Cognos Planning Components and Roles

Component

Roles
Analyst Business modeling tool

To define Business Models and analyze business performance

Modeler financial specialists

Plan for data collection, modeling and reporting requirements.

Design and build the data model. (1)

Manager To create flowcharts, front ends, tables and graphs Administrator Create and configure the contributor applications using the application creation wizard.

(2)

Import the data required by the user. (3)

Publish Data (6)

Contributor For data collection and workflow management

Accessed from Cognos connection.

Planner

&

Reviewer

Using the contributor work flow screen

Planner (manager in the organization who is responsible to submit a budget for a particular area in the business): enter, save and submit data (4)

Reviewers (member in the finance department or the one to whom the planner report)

Review data (5)

Analyst add-in for  excel (bi-directional)

To create reports from analyst data and post data back to analyst.

Contributor add-in for excel To submit data from excel to contributor

Cognos Analytic Solution (Financial Performance Management )

  1. Cognos Planning
  2. Cognos Controller (financial reporting and consolidation, supports IFRS, FASB, Basel II, and Sarbanes-Oxley requirements)
  3. Cognos TM1  (Exceptionally fast, Data and user scalability, OLDP)

Cognos Content Store Database Tables

Cognos Content Store Database Tables
Cmsysprops Content Store Version
Cmobjnames Object Names
Cmobjprops1 User info
Cmobjprops2 Report Schedule
Cmobjprops3 Object Description and Screen tips
Cmobjprops4 Printers paper settings
Cmobjprops6 Published Package information
Cmobjprops7 XML of all reports
Cmobjprops10 Contact information
Cmobjprops11 Data source configuration detail
Cmobjprops13 Parameters passes to the range prompt
Cmobjprops14 Object version information
Cmobjprops16 Cognos Service status
Cmobjprops17 cognos components Performance detail
Cmobjprops18 Drill path from source to target report
Cmobjprops20
Cmobjprops24 Printer configuration detail
Cmobjprops25 Deployed objects detail
Cmobjprops26 Imported and exported packages
Cmobjprops27 Cube data sources
Cmobjprops30 Portlet information
Cmobjprops31 Custome logging level
Cmobjprops32 Stored procedure used as query items
Cmobjprops33 User, groups and roles
Cmobjprops34 Drill through parameters
Cmobjprops36 All models puplished from FM
Cmobjprops37 Load balancing info
Cmobjprops52 Properties for the connections
Cmobjprops55 URI of icons in cognos connection
Cmlocales Locale id associated with each language



Cognos Analytic Applications

Cognos Analytic Applications consists of

1- Business Content

2- Adaptive Application Framework

Adaptive Warehouse

  • Metadata modeling environment for designing and populating a target Data Warehouse
  • It has an interface that help in the ETL of data from the ERP systems into the the target data warehouse.
  • It includes Source mappings, Prepackaged metadata

Adaptive Analytics

  • This is a report-generator component used to create and administer reports for business users.

3- Cognos 8 Runtime Components