Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Power BI Cortana Search


By:   |   Read Comments (2)   |   Related Tips: More > Power BI

Attend these FREE SQL Server 2017 webcasts >> click to register


Problem

How can I enable PowerBI to use the Cortana search capability?  How can Cortana help users be productive?  Can you provide any examples?

Solution

You may or may not be familiar with the Cortana search tool which is an integral part of the Windows 10 operating system (think Apple Siri or Google Now). Cortana is billed as a personal assistant which among things can interact directly with Power BI. This interaction with Power BI behaves in a similar method to the Q & A functionality in Power BI. You can ask Cortana natural language questions and expect to get results similar to what you would get from Power BI Q & A. Of course, if your questions are better formed and use more appropriate verbiage, then your results will be more accurate, timely, and relevant. The more details you phrase in your question, the closer the Cortana results will be to your expectations.

Cortana to the Questioning Rescue

Cortana works hard to bring the Power BI processing power to decipher our natural questioning ability. It picks out the appropriate key words and phrases in order to select the appropriate Power BI dataset and then present the results using the most appropriate visualization. These results are all displayed within the Cortana search box. As with most any tool with the power of Cortana, there are required setup items which much be completed to allow Cortana to interact with Power BI.

Furthermore several caveats exists when integrating Cortana with Power BI. First, Cortana is only available in Windows 10, and specifically Power BI integration requires Windows 10, version 1511 or higher. Without this version, Cortana will not connect to Power BI. You can check the Windows Version in Settings > About, as shown below.

check windows version

Next, Direct Query datasets are not available for Cortana search (at least for now, but I suspect that will change with future releases); datasets must be housed within your Power BI site in order for Cortana to query them. Second, in order for a Cortana user to see specific Power BI dataset results, that user must have been granted access to that dataset. Finally, in order to get Power BI results in Cortana, you must type (or speak... I will use type throughout the rest of the article but both work) at least two words in the search box.

Now that we have all the caveats out of the way, let us start the setup process. Step 1, is to enable Cortana for datasets. This step must be completed for each and every dataset that you would like to enable for Cortana. To enable this access, you go to Gear button and then Settings. Next click on the Datasets tab, and expand the Q & A and Cortana areas. Finally and simply just click on "Allow Cortana to access this dataset" checkbox and then select Apply.

Settings

Enable Cortana

After enabling the dataset (s), we now must make sure that Windows 10 has the user account which accesses Power BI setup in the machine's Account list. In the below screen print, you can see I had to add my "school / work" account to my personal machine in order for Cortana to Access Power BI. Microsoft did note that after enabling Cortana access and adding a new account, it could take 30 minutes plus for Cortana to begin to see and search datasets.

Windows 10 settings

Add Account

At this point, on any Windows 10 device that has the proper account access can begin to use Cortana for Q & A against the enabled datasets. As shown below, we can open Cortana and just start typing our questions. Notice how the section header shows "Power BI". Cortana bases the list of possible answers in order of its perceived relevance to your question. Thus the top result would be the most likely results for your request.

Sales Order Search

If we click on one of the offered suggested questions, Cortana actually displays the visualization based on the question asked (notice how I put "in table") in my question.

Sales Order by Date

We could easily adjust our query to give us the sales amount instead of the sales orders as shown below. Also notice the sort triangle just to the right of the OrderDate header. You can interact with the data just as you would in Power BI.

Sales amount

Additionally, we can drill through to the Power BI site by selecting the "Show more details in Power BI" link; I was sort of intrigued that the Power BI site did not properly format the Sales Amount, but Cortana did.

Power BI Drill Through

On the drill through page, Power BI Q & A may instead open with a list of suggested questions if the "original" Cortana question does not ask a fully recognized question. Again, the better the question, the better the results will be, but my experience has been that the Power BI Q & A is much more sensitive to offering a result than Cortana. This situation means Cortana will offer a relevant visualization whereas Power BI Q & A may not have presented any visualization.


Q & A

Furthermore, if the datasource / data set runs into refresh errors you could encounter the below error. In this below case, I had to "cleanup" my question in order to get proper results. You will notice that the question in the below screen print really does not make much sense.

bad search

Cortana is definitely responsive to visualization suggestions as shown in the next illustration. I asked for a map and received a bubble map of the customers by region.


cust map

One way to improve the results that are presented to Cortana users, is through the use of Answer Pages; these pages are pre-defined visualization sets that are sized appropriate for Cortana and includes important or key figures which will be beneficial for most responses. These pages are designed in the Power BI Desktop application. Within Power BI Desktop (PBID), you would create a new page; then, as shown below, you will set the Page Size Type setting to Cortana. Notice how the design grid is resized to a smaller design grid.


answer page size

The next step for the Answer Page creation is to add whatever visuals you would like to be displayed on this special report page. In the below example, we added a Sales Amount by Year column chart, a Discount by Promo type pie chart, and a Slicer visual by region.

answer page design

After completing the design, you want to appropriately name the page and then also add any alternate names for the page. The original name and alternate names are used to help Cortana find the most appropriate answer to the posed question.


answer page name

Now when we ask our question of Cortana, our newly designed answer page is displayed as a Best match when using our page name in the question.

Great Answers

Great Answer results

Similarly, if we use our alternate page names in the Cortana question, these items also show up as a best match for our question.


internet sales

Discount Code

Internet sales

Conclusion

The Cortana search tool included with Windows 10 can directly integrate with Power BI's reporting functionality. By typing a short question, Cortana seeks out relevant and accurate Power BI visualizations which relate to the question asked. Similar to the Power BI's Q & A function, the better the quality of the question asked, the better the results will be. In order for a dataset to be Cortana ready, you must enable it to interact with Cortana within the dataset setting. Once enabled, each user who has access to that Power BI dataset, will now see Power BI results in the Cortana search box. To further assist Cortana with returning the best results, specially designed and sized Power BI pages, called Answer Pages can be created in Power BI Desktop. These Answer Pages, when appropriately named, allow Cortana users to retrieve relevant pages by just typing the Power BI page or alternate names.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, November 17, 2016 - 9:57:54 AM - Scott Murray Back To Top

 

Hi Sarah... that should be possible.  check out: https://powerbi.microsoft.com/en-us/integrations/sharepoint/

 


Thursday, November 17, 2016 - 9:19:42 AM - Sarah H Back To Top

Appreciate your posts. They are very helpful! 

Is there a way to extract Excel online published on the Sharepoint to SQL server database?

Thank you very much!


Learn more about SQL Server tools