Cube Deploy to Another Server

Use SFTP login with no password. Follow instructions in this link:

http://www.jscape.com/blog/setting-up-sftp-public-key-authentication-command-line

Creating Durable Models

One big advantage using durable Framework Manager model is that any change you make in the model will not have any effect on reports using that model (at least in theory). For example, should you change the name of a Query Item from "Company" to "Companies", after publishing, any report using this query model will still work fine (at least in theory). 

To create durable FM models, follow these steps:

1. Click on Project -> Languages -> Define Languages

2. Add another language preferably the same as what you are currently using. Example if your current Language is English, you can choose to add English (Malta).

3. Set this language as your Active Language.

4. Click ok

5. Click your Project in Project Viewer.

6. Set the Use Design Locale for Reference ID to True

7. Save

This should make your FM model durable.

Please not as of this time, I have not yet tested this process thoroughly. Hopefully I will be able to do so soon when I am not so busy anymore.

Free Disk Space 2

Another directory to look at if you find you are running out of disk space is the bin folder in your Cognos server installation.

Cognos will usually huge core dump files and save it in the bin directory. For windows the files will have a <process id>.dmp filename while in Linux the file will have core.<process id> filename.

I found several in my server and they consume nearly 20GB in disk space.

Hide an Object (Object Level Security)

At times, hiding an object to users is required. For example, only managers should have access to a certain table. Using data level security/filtering can still prevent viewing the data, but the users can still see the object.

To add object level security, select the object from Framework Manager, open Actions from Menu, and choose Specify Object Security. A pop up window will appear where the object security can be edited.

There are things one need to consider however when using object level security. The first is that when you add object level security, everyone is denied access to the object EVEN SYSTEM ADMINISTRATORS. Those who have access to the objects needs to be explicitly specified.

In this example below, I denied the System Administrators access to table Table2 and gave Everyone access to Table 1.

In Report Studio, even a System Administrator will not be able to access Table2.



Please note that if a user belongs to more than 1 role or group, and one of those groups are denied access, the user WILL BE DENIED ACCESS as well. All the groups the user belongs to should have access for a user to gain access to an object with object level security. For any conflicting security access, the deny access will have priority.

Cryptographic Key Error

Part of Cognos configuration is the length of time the encryption key will stay valid. If the encryption key expires, the Cognos administrator needs to regenerate the key in EVERY instance/installation. The setting can be found here:

To regenerate the encryption keys, follow these instructions, which can be found from this link as well:

http://www-01.ibm.com/support/docview.wss?uid=swg21383421

Steps to Regenerate Cryptographic Keys
1. Stop the Cognos services.
2. On the Content Manager computer, back up the existing cryptographic keys by saving the
following directories to an alternate location that is secure:
● c10_location/configuration/csk
● c10_location/configuration/encryptkeypair
● c10_location/configuration/signkeypair
3. Open Cognos Configuration, go to the File menu and select Export As, click yes at the prompt and save the file as backup.xml in the c10_location/configuration folder.
4. Delete the csk, encryptkeypair, and signkeypair directories.
5. In the c10_location/configuration folder, rename the existing cogstartup.xml to cogstartup.old
6. In the c10_location/configuration folder, rename backup.xml to cogstartup.xml.
7. Open Cognos Configuration, save the configuration and restart the services.
8. Repeat steps 1 to 7 on all computers that have Cognos components installed.

