Integration Kit has currently these features:
•Tables: SC_IMPORT_VALUES, UMS_ActionReportingTable
•Views: SC_ReportingView, SC_ReportingViewNR, UMS_UserView, UMS_ActionView, UR_UserProductInfoView, UR_GroupMembersView, UR_ClassRightsView, UR_ObjectRightsView, SC_ReportingViewMeaDef and SC_ReportingViewSeriesDef.
•Stored Procedures: UMS_ActionHierarchy (Only SQL Server and Oracle), UMS_PopulateActionReportingTable (Only SQL Server and Oracle)
•Job: SC_ReportingUpdate (Only SQL Server and Oracle)
This integration table functions as a pre-built middle database for integration purposes allowing users to push measure value data to this table.
Table contains necessary information for the SQL mass imports to proceed correctly.
•INSERT INTO SC_IMPORT_VALUES VALUES (.....)
This integration table functions as a middle database for integration purposes allowing users to store actions with connected measures in this table. The table can be populated with the UMS_PopulateActionReportingTable procedure.
This view combines information from several QPR Metrics tables. There is approximately one row for each measure value. Estimated running time for a query is several minutes, even hours, and thus an additional SC_ReportingTable table can be created.
Inserting values into the SC_ReportingTable can be automated through "jobs":
•eg. a job starts daily at 05:50. First it removes old values and then it inserts new ones.
Examples for selecting values:
•SELECT * FROM SC_ReportingView WHERE Model = 'Dentorex'
•SELECT * FROM SC_ReportingTable WHERE Model = 'Dentorex' and MEA_Symbol = 'MEA240'
This view is similar to the SC_ReportingView but it does not contain range information. Estimated running time for a query is few seconds.
•SELECT * FROM SC_ReportingViewNR WHERE Model = 'Dentorex'
This view returns basic user information from QPR Foundation Server.
This view returns top level actions (ie. Action plans, comments, lessons, documents) from Portal. It does not return the whole discussion thread that can be seen in Portal.
•SELECT * FROM UMS_ActionView WHERE CreationDate between '17.6.2003' AND '17.7.2003'
This view returns information about a user's access rights to each QPR product.
•SELECT * FROM UR_UserProductInfoView WHERE USE_LOGIN_NAME = 'timbur'
This view returns groups and its members in the QPR Foundation Server.
•SELECT * FROM UR_GroupMembersView WHERE GROUP_NAME = 'Europeans'
This view returns information about users' access rights to element types in a QPR Metrics model.
•SELECT * FROM UR_ClassRightsView WHERE MOD_NAME = 'Dentorex Group Scorecard'.
This view returns information about users' access rights to elements in a QPR Metrics model.
•SELECT * FROM UR_ObjectRightsView WHERE MOD_NAME = 'Dentorex Group Scorecard' and USE_LOGIN_NAME = 'timbur'.
This view returns detailed information about measures.
•SELECT * FROM SC_ReportingViewMeaDef WHERE Model = 'Dentorex Group Scorecard'
This view returns detailed information about a measure's series.
•SELECT * FROM SC_ReportingViewSeriesDef WHERE Model = 'Dentorex Group Scorecard'
UMS_ActionHierarchy Stored Procedure (Only SQL Server and Oracle)
This procedure returns information about a measure's various actions. Those actions are created in Portal.
Procedure takes two parameters: model id and measure id.
•UMS_ActionHierarchy 1, 670
UMS_PopulateActionReportingTable Stored Procedure (Only SQL Server and Oracle)
This procedure calls UMS_ActionHierarchy for each possible model and measure ID. Results are inserted to table UMS_ActionReportingTable.
Note: The procedure is called UMS_PopActionReportingTable in Oracle.
This is a database job to periodically update values in the SC_ReportingTable.
The job basically executes the following two sql sentences:
|1.||DELETE FROM qpr.SC_ReportingTable|
|2.||INSERT INTO qpr.SC_ReportingTable SELECT * FROM qpr.SC_ReportingView|