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)

 

 

SC_IMPORT_VALUES Table

 

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.

 

Examples:

 

•INSERT INTO SC_IMPORT_VALUES VALUES (.....)

 

 

UMS_ActionReportingTable Table

 

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.

 

 

SC_ReportingView View

 

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'

 

 

SC_ReportingViewNR View

 

This view is similar to the SC_ReportingView but it does not contain range information. Estimated running time for a query is few seconds.

 

Examples:

 

•SELECT * FROM SC_ReportingViewNR WHERE Model = 'Dentorex'

 

 

UMS_UserView View

 

This view returns basic user information from QPR Foundation Server.

 

 

SC_ActionView View

 

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.

 

Examples:

 

•SELECT * FROM UMS_ActionView WHERE CreationDate between '17.6.2003' AND '17.7.2003'

 

 

UR_UserProductInfoView View

 

This view returns information about a user's access rights to each QPR product.

 

Examples:

 

•SELECT * FROM UR_UserProductInfoView WHERE USE_LOGIN_NAME = 'timbur'

 

 

UR_GroupMembersView View

 

This view returns groups and its members in the QPR Foundation Server.

 

Examples:

 

•SELECT * FROM UR_GroupMembersView WHERE GROUP_NAME = 'Europeans'

 

 

UR_ClassRightsView View

 

This view returns information about users' access rights to element types in a QPR Metrics model.

 

Examples:

 

•SELECT * FROM UR_ClassRightsView WHERE MOD_NAME = 'Dentorex Group Scorecard'.

 

 

UR_ObjectRightsView View

 

This view returns information about users' access rights to elements in a QPR Metrics model.

 

Examples:

 

•SELECT * FROM UR_ObjectRightsView WHERE MOD_NAME = 'Dentorex Group Scorecard' and USE_LOGIN_NAME = 'timbur'.

 

 

SC_ReportingViewMeaDef View

 

This view returns detailed information about measures.

 

Examples:

 

•SELECT * FROM SC_ReportingViewMeaDef WHERE Model = 'Dentorex Group Scorecard'

 

 

SC_ReportingViewSeriesDef View

 

This view returns detailed information about a measure's series.

 

Examples:

 

•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.

 

Examples:

 

•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.

 

Examples:

 

•UMS_PopulateActionReportingTable

 

 

SC_ReportingUpdate Job

 

This is a database job to periodically update values in the SC_ReportingTable.

 

The job basically executes the following two sql sentences: