Skip to content

How To Start a Power BI Project — A Consultant’s Perspective

Technology

Jun 15, 2022 - 8 minute read

2480 Powerbi 416X300px
Injae Park BI Tech Lead

A Swedish-Korean Business Intelligence Technical Lead specialising in Power BI and Azure. Recently honoured with the Microsoft MVP recognition, Injae is an avid contributor of Power BI content on platforms like YouTube and LinkedIn. 

See all Injae's posts

2988 HC Digital Transformation 476X381

Share

Power BI at its core is a tool to improve and facilitate. It can help drive decisions with structured analytics or serve as a knowledge base of the latest data. As a consultant working on Power BI projects, I notice a common obstacle to a smooth development process. It’s the lack of architectural definitions during the planning phase, which may result in having to restructure the report or data model at later stages of the development.

This risk may be minimised by putting a strong emphasis on requirement collection at the beginning of the project. There are several questions we should ask before we start the development. Some of them can be answered by your stakeholders, while others need to be answered by you. What information should you be looking for? Read on to find out.

What Is the Purpose of the Report?

The purpose of the report is the most important question to ask, but luckily, there will always be an answer (likely, there wouldn’t be a need for a project without it). A lot of technical needs can be identified based on the report type, which is covered extensively in the Power BI usage scenario Documentation from Microsoft. This is an excellent place to start your planning.

Addressing this question will help you focus your attention on the business value, whatever it may be — data, speed, delivery, or something else. Even if your stakeholders can’t give you a complete answer, they should be able to define the most important outcomes.

Consider two different reports; a daily scheduled one with no distinct security and a yearly financial report with strict partitions of data security. The first would require focussing on an optimised backend and data completeness, whereas the other would call for a strong understanding of the different implementations of security within Power BI.

Who Are the End Users?

The need for better data presentation will exist on all levels of a business. However, the needs of the CEO will be different from the needs of a sales rep or HR analyst, and the report should always take this into account. Reports that are universally useful to everyone within an organisation are uncommon, and it’s always a best practice to consider your end users.

If possible, a dialogue with the end users is a great place to start. If the report is a brand new concept, ask what data-driven insights and decisions will bring the most value. If it’s based on an existing process, what functionalities should be kept or modified? These kinds of questions will help you determine not only the user experience that should be delivered but also backend considerations. If the end users don’t require a deep dive into the data, aggregations should happen before data is ingested into Power BI, and very few filters are required on the reporting layer. If a process that was managed in Excel or legacy BI systems is being migrated into Power BI, figuring out the data needs of the client could be the key to the best user experience.

What Is the Level of Technical Ability?

How technical are the end users? Are they Excel gurus who would like to use Power BI as an overview but still extract the data for more personalised use? Are they advanced in Power BI or have knowledge of the dataset and would prefer to have access directly to the data?

Data extraction into Excel might be the most requested functionality for a lot of users new to Power BI. You might be able to use the native extraction functionality in the online environment, but this functionality is limited to 150,000 rows in an .xlsx file. If that’s not enough, the users might require access to the Power Query within their own Excel file to work in pivots directly.

If the end users are skilled in Power BI, the reporting layer may not be functionally necessary. The project could result in a managed self-service model where the data architecture is delivered, but the reports are built internally within the organisational teams or departments. If the end users are more comfortable with the dataset but not Power BI, the personalised visuals feature could be what they’re looking for — a reporting layer where fields can be changed immediately.

Conversely, if the end users aren’t interested in the interactive style of Power BI, they might be happier with an email containing a snapshot of the report, sent at a scheduled interval. This could be achieved by using the native subscription function within Power BI or Power Automate within the Azure system if a more personalised approach is required.

How Will the Users Consume the Report?

