Need More Powerful Data? Harness Microsoft Power BI

Need More Powerful Data? Harness Microsoft Power BI

April Blog Series: Spring Cleaning

Insights From: Tammy Bole (Principal Data Architect at Edgewater Consulting)

In the most successful businesses, users require up to the minute access to large and diverse data sets in order to make the most effective business decisions.  Simultaneously, IT departments are tasked with maintaining the security and integrity of this data, as well as sustaining the mechanics of integrating multiple large and ever-changing heterogeneous data sets.  These two seemingly opposing requirements culminate in one common goal: enabling appropriate access and quick availability of quality data in an easily consumable manner. To perhaps help solve this quandary, Microsoft has recently released Microsoft Power BI, which offers new technology and leverages existing Office 365 and SharePoint technology as well.


Components of the Power BI software include plug-ins for Excel 2013, Office 365 and SharePoint, as well as a standalone Power BI Designer tool. Each of these tools can be used to help quickly design dashboards and other visualizations for group, department or organization-wide consumption, and information can be pulled from a variety of internal and external data sources that may contain a variety of formats.  Power BI uses Power Queries, Power Views, Power Maps and Power Pivot Galleries to help put powerful data in the hands of decision makers quickly while IT remains in control of the security of the data.

Data Sources

When generating dashboards and visualizations, the current data needs of users are no longer restricted to data collected within the enterprise.  Data from the market place, cloud based sources, XML files or unstructured sources (such as Hadoop) need to be integrated into data sets for the user community to access.  Power BI cloud aids in the simple integration of many data sources and offers the additional benefit of not requiring the user to locally download the entire data set to perform analysis. Power BI tools harness data for out of the box connections, which provides easy access to many popular software and services, such as SQL Server Analysis Services, Dynamics CRM Online​ and Salesforce.

Power Queries

Power Queries let users extract corporate data from sources authorized by IT or public sources available on the web to view and determine the appropriate business rules and shape the format of the data for consumption.  Within a Power Query, a user can apply business rules to data sets stored in the Power BI cloud as well as determine how the data should be formatted in dashboards and reports—all without involving IT.

Once the user has crafted the Power Query, it can be saved and accessed by other authorized users as a data source for further development in subsequent dashboards and reports.  Users no longer need to take on the time-consuming and error prone task of cutting and pasting large data sets between Excel workbooks. Instead, users can now reference saved Power Queries to ensure proper use of business logic and consistent formatting.

Introduced in Excel 2013, Power Views are interactive reports within Excel or SharePoint that marry data sets between multiple data sources to establish data relationships between sometimes complex data sets.  Within Power BI, Power views can be combined with Power Queries to create relationships between internal and external data sets to further enrich data without requiring additional data imports.

For example, consider map visualizations integrated with Bing maps external data.  Perhaps a report needs to group sales data by a metropolitan area.  In the raw data, the location may contain values such as "Waltham, MA", "Framingham, MA" or "Boston, MA".  All three locations are considered to be part of the Boston metropolitan area. However, there is not a data element indicating which metropolitan area to which each location belongs.  In a traditional Pivot Table application, without a separate column identifying a metropolitan region for each location, the report creates three separate lines for each separate value and aggregates each individually.  However, Power View integrates with Bing's external data set and the values are categorized according to Boston's Metropolitan area. Three variations for the state of Massachusetts' metropolitan area are now aggregated into a single group.

Power Maps

Power Maps allow organizations to overlay geographic and temporal data onto any type of map or 3-D global image desired as well as create custom maps.  Because the custom map employs the traditional x,y coordinate system, it can be used for anything from territory maps to seating arrangements. As a result, organizations have the power to determine saturation into different markets. Additionally, because the Power Map allows changes over time to be stored and displayed in a toured fashion, it can be used to track penetration into different markets over any given period of time.

Power BI Sites

Once users have created reports to benefit the organization, these reports can be uploaded to SharePoint or Office 365 for inclusion in a Power BI, which is a storage location on a SharePoint server for users to view reports in the future, is similar to a Power Pivot Gallery.  One benefit of uploading reports to the Power BI Site is the ability for authorized users to view and manipulate reports without having to download locally, as well as offering performance benefits through pre-optimization of workbooks. Please see the feature chart below for a comparison of differences between PowerPivot and Power BI.

Powerful Decisions

Microsoft's Power BI can help organizations make better use of internal and external data sources and determine whether Office 365 or SharePoint is the appropriate choice.

If your manufacturing organization is looking to implement the Microsoft Power BI solution, contact one of our expertstoday for more information on appropriate security and compliance considerations. Fullscope and Edgewater Technology have helped organizations identify valuable pieces of data to empower users and relieve inundated IT departments.

More Information: 

Power BI - Overview and Learning

Power View: Explore, visualize, and present your data

Introduction to Microsoft Power Query for Excel

Power BI - Getting Started Guide

Getting Started with Power BI Designer

Power Map for Excel Now Generally Available, Automatically Updated for Office 365

Whither Power Pivot for SharePoint? A Comparison With Power BI

Data, ERP, microsoft dynamics ax, ERP data, ERP Implementation, ERP Trends