IBM Tivoli Common Reporting

Hello folks,

So today I've a couple of quick little tips for you, hence the "bitesize" moniker for this subseries of blogs.  Both of these relate to TCR.

1 -  Can you run both TCR 2.1.1. and TCR 3.1 on the same machine, for example while carrying out a migration?

A - You can, but there's a couple of caveats to bear in mind.  You need to either make sure that the TCR 2.1.1 install is completely stopped before you try to run the 3.1 installation or you need to change the TCR 2.1.1 cognos log port from 9043 to something else to ensure that there's no conflict with 3.1 and then you can run both together.

 2 - Less a question and more a little tip for those people just getting started with TCR - Potential issue with Cognos Framework Manager and Windows User Account Control.

If you are installing on Windows, and are running with the Windows UAC turned on, then you can run into problems with Cognos.  Specifically, you may find that Cognos Framework Manager is unable to connect to the database even if all the tests are successful and it appears that everything is running correctly.   In this instance, if everything else appears to be correctly configured, your only option will be to disable UAC and then the issue should clear.

I hope you find these little hints/tips useful, I will be posting more of these as I come across them.

Subscribe and follow us for all the latest information directly on your social feeds:

Hello folks,

I've got another video for you all today.  Rather than one of the Academy Seminars, this is just me discussing some of the things that you really need to supply when you log a PMR with IBM Support if you want us to be able to help you with a TCR PMR.

Please take a look at this, and keep it in mind the next time you come to log a PMR with us.  Giving us as much of this information as you can right from the outset will really help speed the investigation of the problem.

Subscribe and follow us for all the latest information directly on your social feeds:

Hello folks,

A quick little blog(ette) for you today to kick off the new year.  If you're installing TCR 3.1 as a non-root user, we supply this technote to help you through troubleshooting why it might fail -  http://www-01.ibm.com/support/docview.wss?uid=swg21902346

Another customer, however, has found that there's potentially another couple of points to consider when you're running the install.  Firstly, you need to make sure that your DB user -

a) belongs to the db2iadm1 group, and

b) source in the db2profile in .bashrc.

This customer copied the db2profile sourcing from db2inst1 users .bashrc to the filesystem (read only), but in order to ensure full access to all files they also extracted the image to local disk and changed the x bit as per the example below -

cd /opt/dash/install/TCRCognos/ chmod -R +x *

With these two modifications the installer now runs successfully.  I hope this little bit of information helps you if you run into any problems with your own installations!

Subscribe and follow us for all the latest information directly on your social feeds:

In order to create the dimension tables, we must first configure the historical data collection for the following attributes depending on the OS we are setting reports:

For example, for windows we should have:

From ITM 6.3 , there are two ways to create and maintain the dimension tables:

• Using the summarization and pruning agent
• Manually (as previous versions )

In this section we are going to use the S&P agent to do it but if you want to continue to manually update the dimension tables as it had to be done in versions older than ITM v6.3 you can still do it.

Best Practice:

1.Configure the S&P agent to maintain the dimension tables without starting it after configuration.
2.Create the dimension table using the “schema Publication tools” in update mode . Doing that we create both groups of tables:

• Share Dimension tables:

                       TIME_DIMENSION, MONTH_LOOKUP and WEEKDAY.

• Resource dimension tables:

            MANAGEDSYSTEM, MANAGEDSYSTEMLIST and
            MANAGEDSYSTEMLISTMEMBERS.

3.Restart the S&P agent.

1. Configuring the S&P agent to maintain the dimension tables:

1.Open the S&P agent env. variable file
        Windows: KSYENV
        Linux/Unix : sy.ini

2.Configure/add the following env. variables:

