Sunday, August 4, 2013

Teamcenter Query Builder


Query Builder is one of frequent used module in Teamcenter for creating object based search query. Query Builder is power full tool for quickly configuring Teamcenter for object based search query based on attribute criteria. It is also used in report builder for creating report based on query output. I covered it in detail in my Report Builder blog. In this blog I will explain in detail about local query builder configuration and basic concept to create or modify query.

Basic of Query Builder:
Teamcenter Query is based on Teamcenter persistence data model which I cover in Teamcenter Data Model. Following are the basic characteristic of query in Teamcenter.
  1. Query is created against any of the Teamcenter persistence class.
  2. Query Criteria can be defined either on attribute of target object or for the related object which are related to search class through either GRM relation or reference.
  3. Criteria can be made invisible to end user by making  use entry field empty.
  4. Predefined keyword variable can be used against some of the attributes. These variable values are executed at runtime. For example keyword variable $USERID will resolve to session userid when query will be executed.
  5.  Teamcenter also support keyword query which allow to search dataset files content, that contain a specific keyword or combinations of keywords.
  6. Query can also be customized through Teamcenter use exit.
  7. Query results always shows list of object of class which is defined as search class in query builder.
Steps for creating Query:
Below description shows list of task required to be done for defining new query in Teamcenter through Query Builder.
  1. Define Query : Before creating query, you have to first define what exactly required to be search and in what context. Context means some condition or criteria which user can either provide while executing search or defined as basic criteria for search itself. For example, if you want create a query for find specific Data type only, then you defined Object Type is query builder and make the field invisible.
  2. Map to Data Model : After defining search requirement , you map it to persistence data model of Teamcenter. This become your search class in Query Builder. Basically the output will be shown for instance of this class only. It is assumed the admin user who will be building the query will be aware of basic data model of Teamcenter. I covered it in the my Teamcenter  Data Model blog. Once you select the Search Class, automatically its attributes and GRM relation are shown in Attribute Display area. The attributes can be filter to show only class specific attributes or all inherited attributes from all parent classes.
  3. Define Search Context : Then defined search criteria or context in which search required to happen. For example if you want to get Item Revision which has attachment of specific data set only. In this case you have to  traverse from search class i.e Item Revision  to Dataset through predefined GRM relation shown in attribute display area or through reference by from imanrelation object to  Dataset (Primary object to Secondary Object).
  4. Use Search Criteria Field : At last you defined the the criteria which you want to expose to user for executing the search. This defined list of attributes shown to User whose value can be set by user before query execution. The attribute can be of search class or related class.

Query Builder Dialog Section
Query Builder Modules has three important sections as shown in below image



Search Class: This is target class against which search will be executed. The search result will be list of instance of this class. As defined above section this will be base class for query. You can navigate required class from Class Selection dialog which shows Teamcenter hierarchy data model.

 

Attribute Selection: This provide list of attributes of search class. Option provide to see only display attributes for a given class or all attributes its inherited from all parent class hierarchy. This section help to select attribute used for search Criteria

Search Criteria: This defined criteria for context search as well user search criteria field. Following are field of Search Criteria section
Boolean Rule : This connect multiple search clauses in search criteria through And\OR condition. AND condition required related clauses should be satisfied where as OR condition means any of them.

UserEntry Name : Shows the display name shown in query. It refers to query locale file for actual display name of attributes search dialog. Make it empty if you want to hide some of clauses.

Logical Operator: This is most important field of Search field which defined actual query condition required to be executed. It basically define rules of attributes are evaluated against the value provided for execution. For example for date attribute you can defined greater then or less then Operator to execute query for before or after date clauses. Query builder support almost all sql operator clauses. The below image shows all support operation condition in Teamcenter. 


Limitation of Query Builder:
Although Query Builder is very powerful tool of Teamcenter for configuring object based query, but it also has some limitation due to Teamcenter data model design.
  1. Query can be only be configured for persistence data object.
  2. Query can be only build for object in the context of related object defined in Data Model.  That means query against two independent object which are not related through some relation or reference can’t be created.
  3. There is no way to filter Search Results based on specific filter rules. For example if I want to get latest Revision of Item, it can’t be achieved through configured Query Builder search. The results will be shows all revision of the item. The reason is all Item Revision of Item are related to Item only which is of reference relation and there is no way defined search criteria which filter old revision.
  4. Query rules can’t be build for joint query or set of and /OR clauses as can be defined in SQL statement. For example in SQL you can build where clause based on set of and / or clause through grouping the clauses throuch under different  bracket () . 
  5.  Query results only shows list of instance of search class only. You can't see related object along with search object in query results dialogs.

See Also :

Teamcenter Data Model

Teamcenter Report Framework

 

 

 

12 comments:

  1. We can get latest revision in query builder by stting activesequence!=0 in search criteria

    ReplyDelete
    Replies
    1. that will only give latest sequence of all revision

      Delete
  2. Hi,

    Nice Article..

    I had a doubt..
    I have a hier like

    Item
    -Document
    -Dataset

    I have created a simple saved query in query builder with search class as Item

    Document:IMAN_reference.Dataset:IMAN_reference.object_name

    But its not working..
    Can you please elaborate the reason why its not working if possible?

    Thanks

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Hello Manoj,
      I need to find the empty UGMASTER datasets with no named refs .
      I checked it in gtac but no clue.
      May be if you can help me with this query
      Thnx & Regrds,
      Yogesh

      Delete
  4. Hi, Any idea or help you can provide in registering a custom query call back.

    ReplyDelete
  5. Can you give an example for "Define Search Context":

    Define Search Context : Then defined search criteria or context in which search required to happen. For example if you want to get Item Revision which has attachment of specific data set only. In this case you have to traverse from search class i.e Item Revision to Dataset through predefined GRM relation shown in attribute display area or through reference by from imanrelation object to Dataset (Primary object to Secondary Object).

    ReplyDelete
  6. Hi,I want to query all item revsion which have pdf as attachment,for all relations.How can I do this ?

    ReplyDelete
    Replies
    1. As far as I know, there is no such query which can query all item revisions having pdf with all the relations. However we can query for pdf with a specific relation. I would be interested to know if there is any other way around.

      Delete
  7. Pl anyone tell me what to do if I want lov in search criteria for selection.

    ReplyDelete
  8. How can I select a default value inside Query Builder for an attribute that is member of an Item Revision object and related to my search class (i.e. Schedule via project_list). That attribute has an LOV attached but the LOV is not shown in default value field, so I have to "guess". Anyone?
    Query attribute looks like this: project_list.ItemRevision<-project_list.Attribute

    ReplyDelete