Enabling Cortana for dbareports PowerBi

Last week at the Birmingham user group I gave a presentation about PowerShell and SQL Server

saved-image-from-tweetium-8

It was a very packed session as I crammed in the new sqlserver module, dbatools and dbareports 🙂 On reflection I think this is a bit too much for a one hour session but at the end of the session I demo’d live Cortana using the dbareports dataset and returning a Cortana PowerBi page.

As always it took a couple of goes to get it right but when it goes correctly it is fantastic. I call it a salary increasing opportunity! Someone afterwards asked me how it was done so I thought that was worth a blog post

There is a video below but the steps are quite straightforward.

Add Cortana Specific Pages

Whilst you can just enable Cortana to access your dataset, as shown later in this post, which enables Cortana to search available datasets and return an appropriate visualisation it is better to provide specific pages for Cortana to use and display. You can do this in PowerBi Desktop

Start by adding a new page in your report by clicking on the plus button

 

add page.PNG

and then change the size of the report page by clicking on the paintbrush icon in the visualisation page.

page-size

This creates a page that is optimised for Cortana to display and also will be the first place that Cortana will look to answer the question

Power BI first looks for answers in Answer Pages and then searches your datasets and reports for other answers and displays them in the form of visualizations. The highest-scoring results display first as best matches, followed by links to other possible answers and applications. Best matches come from Power BI Answer Pages or Power BI reports.

Rename the page so that it contains the words or phrase you expect to be in the question such as “Servers By Version” You will help Cortana and PowerBi to get your results better if you use some of the column names in your dataset

Then it is just another report page and you can add visualisations just like any other page

cortana page.PNG

Make Cortana work for you and your users

If your users are likely to use a number of different words in their questions you can assist Cortana to find the right answer by adding alternate names. So maybe if your page is sales by store you might add shop, building, results, amount, orders. This is also useful when Cortana doesn’t understand the correct words as you will notice in the screenshot below I have added “service” for “servers” and “buy” for “by” to help get the right answer. You can add these alternate words by clicking the paintbrush under visualisations and then Page Information

cortana-additional

Publish your PBIX file to PowerBi.com

To publish your PowerBi report to PowerBi.com either via the Publish button in PowerBi desktop

publish

or by using the PowerBiPS module

Install-Module -Name PowerBIPS
#Grab the token, will require a sign in
$authToken = Get-PBIAuthToken Verbose
Import-PBIFile authToken $authToken filePath “Path to PBIX file” verbose

Enable Cortana

In your browser log into https://powerbi.com and then click on the cog and then settings

powerbicom.PNG

then click on Datasets

settings

Then choose the dataset – in this case dbareports SQL Information sample and click the tick box to Allow Cortana to access the this dataset and then click apply

dataset settings.PNG

Use Cortana against your PowerBi data

You can type into the Cortana search box and it will offer the opportunity for you to choose your PowerBi data

cortana-search

but it is so much better when you let it find the answer 🙂

cortana-search-1

and if you want to go to the PowerBi report there is a handy link at the bottom of the Cortana page

cortana-search-2

I absolutely love this, I was so pleased when I got it to work and the response when I show people is always one of wonder for both techies and none-techies alike

The conditions for Cortana to work

You will need to have added your work or school Microsoft ID to the computer or phone that you want to use Cortana on and that account must be able to access the dataset either because it is the dataset owner or because a dashboard using that dataset has been shared with that account.

From this page on PowerBi.com

When a new dataset or custom Cortana Answer Page is added to Power BI and enabled for Cortana it can take up to 30 minutes for results to begin appearing in Cortana. Logging in and out of Windows 10, or otherwise restarting the Cortana process in Windows 10, will allow new content to appear immediately.

It’s not perfect!

When you start using Cortana to query your data you will find that at times it is very frustrating. My wife was in fits of giggles listening to me trying to record the video below as Cortana refused to understand that I was saying “servers” and repeatedly searched Bing for “service” Whilst you can negate the effect by using the alternate names for the Q and A settings it is still a bit hit and miss at times.

It is amazing

There is something about giving people the ability to just talk to their device in a meeting and for example with dbareports ask

Which clients are in Bolton

or

When was the last backup for client The Eagles

and get the information they require and a link to the report in PowerBi.com. I am certain that the suits will be absolutely delighted at being able to show off in that way which is why I call it a salary increasing opportunity 🙂

We would love YOU to come and join us at the SQL Community Collaborative

Help us make dbatools, dbareports and Invoke-SQLCmd2 even better. You can join in by forking the repos in GitHub and writing your code and then performing a PR but we would much rather that you came and discussed new requests in our Trello boards, raised issues in GitHub and generally discussed the modules in the SQL Server Community Slack #dbatools #dbareports. We are also looking for assistance with our wiki pages, Pester tests and appveyor integration for our builds and any comments people want to make

SQL Server Collaborative GitHub Organisation holding the modules. Go here to raise issues, fork the repositories or download the code

dbatools Trello for discussion about new cmdlets

SQL Server Community Slack where you can find #dbatools and #dbareports as well as over 1100 people discussing all aspects of the Data Platform, events, jobs, presenting

