Power BI Global Hackathon Contest Results

The results of last month’s Power BI Global Hackathon are in! The Hackathon was facilitated by our our PUG here in Portland with the goal of welcoming global contenders in subsequent contest. Five teams entered the contest using publically-available data to visualize and tell data stories using our favorite data analysis platform.  Congratulations to Xinyu Zheng and Ron Barrett for winning the challenge with their entry, analyzing Yelp restaurant star ratings.  These were all great entries and you can view the contest results in the Power BI report below.

image  image  image

Here are the published projects that were entered in the Hackathon:

Xinyu and Ron analyzed ratings from nearly 1200 restaurant Pittsburgh, Phoenix and Las Vegas.  Results compare ratings and reviews by restaurant and food categories, sentiment and key phrases in the review comments


I loved the creativity of this solution from Jeremy Black and Kirill Perian who analyzed alcohol sales statistics using infographics and bookmarks to switch out visuals on the same page.  The presentation concludes on the last page of the report with an auto-advancing variation of “100 Bottles of Beer on The Wall”.  Nice touch.


I’m admittedly a bit biased because this was my design, with a lot of help from Brian, Ron and Greg.  We used a series of tables to prompt a user for Power BI solution business requirements and recommend fitting solution architectures and components.  We pushed some practical and technical limits in our project and I’ll write a separate post about it.


This entry from Ron Ellis Gaut is a nice, clean orchestration of county health data, measuring health and comparing personal well-being and program efficacy.


The entry from Daniel Claborne emphasizes machine learning predictions performed with R Script, commonly used in data science.  He actually includes the annotated code and explains the technique and the approach using training and prediction data sets.


The Portland Power BI User Group was one of the first and continues to be one of the most active in the international community.  We meet on the 4th Wednesday evening every month in Beaverton, Oregon. Today there are many active PUGs all over the world.



Managing Multiple Power BI Desktop Application Versions

Question:  How many different versions of Power BI Desktop might you have installed at one time?

Answer: Three (or more)

What happens when you have different versions installed, and how can you make sure that you use the right version for a given Power BI report file?

An issue came up this week when I tried to open a Power BI Desktop file (.PBIX) from File Explorer and Power BI Desktop told me I was headed down a dark and difficult path. Well, not exactly, but it displayed the following message:

Unable to open document

The queries were authored with a newer version of Power BI Desktop and might not work with your version.

Please install the latest version to avoid errors when refreshing.


When I clicked the Close button, rather than leaving me to correct with what seemed to be a complicated and potentially damaging situation, Power BI Desktop starts up and continues to tell me about the perils that lie ahead, in this message:

Report layout differences might exist

This Power BI report file may have some features that aren’t available in Power BI Desktop until the next release.

If you need to see the latest version you worked with on the web (app.powerbi.com), please view the report there. We’re sorry for any inconvenience.


As an unsuspecting user, I might be confused but at least I can rest assured that the application developers at Microsoft who write these warning messages are thoughtful and apologetic.

What’s going on?

In addition to the reports I author and deploy to the Power BI cloud service, I also create reports for my on-premises Power BI Report Server.  Report Server requires an older version of Power BI Desktop which can be installed from the menu on the report server.  This older version of desktop (October 2017 in my case) is sandboxed by Windows so it doesn’t get upgraded by the latest Power BI Desktop installer when I update it from the PowerBI.com.  In Control Panel, you can see both installations:


The problem I experienced was a result of installing the older desktop version for PBRS after the newest version.  The file extensions (PBIX and PBIT) are already associated with whatever version of desktop is installed and registered with Windows.  The remedy is quite simple… just reinstall the latest version of Power BI Desktop and perform a Repair if you already have that version installed.

