IBM Cognos Metric Studio

IBM Cognos Metric Studio is the scorecarding and metrics tool in IBM Cognos Business Intelligence.Metric Studio is a performance tool for managing monitoring and analysing metrics, projects, and other performance measures within an organisation. 

It is used to create a scorecard application and monitor the metrics within the applications.It helps you ensure that your organisation’s performance is consistent with its targets and goals.You can use Metric Studio to create a customized scorecarding environment to monitor and analyze business metrics throughout your organization. A metric is a key performance indicator (KPI) related to time.In other words you use Metric Studio to monitor KPIs between one period and the next.A scorecard shows graphically how a metric is performing.This is typically done by using red, orange and green status notations.

IBM Cognos Metric Studio

Metric Studio helps you translate your organization's strategy into relevant, measurable goals that align each employee's actions with a strategic plan.

Using Group by clause along with ROLLUP or CUBE operators

Use the ROLLUP OPERATOR to produce subtotal values, ROLLUP IS AN EXTENSION OF GROUP BY CLAUSE, Use the CUBE OPERATOR to produce cross-tabulation values.  Use the GROUPING function to identify the row values created by ROLLUP or CUBE.  These operators and GROUPING function can best be used ALONG WITH GROUP FUNCTIONS, as group functions operate on a set of rows to give one result per group.

• Examples of Rollup:

SQL> select deptno,sum(sal) from emp where deptno <30 group by rollup(deptno);

    DEPTNO   SUM(SAL)

---------- ----------

        10       8750

        20      10875

                19625

SQL> select deptno,job,sum(sal) from emp where deptno <30 group by rollup(deptno,job);

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

        10 CLERK           1300

        10 MANAGER         2450

        10 PRESIDENT       5000

        10                 8750

        20 CLERK           1900

        20 ANALYST         6000

        20 MANAGER         2975

        20                10875

                          19625

• Example of CUBE:

The cube operator is used to produce results sets that are typically used for cross-tabular reports. This means Rollup produces only one possible subtotaling where as Cube produces subtotal for all possible conditions of grouping specified in the group by clause and a grand total

SQL> select deptno,sum(sal) from emp where deptno <30 group by cube(deptno);

    DEPTNO   SUM(SAL)

---------- ----------

                19625

        10       8750

        20      10875

The following query produces subtotaling results based on job,based on deptno and based on the individual jobs(clerk or analyst or manager etc in dept 10 and 20)

SQL> select deptno,job,sum(sal) from emp where deptno <30

   group by cube(deptno,job);

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

                          19625

           CLERK           3200

           ANALYST         6000

           MANAGER         5425

           PRESIDENT       5000

        10                 8750

        10 CLERK           1300

        10 MANAGER         2450

        10 PRESIDENT       5000

        20                10875

        20 CLERK           1900

    DEPTNO JOB         SUM(SAL)

---------- --------- ----------

        20 ANALYST         6000

        20 MANAGER         2975

13 rows selected.

Setting or Creating an Implicit Fact Column in OBIEE

To set an implicit fact column follow the steps and screenshot

• Create a Dummy Fact column in a Physical Layer called Implicit Fact Column and map that column to any number of your choice

Creating Implicit Fact Column

• Join all Dimensions to this Fact column in BMM Layer
• In presentation layer, double click the presentation catalog/ go to properties of presentation catalog.
• In the general tab, find the Implicit Fact Column section.

Selecting Implicit Fact Column from Presentation Catalog

• Click on Set button, this will open the browse window
• Select a fact column from the fact tables available in the presentation catalog.
• Click OK.
• Ensure that the selected Implicit Fact Column is displayed in the Implicit Fact Column section in the general properties of Presentation Catalog.
• Click OK.
• Save your changes in the repository.