COME AND JOIN US

 

Speaking? You? Go on. #tsql2sday #84

This is a blog post for this month’s T-SQL Tuesday post, hosted by Andy Yun (b|t). T-SQL Tuesday is a monthly blog event started by Adam Machanic (b|t). The T-SQL Tuesday topic this month was about advice for new speakers. Thanks Andy for hosting. I have created a channel in the SQL Server Community Slack for presenting which everyone can make use of to ask and to answer questions

I think you should share what you know with others.

You will be amazing.

I will give you some great advice I learnt from a fantastic person’s blog post

  1. Start speaking
  2. Keep going
  3. Listen to feedback
  4. That’s it.

Kendra has said it all, you don’t need to read any further 😉

 

 

 

 

However..

Not all plain sailing

I love giving sessions but I never knew or thought that I would. My journey to speaking started at my SQL user group in Exeter and two fabulous people Jonathan and Annette Allen who encouraged me to share some PowerShell with the group. I was terrified, didn’t think I was worthy, my HDMI output wasn’t strong enough to power the projector, I had to transfer my slides and demo to Jonathans laptop. It was a fraught and frustrating experience.

My second presentation was done on Stuart Moores MacBook Pro using Office Online for presentations and Azure for demos. Again a change right at the last minute and using a machine I didn’t know (and a different keyboard set-up).

Stuff will go wrong. Murphy’s Law will always show his head somewhere and no matter how often you test and re-test your demos, sometimes an odd thing will make them stop working

There will be problems and issues, you can mitigate some of them by following the 6 P’s

Proper Preparation Prevents Pretty Poor Performance.

You can read some great blog posts in this T-SQL Tuesday Series and also this one from Steve Jones or any of these But also accept that these things happen and you must be prepared to shine on through the darkness if the power runs out or use pen and paper or even plastic cups like John Martin 🙂

You never know you might enjoy it

I found I enjoyed it and wanted to do more and since then I have presented sessions in a wide variety of places. It was very strange to have been sat watching and listening to all of these fantastic presenters thinking I could never do that and then find out that actually it is something that I enjoy doing and find fun. You can do that too.

Equally, it’s ok to not enjoy it, think its not worth the stress and hassle and support the community in a different way but at least give it a go

You will be nervous

quote-joan-jett-you-want-to-have-butterflies-in-your-185899

I shared a train across Germany with someone who had attended the PSMonday conference in Munich and they were astonished when I said that I get very nervous before speaking. It’s ok to be nervous, the trick is to make use of that nervous energy and turn it into something positive.

I get very nervous before presentations. My hands shake, I sweat, I either babble or loose my voice. I fret and fidget and check everything a thousandillion times. I find it is better for me if I am sat in the room during the previous presentation as that generally helps me to feel more relaxed as I can listen to their talk and also out of respect for the presenter and the organisation it forces me to sit quietly.

You will find your own way to deal with this, maybe listening to music on headphones or just sitting quietly somewhere. Don’t worry if it is not immediately obvious, try some different things, talk with others and believe me, it will be ok.

Don’t try to numb it with alcohol

Once I get up and its ‘my’ turn I take a few deep breaths and suddenly presenter turns on and I forget all about being nervous.

173101-everything-you-want-is-on-the-other-side-of-fear

Something to talk about

I have nothing to talk about.

Or everyone else knows more than I do.

Or X Y and Z talk about this much better than I do.

I’m scared

Richard Munn and I gave an impromptu session at SQL Relay in Cardiff where we talked about and hopefully encouraged people to start speaking and these statements came up.

wp_20161004_13_59_32_pro

Heres (a little of) what we said

No-one knows everything. Many people know a hell of a lot but not everything. You know a lot more than you realise and you also know things that no-one else does.

If you are stuck for things to talk about think about the you of 6 months or a year ago and something that you have learnt in that time and write the session that you wish you could have seen then. There will be other people at a similar stage who will appreciate it.

Don’t be scared, they are only people.

Practice

My dog is the one person who has been present at my presentations the most. He has listened (sometimes intently) to me practicing.

You need to practice speaking out loud.

You need to understand the timings

You need to be comfortable with hearing yourself speaking out aloud

You need to practice speaking out loud

A double reminder because I think it is important. You should practice and practice and practice with an eye on your timings and if you have a good friend who is technical or a small group at work for a lunchtime maybe then ask them if they will listen and give feedback.

Wanna chat?

I am very passionate about community involvement and lucky enough to be involved in two fantastic communities – the SQL community and the PowerShell community and have made some great friends along the way. I was amazed and proud when very soon after my second presentation someone told me that I had inspired them to start to present.

Since then I have gone out of my way to encourage other people to speak and to blog and am really enjoying watching them blossom. If you want to have a chat via email or via slack about speaking or blogging or getting involved in the community please feel free to contact me and I promise you I will get back to you. Better still go to the SQL Community Slack and ask questions in #presentingorspeaking

Go find out more

We are good at sharing and learning technical content but we can share and learn about so much more, about all aspects of our life. Go and read all of the other posts in this T-SQL Tuesday for starters 🙂 and develop