Skip to main content
P
ptc-49796331-Visitor
1-Visitor
February 22, 2013
Solved

Someone has any experience on PTC Integrity data extraction?

  • February 22, 2013
  • 1 reply
  • 6574 views

I am working on a DW project now, and PTC integrity is my primary data source. Anyone has some experience? We can discuss here.

    Best answer by matt_giltaji

    The multiplevalue flag is for picklists, users, groups, and relationships. Since the columns in Issue table will only have space for a single value, if the multiplevalue flag indicates that the value is stored somewhere else.

    For the picklist, user, and group types, the field will be stored in a special table of the form FieldXYZ_Set, where XYZ is from the ID column in Fields table. There will be one or more rows in the FieldXYZ_Set, one for each selection in the field. The Issues table column will hold a reference to the ID value in the FieldXYZ_Set table, and the value column in FieldXYZ_Set will hold the value for the selection.

    An example of the join for one of our multivalue pick fields is below:

    Join Field530_SET ON Field530_SET.ID=Issues.Field530 Join Picks on Picks.ParentID=530 AND Picks.IntOperand=Field530_SET.VALUE

    The multivalue flag can also be used so that you know when your join might start adding additional rows, even if you are pulling a single Issue.

    The flag field in Fields table is used to determine some other encrypted data about the field itself. I am not sure what all the values mean, but 2048 seems to indicate that the field is not stored in the standard tables (Issue, Picks, IIMap, etc). I would guess that the other flag values have to do with how the system uses those fields in calculations or something like that.

    Matt

    1 reply

    M
    MarcusRussell1-Visitor
    1-Visitor
    February 22, 2013

    Hi Raymond,

    There are many ways to extract data from Integrity, e.g.:

    • command line scripting (im issues)
    • running reports (im runreport)
    • Gateway
    • using web services
    • using API
    • using trigger scripts

    What type of information are you trying to exact? What is the purpose (report, integration, migration, ...)?

    P
    ptc-49796331-VisitorAuthor
    1-Visitor
    February 25, 2013

    Hi, Marcus

    Thanks for your reply.

    Because I need to build a DW, and extract data from PTC Integrity then load into DW.

    Currently, I am trying to extract data from PTC Integrity table directly, I did some investigation, most of information need to extract from Issues, Issues2, Issues3, Text0 - Text4, IIMAP, IAMAP, Types, Fields. Almost 80% analysis done, but still have some columns cannot find from those tables.

    M
    MarcusRussell1-Visitor
    1-Visitor
    February 25, 2013

    Hi Raymond,

    Sounds like you are well on your way. There can certainly be some performance gains or added flexibility in going directly to the database, but I generally recommend against it for a couple reasons:

    1. Bypasses security - if this is a concern in your DW, it can be difficult to replicate the security system. If you are just doing aggregate reporting or for execs who are allowed to see everything, this might not matter.
    2. Schema subject to change - release to release we may change the schema (e.g. moving text fields into a separate table was a recent change; they used to be part of IssuesX) which could require rework on your DW
    3. Some things computed on-the-fly by server - phases, ranges, and dynamic computed fields are computed on-the-fly by the server, so these won't exist in the database at all. These might be the columns you are currently missing. There is actually no easy way to determine (from the database) what "phase" an item is in, because the state-to-phase mapping is stored in the DB as a serialized Java object.

    Because of these reasons, I typically suggest pulling data via the Java API as this will take care of all the table joins and figuring out computations for you. I suggest trying to extract the data at off-peak times if possible, and potentially setting up different frequencies depending on how up-to-date you need to be (i.e. gather certain items hourly, others daily during off-peak if you have a slow time, etc.). You can run dynamic queries through the API to gather just changed items and update your DW that way (im issues --queryDefinition="field[Modified Date]...).

    However, it sounds like you are pretty far down the path of direct database access, so you probably don't want to do a huge rework. If you are running into specific issues feel free to post here and we can try to figure out how best to work around them. You might need to supplement your direct queries with some API calls to fill in the missing pieces.

    Hope that helps,

    Marcus

    Terms & ConditionsAccessibility statement