Querying Project Online with Natural Language

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.

image

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.

  1. Open Excel
  2. 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.
  3. Ensure the tables have relationships.

Navigate to your PWA site and add the report to the Power BI app.

image

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.

  1. Get the Site Collection Feature turned on to allow dynamic refreshes
  2. Background Refresh Your Project Online Reports (Peter Charquero Kestenholz)
  3. 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.

image

From there click on the Ask with Power BI Q&A in the top right of the page.

image

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.

image

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.

image

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:

https://support.office.com/en-us/article/Add-synonyms-to-a-Power-Pivot-Excel-data-model-345f4f5b-5ec2-4998-bc46-a26bdc0810b6?ui=en-US&rs=en-US&ad=US 

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.

image

That yields an interface to add synonyms and other key elements to help your users connect with their data.

image

Finishing Touches

Add a link to Q&A onto your Project Web Access and feel free to fry all of your report developers’ squid.

image

Advertisements
Querying Project Online with Natural Language

One thought on “Querying Project Online with Natural Language

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s