This next part is more informational than problematic but it actually is possible to have additional “versions” or packages of Power BI Desktop installed.  If you install Power BI Desktop from the Windows 10 Microsoft Store, you get a sandboxed installation that runs in a restricted “safe” security context.  This is a good option for users in a restricted corporate network environment who don’t have local admin access to their computer/  In most cases, they can install the application this way.  As you can see, I actually have three separate Power BI Desktop installations.


These are all 64 bit builds of the desktop applications so I could even install 32 bit builds of Power BI Desktop as well.  I would only do that for compatibility with an old 32 bit database driver or if I were running on an old 32 bit Windows machine, which is not an ideal scenario.  Keep in mind that 32 bit applications can only use a limited amount of RAM (about 3.7 GB minus some system overhead).

Keeping Up with Power BI – A Never Ending Story

This first week of the new year has been a lot of housecleaning for me (literally and figuratively…  my office desk and cabinet will be clean by the end of the day!).  Three years into teaching classes and workshops on being productive with Power BI, it continues to be a product requiring a lot of work to stay current.  New features are introduced in every Power BI Desktop monthly update; not to mention updates to the cloud service and on-prem server.  You would have to be a child of the 80s to get the Never Ending Story reference.  Otherwise, it’s just a really bad flying dog – and pop song, which are both hard to explain, so we won’t.  The point is that it’s an ongoing effort to keep skills, courseware and presentation material up-to-date.

Image result for never ending story

If you’re like me, sometimes all these updates can be a bit of a distraction (we’re talking about Power BI again – not the dog, movie or song… case in point).  I’m excited by the continual innovations and improvements to Power BI.  However, the foundational rules of good design don’t really change that much.  Effective data curation, correct modeling and good core visualization design are as critical as ever.  The trick is to know which new features may be used to improve foundational design and which ones you can leave as icing on the cake for minor enhancements.  Updating courseware and workshop labs seems to be a never ending task and I’m hard at work revising older material and adding new content to prepare for new events this year.  An important topic I will continue to revisit this year is how Power BI is used along with other Microsoft tools to create different types of solutions.  I’m working on a new presentation to describe all of the incarnations of Power BI, used to deliver everything from self-service desktop reports – all the way up to enterprise-scale solutions for corporate users with governed and secured data – and most everything in-between.

The first workshop of the new year will be a one day preconference before the Redmond SQL Saturday, on Microsoft campus Friday, February 9.  You can sign-up for this event here.  I’m working on a few others in the following months and will update this and other blog posts when they are scheduled.  I hope to see you there.  You are welcome to send me questions or suggestions about specific topics of focus.  Just add a comment to this post, or reach me through Twitter or LinkedIn.



Spontaneous Interviews at PASS Summit 2017

Conversations with Julie Koesmarno, Olivier Matrat, Aaron Nelson, Seth Bauer and Robert Bruckner captured in video interviews below…

Continuing my video blog series of interviews from PASS Summit, I had the opportunity to catch-up with several Microsoft BI and Data Platform industry leaders amid the crowds and between sessions.  Stay tuned to this station for many more interviews and insider information about the Microsoft Business Intelligence and Data Platform.

I caught up with Julie Koesmarno in the Community Zone, a few days after travelling together on the SQL Train (aka “Oregon SQL Party Train to Seattle”) from Oregon SQL Saturday the weekend before Summit.  She’s been a non-stop community advocate for several years, and continues to speak at events all over.  Julie was an Business Intelligence consultant and user group leader in Australia and Southern California before joining Microsoft as technical evangelist.  You might recognize her from the executive demonstration during the opening keynote at PASS Summit last year.

Olivier Matrat, Principal Program Manager (that’s Microsoft job title code for “in charge of a lot of important stuff”), talks about how they are hard at work integrating several products in the “Power *” suite.  This is the first time I’d heard of all the “Power…” prefixed product names unofficially referred to as “Power Star”, but it makes perfect sense.  Olivier said that we can expect to see tighter integration between tools like Power BI, Flow and Power Apps with more embeddable features for developers and solution integrators.