• KSY_TRAM_ENABLE=Y (default is N)
• KSY_TRAM_TD_GRANULARITY=10 (default is 5 minutes, it is the number of minutes that the data is inserted into the TIME_DIMENSION table. 
• KSY_TRAM_TD_INITIAL_LOAD=12 (default 24 moths, it is the amount of data in months to be loaded into the TIME_DIMENSION.

3.Save without restarting the S&P agent.

Now, the S&P agent has been configured to maintain the “resources dimension tables” .

2.Creating the dimension tables using the Schema publication tool:

1.Open the file:

windows: <home>\TMAITM6\tdwschema.rsp
Linux/unix: <home>/arch/bin/tdwschema.rsp

2.Set these env. variables:

• KSY_PRODUCT_SELECT = updated
• KSY_TABLE_FILTER = TIME_DIMENSION, MONTH_LOOKUP, WEEKDAY_LOOKUP
• KSY_SQL_OUTPUT_FILE_PATH = (optional file path for sql output)

3.Ensure the TEPS is started

4.run: tdwschema -rspfile tdwschema.rsp

IMPORTANT: you have to be a database administrator

5.Run the following scripts in order with an user  having admin access to the DW database. If you are using Oracle or MSSQL server you must run the scripts as the TRAM user “IBM_TRAM”.

• db2 connect to WAREHOUS (with db2admin)

• db2 -tvf tdw_schema_table.sql

As we can see the share dimension tables were created:

• db2 -tvf tdw_schema_index.sql
• db2 -tvf tdw_schema_view.sql
• db2 -td# -f tdw_schema_function.sql
• db2 -tvf tdw_schema_insert.sql

Now the table has been filled in:

Note: for Oracle or MSSQL server to check , page 498 for ITM6.3 administration guide.

3.Starting the S&P agent

   1.we are going to  find how the TIME_DIMENSION table has been populated:

IMPORTANT:

• The MANAGEDSYSTEM table is populated using information from the WAREHOUSETCRCONTROL table. This table, WAREHOUSETCRCONTROL, is created on the first successful scheduled execution of the S&P agent.

• It is the responsibility of each agent to put entries in the WAREHOUSETCRCONTROL table. In this case,  the installer for the OS report package will promts us to provide JDBC connection details and credentials for the TDW database. This RegisterPackage script will insert data into WAREHOUSETCRCONTROL table. After this step, the MANAGEDSYSTEM and TIME_DIMENSION tables are kept up to date automatically by the S&P agent.

2.“The Resource dimension”  and WAREHOUSETCRCONTROL  tables  have been created after  the first successful scheduled execution of the S&P agent.

Thanks for reading, Fran.
 

Check out all our other posts and updates:

Academy Blogs:                   http://ibm.co/1sPj9E8
Academy Videos:                 http://bit.ly/1wFKveY
Academy Google+:             http://bit.ly/1sR5QTV
Academy Twitter Handle:  http://bit.ly/1CknfoF
 

We can use different ways to import the reports in TCR :

1.Using report installer into the report package:

• via GUI
• via command (setup_platform.exe/.bin -i console)

2.Through the Dashboard application Services HUB

In this doc, we are going to  use the “report installer” included into the OS report, so that the RegisterPackage script is performed and therefore the tables populated.

1.From the /osreport directory (CD), we run the command :

        windows: setup_platform.exe
        linux/unix: setup_platform.bin

             and the installer windows  is open:

Note: In case we have  problems opening the installer windows.  Error:

 "LaunchAnywhere Error: Could not find a valid Java virtual machine to load”

You have to do:

• Open TEP browser  or JWS to install IBM JAVA 7.0  http://teps_hostname:1920  or http://teps_hostname:15200
• Add the path for java.exe  to the PATH variable  (environment variable) and JAVA_HOME
• If it is windows, check  HLM\software\IBM\Java2 Runtime Environment\currentversion  (regedit) is 1.7 and not 1.5

2.Choose your language and click OK + Next

3.Specify the TCR installation directory + Next

4.Choose the reports you want to install + Next

5.Enter the TCR user name and password (tipadmin) + Next

6.Enter the data source configuration information: DW db, user , password, database type

7.Enter the JDBC User Credentials: TDW database user and password

8.Enter the JDBC database credentiala: JDBC URL, JDBC drivers

9.Next + Install

10.A popup is displayed. Check if the installation was successful + Done

Note:   we can see by using  the “installer package” , the reports have been imported and the “TDW” datasource  created and configured .

Now the WAREHOUSETCRCONTROL  table has data:

Note: MANAGEDSYSTEM  table will be populated after S&P agent has been performed  successfully and having the  WAREHOUSETCRCONTROL table  data.

Thanks for reading, Fran.

Check out all our other posts and updates:

Academy Blogs:                   http://ibm.co/1sPj9E8
Academy Videos:                 http://bit.ly/1wFKveY
Academy Google+:             http://bit.ly/1sR5QTV
Academy Twitter Handle:  http://bit.ly/1CknfoF
 

In case  we decide to import the report package using Dashboard instead of the “report installer” as it indicated in  Link . we'll need:

1.Populate the Managedsystem table
1.Import the reports
2.Create the datasource “TDW”

1.Populate the ManagesSystem table:

• db2 connect to WAREHOUS user db2admin using xxxx
• Create the procedure to populate the manage system table

        db2 -td@ -vf populate_resource.db2

• Populate the table:

        db2 call ITMUSER.POPULATE_OSAGENT()

Note: the script into report package , directory called “db_scripts”

2.Import the OS report:

• Copy the report package (.zip file) in the deployment folder :

        \TCR_component_dir\cognos\deployment

• Start the Dashboard console:

          http://<tcr_server>:16310/ibm/console

• Go to “common reporting”
• Click “Launch” scroll + Administration
• Click “configuration” + “content administration”
• Click “new import” icon and select the report that we want to add in the list + Next
• Select the box of your report + Next +Next +Next
• “save and run once” + finish
• In time: Select “now” + click on “run” + ok
• Now, the new report should appear  in “public Folders”

3.Create the Datasource

• Click “launch” + “administration” + “Configuration” + “data source connection”
• Click “new data source” icon

    IMPORTANT: Data source MUST be named “TDW”

• Enter TDW in the name field + Next
• Select Type: Db2 + Next
• Database name: WAREHOUS, UserID  and password for the user used to connect to the DW db
• Click “test the connection” + “test”
• Close + finish
• Click “Return”

   

  Thanks for reading, Fran.

   

  

   

  Check out all our other posts and updates:
  
  Academy Blogs:                   http://ibm.co/1sPj9E8
  Academy Videos:                 http://bit.ly/1wFKveY
  Academy Google+:             http://bit.ly/1sR5QTV
  Academy Twitter Handle:  http://bit.ly/1CknfoF    
   

   

   

   

E. INSTALLING REPORTING SERVICES AND TCR 3.1.2.1

NOTE: it is a blog following the one "Installing  TCR 3.1.2.1 with Non-Root user account" (https://ibm.biz/Bd4vmC)  . The Reporting services is installed in this way because  to install  TCR 3.1   with  non-root the Content store has to be created manually and the Report Services  configuration   fail because it is already created.

NOTE: add the non-root user , fran , to the db2 group, db2iadm1 ( in fact, it shouldn't be needed but I like it .-) ).

1. start the installation manager , IBMIM, from /home/fran/IBM/InstalationManager/eclipse/ with non-root user, "fran" .

2. Click on “install“ option

3. Select only “reporting Services 3.1.2.1” + Next

4. Select “ create a new package group” + installation directory = /home/fran/IBM/JazzSM + Next

5. As the CS is already created we “un-select” the “configure option for Schema”:

NEXT

6. We set the "tipadmin"  user for WAS configuration:

Click on user and click on “validate...” + Next

7. We set the default ports:

8. We set the database configuration settings and click on validate:

9. Next

10. We select the path where TCR was unpackaged:

Validate + next + Install

11. And the installation starts:

12. Eventually the Reporting Services is installed correctly:

Thanks for reading, Fran.

Check out all our other posts and updates:

Academy Blogs:                   http://ibm.co/1sPj9E8
Academy Videos:                 http://bit.ly/1wFKveY
Academy Google+:             http://bit.ly/1sR5QTV
Academy Twitter Handle:  http://bit.ly/1CknfoF

E. INSTALLING THE REPORTING SERVICES

NOTE: it is a blog following the one "Installing TCR 3.1 with non-root user" (https://ibm.biz/Bd4j94)  . The Reporting services is installed in this way because  to install  TCR 3.1   with  non-root the Content store has to be created manually and the Report Services  configuration   fail because it is already created.

In this case we are going to run the Reporting services installation using the “installation manager

1. cd /home/tcr/IBM/InstallationManager/eclipse

           #./IBMIM

    And click on “install”:

2. We select to be installed the “reporting Services environment”:

NOTE: If you want you can select “IBM Dashboard Application Service Hub” to be installed /configured as well since it will be needed to integrate TCR with Dashboard GUI. In our
case , we will do it later to show you another installation option.

3. Next + Next

4. Leave the default + next

5. In the next step, we remove the option Schema since it was already created and configured in the in the step “C. CREATING AND CONFIGURATION CONTENT STORE”

6. Next

7. Add the same user and password that we set during WebSphere installation, in our case , tipadmin user account:

8. Click on “Validate...” + Next

9. We are going to leave the default port:

10. Next + Install

11. if it was installed correctly it returns:

NOTE: NEXT STEP IN "Installing TCR 3.1.0.1"

Thanks for reading , Fran.

Check out all our other posts and updates:

Academy Blogs:                   http://ibm.co/1sPj9E8
Academy Videos:                 http://bit.ly/1wFKveY
Academy Google+:             http://bit.ly/1sR5QTV
Academy Twitter Handle:  http://bit.ly/1CknfoF

The Prerequisites Scanner report provided by the OS agents report package, allows us to check our system's prereq. It verify that  shared dimensions tables, time dimensions tables and resource dimensions tables  are available and the prereq are right.

1.Open the dashboard:

        http://<TCR_server>:16310/ibm/console
        https://<TCR_server>:16311/ibm/console

2.Enter user and password (tipadmin)
3.Open TCR :

4.Select Public Folder + IBM tivoli monitoring OS Agents Reports + Prerequisites validation + OS agents Report Prequisites scanner report

5.Select between the two reports type:

• Show all sections: success and un-success
• Show failing sections: show sections with failing.

6.Finish

We are going to get something similar to this , indicating what things have to be resolve.

Once the .  Preq problems  have been resolved you can work with the report as usual: clicking on “public folder” + report package and selecting some report and the report scanned should show us something similar to:
 

Thanks for reading, Fran.

Check out all our other posts and updates:

Academy Blogs:                   http://ibm.co/1sPj9E8
Academy Videos:                 http://bit.ly/1wFKveY
Academy Google+:             http://bit.ly/1sR5QTV
Academy Twitter Handle:  http://bit.ly/1CknfoF    
 

A centralized monitoring system can record data on resources located at different geographies. For example, if the monitoring system is residing in Central time zone of USA and is recording the usage data of resources located in the East and West of USA, then the monitoring system records the data with the timestamp along with the time zone offset.

When a report is run from Tivoli Common Reporting, I would like the report to show the usage data of all the assets in one time zone. I should have the felixibility to choose the time zone in which the report should be generated.

Following are the high level steps to achieve this:
In the Cognos data model,
- Add Timestamp_UTC data item by adding the time zone offset to the recorded timestamp.
- Add a session parameter to pick the client's time zone ID.
- Add a parameter map to contain the complete list of time zone offsets.
- Handle the negative sign of the time zone offset by using Calculation items.
- Add a Timestamp field to dynamically apply the client's time zone using the Calculation items
- Save and publish the model

In the Report Studio
- Use the Timestamp field from the model which was created using the Calculation item
- Save the report

In the Connection page
- Update My Preferences to set the required time zone
- Run the report.

Download the detailed document on 'Reporting with Client's Time zone'
 

IBM Tivoli products which store data in Tivoli Data Warehouse (TDW) may be providing a report package or the Cognos data model which can be used to create reports in Tivoli Common Reporting. But in cases where you are required to create a new model and report on the data in Tivoli Data Warehouse, here is an article which covers the following topics:

Exercise 1 – Creating the data source and data model
Exercise 2 – Use direct SQL in a report
Exercise 3 – Use Tivoli Common Reporting Template
Exercise 4 – Use filter to view data of single or multiple server
Exercise 5 – Use summarized tables of Tivoli Data Warehouse
Exercise 6 – Use custom tables in report
Exercise 7 – Add a chart to the report
Exercise 8 – Add drill down capability
Exercise 9 – Export report package
Exercise 10 – Import report package

Download the article on 'Reporting with Tivoli Data Warehouse'.

Even though this document is based on data in Tivoli Data Warehouse, it can be applied to any other data as well. It helps in understanding some of the basic features of reporting in Tivoli Common Reporting.