Scale setup

The Scale setup for the Power BI connector showcases how to connect Power BI Desktop to your Teneo solution and retrieve large amounts of data from a chosen log data source (LDS) by executing shared queries on your behalf. The connector is intended to be used in a production setup with a option to scale and uses a data store (Microsoft Azure SQL in this case) to store the query results. This is not only a smoother connector experience when dealing with large amounts of data, but also more sustainable when working with Power BI. It also helps you combine the Conversational AI log data with other sources in your data lake and lets you analyze it as part of your overall business.

Setup structure

This setup makes use of an extract, transform, load tool, Inquire ETL, to load data into Microsoft Azure SQL to be used in a data lake. Microsoft Power BI is then connected to Azure SQL to access that data and create reports.

This approach has the following strengths:

• Scales well with both data and Power BI usage
• Enables you to combine the Conversational AI log data with other sources in your data lake and lets you analyze it as part of your overall business
• Allows you to use the data store of your choice (in these pages, we will use Microsoft Azure SQL, but you can easily use a different data store if preferred.)

Getting started

Prerequisites

These instructions assume you have a Power BI premium license and a published solution. Alternatively, you can publish and use the prebuilt Longberry Baristas solution and queries included in your free trial environment. The published bot should also have been in use in order to generate some interesting log data.

Set up a SQL database in Azure

Start off by setting up an Azure SQL, or requesting one be set up for you depending on your organization. You will need to request a username and password for a login allowed to import data into the database, together with the URL and database name.

Retrieving the Inquire ETL tool

Download Inquire ETL from GitHub. You will then need to set up a configuration file - in Java Properies format - which looks like this:

# The URL to Teneo Inquire, for example https://teamname.data.teneo.ai/teneo-inquire/rest
inquireBackend=
# Username to reach Teneo Inquire, for example john.doe@example.com
inquireUser=
# Password to reach Teneo Inquire
inquirePassword=
# The name of your lds, for example longberry_baristas_12345
lds=
# outputDir=xxxxx
# separator=json
# Timeout for the Inquire TQL queries in seconds
timeout=1440
#googleCredentialsPath=xxxxx
#googleCloudAppName=xxxxx
#googleSheetId=xxxxx
# The URL to the Azure SQL database, for example teneo-example.database.windows.net
azureServerName=
# The name of the Azure SQL database, for example teneo-example
azureDatabaseName=
# Username and password allowed to import data
azureUser=
azurePassword=

Once you've created the properties file, you can run inquire ETL with the following command: java -jar inquire_etl-0.0.3-jar-with-dependencies.jar --config=YOUR_FILE.properties --azure_sql

Inquire ETL will then retrieve the published queries of the Log Data Source (LDS), execute the queries, and store the results of the queries in Azure SQL.

Publish queries

The solution needs to have published shared queries. There are a few things to you need to do in order to be able to retrieve these in Power BI:

1. While inside the solution, select the 'SOLUTION' button located in the top left corner. This will take you to the solution backstage.
2. Select 'Optimization', followed by 'Log Data'.
3. Expand the 'Manage' button and open up the relevant 'Log Data Source'.
4. Write a query using Teneo Query Language.
5. While inside the same query, click the 'Share' button and give your query a name. This will make sure other developers who have access to the solution can use the same query without having to re-create it.
6. As a last step, we will need to publish the query. This can be done by selecting the query from the 'Shared Queries' section to the left, followed by the 'Publish' button. After pressing that button, another 'Publish' button will appear; click that one as well.

Access data in Power BI

Now you're ready to try the Power BI Desktop data connector for Teneo.

1. Start off by opening your Power BI Desktop.
2. Located in the 'Data' section, select the 'SQL Server' button. This will open up a window to enter the information to your Azure server in your Power BI Desktop.
3. Enter the relevant server host name. For example, teneo-powerbi-connector.database.windows.net.
4. Leave the 'Data connectivity mode' on 'Import' and proceed by clicking on 'OK'.

You will now be greeted with a different window where you can fill in the credentials for the Azure SQL server you are trying to get data from.

5. Continue by selecting one of the three methods to access your database. In this example, we will select the Database method.
6. Enter your credentials and click on the 'Connect' button. If the connection was set up successfully, Power BI will start to show your queries inside the Azure SQL server.

Congratulations, you have now made a connection between your Teneo solution and Power BI!

Build your own dashboards

The Teneo connector will transfer queries that are saved as 'Shared' to Power BI. The data retrieved from these queries are then used inside Power BI to create dashboards. In this step, we will demonstrate how you can quickly use the connector to build your first visual graph inside a dashboard.

1. Located on the right hand side is the 'Fields' section. Expand one of them and select any data.
2. Once selected, you will see a table of data appear.
3. With the data still selected, click on any appropriate visualization to create your first graph.

You have now created your first visualization inside Power BI with the Teneo connector!

Add more queries

Once you have connected your Teneo solution and Power BI, that data can be refreshed to automatically include new log data from new conversations with your bot. If necessary, you can also add more queries after the initial data load. To do this, simply write and publish your new queries in your Log Data Source (LDS) and then follow the steps from above on how to load data into your Power BI document, this time including the new queries which will now be accessible as long as they are published.

Tuning performance

• Please see Microsoft blogs for best practices in Power BI.
• Use Power BI with premium license.
• Make sure to use a suitably sized Azure SQL database.
• Redesign your Teneo Query Language queries to aggregate transactional data to minutes, hours, or even days, depending on the balance between granularity and performance.

Download

• Register for a free trial on Teneo Developers to get access to Longberry Baristas here.
• Import the solution into Teneo. For more details, see How to import solutions.