Connecting to analytic results using ODBC

The ACL Connector for Analytics Exchange uses Open Database Connectivity (ODBC) to connect to analytic results data on AX Server. Use this connection to extract the latest analytic results data for further analysis in a third-party application such as Tableau or MS Excel.

Note

You must have an active license for AX Web Client to use the ACL Connector for Analytics Exchange.

Before you start

Before you can connect to analytic results data, you must:

Install the ACL Connector for Analytics Exchange server component on AX Server.
For information about installing the server component, see Installing the ACL Connector for Analytics Exchange server component.
Download and install the ACL Connector for Analytics Exchange ODBC driver on the client machine.

For information about installing the driver, see Installing the ACL Connector for Analytics Exchange ODBC driver.
Grant a minimum of Read only application permissions to the connecting user for the folder containing the target analytic.
For information about granting permissions, see Managing user permissions.

How it works

Latest analytic results set only

The connector provides access to all results data from the last successful execution of a single analytic. It returns rows of data from one or more tables in the results set.

Note

You cannot access historical analytic results, only results generated during the most recent analytic job for a specific analytic on AX Server.

Example

Your organization runs a scheduled analytic script once a quarter to identify unmatched payments in the accounting system. You then import the analytic results into a third-party application using the ACL Connector for Analytics Exchange and generate a report.

As the connection always returns the latest results set, and no data from previous analytic jobs:

when you run this report at the end of the first quarter, the report contains unmatched payments from the first quarter only
when you run this report at the end of the second quarter, the report contains unmatched payments from the second quarter only

Selecting data from results tables

When you use a third-party application to access analytic results data through the connector, you:

select specific columns from one or more tables in the results
apply any filters

If you select columns from more than one table, you may have to use the third-party application to combine the data into one table that is returned from the connector. Joining is a common method for combining tables.

Table size and concurrency considerations

For optimal performance and stability, limit:

query results to tables that do not exceed 2 GB in size
concurrent connections to a maximum of five

Configure a DSN for the ODBC connection

A Data Source Name (DSN) describes the connection to a data source for an ODBC connector. This connection information specifies which AX Server instance and analytic result set to connect to, as well as the user credentials required to authenticate the connection.

Tip

Set up a DSN for each result set you intend to connect to so that you can easily connect from third-party reporting applications.

From the Administrative Tools folder of your Windows operating system, open the ODBC Data Source Administrator that corresponds with the bitness of the third-party client application you intend to connect with (32-bit or 64-bit).
Note
64-bit Windows 7 users can access the 32-bit ODBC Data Source Administrator from c:\Windows\SysWOW64\odbcad32.exe.
Select the type of DSN you want to configure on this computer:
- User DSN only your user account has access
- System DSN all users of the machine have access
To create the new DSN, click Add and from the list of ODBC drivers, select ACL Connector for AX.
In the ACL Connector for Analytics Exchange dialog, and enter the basic connection properties.
For more information about specific the basic connection properties, see Basic connection properties.
Optional. To configure logging for the ODBC connection, click Logging Options, update the logging properties you want to set, and then click OK.
For more information about the logging properties, see Logging connection properties.
To save the DSN, in the ACL Connector for Analytics Exchange dialog, click OK.

Connection properties

Basic connection properties

Property	Description
Data Source
Data Source Name	The name of the connection. This name uniquely identifies the connection and appears in the list of available data sources in third-party applications when connecting using ODBC.
Description	A description of the data source. Tip Enter a meaningful name that identifies the result set you are targeting or the purpose of the connection.
Server Info
Server	The server address of the AX Server instance. Depending on your SSL configuration, you may need to use the server name rather than the IP address. More information about AX Server connections.
Port	The port on the AX Server computer that is listening for ACL Connector for Analytics Exchange connections. By default, this is port 1543.
Results Target
Target Linked Analytic	Specifies whether or not the target analytic is a linked analytic. When enabled, the Analysis App field is set to Linked Analytics and you cannot change it. Linked analytics do not belong to a specific analysis app on AX Server.
Collection	The name of the collection that stores the target analytic on AX Server.
Folder	The name of the folder that stores the target analytic inside the collection on AX Server.
Analysis App	The name of the analysis app that stores the target analytic. If you selected Target Linked Analytic, the property value is set to Linked Analytics and you cannot change it. Linked analytics do not belong to a specific analysis app on AX Server.
Analytic	The name of the analytic to connect to and extract the latest results set from. Note You cannot access historical analytic results, only results generated during the most recent analytic job for a specific analytic on AX Server.
Authentication
Username	The username of the AX Web Client user to connect as. The user must have at least Read Only permissions for the folder that contains the results set. For more information about credential formats for domain accounts, see Credential formats.
Password	The password of the AX Web Client user to connect as. The password is required to test the connection, however it is not stored and must be supplied when connecting.
SSL Options
SSL Mode	Whether or not SSL is enabled for the connection between the client machine requesting results data and the AX Server machine: Disabled no SSL encryption used in the connection Enabled with Self-Signed Certificate SSL encryption that uses a self-signed certificate form the server Enabled with Trusted CA Certificate SSL encryption that uses a trusted CA certificate to verify the server Any connections made with the saved DNS must use the encryption format specified by this field. Note To support SSL encryption, you must generate and install a set of security certificates on the AX Server machine using OpenSSL. When SSL is enabled, the connector uses OpenSSL to encrypt all data moving across the network connection. For more information, contact your Analytics Exchange administrator.
Trusted CA Certificate	The location on the local file system of the client-side CA security certificate that is used in an SSL connection with the AX Server machine that uses a trusted CA certificate.

Logging connection properties

Property	Description
Global Logging Options
Log Level	The logging level to use for the ODBC connection. This information can be useful when debugging connection problems.
Log Path	The full path to the folder where you want to save log files.
Max Number Files	The maximum number of log files to keep. After the maximum number of log files is reached, each time an additional file is created, the driver deletes the oldest log file.
Max File Size (MB)	The maximum size of each log file in megabytes (MB). After the maximum file size is reached, the driver creates a new file and continues logging.