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
