Power BI Report Development Tools
Power BI Overview
Power BI is collection of tools and services which facilitates the acquisition of data, data modeling, visualizations and reports, as well as the distribution of analytical solutions. This ecosystem includes a collection of software services, applications, and connectors that facilitate the creation of reports and dashboards and facilitates visual analysis of the information.
The most prominent components of the Power BI are:
Power BI Desktop – A desktop-based authoring tool for connecting, transforming, and modelling data for interactive reports. There are two versions available, one that aligns with Power BI Service and one that aligns with Power BI Report Server.
Power BI Service – A cloud-based service that supports collaboration, sharing, enterprise distribution, creation of dashboards and alerts
Power BI App Workspace – A collaboration area within Power BI Service. It is a distinct area, which is dedicated to a specific team, subject area, or project. This is so that colleagues can view the content, and contribute.
Power BI App – A set of packaged content in Power BI service for distributing related reports and dashboards to large base of consumers.
Power BI Mobile Apps – These are native applications for iOS, Android and Windows, for viewing reports and dashboards on a mobile device.
On-Premises Data Gateway – This is an agent installed within the corporate for secure access to on-premises organizational data. It is available in two modes, Enterprise mode and Personal mode.
Power BI Premium – It is an offering that provides dedicated resources within Power BI Service. It offers organizations more predictable performance, larger storage volumes, larger dataset sizes, higher refresh rates, and incremental data refresh. For each read-only user, it also enables widespread distribution of content without a Pro license.
Power BI Report Server – An alternate to the Power BI Service for deploying reports within an on-premises data center. Power BI reports are deployed and delivered in an on-premises portal alongside SSRS reports, Excel reports, and mobile reports.
Power BI Report Development Tools
The report developers has three methods to choose from and to choose the right tool for the job, you should be aware of the strengths and weaknesses of each option.
Power BI Desktop is the default choice for report development, and recommended in almost all scenarios. It allows to import data from the largest number of data sources, combine data from multiple data sources, clean and transform that data, create a dataset, add DAX calculations, and build reports all in one application. Factors to consider:
- Reports can be created in Power BI Desktop, but not dashboard as one dashboard can contain pinned visuals from numerous reports. Power BI dashboards can only be created in the Power BI Service.
- Streaming datasets cannot be created in Power BI Desktop, although reports created in Power BI Desktop can be connected to a streaming dataset.
- Power BI Desktop is available as a Windows desktop application and does not run natively on other platforms such as Apple Mac.
- Updates are released every month for Power BI Desktop. This is brings users new features quickly. However, rolling out these updates for large number of desktops can require planning. This is discussed in other blog.
This web-based Power BI Service offers functionalities which are subset of Power BI Desktop, for report development. It allows you to build reports and save them within an App Workspace, but it does not allow you to import, clean or transform data, create or edit datasets.
The Power BI Service:
- Can create dashboards, as well as reports.
- Can create streaming datasets.
- Is available to all users regardless of desktop operating system they use, because it is a web-based application.
- Requires no specific maintenance or management, because as a web-based application, it always receives the latest updates. But then it means possible delay in the introduction of new features and updates.
- Requires to download a copy of report in .pbix format, to keep back up
Closely related to Power BI, Excel 2013 and Excel 2016 contain several features. These were originally available as add-ins.
- Power Pivot became a native feature of Excel 2013 as the Excel Data Model, and
- Power Query became a native feature of Excel 2016 as the “Get & Transform” feature.
- The Excel Data Model uses the same database engine as Power BI for storing imported datasets;
- “Get & Transform” offers almost identical functionality to the Power BI Query Editor for loading data.
There are some cases where Excel does offer a better experience for report design than Power BI Desktop:
- Excel has the full power of Excel formulas for creating calculations, in addition to what you can do in DAX.
- Excel has a very mature and expanding list of charts/graphs for visualizing data, have many more options for customization of visuals.
- Excel PivotTables are a much easier way of doing ad hoc data exploration.
- Excel cube functions, have no equivalent in Power BI Desktop, are extremely useful for creating financial-style reports.
However, consider the following limitations that Excel as compared to Power BI Desktop:
- The maximum size of an Excel dataset that can be published is 250MB, whereas it is 1GB for Power BI Desktop.
- The maximum size of dataset in the 32-bit version of Excel is around 1-2GB after compression. The 64-bit version of Excel can support much larger data volumes and generally performs much better than the 32-bit version.
- Some features present in Power BI Desktop, like bi-directional cross-filtering on relationships between tables, are not present in the Excel Data Model. Hence, complex modelling cannot be handled in Excel easily.
- Not all data sources are available in Excel. Power BI provides more choices of data sources.
- Excel reports that use live connections (to Analysis Services or to published datasets), are not interactive when they are published to Power BI.
- Excel reports that use data from the Excel Data Model, when opened in the Power BI mobile apps, appear as static reports.
- The Power View add-in should not be used for report development.
- Not all Excel functionality is supported when an Excel workbook is published to Power BI and viewed in a browser.