cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
apfenech Frequent Visitor
Frequent Visitor

How to set up the Model for Q&A, support docs, Q&A buggy and slow

QUESTION:

Does anyone know where to find documentation for the best way to set up a dataset for Q&A?  I've found this article but it doesn't go as in-depth as I would have expected. I know that setting up Q&A relies heavily on setting up the model in the most ideal way including making many changes and cleaning in the Model tab on Desktop, but I can't find anything about how to set it up on the Model tab.  Additionally, with the new Model tab in preview, I can't find documentation talking about what all the new options and settings on that page mean?  There are a lot of things that I don't understand what they do (namely the info under the Advanced dropdown on the new Model tab and the Display Folder under General).  Anytime I have found something that does go into a deep dive it's outdated or talking about setting up Excel spreadsheets.

 

WHAT I'VE DONE:

I've done everything I can think of in setting up the Model in desktop:

  • Star schema
  • Simplified model
  • Removed unnecessary columns
  • Renamed Columns
  • Added synonyms
  • Ensured no duplicate names or synonyms
  • Changed "Summarize By" to be mainly 'none' except the main column (order number) is a count.

Even with all of this, my Q&A is slow and not intuitive with the visuals it is producing.

 

EXAMPLES OF ISSUES 

  • I'm cruncing about 30 million records and about 20-30 columns.  When asking a question, it takes an average of 15-30 seconds to produce a visual (if it does produce a visual...).
    • This, to me, says something is wrong with my model.  In many demos, they are cruncing 300M+ records and are able to return visuals in less than a second in most cases.
    • I would dive more into my model, but I've done everything I can think of - I was hoping there would be some documentation so I could compare what I've done against what is suggested to see why mine is so slow
  • Many times if/when it produces a visual, it's sloppy and doesn't group the way I would expect it to. Sometimes it'll just ignore the visual defined/requested and just return a table of data.  Examples:
    • When asking "Show me orders by month for 2018" gives a column chart with order count for each month (expected and desired).  However, if I add "Show me orders by month and client for 2018 as stacked column chart" it now gives a clustered column chart with Client on the X-axis and each client has 12 bars.  I would have expected that question to produce a clustered column chart with the X-axis as the month and the legend as the Client
    • Even if I tell it to "Group by Month then [otherdata]" I cannot get it to do the grouping on the visual as Month then add the legend as [otherdata].  The Q&A will make a clustered column cart with the [otherdata] on the axis and clustered bars for each month.
    • If I ask for "Top client by count of orders in 2018" returns a scatterplot with all clients and their order count.  Even changing to "Top 1 client by count of orders in 2018" or "What client had the most orders in 2018" returns the same thing.

 

WHERE IS THE ISSUE

Again, I really feel like I must be missing something in setting up my model for Q&A but I can't figure out what I've missed.  Searching for support docs on best practices for Q&A data modeling has not really gotten me anywhere and at this point.  All behavior is replicated in both Power BI Desktop as well as in our Premium Power BI Workspace on both the report and the dashboard (I'm aware on dashboard Q&A you can change the visual by opening that pane, but that feature is not available in the report Q&A).

 

It's worth noting, I only have 1 calculated column and it's a simple IF function and the report has no measures.  Even after removing this calculated column there was no difference in performance.

 

Thank you for any guidance/assistance!

 

EDIT: some grammar and clarification

2 REPLIES 2
Community Support Team
Community Support Team

Re: How to set up the Model for Q&A, support docs, Q&A buggy and slow

@apfenech,

 

So your requirement is to achieve a better quick insight using Q&A, right? You may refer to tips below:

  • Hide or unhide columns in your dataset. Power BI quick insights doesn't search hidden columns. So hide duplicate or unnecessary columns and unhide interesting columns.
  • Use a mix of data types such as names, times, dates, and numbers.
  • Avoid (or hide) columns with duplicate information. This takes valuable time away from searching for meaningful patterns. For example, one column with state names spelled out and another column with state name abbreviations.

 

Also, here're some blogs which may guide you to improve the model performance:

http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques

http://www.crmsearch.com/powerbi-best-practices.php

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
apfenech Frequent Visitor
Frequent Visitor

Re: How to set up the Model for Q&A, support docs, Q&A buggy and slow

Thank you for your reply.  I've done most of those articles - I'm looking for documentation specific to Q&A.  One big one is information on the model pane and what those options do because I'd guess they are important to set, but I don't know how/what to set them up as.  All the articles I find are very similar to the ones you sent and mention the same basic ideas of what to do, but there aren't any details of what to actually set/where - the ones that do mention this are older and don't consider the big changes that have happened over the lat ~8 months.  Also, I haven't found anything that talks about how to set up your data to get it to produce the visuals you ask for.  I guess to narrow it down, here are the big 3 questions:

 

  1. Support docs for working in the Model tab of Power BI Desktop and ideal setups for Q&A
    1. There is a tab in 'preview' but I still would assume they should have something to say what all the new options are/what they do - I would assume these have a pretty strong impact on performance based on how much they stress so much on "Set up your model" that these options must  be important but finding documentation around what they do has been impossible.
  2. How to set up datatypes/summarizations/'Sort by' options to ensure Power BI knows what to do for visuals
    1. I know a lot of this will depend on how the user phrases the question, but there must be some way to set up the data so Power BI has a starting point of what generally should happen.  I can't get consistency around what visuals are created, how the legend/axis is determined, and it plain ignores defined visuals sometimes and just outputs a table.
  3. Ideal enviornment/setup or necessary steps to ensure faster performance
    1. Going back to our performance/speed issue.  When they are crunching 300M+ rows in a second, that's some insane performance.  We have a premium workspace but wondering if they have a super premium or must be on the highest level of premium?  I just recently found out that there are levels of premium capacity so I think this may be a big contributing factor but I'm not sure...Even in desktop with a gaming PC (running i7 6 core 4.1ghz with 2x16GB of DDR4 ram) I can't get it to return a visual to return anywhere close to as fast as they do in their demos with significantly more rows.

 

EDIT: Grammar and clarity.