cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Learn all about the Community Ranking System, a fun gamification element of the PTC Community. X

Transferring some data from Integrity to a SQL Server databese

ptc-5349095
1-Newbie

Transferring some data from Integrity to a SQL Server databese

Hi,

I'm a newbie in Integrity (just two days since I installed Integrity client) and I need to transfer some data from Integrity 10 to a SQL Server database.

I tried using CLI "im exportissues " into an Excel file, but my preferable way is to be able to use SSIS (SQL Server Integration Service) to connect directly to our Integrity database which is a SQL Server database and extract required data by running a SQL query and finally transfer them into our data warehouse to do some complicated reporting involving other data from various sources.

So I'm wondering is there any way to define an Integrity query having high level field names and as output get the proper SQL query containing low level real physical database names, and use this query as my data source in SSIS package?

This is a very high priority task and I need to do it very soon.

Thanks,

1 ACCEPTED SOLUTION

Accepted Solutions

Hi Gila,

I agree with both Kael and Matthias that contacting integrity support is the best option.

With that said, the Integrity db schema is fairly stable (but as Matthias warned, it could change drastically without much notice in a future upgrade), so it is possible to set up some queries for pulling and transforming the integrity backend data into your data wareshouse. There is not a single query which pulls everything, some of the field metadata is encrypted in the DB so it is not useful and needs to be supplemented with information from your Integrity Admin.

If you want to go the DB to DW direct route, here are some pointers from my company's experience with it.

1) Talk to a support rep (or your sales rep) about getting a copy of the integrity schema documentation (MS word doc). Your company may need to sign an NDA, but it is very helpful for understanding how the backend DB is structured and how it is changed when your Integrity Admin adds new fields or changes picklist values, etc.

2) Take a look at this question and the answers: http://communities.ptc.com/message/198974, it has a good discussion about this topic and some detailed info.

3) The way that individual field values are stored in the DB varies depending on the field type and a few other factors. Most of the built in fields (like State, Type, Summary, Assigned User, etc) are stored differently than the custom fields added by Integrity Admins. Custom text fields, pick fields, date fields, and relationship fields are all stored in slightly different ways, but once you understand how each field type works it should be easy to start grabbing the field values in bulk for your SSIS job.

4) Not everything stored in the DB is readable with plain SQL. Some attributes are encrypted, such as which types contain which fields, or the formulas for computed fields. Other field values are not even stored in the database at all (such as Field Value Attribute type fields and dynamically computed fields). For those fields, I would recommend working with your Integrity Admin to document the various formulas involved so that you can recreate the values with some SSIS job. You may also want to document the field metadata manually in some side table so that you can refer to it later on.

5) A good exercise to help understand how the values are stored is to look at an Integrity item in the desktop client or web interface, then try to look up the fields for that item in the database and verify that your SQL is correct. Mostly it is just simple joins from Issues table to other tables, but it can get complex with multivalue fields and other values with an "Active" flag

6) It may be helpful to use a staging database in between the live Integrity DB and your DW. The indexes on the live DB are optimized for heavy writes and a few specific reads, so your SSIS that pulls all the data into the DW and runs transformations will probably perform better with a staging database that has different indexes. You and your DBA are probably much more well versed with this sort of thing than I am.

To answer your original question:

is there any way to define an Integrity query having high level field names and as output get the proper SQL query containing low level real physical database names, and use this query as my data source in SSIS package?

No, there is not, but if you can invest the time to understand how the different fields work, it should pay off with the value of robust reporting for your DW. You also may want to talk to your sales rep about paying for PTC professional services to come in to set it up initially if there is a very tight timeline.

Hope that helps,

Matt

View solution in original post

4 REPLIES 4
mrump
14-Alexandrite
(To:ptc-5349095)

Hi,

As far as I know the Fields in the issues table inside the db are not "named", but simply "numbered". There is a second table containing the matching fieldnames from your configuration. Furthermore, not all data is stored directly inside the issues table, but is spread over a number of different other database locations.

