Oct 12, 2022
Demystifying Power BI — Data and Report Design Tools
A summary of data modeling and report design tools in the Power BI ecosystem
Power BI empowers users and developers by offering a tool that meets their skillsets and requirements. Power BI Desktop for professional developers, Excel for traditional self-service users, and many more.
This article will discuss the design and modeling tools available to the developers and self-service BI users in the Power BI ecosystem.
This article is a part of the multipart series on the Power BI ecosystem and continues the concepts introduced in the introduction article and refers heavily to the Power BI back-end platforms. We recommend reading through both articles for a complete understanding.
Power BI supports and offers multiple tools for dataset and report development. Every tool has its strengths, weaknesses, and scenarios, making it ideal over the others. In addition, Power BI supports more than just one type of report.
Before we discuss the content creation tools, we must learn about the report formats. The following article summarizes the report types and file formats in the Power BI ecosystem.
Data and Report Design Tools
Multiple tools are available to developers and self-service users for content creation in Power BI. The available tools can be divided into two broad categories: Tools native to Power BI and tools from other Microsoft reporting technologies that work for Power BI.
Native Power BI Tools
Power BI Desktop: Power BI Desktop is the default choice for the data model and report development and is recommended in most scenarios. Power BI Desktop is the primary tool to create the Power BI report (.pbix) files. Power BI Desktop is updated frequently and is usually the first tool to receive new features, including preview features. Power BI Desktop is available for Windows devices only.
It allows authors to build reports that leverage all available Power BI features, such as automated insights, AI, and smart narratives. Power BI Desktop combines state-of-the-art interactive visualizations with industry-leading data query and modeling capabilities built into one tool. It allows you to:
- get data from many sources
- combine data from multiple sources
- clean, transform and enrich that data
- add measures and DAX calculations
- define Row Level Security (RLS)
- build and publish reports
Power BI Desktop for Power BI Report Server: Power BI Desktop for Power BI Report Server is designed and optimized for Power BI reports created for the Power BI Report Server. Power BI Desktop for Power BI Report Server offers only the features generally available for Power BI Report Server.
Power BI Desktop for Power BI Report Server receives quarterly updates and ensures that your report and data model are compatible with the Power BI Report Server.
Power BI Service: The Power BI service is primarily for sharing and distribution capabilities. Power BI service provides a subset of report-building features for creating Power BI reports compared to Power BI Desktop. The service lacks advanced data query and data modeling capabilities. You can connect to data sources in the Power BI service, but modeling is limited.
Power BI service is a web-based application, and it is available to all users regardless of what desktop operating system they use. Power BI service needs no specific maintenance or management by the users to "upgrade" to the latest version. In Power BI service, you can:
- create dashboards and reports
- create Apps to package up a set of related content for distribution
- create scorecards and metrics
- build dataflows and datamarts
- manage user access, permissions, and security
Excel: Microsoft Excel is used to create Excel workbooks that can be hosted on the Power BI platform. Although not native to Power BI, Excel has substantially evolved to support Power BI and contains several features closely related to Power BI. Such as, the Excel data model uses the same database engine (VertiPaq) as Power BI for storing imported datasets.
Using Excel to build reports provides a valuable alternative to Power BI Desktop in certain circumstances. Excel enables detailed ad hoc analysis and what-if scenarios. Users can manipulate data in sheets and use formulas to combine or shape data to fit into custom processes. However, Excel does have several limitations compared to Power BI Desktop:
- Excel data models have a lower size limit on Power BI service than Power BI premium datasets
- in Power BI mobile Apps, Excel reports appear as static
- not all Excel functionality is supported in the Power BI service
- some data sources are not available in Excel
Hosting Excel workbooks on the Power BI service also introduces additional licensing requirements.
Power BI Report Builder: Power BI Report Builder is a standalone Windows Desktop application to author paginated reports. Power BI Report Builder is optimized for authoring paginated reports for the Power BI service. It can sign in to Power BI, allowing users to open and publish from/to the workspaces. In addition, it supports connecting and querying Power BI Premium datasets.
With Power BI Report Builder, you can:
- modify reports that were created in SSDT Report Designer or SSRS Report Builder
- preview reports in HTML or print format
- export reports to other file formats such as MHTML, PDF, XML, CSV, Word, and Excel
Power BI Embedded Analytics Playground: Although not a report development tool, Power BI Embedded Analytics Playground is essential for developers who embed Power BI content in custom applications.
Power BI Embedded Analytics Playground is a web app for developers to learn, explore, and try out Power BI embedded analytics. The playground provides a hands-on coding experience. Developers can embed reports and interact with Power BI client APIs with instant results.
Tools for Analysis & Reporting Services
Report Design Tools: Paginated reports are based on Microsoft's Report Definition Language (RDL) report technology in SQL Server Reporting Services (SSRS). Thus paginated reports can also be created using the following tools:
- Report Designer in SQL Server Data Tools (SSDT)
- Report Builder in SQL Server (aka SQL Server Report Builder)
However, these paginated reports are best suited for the Power BI Report Server and not for the Power BI service due to feature parity between these platforms.
Data Modeling Tools: Microsoft Analysis Services engine uses the XMLA (XML for Analysis) communication protocol to support open-platform connectivity. XMLA is a protocol for communication between client applications and an Analysis Services instance such as Azure Analysis Services, SQL Server Analysis Services, and Power BI.
Under the hood, analysis services run Power BI's semantic modeling, governance, lifecycle, and data management. XMLA endpoint support is a Premium feature in Power BI. Only the datasets hosted in the premium workspaces support XMLA endpoints: Power BI Premium, Premium Per User (PPU), and Power BI Embedded. Thus, applications and tools designed for Analysis Services can connect to Power BI premium datasets.
Some of the most common tools used with Azure Analysis Services and SQL Server Analysis Services are:
- SQL Server Data Tools (SSDT)
- SQL Server Management Studio (SSMS)
- SQL Server Profiler
- Analysis Services Deployment Wizard
Third-Party Tools
This article will not be complete without mentioning the third-party tools available in the market. The power BI community is innovative and ever-evolving. However, a detailed note of third-party tools is beyond the scope of this article. Some of the most valuable and standard third-party tools are Tabular Editor, ALM Toolkit, and DAX Studio. You can read more about these tools on Microsoft Docs and SQLBI.
That's all for this article. Take a break! But, for developers, we have a question. What are the side effects of using XMLA to connect to the datasets?
Conclusion
Power BI offers multiple tools to seasoned developers and self-service business users. As a result, everyone can design and analyze data and reports in a tool of their choice.
This article discussed the content creation tools available in the Power BI ecosystem. We also briefly discussed the tools initially designed for Analysis and Reporting services that work well with Power BI.
Next in series
In the next article, we will discuss the client applications and sharing options for the end-users and APIs, interfaces, and embedding options available to developers in Power BI.
In another article, we talked about the data management capabilities of Power BI. We will discuss the types of data models, storage modes, connectivity modes, data cleansing, and refresh options in Power BI.