Aaron Nelson, Data Platform MVP and hard-core PowerShell enthusiast, spoke about some new capabilities he presented in his session about PowerShell for Business Intelligence. The new REST API will let Power BI and report server admins orchestrate server migrations and task automation with PowerShell CmdLets.  He seized the opportunity to promote the PASS PowerShell virtual group that he helps manage, at SQLPS.IO.  I’ve promised Aaron a follow-up post to demonstrate how the REST API works with PowerShell and the new MSBuild integration, so please watch my blog for that in the next few days.

I chatted with Seth Bauer, BI consultant and Data Platform MVP, on the escalator in the Washington State Convention Center between sessions.  Seth has been on the front lines of the Power BI advisors community since the product launched.  He cites Q&A Natural Language and Explain the Difference as examples of the most compelling features.   He participates in PASS Summit for professional networking and to stay current with BI technologies.

Robert Bruckner is a Senior Architect on the Power BI team and long-time developer lead for Reporting Services.  He told me that there are many exciting capabilities on the horizon for Power BI and other integrated reporting technologies that are still under NDA.  He mentioned a recent announcement that the On-premises gateway will soon support single sign-on, delegation, load balancing and high-availability.  It is truly exciting to see such emphasis on enterprise-scale capabilities for these tools.

The Future of Power Query – Interviews with Chris Webb at PASS Summit 2017

In a series of interviews during PASS Summit 2017 in Seattle, the week of October 30 through November 3, I caught up with Chris Webb, who is an international BI community thought-leader and product team advisor for Microsoft.  We chatted about his love affair with Power Query, lessons learned from many years of expert Business Intelligence project work and his thoughts about the future of Power Query and other Microsoft tools.  Chris had just finished an all day preconference presentation the day before our interview and his experience was fresh in-mind.  He has a long history of deep expertise with SQL Server, Analysis Services multidimensional and tabular technologies, MDX and DAX.  His focus lately has been teaching, writing and using Power Query in practice.

He was so interesting and insightful that I honestly couldn’t edit anything out of the final recording, so I split it into three parts.

Chris Webb Interview Part 1 of 3

In the first installment, Chris talks about how he got started with Power Query and why he thought it was worth his investment of time and energy.  He hadn’t planned to bet his career on Power Query.  He said “this tool was cool!  I liked playing with it…”  He carried on playing with Power Query until he fell in love with the product and its awesome ability to transform data in ways that weren’t possible before with the same easy and elegance.  Maybe the long-term success and pervasive integration of Power Query into more products will be bolstered by the enthusiasm of Chris and others in the community who just love to use it.

Chris Webb Interview Part 2 of 3

We began with the question: “Do you ever foresee Power Query being used as an enterprise ETL tool in lieu of something like SSIS?”  Chris shares his thoughts about Power Query as a self-service, desktop data transformation tool and its juxtaposition with other with older, more complicated data tools.  He says it is “a universal query generator”, capable of performing “query folding” (translating and pushing  queries back to the data source engine for each data provider).  We discussed how Microsoft is committed to supporting and enhancing Power Query, not only for desktop analysis but to perform tasks partitioning in Analysis Services tabular, with many other possible scenarios.  His passion for this tool is so apparent in our discussion.

Chris Webb Interview Part 3 of 3

How can you get started with Power Query and where is the best place to go for expert advice?  Chris wrote one of the best books on the topic that, in my opinion, is very relevant today.  I gave him the bait and he wouldn’t take it when I asked: “can you recommend any good books on Power Query?”  In his usual, humble, fashion he tells me how difficult it is to write a good book about a product that changes so much; and goes on to recommend several online learning resources.

We conclude with Chris’ advice about how to get started and where to go for best practices, to develop expertise and advanced knowledge.

You can follow Chris Webb’s frequent posts on his blog, at: blog.crossjoin.co.uk
Thank you, Chris, for your time and willingness to share your thoughts and expertise.

Resources for Power Query Chris mentioned: