The Production setup for the Microsoft Power BI connector is intended as the middle ground between the Scale and the Development setup. While the Scale setup is the best when it comes to scaling with data, the Production scales sufficiently well for clients that do not have large amounts of traffic.
This setup is a good fit for clients who:
- Do not care about putting bot data into a data lake to be analyzed alongside other data
- Do not want to get an Azure SQL service (or similar service)
- Have lighter traffic and may run dashboard using data for 3-6 months, rather than up to 12 months
These instructions assume you have a Power BI Premium or Pro 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.
The solution contains a .mez file that enables Power BI to connect to Teneo Inquire and execute shared queries. Shared queries can be created and saved in Studio at any time to allow a team of developers to quickly extract the information they need from a project's log data. When using the Power BI connector, this makes it easy to access the data needed for whatever Power BI visualizations the team is using. More information on how to create and save these queries can be found here.
- Download the connector from GitHub.
- Move the file to C:\Users\USERNAME\Documents\Microsoft Power BI Desktop\Custom Connectors. Alternatively, if your 'Documents' folder is located in 'OneDrive', use the path C:\Users\USERNAME\OneDrive\Documents\Microsoft Power BI Desktop\Custom Connectors.
By default, Microsoft Power BI will not allow access for third party extensions unless they are manually enabled. To use our connector, we will need access.
Adjust the data extension security settings as follows:
- In Power BI Desktop select 'File', followed by 'Options and settings'.
- In 'Options', navigate to 'Security'.
- Under 'Data Extensions', select (Not Recommended) Allow any extension to load without validation or warning.
- Press 'OK', and then restart your Microsoft Power BI Desktop.
The Power BI Saved Queries Connector supports converting the data types between Teneo Inquire and Power BI by utilizing type prefixes. This can be done in Teneo with the 'as' notation, demonstrated here, and variable prefixes. The following table contains examples of how to use variable prefixes:
|d||d:Date||This prefix denotes a date, without the addition of time. If not stored as a date variable, Power BI will treat this value as text (string), and will not be able to compare different dates.|
|dt||dt:DateTime||This prefix denotes a date together with time. If not stored as a date and time variable, Power BI will treat this value as text (string), and will not be able to compare different dates.|
|f||f:Float||This is the prefix for a float number.|
|l||l:Logical||This prefix is used for logical values, e.g. a boolean.|
|n||n:Integer||This prefix is used for whole numbers without any decimals.|
|s||s:String||This is the prefix for a string.|
|t||t:Time||This is the prefix for a time, without the addition of date.|
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.
Once your queries have been written and published, they are ready to be used to access the data you need in Power BI. In order to access that data inside Power BI, you will first need to ask Teneo Inquire to save the query results.
Teneo Inquire supports saving and exporting query results data: Save stores query results using name as the data results identifier. When results have been saved, the source command can retrieve those results using the parameter name and use them as the source data for another query.
- This can be done inside Teneo by starting your queries with
1save (name="NAME") 2
to save the query, and
1source (name="NAME") 2
to load the saved results.
If we use the following query as an example,
1 1000 la t.e.userInput 's:UserInput' : t.e.userInput ~= ".(\w.+\s).+" 2
then the query can be saved as follows:
- Run the following query to save the results under the name word_cloud:
1save (name="word_cloud") 1000 la t.e.userInput 's:UserInput' : t.e.userInput ~= ".(\w.+\s).+" 2
This will return a version number which indicates that your query has been successfully saved under the word_cloud name. 2. Now it is time to save and publish the source query to extract the information in Power BI. This can be done with the following query:
1la(source="word_cloud") s:UserInput 2
Here are the steps for a SaaS customer:
- Start off by opening your Power BI Desktop.
- Located in the 'Data' section, select the 'Get Data' button. This will open up a list of connectors in your Power BI Desktop.
- In the search bar, search for 'TeneoInquire' and select the connector that appears.
- With the connector still selected, click on the 'Connect' button.
- You will now be prompted to enter the URL of the Teneo Inquire instance and the desired LDS. Enter these and click on 'OK'. The URL and LDS should be in the following formats:
The Teneo Inquire URL looks like this for SaaS customers:
https://query.[CUSTOMERNAME].teneo.solutions/teneo-inquire/rest. You should replace [CUSTOMERNAME] with the name of your SaaS client, which is shared with you when you become a client, together with your credentials. Alternatively you can find your Customer name in your Teneo Connection URL which is located in the 'About' section on the main Solution Main Window as seen below,
The Log Data Source (LDS) can be found in the Teneo Studio, 'Log Data' section on the Solution Main Window.
- Lastly, you will need to enter your login information to this Teneo environment. These are the same as the ones used when you login to your Teneo Studio.
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 a Power BI with premium license.
- 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' 2
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 2
This will aggregate the API Calls per hour.