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.
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.)
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 environment. The published bot should also have been in use in order to generate some interesting log data.
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.
Download Inquire ETL from GitHub. You will then need to set up a configuration file - in Java Properies format - which looks like this:
1# The URL to Teneo Inquire, for example https://teamname.data.teneo.ai/teneo-inquire
3# Username to reach Teneo Inquire, for example email@example.com
5# Password to reach Teneo Inquire
7# The name of your lds, for example longberry_baristas_12345
11# Timeout for the Inquire TQL queries in seconds
16# The URL to the Azure SQL database, for example teneo-example.database.windows.net
18# The name of the Azure SQL database, for example teneo-example
20# Username and password allowed to import data
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.
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:
- While inside the solution, select the 'SOLUTION' button located in the top left corner. This will take you to the solution backstage.
- Select 'Optimization', followed by 'Log Data'.
- Expand the 'Manage' button and open up the relevant 'Log Data Source'.
- Write a query using Teneo Query Language.
- 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.
- 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.
Now you're ready to try the Power BI Desktop data connector for Teneo.
- Start off by opening your Power BI Desktop.
- 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.
- Enter the relevant server host name. For example, teneo-powerbi-connector.database.windows.net.
- 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!
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.
- Located on the right hand side is the 'Fields' section. Expand one of them and select any data.
- Once selected, you will see a table of data appear.
- 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!
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.
- 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.
Teneo Query Language queries can be redesigned to aggregate transactional data by combining the
catd transformer with prefix operators.
Consider the following query:
1listAll s.beginTime 'Time', s.transactionCount 'API Calls'
This query will list the number of API calls per session and can be rewritten to:
1listAll time, sum s.transactionCount 'API Calls' : catd(pattern="yyyy-MM-dd'T'hh':00:00Z') s.beginTime time
This will aggregate the API Calls per hour.