Bo is a Principal Consultant for ThreeWill. He has 18 years of full lifecycle software development experience.
In honor of David Letterman’s departure from Late Night with David Letterman, I’m doing a top ten on the reasons you need Power BI. Unfortunately, you’ll have to imagine the drumroll for each. What I like about Dave’s Top Ten is that they are timely, relevant and usually pretty funny. So I thought that while Power BI is a typically a serious topic, it can probably be a little fun, at least I hope so. While my top 10 list are obvious exaggerations I hope you can find some of them a little relatable and maybe some of the details for each relevant to you and your needs. You may also notice below that I’ve broadened Power BI to include features that you’ll find in both Excel, Power BI for Microsoft 365 and in the Power BI Preview because all of these technologies work together. Drum roll please….
#10. Corporate Upgrade from Office 95 is Behind Schedule
We’ve all been in the situation before where the latest and greatest is out there but you just can’t have it yet. You might be looking at all the new shiny things Power BI has to offer, but for one reason or another, you’re stuck back on an old version of Excel. Microsoft has recognized this and within the Microsoft Power BI Preview has created the Microsoft Power BI Designer client tool. It’s specifically designed to create reports for the new Power BI experience. Even if you are on the latest version of Excel, this is a tool worth checking out since it has built from the ground up to work with the new Power BI experience.
#9. Haven’t taken Vacation in 27 Years because no one else can Create the Sales Report
It’s likely today someone still has the role of gathering up data on a daily, weekly or monthly basis compiling it into some sort of report-able form and then disseminating that information out. Often times these roles are called analysts but they aren’t getting to analyze data, instead they act more as data curators. The client tools available in Excel along with the features in Power BI for Microsoft 365 and Power BI Preview should help users focus less on repeated data curation and more on data analysis. By using Power Query, an analyst can to connect to many different sources of data, merge that data together and load it into PowerPivot all in a manner than is recorded and repeatable as a Power Query. Then with PowerPivot they can compress huge data sources, create relationships between data sources and even model hierarchies or create calculations. Once all the data is modeled and in a friendly format for end users, then it can be easily put into Power View by dragging and dropping fields to create visualizations. Finally, with Power BI for Microsoft 365 or Power BI Preview these workbooks can be uploaded, shared and set up with scheduled data refreshes so that they are always up to date.
#8. Management’s New Chant is “Hadoop, There It Is”
Honestly, I don’t know much about Hadoop, but I’m sure there are a lot of you out there who do and Microsoft has realized that, too. When I opened up Power Query in Excel, I counted 32 different types of data sources I could use, seriously 32! You’ve got all the standard fair in Microsoft’s wheel house from Excel, Access, SQL Server and Azure all the way to databases like Sybase, MySQL, DB2 and Teradata. If that’s not enough there’s basically any OData feed, anything that’s like a table on the web (more on that in a second) and stuff like Salesforce and Hadoop. Beyond the sheer number of available data sources, Power Query also supports a Data Catalog where queries are available for use by others. In this catalog there are public and private catalogs available. You can think of public queries as those that Microsoft makes available for everyone. Public examples include things like population data pulled from a table in Wikipedia or census data from the US Census Bureau. The private catalog would be your corporate queries that you or others in your organization have published to your own tenant for use just by others within your organization. While on the topic of corporate queries, another thing to think about is that underlying your corporate queries is likely corporate data that is in all likelihood stored on-premises. Since Power BI is essentially Microsoft’s cloud BI offering, you’ll need to make data available by using the Data Management Gateway service. A Power BI administrator can manage the settings for your tenant, and it does require the installation of gateway software to make your on-premises data available within Microsoft 365. If you are interested in more you can follow the instructions in Create a Data Management Gateway.
#7. Email Inbox is now referred to as My Reports Repository
By now everyone knows that emailing attachments is generally a bad idea. There are the obvious issues of multiple copies, lost emails, storage space and so on. Within Microsoft 365, the share button has really become the way to ensure everyone is on the same page for any type of content and ensure we don’t end up clogging our inboxes with attachments. Technologies like Delve and One Drive for Business are really leveraging this sharing behavior to create a powerful experience for users and a single place to go for content. Power BI is no different when it comes to leveraging sharing. Whether it’s the current Power BI for Microsoft 365 sites experience or the new Power BI Preview experience, sharing is the way to point everyone to a single source of a report or dashboard. Obviously sharing is a great way to pull users into a report, but sometimes you likely also want to allow for a better self-discovery experience and this is where Power BI for Microsoft 365 sites comes in. When you install the app in a site, you get a special landing page that rolls up all your Excel documents from your document libraries. You can then build a more engaging and cohesive experience for your users by choosing to enable specific Excel reports which creates a thumbnail of the contents for a visual representation. You can also chose to feature key reports and even Power BI Q&A results (Natural Language Queries) on the page. Through featuring items, you can have them shown more prominently to users without having to navigate down through document libraries and folders.
#6. Excel Spreadsheet is so big people just call it “Big Mama”
We’ve all probably had to deal with a large Excel workbook in the past at some point. Maybe it’s one that has just grown overtime or maybe it’s one that is an extract of an internal system that allows people to report on it. In either case Power BI supports larger workbooks than just a regular SharePoint Site, thus allowing you to work with more Excel data in Microsoft 365. There are some important things to consider about your work book size and what happens when you try to view the Excel file online. In addition to supporting larger workbooks, if you utilized PowerPivot you can create a memory-efficient Data Model so you don’t run into the 10 MB size limit for data outside your model. How good is the compression you ask? Well Matt Allington has shown an example where the compression ratio was 92.2%. In tests of my own using the Power BI – Getting Started Guide I saw significant compression of the 2 sample Excel inputs (each 45 MB) as well as other data sources and a final Excel workbook of around 5 MB. While working through the getting started guide, I did also learn a valuable lesson, you have a choice between using the 32 bit and 64 bit versions of Excel, go with the 64 bit version, otherwise, you can run up against the 2 GB limit just based on how much memory Excel and PowerPivot need to work.
#5. Everyone Keeps Telling You How Much Cooler The Charts on The Fitbit Site Are Then Yours
We all love visualizing data and it really confirms the old saying “a picture is worth a thousand words”. I guess in BI it might more aptly be “a chart is worth a thousand data points”. In Excel, we’ve always had lots of charts available as Pivot Charts but these days Power View is where it’s at. While Pivot Tables and Charts are typically okay solutions for dealing with relatively small sets of data you really want to leverage PowerPivot and Power View for enterprise data or if you are planning on uploading your Excel files to Power BI Preview where only Power Views are available to be shown as reports currently. Power View is actually a special type of worksheet within Excel and there are a large number of visualizations available. Since Power View is a special type of worksheet in Excel when you upload it into Power BI it can be surfaced as an interactive report where users can do things like click on bar charts to affect other charts or use slicers to filter data and even use a timeline control to see how data changes over time which is one of my favorites. If you haven’t thought about the value in how seeing something like a bubble chart when viewed over time you should really check out Let my dataset change your mindset from Hans Rosling. His Ted talk isn’t on Power BI but it’s an eye opener on seeing a bubble chart as it changes over time. Additionally, you have Power Map which allows for showing data on geographically. If your data source has city, state, zip code or even latitude and longitude it can be mapped and combined with different calculations to visually show your data on a map in many different ways like heat maps, bubble charts, and column charts. There is actually another great demonstration from that’s part of the Microsoft Virtual Academy Power BI Jump Start where Michael Tejedor shows a Power Map over time to determine energy usage for Microsoft buildings in Seattle.
#4. Support Center is really into NASCAR
The Internet of Things (IoT) is a huge buzzword these days, and there is no escaping it. Not only are people creating more data, but now so are “things.” Regardless of the source sometimes this data is really just transient data that you just want a real time view of how things are doing and not necessarily a more static historical view. It is times like these that things like gauges in your car such as your speedometer come to mind. The great thing is that the new Power BI dashboard can support just such real-time scenarios as shown by Amir Netz in his Power BI and Azure Stream Analytics demo. While a typical data source in Power BI has a daily or weekly refresh frequency through the powerful integration of Azure and Power BI, you can use Azure Stream Analytics to push data into Power BI rather than have Power BI attempt to pull data from a source. If you are interested in this you can find an article here.
#3. Marketing Team just got iPads and Immediately Threw their Desktops out the Window
Let’s face it, gone are the days where in corporate IT we can really force everyone to be on a specific version of Internet Explorer. There are more devices than ever with executives on tablets, marketing on MacBooks and many companies embracing a Bring Your Own Device (BYOD) philosophy. What’s great about Power BI for Microsoft 365, Excel Services and certainly the Power BI Technical Preview is that they leverage HTML 5 so that users on tablets and mobile devices can have a great experience. When building your reports in client applications like Power View, you can be sure that when they are published and shared your users will have a great experience with powerful visualizations and interactivity. Additionally, within the Power BI Preview it even has native apps for iOS and Windows for an even better experience than the browser.
#2. Focus Groups have determined everyone likes “Dashboard Thingies”
In the land of BI, dashboard is a pretty broad term and most people use it all the time to mean many of things. I’d like to define it as a “one stop shop” for one or more visualizations that matters to me. While that sounds pretty simple I really want to focus on that last part “that matters to me.” Depending on a person’s department or role within an organization, what matters to them can vary. I really like the simplified approach inside of Power BI Preview where Dashboards are front and center and can be easily manipulated. Users can start with nothing but a dataset created from an ever growing number of data sources and create reports within the browser, in Excel or in Power BI Designer. Dashboards can be assembled for each user’s preference by pinning different reports or pinning the results of simply Natural Language Queries against the available datasets. The ability to pin and easily manipulate the size and position of each visualization on a dashboard means everyone can see just the things they care about from an underlying data source. Also if an analyst is creating dashboards for others, they can of course also share their dashboard with others so there is a common dashboard as well.
And the #1 Reason You Need Power BI is… Reporting Strategy, code-named Whack-A-Mole, is really Killing your Arm
If you’ve had to create charts, reports and dashboards for very long a theme that often emerges is that as soon as your consumers look at the reports they often have more questions about the data. First, asking questions of the data is a good thing; it helps deepen an understanding of it. However, it can feel a bit like a game of Whack-a-Mole where you are constantly responding to variations of reports that are based on the same data because you don’t have a tool or strategy in place that lets user’s do this for themselves. In Power BI, the mantra is self-service BI and it applies to many different roles or experience levels that must interact with data. In Excel an analyst can utilize self-service BI though things like Power Query to find and gather data from many data sources. They can publish these queries to a catalog for others to use. They can utilize PowerPivot to allow Excel to create a Data Model for them and, of course, they can create Power View reports for users. Finally, they can upload the Excel to a SharePoint site as a Report or Power BI Preview to make it available as a dataset to create reports and dashboards on. In Power BI (both Power BI for Microsoft 365 and Power BI Preview) report consumers can use the Natural Language Query to ask questions of the data which will render charts and reports real time that were not even created by an analyst but simply as a result of the questions asked. In Power BI Preview, users can even open datasets and just drag and drop available fields in the browser to create reports which they can pin to a dashboard. This sort of power really empowers the users and speeds up the cycles, no longer requiring an analyst to create yet another report variant.
Hopefully, you enjoyed a little humor around some of the really great self-service features that Power BI offers. Whether you’re an analyst, a power user or just a consumer of reports and dashboards, there is bound to be something you can benefit from with the client tools offered in Excel like Power Query, PowerPivot, Power View and Power Map and the online offerings of Power BI for Microsoft 365 sites and the new Power BI Preview.