5. Access Measurement Data using ODBC Driver¶

5.1 Problem statement¶

Users would like to pull the spec mapped data to the visualization tool and plot the data in the graphical representation to figure out the outliers which can be invalidated and excluded from the compliance calculation through public APIs.

5.2 Pre-requisites¶

1. The remote connection should be enabled in the SQL server. (Server machine) - IT Admin

2. The user login should be created with the required access to the SQL server and database. (Server machine) - IT Administrator

3. The ODBC driver should be installed on the client machine. (Client machine)

5.2.1 How to enable remote connection in the SQL Server?¶

Complete the following steps to enable the remote connection in an SQL Server,

1. Configuring remote access on a SQL Server instance. Perform this step using login which has “sysadmin” role at the SQL server. (If the default database created by installer is used for the application, this step can be skipped)

   1. If SQL Server Management Studio (SSMS) tool is installed, right click on the server instance, and open the properties. Check the “Allow remote connection to this server” option and click ok.
      

   2. If SSMS tool is not installed, connect the database through “SQLCMD” and execute the following query,
      

      EXEC sp_configure 'remote access', 1;  
      RECONFIGURE;
      

2. Enable TCP/IP protocol

   1. Search and open “SQL Server Configuration Manager” tool from the Start.

   2. Expand on “SQL Server Network Configuration” and click on “Protocols for SQLEXPRESS”
      

   3. Right click on the “TCP/IP” and open “Properties” popup. In the IP Address tab, “IPAll” section, provide the port at which SQL server is running.
      

   4. Click “Apply” and “OK” to close the properties. (Click ok on dialog popup)

   5. Right click on “TCP/IP” and click on “Enable”.
      

   6. Restart the SQL Server service.

3. Configure a Windows Firewall for Database Engine Access

   1. Add new inbound rule to allow port at which database server is running, to connect from required machines.

Reference: https://knowledgebase.apexsql.com/configure-remote-access-connect-remote-sql-server-instance-apexsql-tools/

5.2.2 Creating a User Account to Pull Mapped Data¶

NI recommends that you create a user login account for the SQL Server with the following levels of access permissions.

• Server Level Access – public

• Database Level Access– public

• Enable access to execute the “GetSpecMappedMeasurement” stored procedure.

• Enable SELECT access to the following view objects:

• AvailableDisciplineList

• ConditionMappingInfo

• ConditionView

• MeasurementConditionInfo

• MeasurementConditionNameInfo

• MeasurementDataWithSpecID

• MeasurementNameInfo

• Products

• RunInfo

• SpecGroupInfo

NOTE: Specification Compliance Manager (SCM) supports both SQL Server authentication and Windows authentication when pulling mapped data. NI recommends using Windows authentication for this feature.

Complete the following steps to create a user account that can pull mapped data from SCM.

1. Open the SQL Server Management Studio (SSMS) tool

2. Connect to the required database by providing server name and user credentials (required system admin user access)

3. Once the database connection is successful, server details will be listed in “Object Explorer”

4. Click New Query to open a query window.

5. Enter the following SQL query into the window.

USE [master]
CREATE LOGIN [<domain_name>\<login_name>]
FROM WINDOWS;
USE [<database_name>]
CREATE USER [<domain_name>\<login_name>] FOR LOGIN
[<domain_name>\<login_name>];
GRANT SELECT ON [dbo].[AvailableDisciplineList] TO
[<domain_name>\<login_name>]
GO
GRANT SELECT ON [dbo].[ConditionMappingInfo] TO
[<domain_name>\<login_name>]
GO
GRANT SELECT ON [dbo].[ConditionView] TO
[<domain_name>\<login_name>]
GO
GRANT SELECT ON [dbo].[MeasurementConditionInfo] TO
[<domain_name>\<login_name>]
GO
GRANT SELECT ON [dbo].[MeasurementConditionNameInfo] TO
[<domain_name>\<login_name>]
GO
GRANT SELECT ON [dbo].[MeasurementDataWithSpecID] TO
[<domain_name>\<login_name>]
GO
GRANT SELECT ON [dbo].[MeasurementNameInfo] TO
[<domain_name>\<login_name>]
GO
GRANT SELECT ON [dbo].[Products] TO
[<domain_name>\<login_name>]
GO
GRANT SELECT ON [dbo].[RunInfo] TO
[<domain_name>\<login_name>]
GO
GRANT SELECT ON [dbo].[SpecGroupInfo] TO
[<domain_name>\<login_name>]
GO
GRANT EXECUTE ON [dbo].[GetSpecMappedMeasurements] TO
[<domain_name>\<login_name>]
GO

1. Replace following variables with the correct values for your user account.

   1. \<domain_name>: Replace with the organization domain name.

   2. \<login_name>: Replace with the Windows username.

   3. \<database_name>: Replace with the database name. If you are using a database created by SCM installer, the default database name is “NI_Spec_Mgmt”.

