One of the most useful things to surface in the latest Microsoft BI offerings is the Natural Language Query (NLQ) functionality inherent in the Power BI online offering. NLQ allows users to simply type questions into the interface to generate dynamic reports.
The beauty of this is that it’s not limited to Project Online data – or even a single source of data. I can add multiple sources of data, create a data model and then go to town on asking questions of the data, literally.
This post will walk you through how to get this up and running in Project Online. This assumes that as part of your Project Online tenant, you have at least one license for the Power BI offering.
Create the Data Source
Creating the data source is relatively simple.
- Open Excel
- Add an OData connection. In this case, I actually added a couple of tables (Project, Assignment, Resource). Feel free to check out prior blog posts on the topic here.
- Ensure the tables have relationships.
Navigate to your PWA site and add the report to the Power BI app.
Set Up the Refresh
This took a couple steps and some troubleshooting before I got it all working. The basic instructions can be found in the following three blog posts.
- Get the Site Collection Feature turned on to allow dynamic refreshes
- Background Refresh Your Project Online Reports (Peter Charquero Kestenholz)
- Setting up the Scheduled Refresh (Peter again)
To check if the refresh is working, try refreshing the data connections in Excel Online. If that works, the scheduled refresh should work.
Set up NLQ
Click on the ellipses next to the report, and add the report to Q&A.
From there click on the Ask with Power BI Q&A in the top right of the page.
Throw some queries into the search interface to confirm data is being returned. You may note that it’s showing the wrong fields by default, i.e. it’s showing the Project ID instead of the Project Name. We’ll take care of this in the next step.
Training The Language Model
Open the data source in Excel again, and navigate to PowerPivot. You’ll see that you can right click on the column headers to select the option to Hide from Client Tools.
The other thing you can do to train the language model is to change the default field set. You’ll see this option in the Advanced tab in PowerPivot.
Finally, you can go back in and add aliases for many of the fields. Note that for this option to even be available in PowerPivot, you need to have installed Excel from the Office Online Click to Run installation option. I don’t have that available right now, so I’ll provide you with the link to the reference:
Luckily, there’s also a Web based option for training your Power BI model. Buried in the Power BI Site Settings, underneath the Q&A tab, you get the option to Optimize for Q&A.
That yields an interface to add synonyms and other key elements to help your users connect with their data.
Finishing Touches
Add a link to Q&A onto your Project Web Access and feel free to fry all of your report developers’ squid.
thnaks for this article, I really like that functionality, easy to code and easy to use. PMO and executives will be happy!