Export Analytics Data to SQL Server for Use in PowerBI, Tableau

This tool exports Analytics data from the Elastic index (paxindex) to your SQL Server.

How to Export Data

  1. Navigate to your AnalyticsExporter location.
    In SmartHub the tool can be found at:
       <SmartHubRootFolder>\Tools\AnalyticsExporter
  2. Open the configuration (config) file and complete the following keys:
    1. ConnectionString
      1. Your SQL Server connection string.
      2. Default Value: Integrated Security=True;Persist Security Info=False;Data Source=localhost
    2. ElasticServerAddress
      1. Path to your elastic index
      2. Default Value: http://localhost:9200
    3. ElasticIndexName
      1. Name of the index that is wanted to be exported
      2. Default Value: paxindex
    4. ElasticQuery
      1. Write any valid elastic query(if left empty, it's going to run a wild card search, by default it is going to return all entries starting with 2018)
      2. Default Value: Time:>2018-01-01
    5. AllowScheduledUpdates
      1. Value must be set to either "true" or "false"
      2. Default Value: false
    6. DatabaseName
      1. The name you want for database/ The name of database created in Azure Sql Server
      2. Default Value: BAInsightAnalyticsData
    7. TableName
      1. The name you want for data table
      2. Default Value: AnalyticsDataTable
    8. NumericMultiValueFields
      1. Enter any numeric fields that support multi value
      2. Default Value: Pages
    9. IsAzureDatabase
      1. Value must be set to either "true" or "false"
      2. Default value: false
      3. In case that Analytics data are exported to an Azure Database, this option must be configured on "true".

        Note: The Azure Database (DatabaseName) must be created manually.

    10. ElasticBasicSecurityEnabled

      1. Value must be set to either "true" or "false"

      2. Default value: false

    11. ElasticCredentialsFilePath

      1. Path to elastic credentials file

        1. First line: username

        2. Second line: password

          Note: Path cannot contain spaces

  3. Run AnalyticsExporter.exe.
  4. Wait for the tool to finish exporting. Export complete.

Allowing Scheduled Updates

Use Scheduled Updates under the following conditions:

  • You don't want to delete the database after the first export
  • New, DISTINCT, data has to be added and old entries must be kept

To enable this, set your configuration AllowScheduledUpdates property to "true" and ElasticQuery to the required query.

Use the following query examples when exporting based on date:

  • All data from year 2000: 
    • Time:>=2000-01-01 AND Time:<2001-01-01
  • All data from March 2000: 
    • Time:>=2000-03-01 AND Time:<2000-04-01
  • All data from the previous day: 
    • Time:{now-2d/d TO now-1d/d]
Important: Make sure to modify the ElasticQuery when you run the tool subsequently with a query which will return distinct results.
If the tool is run twice with the same query it will duplicate existing items. 

Use Analytics Data in PowerBI

  1. Click the Get Data link and select SQL Server (if you have used AnalyticsExporter or any other tool to export data to a SQL database).

  2. Server: Enter your database server: localhost (for example).

  3. Database (optional): Enter the database name BAInsightAnalyticsData.

  4. Press OK.

  5. Select AnalyticsDataTable.


  6. ClickLoad.
  7. Now you can select your fields and any type of visualization you would like.
    An example of a Pie chart for the different types of Actions based on BA Insight's analytics index is shown here:

Use Analytics Data in Tableau

  1. Import your Data Source into Tableau.
    Note: If you are using SQL Server, a prompt appears for database name, user, password etc..


    1. Server: Enter your database server.
    2. Database: Enter BAInsightAnalyticsData.
    3. Select authentication type and enter log-in credentials, if necessary.
    4. Click Sign In.
  2. Here you can create a type of visualization to best use your analytics data.
    • Here we have created horizontal bars for the type of actions found in our report, based on the number of records.