2. Click Execute.

After query execution is complete, a user account is created with the access permissions that are required to use the stored procedure to pull spec mapped data. The Messages tab will display the following text after query execution is complete.

Commands completed successfully.

Completion time: \<Timestamp>

Refer to the following strings for examples of ODBC connections using Windows authentication.

• Non-credential format:

  “Driver={ODBC Driver 17 for SQL Server};Server=\<DB Server Name>,\<Port(if custom port is used)>;Database=\<DB Name>;”

• Including Windows authentication:

  “Driver={ODBC Driver 17 for SQL Server};Server=\<DB Server Name>,\<Port(if custom port is used)>;Database=\<DB Name>;Trusted_Connection=yes;”

5.2.3 Verifying Open Database Connectivity (ODBC) Driver Installation¶

Complete the following steps to verify that the ODBC driver is installed on your machine.

1. Open the Control Panel.

2. Navigate to “Control Panel\All Control Panel Items\Administrative Tools”.

3. Open ODBC Data Source (64-bit).

4. Open the Drivers tab of the window that appears and search for “ODBC Driver 17 for SQL Server” in the list.
   

If “ODBC Driver 17 for SQL Server” is included in the list on the Drivers tab, the driver is installed. If the driver is not installed, refer to the Microsoft download page for the driver.

5.3 Establishing Database Connections in Visualization Applications using the Open Database Connectivity (ODBC) Driver¶

A Specification Compliance Manager (SCM) database supports connections to PowerBI and JMP to visualize data within the database.

5.3.1 Connecting a Database to PowerBI¶

Complete the following steps to connect a Specification Compliance Manager (SCM) database to PowerBI.

1. Launch PowerBI.

2. Click Get Data.

3. Select Other >> ODBC and click Connect.

4. Open the Data source name (DSN) drop-down menu and select None.

5. Expand the Advanced options section and enter the following Connection string and SQL statement. Refer to 5.4.2 Example Stored Procedure Execution Queries for SQL statement examples.
   
   Connection String Text:
   “Driver={ODBC Driver 17 for SQL Server};Server=\<DB Server Name>,\<Port(if custom port is used)>;Database=\<DB Name>;”

   Example connection string if database runs in default port (1433),
   “Driver={ODBC Driver 17 for SQL Server};Server=34.202.61.230\SQLEXPRESS;Database=NI_Spec_Mgmt;”

   Example connection string if database runs in custom port,
   “Driver={ODBC Driver 17 for SQL Server};Server=localhost\SQLEXPRESS,5005;Database=NI_Spec_Mgmt;”

6. If you are using SQL Server authentication for database connection, open the Database tab of the OBDC driver pop-up window and enter the user credentials.
   

7. If you are using Windows authentication for database connection, switch to the Windows tab and select Use my current credentials.
   

8. Click Connect.

After a connection is established, PowerBI will load the data from the SCM database.

5.3.2 Connecting a Database to JMP¶

Complete the following steps to connect a Specification Compliance Manager (SCM) database to JMP.

1. Launch JMP. 

2. Select File > New > Database Query

3. Click New Connection… in the Select Database Connection window.

4. Open the Machine Data Source tab in the Select Data Source window.
   

5. Click New, select the type of Data Source for the connection, and click Next.
   

6. Select the ODBC Driver 17 for SQL Server and click Next.

7. Click Finish.

8. Enter the Name, Description, and the Server name in the Create a New Data Source to SQL Server window.

   Refer to the following server name pattern and example:

9. Pattern: \<DB Server Name>,\<Port(if custom port is used)>

10. Example: localhost\SQLEXPRESS or localhost\SQLEXPRESS,1433

11. Click Next.

12. Select the authentication mode for the data source.

    1. To use SQL Server Authentication, select With SQL Server authentication using a login ID and password entered by the user and enter the username and password

    2. To use Windows Authentication, select With Integrated Windows authentication.

13. Click Next.

14. Enable Change the default database to, select your database from the drop-down menu below, and click Next.
    

15. If needed, edit the default settings to align with your practices.

16. Click Next, then click Finish.

17. Click Test Data Source… in the ODBC Microsoft SQL Server Setup pop-up window.

18. Verify that the test connection is successful, then click OK and close all pop-up windows.

19. In the JPM window, Select File > Database > Open Table.
    

20. Click New Connection, open the Machine Data Source tab, select the data source that you created, and click OK.
    

21. Select the authentication mode in the SQL Server Login pop-up window.

22. If needed, enter a username and password.

23. Click OK.

24. Open the Database Open Table window and click Advanced… within the Schemas – Tables section.

25. In the Advanced window, enter the SQL query to call the stored procedure “GetSpecMappedMeasurements” in the SQL field and click Execute SQL.
    