I however encountered an error when I was about to export the file (step #3). Fortunately, Cognos periodically saves cogstartup.xml file in \configuration
I renamed (backup) the current cogstartup.xml, and replaced with the latest cogstartup that Cognos saved. The error that I encountered in step #3 went away.

Some configuration settings however changed. Fortunately, I was able to save a screenshot of all the configuration values while the server was still up and running. That is also a very good tip, the moment you get access to Cognos server configuration, take a screen shot of all the values.

Create Half Year Level

When Cognos Transformer generates the Time Dimension, it does not have a Half Year Level. To add a half year level in your cube follow the following steps:

1. Generate the Time Dimension
2. Insert Half Year level above Quarter

3. Generate categories
4. Click Show Diagram. You will see that from Year level, the data directly links to Quarter level

5. Click on a Year and drag and drop the line to Half Year column. Fill up the category information with Half year label and code.
6.Link the associated Quarters with the Half Year value by clicking on the Half Year value and dragging and dropping the line to the Quarter value.

Keep in mind that if you do a full clean house, the Half Year member you created will be erased and you have to add them again manually.

Cube Refresh Report

I was asked to create a report that will show the refresh times of three cubes. The first thing I thought was three different reports displayed in a tab. The task however can be accomplished with one report only.

To do this, you must import the data source connection of the cubes in your FM package. Use the usual “Run Metadata Wizard” and select the Cube data source.

After successfully importing the cube data source, the icon looks like the one above. Publish the package and use normally.

Refresh Transformer Cube from UNIX

So I was able to successfully made my cubes refresh from UNIX. Here are the steps:

First, set the processing option to “On the server”. This can be found in the Processing tab of the cube properties:

Next change the deployment folder. Use UNIX syntax. Note that Transformer will not be able to verify the folder that is specified so it is important that the correct directory path is given.

Almost there. The command line should be edited to that which is compatible with UNIX. Here is the syntax I used based on the cube from the previous post:

./cogtr -c -lS1=’admin/admin123′ -g’My Cube’ -m’/cube/location/in/unix/mycube.mdl’

A few reminders:

1. avoid using cube names or deployment folders with spaces
2. avoid using uppercase in cube or folder names as well as UNIX is case sensitive

I have not discovered how to properly set the location of the log files so I redirect the output to a file to save my logs:

./cogtr -c -lS1=’admin/admin123′ -g’My Cube’ -m’/cube/location/in/unix/mycube.mdl’ >> /logfolder/mycube.log

Refresh Transformer Cube from Windows Command Line

I would like to record the things I remember about refreshing cubes using Windows command line parameters before I forget about them. Here are the things and tips I remember.

First, open your cube from Cognos Transformer. Determine dimension(s) wherein the lowest levels might have duplicates during refresh. Right click on the lowest level and tick the “Move” option. This will prevent the process from terminating once a duplicate is encountered.

Next make sure you only have one cube defined. More specifically, remove the unneeded cubes as they would also be refreshed automatically, adding to your refresh time.

Make sure you set the deployment parameters properly.

Create a singon for security. Give the correct username and password. Remember the name of the signon as it will be used in the command line. In the sample image below, the name of my signon is S1.

At this point you are almost ready to create the command line script. Go to the machine from where you intend to run the script. Ideally, it is the same machine where Transformer and Framework Manager is installed. This is to make sure that the proper configuration is already in place.

From here on, basic knowledge of DOS is required. Locate the Cognos installation folder, it should contain the bin directory which in turn contains the cogtr.exe file. In your script it is advisable to run the CD command to this folder (although this is not necessary if you can set the search path, etc, that is if you are really good at DOS). For example:

cd \Program Files\ibm\cognos\cognos10\bin

Next run the cogtr.exe with the following options:

cogtr.exe -c -n2 -lS1=”admin/admin123″ -g”My Cube” -m”c:\cognos\cube\location\mycube.mdl”

You can use other options as necessary. Here is a simple explanations on the sample options I used:

-c = This option loads a model file, interprets MDL statements, generates categories, and creates cubes.

-n2 = opens Transformer in batch mode with the Transformer application window hidden. There is no space between -n and the display_state argument, 2.

-lS1 = “admin/admin123″ = uses the signon “S1″ as specified above. admin/admin123 is the login name and password

-g”My Cube” = deploys “My Cube” as created above

-m”c:\cognos\cube\location\mycube.mdl” = location of your cube.

Detect Disappearing Member

Recently I have a requirement to create a report where we need to display either “List Before” or “List After” depending on information status for the month. Upon further checking I realized the information status can be determined depending on whether a member (Before) is present or not in a dimension:

Presence of “Before” member means to display “List Before”


Later part of the month, member “Before” will disappear and the report should display “List After”

I added the Flag level in the Query, then added a calculation to count how many members are present:

Then I added a string variable (BeforeOrAfter) to set a value depending on how many members the level returned. If the level returned three members (‘NA’,’BEFORE’ and ‘AFTER’), the variable will return the value “0″. If the level do not have “BEFORE”, it will return only two members (‘NA’ and ‘AFTER’) and the string variable will return the value “1″.

Then I set the BeforeOrAfter as the render variable of my lists. “List Before” will render when BeforeOrAfter = 0 and “List After” will render when BeforeOrAfter = 1.

When I run my sample report, I get the correct list as the information status for the month is currently “After” .

To try to simulate when “BEFORE” member is present, I change the string variable to:

BeforeOrAfter =if([Query1].[Count Flag] > 1) then (’0′) else (’1′)
Go figure it out 
I get this output:

Triggers

Not much documentation can be found about triggers. However we were able to successfully make it run on a Windows machine. We use this machine to create and publish Framework Manager models to our Cognos Server. Somehow Java is also included when FM was installed.

First thing we did is to copy these jar files to a folder in the Windows machine:

Let us call that folder c:\trigger\jar\lib

These jar files can be found in one of the the Cognos Server installation folders. In our case the exact folder is:

<Cognos installation folder>\webapps\p2pd\WEB-INF\lib

Next we copied these triggers files in another folder (let us call it c:\trigger\runtrigger)

I would like to mention that I did not find these files in our current Cognos Server installation folder. I however looked in other installation folders in our production and development environment. Anyway these files can usually be found in

<Cognos installation folder>\webapps\utilities\trigger

 Now we need to know where is the Java folder in our Windows machine. I said earlier that Java is installed together with Framework Manager.  I found it in:

<Cognos installation folder>\bin\jre\6.0

If you don’t have Java in the machine you intend to use, you can install it manually. In any case you need to know which folder is your Java installed as you need to set the JAVA_HOME variable value to that folder.

So we edit the trigger.bat file mentioned above to look something like this:

To manually run trigger.bat, open the command prompt window, then navigate to the folder where it is located in the command prompt:

cd c:\trigger\runtrigger

This is how to run trigger.bat:

trigger “http://<server ip address>:9400/p2pd/servlet/dispatch” “<cognos admin login>” “<password>” “<LDAP name>” “<trigger name>”

where

<server ip address> is the ip address or url of your Cognos server (example: 10.20.30.123)

<cognos admin login> is the login id of Cognos Administrator (or any user with enough admin rights)

<password> the password of <cognos admin login>

<LDAP name> is the name of the Namespace ID which can be found in Cognos Configuration

and <trigger name> is the name you give to your trigger.

List the Groups a User Belong To

Setting up security as a Cognos Admin requires to know which group a certain user belongs to. I created a simple report that will show a user’s group and identity:

The code for each column is shown here:

#sq(CAMIDList())#

#sq(CSVIdentityNameList())#

The limitation of this report however is that it ill display the identity and the group of the user who ran this report. So a system administrator has to ask a user to run this report and send a screenshot in order to find out that user’s groups and identities.

If you have access to your Cognos Content Store database however, you can use this SQL statement (or version of it) to list the groups of user(s):

You may need to change <user name> to the name of the user you want to query. If you need to query multiple users, then the query conditions should be changed a little bit. Also you might need to change the value in “b.maplocaleid = 96″  to suit you better.

Export to Excel Single Worksheet

I recently had this requirement to export a report to excel. The user wanted everything in one sheet. It was a long report so the data was spread over several sheet upon export.

The solution is to set the Rows per Page Property of the list or crosstab to maximum. Excel 2010 can accommodate 65536 rows. So if your report has less rows than that, you are in luck.

So to conclude, Rows per Page property of list or crosstab also affects the output export to excel, not just for HTML outputs, as I previously thought.

Free Up Disk Space

If server disk space issue is being encountered then its time to clean up those files in your Cognos server. We usually encounter errors, such as report saving and exporting to excel, scheduled run fails and even 100% CPU utilization when our server’s disk space usage goes above 90%. The higher the disk usage above 90%, the more the number of errors and the more severe.

Upon checking, these two folders,among others, consume the largest spaces:

\data\cqe\RTModels
\temp

The files in these folders are generated by Cognos to use temporarily when tasks and reports are executed. When a task is terminated abnormally, some of the files do not get deleted. So over time, the contents of these folders can eat up significant server disk space.

To delete files in these folders:

1. Stop the Cognos service.
2. Delete the files in the said folders.
3. Start the Cognos service.

After undertaking this process, our disk space usage went down to 75%. So regular Cognos services restart should be done.

Format Chart Tooltip Data

So you are creating this report with a list and a chart. One of the data to display is a percentage value. When the mouse is over a market the percentage value is not formatted. Naturally you want the data in the chart to be formatted as that in the list.

proceed to set your series data format to percentage.

After running the report, nothing changed. The data in the chart is not formatted as a percentage value. You spend hours and hours revising the formula (because of course in the real world the report is not as simple as this example), and setting the format to no avail (just like I did).

Then suddenly, when all hope seem lost, your focus shifted to the default measure. Whatif I format the default measure, you say to yourself. Then you tried, because maybe, just maybe…

Viola!!!!! The chart data is formatted!!! All is well.

Searching A Dimension Using Value From Another Dimension

I recently have a requirement wherein, there are two time dimensions and I have to get a member in the first time dimension (T1), which is two months earlier than the member in the second time dimension (T2).

The column of the crosstab looks like this:

The first thing is to determine whether the two time dimensions have some common values. In this case, it was a good thing that the captions of members in both dimensions are the same even if their business keys are different.

So the next thing to do is to get the current member of T2 using well… currentMember function:

currentMember([Package].[T2].[Time Dim])

Next I get the caption of the current member of T2, in this case I used roleValue function (you can try using caption as well).:

roleValue(‘_memberCaption’;currentMember([Package].[T2].[Time Dim]))

Now that I have the caption of T2 member, I’ll filter T1 with the condition that the caption should be the same as T2:

filter([T1]; caption([T1]) = caption([T2])) or

filter([Package].[T1].[Forecasting Time].[Forecasting Month];roleValue(‘_memberCaption’;[Package].[T1].[Forecasting Time].[Forecasting Month]) =roleValue(‘_memberCaption’;currentMember([Package].[T2].[Time])))

Now you have the member in T2 which has the same caption as the current Member in T1… or in other words, the month in T2 which is the same month in T1.

However the filter functions returns a memberset. We need a member. But we do know that while the filter function returns a memberset, it will contain only 1 member. So we extract that member using the item function.

item([memberset], index)

or

item( filter([Package].[T1].[Forecasting Time].[Forecasting Month];roleValue(‘_memberCaption’;[Package].[T1].[Forecasting Time].[Forecasting Month]) = roleValue(‘_memberCaption’;currentMember([Package].[T2].[Time])));0)

Now I got the member, I want the member two months prior. I can use the lag function:

lag(item( filter([Package].[T1].[Forecasting Time].[Forecasting Month];roleValue(‘_memberCaption’;[Package].[T1].[Forecasting Time].[Forecasting Month]) = roleValue(‘_memberCaption’;currentMember([Package].[T2].[Time])));0);2)

Filter not in Display

Here is another very common mistake in both relational and dimensional reporting. In this example a report contains Year, Month and Month No.

Somehow, there is another calculation/condition that chooses certain month values, like for example Month No is 10 or 11. In actual reporting, this may or may not be a fixed value. Most of the time it is calculated or derived from somewhere else, like a prompt value.

Now we insert this filter in our list and we get the correct result.

However, this filter is not needed in the display output so it should be removed. It is included in our query however so things should be just fine, right?

As seen in the previous image, removing the filter query item from the list makes it ineffective even if it is still included in the query. But you do not need this in the display, so how do you make it effective?

The solution is to select the whole list, then open the Properties property (sorry can’t help it), check the query items that are not included in the display that should take effect.

Now the output is correct.

The same technique can be used if a crosstab is used instead of a list. It is a little bit different however as the Properties propery is available for each item in the crosstab.

How To Join Without Using Join in Framework Manager

The title says it all. This is not a usual issue that one faces in working with Cognos. However some smart-alec interviewer will present this scenario to a poor up and coming developer.

So how will you get the correct data from two query items (in this example Time and Fact) that are not joined? And accomplish this in Framework Manager environment only.

First thing is that if you take data items from two query items without a join (direct or indirect) , you’ll going to get a cross join. In the Framework Manager governor settings, a crossjoin is prohibited by default. So you need to allow cross join from the Project menu -> Edit Governor Settings.

Create the new query, and drag the data items you need.

Open the filter tab of the new query subject. Add the condition you would use when you would join the two query subjects. In this case we equate the TIME_ID columns of both Time and Fact.

How this work is that filter and join expressions are both added in the where clause of your query. You can verify this fact when you check the generated SQL statement of your new query subject.

Sets

The Set function look quite mundane and even unimportant at first glance that you would not think much of it. In fact, I did not see it used during my search for solutions to Cognos problems. I just stumbled upon it by chance. But when I did, I considered it one of the milestones of my dimensional reporting study.

I compare the Set function to ropemanship. In your daily routine, tying the right knot is not really essential; at best it can make things easy for you. However in certain situations, like rappeling or hanging over a cliff and a rope is tossed to you, knowing how to tie the right knot is a matter of life and death.

Ok so that might be a bit overdramatic. But the very same logic can be said about Set – it usually just make things easy for you, but there are times when its the only solution to a problem. Now lets get to the technical details; if one have the following members in the query: [Jan], [Feb], [Mar] and want to use them as columns for your crosstab, you can drag them to the columns section one by one and place beside each other:

Or you can use a set: [Chosen Months] = set([Jan];[Feb];[Mar])

Both will give the same result.

So whats the big deal with sets? Well you can add other members as long as the said member belongs to the same hierarchy: [Chosen Months] = set([Jan];[Feb];[Mar];[Q1]) And you can also add calculated members from the same hierarchy: [Last Year Q1] = lag([Q1];4) [Chosen Months] = set([Jan];[Feb];[Mar];[Q1]; [Last Year Q1])