Technical

A Developer’s Guide to Power BI

By October 31, 2018 No Comments
A DEVELOPERS GUIDE to POWER-BI

There are many options when it comes to data analytics tools. Choosing the right one for your organization will depend on a number of factors. Since many of the reviews and articles on these tools are focused on the business users, the Aptitive team wanted to explore these tools from the developer’s perspective. In this developer’s guide to power BI, we’ll go over the performance, interface, customization, and more to help you get a full understanding of this tool.

Why Power BI?

With a free Desktop version and a $10 per user per month price tag on Power BI Services, Power BI is a financially attractive alternative to the likes of Tableau, Qlik, and Looker, who either offer custom-tailored pricing models or a large initial per user cost followed by an annual fee after the first year. However, do not conflate cost with quality; getting the most out of Power BI is more dependent on your data environment and who is doing the data discovery. Companies already relying heavily on Microsoft tools should look to add Power BI to their roster, as it integrates seamlessly with SQL Server Analysis Services to facilitate faster and deeper analysis.

Performance for Developers

When working with large datasets, developers will experience some slowdown as they customize and publish their reports. Developing on Power BI works best with small-to-medium sized data sets. At the same time, Microsoft has recently come out with more optimization options such as drillthrough functionality, which allows for deeper analytical work for less processing power.

Performance for Users

User performance through Power BI Services is controlled through row-level security implementation. For any sized dataset, the number of rows can be limited depending on the user’s role. Overviews and executive dashboards may run somewhat slow, but as the user’s role become more granular, dashboards will operate more quickly.

Report Layer Power BIUser Interface: Data Layer

Data is laid out in a tabular form; clicking any measure column header reveals a dropdown menu with sorting options, filtering selections, and the Data Analysis Expressions (DAX) behind the calculation.

User Data LayerUser Interface: Relationship Layer

The source tables are draggable objects with labelled arrows between tables denoting the type of relationship.

Relationship LayerUsability and Ease of Learning

Microsoft Power BI documentation is replete with tutorials, samples, quickstarts, and concepts for the fundamentals of development. For a more directed learning experience, Microsoft also put out the Microsoft Power BI Guided Learning set, which is a freely-available collection of mini-courses on modeling, visualization, and exploration of data through Power BI. It also includes an introduction to DAX development as a tool to transform data in the program. Additionally, the Power BI community forums almost always have an answer to any technical question a developer might have.

Modeling

Power BI can easily connect to multiple data sources including both local folders and most major database platforms. Data can be cleaned and transformed using the Query Editor; the Editor can change data type, add columns, and combine data from multiple sources. Throughout this transformation process, the Query Editor records each step so that every time the query connects to the data source, the data is transformed accordingly. Relationships can be created by specifying a From: table and To: table, the keys to relate, a cardinality, and a cross filter direction.

Customization

In terms of data transformation, Power Query is a powerful language for ensuring that your report contains the exact data and relationships you and your business user are looking to understand. Power Query simplifies the process of data transformation, with an intuitive step-by-step process for joining, altering, or cleaning your tables within Power BI. For actual report building, Power BI contains a comprehensive list of visualizations for almost all business needs; if one is not found within the default set, Microsoft sponsors a Visual Gallery of custom user-created visualizations that anyone is free to explore and download.

Permissions and User Roles

Adding permissions to workspaces, datasets, and reports within your org is as simple as adding an email address and setting an access level. Row-level security is enabled in Power BI Desktop; role management allows you flexibly customize access to specific data tables using DAX functions to specify conditional filters. Default security filtering is singe-directional; however, bi-directional cross-filtering allows for the implementation of dynamic row-level security based on user names and/or login IDs.

Ease of Dev Opp and Source Control

When users have access to a data connection or report, source and version control are extremely limited without external GitHub resources. Most of the available activities are at the macro level: viewing/editing reports, adding sources to gateways, or installing the application. There is no internal edit history for any reports or dashboards.

Set Up and Environment

Set up is largely dependent on whether your data is structured in the cloud, on-premise, or a hybrid. Once the architecture is established, you need to create Data Gateways and assign them to different departments and data sources. This Gateway acts as a secure connection between your data source and development environments. From there, security and permissions can be applied to ensure the right people within your organization have access to your Gateways. When the Gateways are established, data can be pulled into Power BI via Power Query, and development can begin.

Implementation

The most common implementation of Power BI utilizes on-premise source data and Power BI Desktop for data preparation and reporting, with Power BI Service used in the cloud to consume reports and dashboards, collaborate, and establish security. This hybrid implementation strategy takes advantage of the full range of Power BI functionality by leveraging both the Desktop and Service versions. On-premise data sources connect to Power BI Desktop for development, leading to quicker report creation (though Power BI also supports cloud-based data storage).

Summary and Key Points

Power BI is an extremely affordable and comprehensive analytics tool. It integrates seamlessly with Excel, Azure, and SQL Server, allowing for established Microsoft users to start analyzing almost instantly. The tool is easy to learn for developers and business users alike and there are many available resources, like Microsoft mini-courses and community forums.

A few things to be aware of with Power BI is that it may lack some of the bells and whistles as other analytics tools. It’s also best if you are already in the Microsoft ecosystem and are coming in with a solid data strategy.

If you want to learn more about Power BI or any other analytics tools, contact us today to schedule a no obligation whiteboard session.

Related Articles and Case Studies on Analytics Dashboard:

A CTO’s Guide to Selecting an Analytics & BI Platform
How to Improve Dashboard Performance When Using  Tableau, Power Bi, and Looker
5 Important Principles for Dashboard Development
Google Analytics & Tableau Case Study
Tableau Visualization Case Study

 

Schafer Gizel is a data consultant at Aptitive with a passion for data analytics.