Connecting to Analytics from a third-party reporting application

You can use any ODBC-compliant reporting application to connect to Analytics data and report your findings. The applications you can use include:

• Tableau
• Microsoft Power BI Desktop
• Excel
• Crystal Reports
• Qlik
• MicroStrategy

How does it work?

In your chosen reporting application you use the ODBC feature to create a connection to any Analytics project, using the ACL Connector for Analytics.

Once connected, you can select tables and fields from the Analytics project, and use any additional ODBC features that are available in the third-party application, such as joining and filtering.

Optimal performance

You get the best performance from the ACL Connector for Analytics by limiting the size of the Analytics data sets you connect to. The connector is designed to support a wide range of reporting tools, but it is intended to work with the smaller data sets typical of results rather than with entire source data tables. Joining tables in the process of connecting is particularly resource-intensive.

Analytics data is stored in flat files, which are slower to access with ODBC than databases.

Create a data connection (DSN)

Depending on which reporting application you use, you may have to first create a data connection in Windows before you can connect from the reporting application to Analytics.

For example:

• Microsoft Power BI Desktop requires that you first create a data connection if you want to avoid manually entering a connection string.
• Tableau and Excel do not require that you create a data connection because they automatically create the connection for you.

The data connection is a DSN, which stands for Data Source Name.

1. From the Administrative Tools folder of your Windows operating system, open the version of the ODBC Data Source Administrator that matches the bitness of the third-party application you want to connect from (32-bit or 64-bit).

   For example, if you want to connect from a 32-bit version of Excel, open the 32-bit ODBC Data Source Administrator.

   Caution

   If you create the data connection in the wrong version of the ODBC Data Source Administrator, the connection is not visible or accessible in the third-party application. Or it may be accessible, but causes a connection error.

2. In the ODBC Data Source Administrator, do one of the following:
   • Select the System DSN tab if you want the data connection to be available to anyone who uses the computer.
   • Select the User DSN tab if only you will use the data connection.

   Note

   A default Analytics data connection named ACL ODBC or ACL ODBC 64 already exists in the System DSN tab. Do not modify this default data connection. For more information, see Default Analytics data connection.

3. Click Add, select ACL Connector for Analytics, and click Finish.
4. In the ACL Data Store Interface DSN Setup dialog box, enter the following information:
   • Data Source Name enter a meaningful name such as "Analytics General Ledger project".
   • Description enter a meaningful description of the Analytics project, such as "General Ledger audit 2017".
   • ACL Project File click Browse and select an Analytics project in the Open Project File dialog box.
5. Click OK.

   The new data connection to the specified Analytics project is created and is now available to select in a third-party reporting application.

   If required, you can create additional data connections to other Analytics projects.

6. Click OK to exit the ODBC Data Source Administrator.

Default Analytics data connection

When you install Analytics, a 32-bit and a 64-bit data connection (DSN) with the following names are created on your computer:

• ACL ODBC (32-bit)
• ACL ODBC 64 (64-bit)

These are Analytics data connections with an unspecified Analytics project. You can use them to connect to different Analytics projects on the fly – that is, you can select the Analytics project to connect to at the time that you make the connection. Some reporting applications may not support this use, and may require a data connection with a particular Analytics project specified in advance.

Connecting to an Analytics project

The instructions below provide three examples of connecting to an Analytics project using a third-party application:

• Connect from Tableau Desktop 10.1
• Connect from Microsoft Power BI Desktop 2.42
• Connect from Excel

Connect from Tableau Desktop 10.1

1. In the Tableau Connect panel, under To a Server, click More.
2. Click Other Databases (ODBC).
3. In the Other Databases (ODBC) dialog box, select DSN, and in the DSN dropdown list select one of the following:
   • To use a pre-existing data connection to an Analytics project, select the name of the connection, and click Connect.

     Note

     You must already have created the data connection. For more information, see Create a data connection (DSN).

   • To create a data connection to an Analytics project on the fly, select ACL ODBC or ACL ODBC 64 and click Connect.

     Note

     If both ACL ODBC and ACL ODBC 64 appear, select the one that matches the bitness of your version of Tableau (32-bit or 64-bit). For more information, see Default Analytics data connection.

4. If you selected ACL ODBC or ACL ODBC 64, in the Open Project File dialog box, navigate to an Analytics project (.acl), select it and click Open.
5. In the Other Databases (ODBC) dialog box, click Sign In.

   Tableau connects to the Analytics project.

