As a challenge for the recent Power BI demo competition I attempted to take our entire 90 minute Irish Economic Crisis BI demo and do three things
- Reduce it to six minutes, with a 2-3 minute making of video added on.
- Do the entire demo using Q+A (well apart from the Power MAP bit)
- Try and get one of every type of visualisation possible with Q&A into the demo.
On the way I learned a lot about what you could and couldn’t do with the latest addition to the Power BI Stack – the natural language visualisation tool Q&A.
Getting Started
You don’t have to do anything special to get started with Q&A. just right click your Power Pivot workbook in the Power BI App and select “Add to Q&A”.
Once you do that, a new type of search will appear at the top of the web application.
When you are in, you can ask any question using natural language, or click on a featured question.
While you may start using natural language very quickly you realise that the tool is keyword driven using value names, table names or column names with a few reserved words “map”,”column chart” etc. Commas in natural language help in places for lists. It helps show you words it has ignored by greying these out and also gives you a hint at how to re-express the question along with suggestions on what the next question could be.
Lets look at the different types of visualisation you can get out of Q&A
1. The Card View – Good for scalar values and more
Not much to say here, It can display multiple answers if you use a comma or the word “and”
I am kinda disappointed that Q&A doesn’t support external images in the card view (shoot the security teams!). This allows for some nice views in the normal version of Power View (see below)
2. The Table
Q&A treats the name of a table as a noun and each value in a table as a Proper Noun. This allows for queries like the one below.(Note the spelling mistake on “show” – we could have just typed in “Countries Europe” and achieved the same result.
You can customise what you want to appear as columns for a noun (or entity in IT speak), by using the Default Field list in Power Pivot
One limitation is if you wanted to display some default measures when displaying each country. Eg its population. To date Q&A doesn’t have a way to describe an entity that crosses multiple tables. I got around this by adding calculated columns in Power Pivot to denormalise attributes to the right tables, such as adding population to the countries table.
Another limitation is I wanted to have multiple related measures coming from one table. Eg the economic indicators table may have entities such as “growth”, “debt”, and “income” which would be 2-3 measures each. Sure I could split the measure group but this would be messy model-wise.
3. Ask the Map, Ask the Map…
The map visualisation works very seamlessly.
One annoyance is the continual warning or privacy. Sure I get that you want to ask me if I’m ok sending my data to bing to display a bing map. But can we not have a “yes, now go away forever” rather than a “Mr Clippit” style interruption mid-demo.
If possible its a good idea to use the advanced reporting properties in Power Pivot to tell the model which fields contain which types of geographic data. It handled continents, countries, counties and custom groups (eg EuroZone) very well.
3. The Column Chart
With the reserved words “column chart” you can force the same data to come out as a column chart, or Q&A will often choose a column chart as the appropriate visualisation.
Go Ireland on GDP per capita for 2007! (pity about 2013).
The axis always seems to start at zero. Most Data Visualisation gurus like Stephen Few (just read his latest book, “Now You See it”) like to start the axis at just below the lowest value as the purpose of bar chart is to demonstrate differences, right ?
4. The Line Chart
The line chart lets you do both a simple series and also multiple series
Unlike the bar chart , the line chart avoids starting the axis at zero (weird – I would have started the line chart at zero and the bar at a staggered value).
The line chart does seem to start the Y axis at a non zero value unlike the column chart which doesn’t make sense to me, but it does help make better use of the screen space.
4. The Trellis Chart
Nice !
4. The Scatter Chart (The Holy Grail of visualisations)
Great visualisation if used right. The best way to use an animated scatter seems to be to think of it as a “Gartner quadrant” chart. E.g. for the chart below we have Govt Debt per Capita versus GDP Growth. So we can see how Ireland moved from the top left quadrant (high growth and low debt), to the far right and nearly bottom quadrant (high debt and lot growth).
In this chart, GDP growth below zero means countries are in “recession”.
One major obvious issue. Where’s the legend dude! try as a may I could not get a legend from Q&A or find the words to express that I wanted the country as a label / color item. I hope that this is made easier in the RTM version.
Here is the complete demo video along with a 2 minute behind the scenes at the end. Enjoy!
Don’t Forget Synonyms!
You may find that there are multiple natural language words to describe an entity (table or column), or that the name used in the model is not quite right.
A new synonym editor has been added to Power Pivot to support editing these. In the example below we have many types of debt, but we just want “Govt Debt (€m)” to be known as “Debt”.
Two warnings:
- You NEED to use office 365 as downloaded from the Power BI site as opposed to deployed in another fashion (like from MSDN, or RTM Office 365 site).
- If you open the workbook in a version of excel that does not support synonyms you loose all the synonyms. Found this out the hard way as my desktop has office 365 RTM and my laptop had the Power BI version – i didn’t realise there was a difference till I kept loosing all the synonyms.
My Wish List for Q&A
- Let ME choose if I want to allow external images and then please go away and stop pestering me with warnings while I am trying to work.
- Add Q&A into excel, this helps us folk who use iterative development. E.g. we don’t have to keep uploading the workbook to see the result.
- Allow reports to be saved as first class Power View reports
- Allow Column charts to have a staggered axis like line charts (in normal Power View and Q&A)
- Show me the legends on scatter charts, or let me choose with a “legend” keyword.
- Allow control of the “colour” attribute in scatter charts. Maybe with a reserved word like “color as “
- Allow me to make a report with the designer and then show me the language that would have made the same report.
- The Q&A window seems to only work well at 1,280. Projectors of the world are still mainly 1024!
- Add microphone support like Google search
If EVER there was a feature that needed voice recognition, then Q&A is it!
Behind the Scenes
For those of you who don’t want to see the actual demo video here is a 90 second behind the scenes look at a Power BI project
