ShowTable of Contents
Background
Metrics is a refreshed component in Connections 4.0. The new Metrics application employs the analytic capabilities of the IBM Cognos Business Intelligence to collect and display statistics that show how people use Connections.
As an important function of Metrics, you can break down the metrics report with a user attribute by selecting a different category in Group by filter. By default, there are 4 categories: all people, geography, department and role.
For example, in report “Number of unique authenticated visitors”, by default, you will see a trend chart which is regardless with any visitor attribute.
* The report shows in All People.
When change Group by to Geography, the report will be :
* The report shows in Geography.
The total visitors number 201 is broken down by the geography. The geography information is associated with each visitor in the profile database. In the most cases, the user data is from the LDAP as the source. In this sample, among the 201 visitors, 87 people are from China, 111 people are from US, also 3 people has no geography information associated with.
Problem Description
In order to make “Group by” to function well, the corresponding user attribute must be provided in the profiles database. The user attributes are filled during the profile population, at the step of Profiles database mapping. In that step, the attributes of ldap entry will be mapped to the database fields of profiles.
If no mapping is made for the required attribute between profiles and ldap, the users in profiles will lost the information. As the result, Metrics cannot break down the report by these attributes.
As the above sample, if the attribute standing for geography is not mapped during population. The field will be null for all users in profiles. In this situation, group by geography will become meaningless, like:
Solution
If you miss the chance to map attributes during the profiles population, it cannot be fixed by running the population wizard again, because the tools will skip the users if they have been existed in profiles database. To make it up, you need to use TDI script to synchronize the data between the ldap and profiles. After that, use metrics function to synchronize the changes in profiles database to metrics database and make it be effective.
1. Use sync_all_dns command to make up attribute values to profiles database.
The sync_all_dns command can be used to take changes from the ldap and apply them to the Profiles database. As the mapping values configured in map_dbrepos_from_source.properties are compared to determine if an update is needed, we can modify this property file to add the mapping on the interested attributes. That will make sync_all_dns feel the mapped attributes have different values between ldap and profiles database, so it will perform the synchronization to push the attributes values from ldap to profiles database. Then our goal is achieved.
1.1 Prepare the map_dbrepos_from_source.properties.
Assume you have used the population wizard to populate the people information, then you can find the map_dbrepos_from_source.properties file at the folder ..\Wizards\TDIPopulation\TDI. The path will be different upon the OS type. For windows, the accurate path should be ..\Wizards\TDIPopulation\win\TDI. The file is updated according to the setting you made in the last time you run the wizard.
-
Open the property file. It is an attribute mapping list. For each mapping, the left part is the database field of profiles, the right part is the entry attribute of ldap.
-
Find the field you want to make up. In the above sample, the field used by Metrics as geography is countryCode.
-
Identify the county attribute in your ldap. For example, TDS ldap uses attribute “c” to store the country code.
-
Fill the mapping for country code such as:
countryCode=c
1.2 Set the profiles_tdi.properties file to control the synchronization process.
You can set the following properties in the profiles_tdi.properties file to control the synchronization process. The file also locates at the ..\Wizards\TDIPopulation\win\TDI.
One option should be mentioned is: sync_updates_show_summary_only
When set to true, this property shows only the records that need to be changed, but does not make the changes.
More information can be found in this link: Synchronizing source changes such as LDAP with Profiles
1.3 Run sync_all_dns to synchronization
Be sure you are in the folder of the wizard you have used to populate profiles database, because sync_all_dns command depends on some property files generated after running population wizard. To reduce the risk, suggest to set sync_updates_show_summary_only to true in the first time running. When the command is finished, the following files in the TDI solution directory record the changes made during synchronization.
-
employee.adds
-
employee.delete
-
employee.error
-
employee.skip
-
employee.update
Check the files to see if the changes are consistent with the expectation. If everything is right, set sync_updates_show_summary_only to false and run sync_all_dns command again.
After the synchronization, if authorized, you can check the employee table in profiles database to see if the new mapped attribute information has been filled in the corresponding field.
Note: If the mapping attribute you selected is in the following list:
-
countryCode
-
deptNumber
-
orgId
-
employeeTypeCode
-
workLocationCode
You need to provide supplemental user data for the code-name mapping for your attribute.
More information can be found in this link: Supplemental user data for Profiles
2. Synchronize the profiles changes to metrics.
After the additional people attributes are supplied in profiles database, you still need to synchronize the change from profiles database to metrics database, because the reports retrieve the people attributes from metrics database, not profiles database.
2.1 Update user table in metrics database.
To do that, you must have the authority to handle the database operation. Run the following SQL in metrics database:
UPDATE METRICS.F_TRX_USERS
SET UPDATE_TS = NULL
The purpose is to let Metrics app to be aware that the users information have changed and need to be refreshed.
Note: The SQL will be a little different from DB types.
2.2 Synchronize the people information from profiles to metrics
There are 2 ways to do that.
http://[connection_hostname]:[port]/metrics/service/PopUserMetadata.jsp
After synchronization, check the F_TRX_USERS table on the fields attribute1/2/3, the additional people attributes should be filled in these fields.
Limitation: The change made in this article will only impact the metrics reports in the future time period. It cannot trace back to the reports on the previous time range, unless run build-all to rebuild the whole power cubes. If there is any incorrect or meaningless user attribute values saved in metrics user table before, you will still see those incorrect attributes even after running build all, although the percentage will be zero. To totally solve the problem, you need to run cognos-configure-update.bat/sh before running build all.
About the authors
Zhi Gang Lin is a Staff Software Engineer working for IBM since 2007. His current role is as the Team Lead for Functional Verification Test for the Metrics feature in IBM Connections, before which he worked on Moderation in Connections, Lotus Quickr, and Quickr & ECM integration. He can be contacted at linzhig@cn.ibm.com
Chun Ling Li is Advisory Software Engineer working for IBM since 2004. Her current role is the team lead of development for the Metrics feature in IBM Connections. Before that, she worked on Quickr Administration Console, IBM Symphony and Lotus Notes development. She can be contacted at lichunl@cn.ibm.com.