6. Optional. If you want to connect to more than one Analytics project at the same time, in the Data Source tab, in the Connections panel, click Add, and repeat steps 2 to 5.
7. In the Data Source tab, in the Database dropdown list, select the Analytics project you are connected to.

   If you are connected to more than one Analytics project, select the appropriate project first in the Connections panel.

8. In the Table panel, do one of the following:
   • To list all the tables in the Analytics project: click Search .
   • To search for a specific table: type the name of a table and press Enter.

     Tip

     The table name search is case-sensitive.

     If an Exact search is not returning any tables, try Contains or Starts with.

9. Drag a returned Analytics table to the working area.
10. Click Update Now in the data preview area to see the data in the Analytics table.
11. Perform any additional tasks you require:
    • Add additional tables to the working area and join them

      If required, you can join tables from different Analytics projects.

    • Filter data
    • Update field names

    For detailed information about joining tables or other data preparation tasks, consult the Tableau Desktop Help.

    Note

    Joining can be slow if one or both tables are large.

12. Click File > Save and save the Tableau workbook.

Connect from Microsoft Power BI Desktop 2.42

1. In the Power BI Home tab, click the Get Data dropdown list, and select More.
2. In the Get Data dialog box, select Other > ODBC, and click Connect.
3. In the From ODBC dialog box, do one of the following:
   • To use a pre-existing data connection to an Analytics project, select the name of the connection from the Data source name (DSN) dropdown list, and click OK.

     Note

     You must already have created the data connection. For more information, see Create a data connection (DSN).

   • To create a data connection to an Analytics project on the fly, select ACL ODBC or ACL ODBC 64 from the Data source name (DSN) dropdown list, click Advanced options, enter the appropriate Connection string, and click OK.

     The connection string must use this format: DBF=;DBQ=<Analytics project path and filename.acl>

     For example: DBF=;DBQ=C:\Users\john_smith\Documents\ACL Data\Sample Data Files\Sample Project.acl

     Note

     If both ACL ODBC and ACL ODBC 64 appear, select the one that matches the bitness of your version of Power BI (32-bit or 64-bit). For more information, see Default Analytics data connection.

4. If the ODBC driver dialog box appears, do the following:
   1. Select Windows.
   2. Leave Use my current credentials selected.
   3. Click Connect.

   Power BI connects to the Analytics project.

5. In the Navigator dialog box, expand the node containing the Analytics project tables and select one or more tables to connect to.

   When you highlight a table, a preview of the table data appears in the right-side Preview pane.

6. Do one of the following:
   • Click Load to load the selected table or tables into Power BI.
   • Click Edit to edit the ODBC query. When you have finished editing the query, click Close & Apply.

   Multiple tables are separately loaded into Power BI. If required, you can relate tables in Power BI. In some cases, table relations are automatically generated.

   For detailed information about relating tables or editing the ODBC query, consult the Power BI Desktop Help.

7. Optional. If you want to connect to more than one Analytics project at the same time, repeat steps 1 to 6.

   If required, you can relate tables from different Analytics projects in Power BI.

8. Save the Power BI file.

Connect from Excel

1. In the Excel Data tab, click the From Other Sources dropdown list, and select From Microsoft Query.
2. In the Choose Data Source dialog box, make sure Use the Query Wizard to create/edit queries is selected.
3. In the Databases tab, do one of the following:
   • To use a pre-existing data connection to an Analytics project, select the name of the connection, and click OK.

     Note

     You must already have created the data connection. For more information, see Create a data connection (DSN).

   • To create a data connection to an Analytics project on the fly, select ACL ODBC or ACL ODBC 64, and click OK.

     Note

     If both ACL ODBC and ACL ODBC 64 appear, select the one that matches the bitness of your version of Excel (32-bit or 64-bit). For more information, see Default Analytics data connection.

4. If you selected ACL ODBC or ACL ODBC 64, in the Open Project File dialog box, navigate to an Analytics project (.acl), select it and click Open.
5. In the Query Wizard, follow the on-screen instructions to do the following:
   • Select the tables or columns that you want to import from the Analytics project.
   • Join tables if you select more than one Analytics table.
   • Optional. Filter the data that will be imported.
   • Optional. Specify a sort order for the imported data.

   For detailed information about using the Query Wizard, consult the Excel Help.

   Note

   If you want to connect to multiple Analytics tables without joining them during the ODBC connection process, you must perform separate connection operations.

6. In the Import Data dialog box, specify any options you require and click OK.

   Excel runs the ODBC query and populates an Excel worksheet with the Analytics data.

7. Click File > Save and save the Excel workbook.