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:
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.
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:
# 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
There are two more very useful options for Inquire ETL, which are --from
and --to
, which allows you to specify timestamps for the period you wish to import. For example, --from=2021-01-01T00:00:00Z
, which imports data from midnight of January 1st.
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.
It's a great idea to set this up in a cron or scheduled job to continously feed data to your Azure SQL data lake. In those cases, remember the from and to parameters to retrieve only the latest data. In the beginning, you will also likely want to bootstrap your data lake with data, in which case we recommend that you use the bootstrap shellscript to load the data one day at a time, as this will go faster.
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:
Now you're ready to try the Power BI Desktop data connector for Teneo.
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.
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.
You have now created your first visualization inside Power BI with the Teneo connector!
Please note that the video is only a demonstration and the selected queries should be applied to appropriate visualizations for a real use case.
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.
Teneo Query Language queries can be redesigned to aggregate transactional data by combining the catd
transformer with prefix operators.
Consider the following query:
listAll s.beginTime as 'Time', s.transactionCount as 'API Calls'
This query will list the number of api calls per session and can be rewritten to:
listAll time, sum s.transactionCount as 'API Calls' : catd(pattern="yyyy-MM-dd'T'hh':00:00Z') s.beginTime as time
, which will aggregate the API Calls per hour.
Was this page helpful?