This post is the first in a series describing our experience using
Microsoft Power BI for an internal dashboard project.
When not delivering Business Intelligence/Analytics, Data Warehousing, and Collaboration/Portal solutions to the world’s leading brands, we periodically consider ways to improve our own business processes. We recently decided we could improve the way we track and manage our business through updating our dashboards. Like most companies, we use a number of tools and systems to manage our day-to-day activities. Sales uses a CRM system, Solution Delivery uses a time tracking system, Talent Acquisition uses a job application and candidate management system.
Sound familiar? Rather than letting the shoemakers children go without shoes, we decided we should do something about it. While each system has its own reporting system, we wanted an integrated dashboard to combine all of our data and metrics in one location.
Goals for Our Power BI Dashboard
We knew where we wanted to go, but how should we get there? In choosing an approach, we had a few goals in mind:
- Quick and easy way to update report data
- A way to easily report on specific time periods e.g. Fiscal YTD, Last Two Weeks, etc.
- A way to create graphs for trend analysis
- Mobile access for reports
In considering the goals, we decided this was a good opportunity to explore Microsoft’s Power BI offering in a real-use scenario. This particular project was also a proof of concept, taking an iterative approach to see what was possible, and improving upon it with as time permits between client commitments.
What is Microsoft Power BI?
The Microsoft Power BI tools include a set of Excel add-ins, specifically Power Pivot, Power Query, and Power View. The cloud service is described by Microsoft as the current experience and the new experience.
The current experience is enabled using Office 365, with Power BI Sites that reside in your organization’s SharePoint deployment. It includes many features that let you share your findings and your reports with others inside your organization.
The new experience is centered on PowerBI.com, a preview release of an online service where you can quickly create dashboards, share reports, and directly connect to data. The new experience also introduces the Power BI Designer, a dedicated report authoring tool that enables you to transform data, create powerful reports and visualizations, and publish to the PowerBI.com service. It also extends to all your mobile devices with dedicated viewer apps.
Given the set of available Power BI tools, we decided to use Excel as our starting point. The plan:
- Pull the data in with Power Query
- Model the data in Power Pivot (also called the Excel Data Model)
- Create pivot tables and charts in Excel connecting to data model
- Host the file in in our Office 365 SharePoint for distribution & consumption
This blog series will include a different post for each step of the plan.
What's Up Next?
Next, we’ll show you just how to get the data with Power Query.