There are several ways that reports can be delivered to end users. This aspect has a lot to do with the security of the data, who the end users are, how technical they are, and the complexity of the information in the report.

  1. Snapshots sent through email
    As mentioned above, this is possible with the subscription function within Power Bi, which can send a snapshot image/pdf of a report page on a scheduled basis. A drawback of this option is that it only works with Premium workspaces.
    Something similar can be achieved by using Microsoft Flow to send emails with links to the report, in case a customised email is needed.
  2. Sharing Power BI reports or dashboards
    This will be the most common way to consume Power BI reports, but it may not always be optimal, depending on the Microsoft licences each user has. If the report is built on a Power BI Pro licence, all end users need to have a Pro licence to consume the report. There are, however, certain ways of using Power BI Premium where end users do not need a licence.
  3. Embedded reports
    Reports in the online environment can be embedded into websites, PowerApps (model-driven), SharePoint, or Teams — anywhere you can paste a piece of HTML code. On the one hand, this is a nice feature to make the report more accessible, but all end users still need to be granted access to the report itself. Reports can now even be embedded into PowerPoint slides, allowing for a smooth presentation.
    Moreover, by combining embedded reports with URL filters, you can have a Power BI report that can technically be interactive within its space, e.g., a model-driven app where the end user selects some options and is presented with a prefiltered Power BI report.
  4. Published applications
    This is probably the most recommended method of sharing Power BI reports within an organisation. Several reports can be grouped into an application, where the governance for access can be maintained in one generalised location. Row-level security (RLS) still needs to be applied at the report level, but applications have a much more “complete” look than a standalone report

What Licence Will Be Used?

Licencing is always linked to cost, and it’s important to have the correct Microsoft licence. There’s a considerable number of functionalities which are only available at the Premium capacity — you can find a full list here. The most important limitations of not having the Premium package are storage space, refresh rates, and end user licencing. The Pro license, the level under Premium, has a 1 GB limit on Power BI file size and a 10 GB limit on workspace size. This effectively narrows the data content of reports and applications that ingest data using Import mode, where the database is stored within the Power BI file. Additionally, the Pro licence can only refresh the underlying data in Import mode 8 times per day, compared to Premium capacity’s 48 times a day. However, perhaps the most important aspect of licencing is that reports published using the Pro license can only be accessed by other users with the Pro licence — and the Premium licence can circumvent this need on applications

How Is the Data Going To Be Ingested Into Power BI?

How the data enters the report is a question directly related to licencing and infrastructure. It depends on the model size, the complexity of the extract, transform, load (ETL) process, and the refresh rate needed for the report. The greater the complexity, the greater the price, but in general, smaller models with simple ETL should be imported and the transformations should occur in Power Query. A larger model with a complex ETL solution should use an orchestration tool such as Azure Data Factory to perform the ETL transformations and load data in Power BI using DirectQuery, analytical services, dataflows, hybrid tables, or other options if an import isn’t possible.

How Will Security Be Handled?

There are a lot of different ways that security can be implemented within Power BI, and which one to choose depends on the organisational structure of the stakeholders.

The most basic level of security in the Power BI service is workspace access. There are four different pre-set access levels, but in most cases, developers will be given admin or member roles, whereas end users should be given a viewer role.

From the viewer role, the report can apply RLS, which is implemented by creating roles in the Power BI Desktop and assigning DAX code to act as a data filter. In the Power BI service, Azure Directory (AD) groups or individual email addresses can be added to a role, and users with the corresponding RLS will see data filtered to the expressed DAX code.

By using an external tool such as Tabular Editor, object-level security (OLS) can also be implemented. Objects within the Power BI model (such as columns and tables) can be restricted and tied to RLS roles.

RLS roles are very easy to implement but with a large number of users and no AD groups, they can be difficult to maintain (imagine 50 roles where users need to be added and removed manually). Therefore, an organisation with a high level of Azure adoption might opt for dynamic RLS, which uses the function USERPRINCIPALNAME() in Dax to retrieve the email address of the user who is viewing a report. A specific access level could be assigned using relationships, where users can all be added to a single role or AD group.

On the backend, there will also usually be some level of security, and depending on the maturity of Power BI adoption or the existing framework, it may be used entirely instead of any native Power BI security. An example of this is how new users within an environment can be added to a specific Azure Directory. The AD group would have specific restrictions or permissions in SQL databases, which would directly enable access to the correct data.

Final Thoughts

To recap, there’s a considerable number of questions to ask at the beginning of a new Power BI project. As developers, we should always start with a stakeholder focus and determine how to provide value. This involves asking ourselves how to deliver the best user experience through design and accessibility, what infrastructure best suits the organisation, and how the security should be modelled. Having read this article, I hope you’ve gained insights that will help you successfully plan and execute your future Power BI projects.

2988 HC Digital Transformation 476X381
Injae Park BI Tech Lead

A Swedish-Korean Business Intelligence Technical Lead specialising in Power BI and Azure. Recently honoured with the Microsoft MVP recognition, Injae is an avid contributor of Power BI content on platforms like YouTube and LinkedIn. 

See all Injae's posts

Related posts

You might be also interested in

Contact

Start your project with Objectivity

CTA Pattern - Contact - Middle