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

image

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.

image

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.

image

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.

image

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.

image

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.

 

Advertisements

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.

image

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.

image

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:

SNAGHTML9735e46

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.

2018-02-03_11-24-26

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).

Redmond, Washington SQL Saturday & Precons: Feb 9th & 10th on Microsoft Campus

SQL Saturday events occur in cities all over.  These events give technology professionals and students the opportunity to learn about database technologies, business intelligence, and new and emerging data trends to improve skills and master data.  I have been privileged to attend and speak and several SQL Saturdays around the world but the SQL Saturday in Redmond, Washington is special because it is close to home for Microsoft and people in the greater Seattle area.  SQL Saturday is always a free, sponsor-supported event with 60 to 90 minute, conference-length sessions presented by several noted industry professionals, authors and trainers.  Many of these sessions are selections of the same great learning content you would get from the same presenters at a large industry conference which might cost thousands of dollars to attend.  One of the great perks of being in Microsoft’s backyard is that several sessions are delivered by Microsoft product team leaders, with insider tips and timely information available from the people who develop SQL Server, Azure Services, Power BI and the rest of these great products.

In addition to the shorter sessions on Saturday, all-day preconference sessions on Friday give attendees the option for deeper, focused learning for a small fee to cover travel, facility and material costs.  This year, on Friday, Feb 9th; four preconference sessions are offered by traveling presenters.  Join Arnie Rowland, Ben Miller, Vern Rabe or myself for a full-day deep-dive into one of these compelling topics.  The following is from a recent announcement to the Pacific Northwest SQL User Group Members:

SQL Saturday Redmond – Feb 10, 2018

Just a reminder that SQLSaturday#696 will take place on Saturday, February 10, 2018 at Building 92, 15010 NE 35th St, Redmond, Washington, United States, 98052. SQLSaturday#696 is a free one day training event for SQL Server professionals and those interested in SQL Server. Please register for SQL Saturday Redmond 2018 at Registration.

Four All-day Preconferences – Friday, Feb 9, 2018

Also this year, we are offering Pre-Cons on Friday February 9, 2018, the day before SQLSaturday, in the same building (92): 8:00 AM to 4:30 PM. Each PreCon is well worth the modest fee. Register for a SQL Saturday Redmond PreCon by accessing its Eventbrite link below:

Building a Business Intelligence Solution with Power BI – Paul Turley https://www.eventbrite.com/e/power-bi-hands-on-workshop-tickets-41327327148

T-SQL for Performance and Accuracy – Vern Rabe https://www.eventbrite.com/e/t-sql-for-performance-and-accuracy-tickets-41172854115?aff=es2

Quelling Your Queasies: Mastering Technical Presentations – Arnie Rowland https://redmondprecon2017.eventbrite.com/

PowerShell Modules for the DBA – Ben Miller https://www.eventbrite.com/e/powershell-modules-for-the-dba-tickets-41368075026

See you there!

Please plan to join us in Redmond, on the Microsoft Campus – for the preconference on Friday, February 9th – and for SQL Saturday on February 10th, 2018.

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.

image

 

Power Query Skills Apply to Excel, Power BI and SSAS Tabular

Did you know that if you learn to use Power Query to “Get” and transform data in one of these Microsoft Business Intelligence & data analysis tools, you actually have the skills to use any of them?  Power Query is an amazing technology for acquiring and wrangling data from a vast portfolio of data sources, and it can be used to perform simple and very advanced data transformations.  In my interview with Chris Webb at the 2017 PASS Summit, he said that Power Query is everywhere and being added to more and more Microsoft products.  We can expect to see Power Query in web-based tools for use with cloud services.  Today, it is the “Get Data experience” on the Data ribbon in Excel, Power BI Desktop and now in SQL Server Data Tools (SSDT) for SSAS 2017 Tabular projects.  The adoption rate  for Power Query has been fantastic and it is a tool that once you learn the basics, you just can’t go back and use older tools.  Power Query makes data wrangling and transformations a creative process rather than simply an exercise in connecting the dots (or tasks and transforms).  The Query Editor generates “M” Script, a powerful and flexible data mashup language that translates transformation steps into native query code.

In this tutorial, I use Excel 2016, Power BI Desktop and SSDT for Visual Studio 2017 to create three different sample projects.  Using a few of the lab data files from my Mastering Power BI Workshop course, I import a folder full of CSV files; using the same technique in each of the three tools.  This demonstration makes the point the Power Query generally works the same in workbook, desktop and enterprise BI and analytics solutions.  There are a few subtle differences to be aware of.  By default, Excel uses the Power Query output to populate worksheet tables in addition to the embedded Power Query data model.  In the new SSAS Tabular designer within SSDT, you define connections first, and then use the Tabular Model Explorer to import tables from established connections.  File, folder and data source connections are also managed a little differently in an SSAS project because the deployed databases is managed by the SSAS service account.