After you click Execute SQL, JMP will create a data table using the measurement data for the product that is fetched from the SCM Database. Refer to Stored GetSpecMappedMeasurements Procedure Overview for more information about the parameters and output of the procedure.

5.4 Stored GetSpecMappedMeasurements Procedure Overview¶

Stored procedure name: GetSpecMappedMeasurements

Input Parameters

5.4.0.1 @ProductName¶

Name of the product to pull mapped specification data from.

5.4.0.2 @ProductRevision¶

Revision of the product to pull mapped specification data from.

5.4.0.3 @DisciplineNames¶

Name(s) of the measurement disciplines to pull mapped specification data from. Multiple discipline name values must be included in a comma-separated list with no spaces in-between. For example, “Validation,Simulation,ATE”.

5.4.0.4 @SpecID¶

Optional input that indicates the SpecID to use when pulling mapped specification data from the product. If a SpecID is not input, the procedure will pull all specifications available for the product revision.

You can enter multiple SpecIDs as a comma-separated list with no spaces in-between. For example: Spec_01,Spec_02,Spec_03

This parameter also supports wildcard inputs as part of a SpecID. Refer to the following SQL statement syntax for an example of wildcard input usage for this parameter: LIKE Spec_%,Spe_%

• You must begin the statement with “LIKE “. This is not case sensitive.

• Add a comma-separated list of each SpecID with a wildcard character (%).

5.4.0.5 @MeasurementType¶

Optional input that indicates the measurement type to filter when pulling mapped specification data from the product. MeasurementType supports the following values: Included and Excluded. If a MeasurementType is not input, the procedure will pull both measurement types available for the product.

5.4.1 Query Output Table Format Information¶

Refer to the following table for information about the table columns that are generated by [GetSpecMappedMeasurements].

NOTE: The table columns display the base unit of measure for all condition values and measurement result values.

TIP: If a measurement point is mapped to multiple product specifications, the query duplicates the measurement point for each specification that it is mapped to.

5.4.2 Example Stored Procedure Execution Queries¶

Query for CSV SpecID Inputs:

EXEC [dbo].[GetSpecMappedMeasurements]

@ProductName = N'Product Name',

@ProductRevision = N'Revision', // ‘1.0’ is the default revision value

@DisciplineNames = N'Validation,Simulation,ATE',

@SpecID = N'Spec_01,Spec_02,Spec_03', // Optional

@MeasurementType = N'Included,Excluded' // Optional

Query for CSV SpecID Wildcard Inputs:

EXEC [dbo].[GetSpecMappedMeasurements]

@ProductName = N'Product Name',

@ProductRevision = N'Revision', // ‘1.0’ is the default revision value

@DisciplineNames = N'Validation,Simulation,ATE',

@SpecID = N'LIKE Parmetric_Spec%,Spec%', // Optional

@MeasurementType = N'Included,Excluded' // Optional

5.5 Marking Measurement Data Points as Valid or Invalid Using the Public API¶

Complete the following process to validate or invalidate a measurement point after data has been uploaded to SCM.

1. Validate and invalidate measurement points

2. Check Validate/invalidate measurement process status

3. Trigger compliance recalculation for modified disciplines

5.5.1 Accessing Specification Compliance Manager API Documentation¶

You can access the public API documentation from a menu within the Specification Compliance Manager (SCM) UI.

Complete the following steps to access the documentation from SCM.

1. Log in to SCM.

2. Click on the User Profile menu in the top-right corner of the interface.

3. Click Manage API Tokens.

4. Click SCM Rest APIs in the Manage API Tokens window that appears.

5.5.2 Validate and Invalidate Measurement Points¶

When the data is pulled from database using ODBC connections, the “MeasurementID” column in the query result table can be useful to validate or invalidate a measurement point. Entries in this column are unique for every measurement point that is uploaded to the database. Refer to the following figure for an example of the “MeasurementID” table column.

Collect the IDs that need validity status updates using the following API. Refer to the public API documentation for additional information.

/niscm/public/product/{productName}/data

5.5.3 Check the Execution Status of the Update Process¶

The public data API is asynchronous. Use the following API to view the status of the data API. Refer to the public API documentation for additional information.

/niscm/public/processexecutionstatus

5.5.4 Trigger Compliance Recalculation for modified disciplines¶

After the operation to update measurement point validity is completed successfully, you must trigger recalculation of compliance for the product, revision, and discipline that includes the changed measurement point. Use the following API to recalculate specification compliance. If multiple disciplines include the same changed measurement point, use the API for each affected discipline. Refer to the public API documentation for additional information.

/niscm/public/product/{productName}/discipline/{discipline}/compliance/trigger

This API is asynchronous. Use the ProcessHistoryID value that the API returns to as an input for the ProcessExecutionStatus API to check the status of the recalculation process.