Anyway, IMHO a direct access to the PTC Integrity database (bypassing the server) is a bad idea, as the underlying db schema is likely to change without your notice.

You should definitely call your local PTC Support to discuss your needs and find an appropriate solution;

maybe a report (csv or xml) can do the job much easier.

HTH

Matthias

KaelLizak
14-Alexandrite
(To:mrump)

In a simple case, im exportissues would likely work, but then you have to parse the ouput somehow (perl/python/awk&sed&grep).

In anything that gets a little complicated, you are likely better off creating a report. If you are particularly savvy and have access to the Integrity server's file system (i.e. if you are an administrator), you could do create your own report recipe, and dump the information to whatever format you like.

If that's outside of your area of experience, as Matthias said, you should definitely contact Integrity Support so they can help you figure out the best way for you to proceed.

Regards,
Kael


Kind Regards,
Kael Lizak

Senior Technical Support Engineer
PTC Integrity Lifecycle Manager

Hi Gila,

I agree with both Kael and Matthias that contacting integrity support is the best option.

With that said, the Integrity db schema is fairly stable (but as Matthias warned, it could change drastically without much notice in a future upgrade), so it is possible to set up some queries for pulling and transforming the integrity backend data into your data wareshouse. There is not a single query which pulls everything, some of the field metadata is encrypted in the DB so it is not useful and needs to be supplemented with information from your Integrity Admin.

If you want to go the DB to DW direct route, here are some pointers from my company's experience with it.

1) Talk to a support rep (or your sales rep) about getting a copy of the integrity schema documentation (MS word doc). Your company may need to sign an NDA, but it is very helpful for understanding how the backend DB is structured and how it is changed when your Integrity Admin adds new fields or changes picklist values, etc.

2) Take a look at this question and the answers: http://communities.ptc.com/message/198974, it has a good discussion about this topic and some detailed info.

3) The way that individual field values are stored in the DB varies depending on the field type and a few other factors. Most of the built in fields (like State, Type, Summary, Assigned User, etc) are stored differently than the custom fields added by Integrity Admins. Custom text fields, pick fields, date fields, and relationship fields are all stored in slightly different ways, but once you understand how each field type works it should be easy to start grabbing the field values in bulk for your SSIS job.

4) Not everything stored in the DB is readable with plain SQL. Some attributes are encrypted, such as which types contain which fields, or the formulas for computed fields. Other field values are not even stored in the database at all (such as Field Value Attribute type fields and dynamically computed fields). For those fields, I would recommend working with your Integrity Admin to document the various formulas involved so that you can recreate the values with some SSIS job. You may also want to document the field metadata manually in some side table so that you can refer to it later on.

5) A good exercise to help understand how the values are stored is to look at an Integrity item in the desktop client or web interface, then try to look up the fields for that item in the database and verify that your SQL is correct. Mostly it is just simple joins from Issues table to other tables, but it can get complex with multivalue fields and other values with an "Active" flag

6) It may be helpful to use a staging database in between the live Integrity DB and your DW. The indexes on the live DB are optimized for heavy writes and a few specific reads, so your SSIS that pulls all the data into the DW and runs transformations will probably perform better with a staging database that has different indexes. You and your DBA are probably much more well versed with this sort of thing than I am.

To answer your original question:

is there any way to define an Integrity query having high level field names and as output get the proper SQL query containing low level real physical database names, and use this query as my data source in SSIS package?

No, there is not, but if you can invest the time to understand how the different fields work, it should pay off with the value of robust reporting for your DW. You also may want to talk to your sales rep about paying for PTC professional services to come in to set it up initially if there is a very tight timeline.

Hope that helps,

Matt

Thanks everyone for your helpful responses, I guess connecting to the DB directly is not an option for me now and considering our tight time frame I started pulling out the data using exportissue into Excel files, and use these files as my data source.

Gila

Top Tags