Someone has any experience on PTC Integrity data extraction?

[ptc-4979633]

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

[MarcusRussell]

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, ...)?

[ptc-4979633]

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.

[MarcusRussell]

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

[ptc-4979633]

Marcus

1. Security: it will not be a problem, we are the owner of the DB, I can have enough access right.

2. Schema subject to change: It is a problem, and when I performed the analysis, I found the risk. but maybe I have to accept, if we use JAVA API to develop the program, it will make at least double effort. And for future changes, I plan to design a middle layer to reduce the rework for future.

3. This is a problem, I don't have a solution yet, all dynamic compute items, I may have to read the logic then reimplement in ETL system.

For future, we want to design the system to track all history data, also near real time ETL. And the data volume of PTC Integrity is not huge, I think we will not have big performance risk.

Anyway, thanks, your answer is very helpful.

[MarcusRussell]

Hi Raymond,

Regarding the security, I meant the security implemented within Integrity rather than your access to the DB. For instance, as "joe average user" logging into Integrity, I might not have access to certain secret projects. However if the data warehouse goes directly to the database, and I have access to it, I might be able to see things I shouldn't. It depends on who is able to access the DW and what kind of data you have in there. It was just something to consider.

Depending on how complex the computations are, it might be easy enough to reimplement them in your DW (particularly if they just do basic math operations within the item).

Feel free to open a support request with PTC and request better 3rd party access to the Integrity database.

Cheers,

Marcus

[ptc-4979633]

Hi, Marcus

I will perform the row level security control in DW layer, so it will not have risk on security, the ETL id which is used to extract data from PTCI will have enough read priviliedge to query data from all of PTCI tables.

For Dynamic Formular issue, I checked the data in our system, less than 5% columns, and this kind of columns cannot support summary, such as 'Percentage", than I may implement in JAVA API or Using some other reporting tool to rebuild the columns.

And, I have a question, where i can get the formular in DB?

For example, I know Issue2074 is a dynamic computed item, I can see the formular in UI, and I can get the formular via JAVA API. but whether I can read that from DB directly? I cannot find the table.

[matt_giltaji]

We do similar extraction from the DB backend into a DW. From what we can see, the formulas are stored in the Fields table in the DB, but they are in an encrypted format.

Our workaround has been to add special handling to those fields in the ETL to generate them as part of the process.

For example, we have a field which is an "FVA (field value attribute)", meaning it is a reference to a field on a related item.

To generate that value in the DW, we use a SQL join to IIMap table to get the related Issue ID, then join to Issues table again (or one of the Text Tables if its a text field, picks table if its a pick field, etc) to get the value.

We do similar things for computed fields which are not stored in the DB, basically redoing the app-side calculation in SQL.

Hope that helps,

Matt

[ptc-4979633]

Matt

Thanks!

For those formulars, I have to open from PTC Integrity UI to read them, then manually translate into ETL program, or use JAVA API to calculate them.

For FVA, I guess the logic is:

in table FIELDS, there is a column named "DATATYPEID"

0: Integer

1: Float

2. Tiny Int

3. Datetime

4. Text, then from another column TEXT to get where I should get text from, in Text0,1,2 they are rich text, in Text3,4 they are pure text

5. User, get data from table Users

6. Group, get data from table Groups

7. Another issue, as your desc, getting data from IIMAP, then go back the table again to get data

8. Attachment, get data from IAMAP, and Attachments tables

There are some other columns in the table FIEDLS I cannot understand, could you pls share the logic with me?

1. Multiplevalue

2. Flag

[matt_giltaji · Accepted solution]

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

[ptc-4979633]

Thanks Matt

Your answer is really helpful to me!!!!

[MarcusRussell]

As Matt indicated I believe the computation formulas are stored in the fields table, but as a serialized Java object so they are unfortunately not easily decipherable.

[ptc-4979633]

understand, thanks