You can read the Enterprise Tip of the Month here also authored by Bruce Hulse and you can also read the Desktop Product Focus of the Month here and the Desktop Tip of the Month here authored by Jon Jarvis.
| PTC Technical Specialists Newsletter - January 2011 |
Product Focus : Using Info*Engine Queries with Windchill Business Reporting |
|---|
Overview
With Windchill Revision 9, PTC introduced the use of Cognos Business Intelligence as a means for producing high quality reports against Windchill data. In order to supply Windchill data to Cognos, you need to use either QueryBuilder queries or Info*Engine tasks registered against a specific SOAP type identifier called com.ptc.windchill.enterprise.report.ReportTasks. In this writeup, we will focus on using Info*Engine tasks.
Windchill / Cognos Interface
The first thing to understand is how Windchill and Cognos work together to produce reports. Here is the rough sequence of events that occur when a report is run:
- Starting with the list of reports in one of the various Reports subtabs in the UI, the user chooses a report to be run. Execute the report by clicking the icon under the View report column
- The system consults the wt.properties switch wt.reporting.thirdparty.enabled switch to see if it is to be run by Cognos (true) or by QueryBuilder (false). Unless it is set to true, none of the following applies, as Windchill will just run the pre-Rev 9 QueryBuilder reporting interface.
- In the report definition entry under the Reports subtab, each report has an optional JSP prompt page that can be specified to provide some sort of parameter setup for the report. Each JSP is responsible for knowing what parameters it must create for the report it is requesting. After getting the user’s input, a URL is created that now includes all parameters that the report needs to execute without further prompting.
- If no JSP page is specified, the report is executed using only basic parameters that are created by Windchill based on which Reports subtab was used to launch the report. The parameters automatically supplied are shown in the table below. If you eventually build some of your own JSP pages, this will come in handy.
Param / Context | Part | Home | Change | Product | Program | Project | Library |
cookieName | PartStructure-[part oid] | ||||||
containerType | WTContainer classname | PDMLinkProduct classname | Project2 classname | Project2 classname | Library classname | ||
oid | Structure oid | Report oid | Report oid | Report oid | Report oid | Report oid | Report oid |
axlcontext | AXLContext oid | ||||||
part | Short structure ufid | ||||||
actionOid | Container oid | WTUser oid | Product oid | Project2 oid | Project2 oid | Library oid | |
containerId | Container obj id (just the number) | Container obj id (just the number) | Container obj id (just the number) | Container obj id (just the number) | Container obj id (just the number) | ||
applyDCSToUnresolved | True/false | ||||||
reportOid | Report oid | ||||||
action | ProduceReport | ProduceReport | ProduceReport | ProduceReport | ProduceReport | ProduceReport |
- The URL is executed by the Cognos servlet as defined in your environment. Cognos looks up the referenced report and starts to process it.
- Should there be a Prompt Page defined in the report, it will be presented to the user for their input. The prompt page capabilities include several complex widgets for producing lookups and pulldowns, but they are much more suited toward traditional database queries than for Windchill. While you can accomplish a lot of functionality with the prompt pages, you can also get into some very large query result sets which you really don’t want to have to wait for. It is a much better practice to use the JSP prompts at the Windchill report definition to do any necessary prompting as it will be more efficient and can be made to look more like the Windchill UI.
- The query parameters for all queries used in the report will be examined and a default prompt page will be created for any parameter not supplied with a value on the Cognos URL. While this works, the Cognos default prompt pages are extremely primitive (all text fields into which you type the parameter values), so it is not recommended to allow this to occur unless they are very easy to remember text fields. Having to type Windchill object IDs into this UI is not at all user friendly. Use a JSP page to insure you have all your parameters selected in a much more user friendly way. Here is a sample of a prompt page – in this case, there is a parameter called state that must be filled in with the internal Windchill name of one of the valid lifecycle states. However, the user can type anything in, resulting in a failed query:
- For each query identified in the report, Cognos will do the following:
- Finally, once Cognos knows that it has all of the parameters, it makes a SOAP call back to Windchill and requests it to run the Info*Engine task registered against the query name as defined in the Cognos Report Studio. All of the needed parameters that Cognos has collected are sent with the query for Windchill to use to create an XML dataset to send back to Cognos.
- Windchill runs the task and creates the XML result data which is transformed into the Cognos XML format and returned as the SOAP response.
- Finally, once Cognos knows that it has all of the parameters, it makes a SOAP call back to Windchill and requests it to run the Info*Engine task registered against the query name as defined in the Cognos Report Studio. All of the needed parameters that Cognos has collected are sent with the query for Windchill to use to create an XML dataset to send back to Cognos.
- Cognos formats the data as directed by the report definition and displays it to the user. In this particular example, it is a list of documents at the RELEASED state.
Cognos Template Task File
So an Info*Engine task has to be configured with three key pieces of information in order to participate in a Cognos report.
This is a template task for working with Cognos:
<%@page session="false"%>
<%@taglib uri="http://www.ptc.com/infoengine/taglib/core" prefix="ie"%>
<%@page import="java.util.*"%>
<%@page import="com.infoengine.object.factory.Group"%>
<%@page import="com.infoengine.object.factory.Att"%>
<%@page import="com.infoengine.object.factory.Element"%>
<%@page import="com.infoengine.SAK.BasicTasklet"%>
<%@page import="com.infoengine.util.IEException"%>
<!--com.infoengine.delegate.def
@delegateName YourTaskNameHere
@repositoryType com.ptc.windchill
@typeId com.ptc.windchill.enterprise.report.ReportTask
@installDelegate true
-->
<!--com.infoengine.soap.rpc.def
Fill in task description here.
@param java.lang.String paramname description
@return INFOENGINE_GROUP ${out} {columns: java.lang.String val1, java.lang.String val2}
-->
<%
BasicTasklet ie = null;
Object me = this;
ie = (BasicTasklet) me;
String gout="out";
%>
<ie:webject>
<ie:param data="<%= gout %>"/>
</ie:webject>
Notice the three highlighted sections in the task. These are critical to the proper operation of the task during Cognos processing.
The yellow highlighted section is a comment that can be used by Windchill to automatically register the task as a SOAP method for the Cognos SOAP type identifier. Replace YourTaskNameHere with the name you want to have shown in the Cognos Report Studio. The remainder of the highlighted text should remain exactly as shown. When used with the tools that auto-register task delegates (which we will not cover here), this task will become the soap method for Cognos use with the name you specified.
The blue highlighted section is a comment that is used by SOAP tasks to produce WSDL for the SOAP call (with one special addition for use with Cognos). Replace “Fill in task description here.” with the multi-line description of what the task does.
Then produce one @param line for each parameter the task needs to accomplish its function. Each @param line needs a datatype, a name and then a description of what the param is for. There are few, if any cases, where the datatype for a Cognos query will not be java.lang.String.
The @return line is the really interesting one for interfacing with Cognos. It supplies the name of the group that will be returned from the task, then a list of data columns and their data types that will be returned in each element of the task result. In the template, the @return is indicating that the task will return a group called “out” and that there will be two data fields in the result – val1 and val2, both of which are java.lang.String objects. In many cases, you will return String objects, but whenever you intend to do math on the data, you should return the column as java.lang.Long. Whenever you need to manipulate dates in the report, return the Windchill date field as java.sql.Timestamp.
The green highlighted are in the Return-Groups webject indicates the name of the group to be returned at the end of the task. Since the @return comment specifies the name that will be returned by the task, you need to insure that the @return entry and the returned group name matches or Cognos will not see any of the data.
Register the Task as a SOAP method for com.ptc.windchill.enterprise.report.ReportTasks
To register the task as a SOAP method, use the Task Delegation Administration link on the Info*Engine Administrator page. This results in the following dialog:
When the Task Delegate Administration dialog first comes up, you must click the Create Delegate link on the left side. Then, fill in the following fields:
- Repository Type – choose com.ptc.windchill from the dropdown list
- Name – enter the name you wish to see in Cognos for this Query
- Source URL – enter the relative pathname from Windchill/tasks to your query task file.
- Type Identifier – choose com.ptc.windchill.enterprise.report.ReportTasks from the dropdown list.
- Description – Enter any notes you might wish to make about the delegate.
Press the OK button to create the delegate.
Update the Windchill Model for Cognos
Once you’ve registered the task, you need to update Cognos’ view of the Windchill data model. This is done from a Windchill shell using the command line:
java com.ptc.windchill.enterprise.report.ReportHelper updatemodel
You will be prompted for authentication information. Enter your credentials and press OK. The program will run for a short period of time and then indicate if it was successful or not. Once this is done, Cognos will now see your new task. Should the ReportHelper class fail, you either have not activated the wt.reporting.thirdparty.enabled switch or you have an error in the soap.def comments in one of your cognos task files (those that are registered in the Task Delegate Administration page)
You must run this program whenever you have changed any task delegate information in com.ptc.windchill.enterprise.report.ReportTasks and / or changed any of the soap.def comments in any task. If you are merely updating the functionality of the report and not its “SOAP signature”, you can just edit the task and immediately run the report again.
Note: you must restart the Report Studio tool whenever you run ReportHelper. The Report Studio does not monitor the model information for changes, so it must be stopped and restarted in order to see an updated model.
Keys to Success
Proper data types
- Use java.lang.String as the datatype for all @param entries
- Use java.lang.Long for number fields
- Use java.sql.Timestamp for date fields. DO NOT USE java.util.Date datatypes. It does not convert Windchill date fields properly for use in Cognos.
Don’t return more fields than necessary
As you execute queries in Info*Engine, the groups will pick up any number of foreign key references as ufids (a very long object identifier string). If you do not need them (and there are very few instances of needing to do so, you should make a pass through the result group just before finishing the task and remove any attribute name not expected by the query. This will make the task and report run faster since there is (possibly much) less data being sent back to Cognos.
Filter records in the tasks, not in Cognos unless absolutely necessary
While Cognos does allow for post-query filtering of results, it is not a good idea since there is no way to for Cognos to communicate the filters back to the Info*Engine task. This means that in order to filter for number=’12345’, you would have to query for all object instances, possibly returning millions of entries, then have Cognos throw away all but one. Not at all efficient. Create the tasks to perform the filtering as part of their work and return as few records as possible to Cognos.
Use Cognos date functions to compute deadlines, determine status
If you need to do operations in the report like highlight a column in red anytime the data value is past today’s date, you need to send java.sql.Timestamp values back to Cognos. It makes it considerably easier to do the calculations using data of this type.
Use Cognos math functions to perform in-line calculations
If you know you are going to need to perform calculations on the data field when you get into Cognos (maybe cost * quantity), return the fields as java.lang.Long datatypes so conversion calls will not be needed anywhere in the report.
Summary
We’ve covered how the interface works between Cognos and Windchill. We’ve seen the basic template files to use when creating Cognos queries as well as the delegate registration and model update procedures. All you have to do now is to fill in the rest of the task with the real work to supply the data to be used in your Cognos reports.
You can read the Enterprise Tip of the Month here also authored by Bruce Hulse and you can also read the Desktop Product Focus of the Month here and the Desktop Tip of the Month here authored by Jon Jarvis.
