5# - A semi-self-service Power BI implementation model
to get quicker maintainable reports
Reading time: 10 minutes
Imagine, you are one person working in a back-office team. You want to create a new report, and you want to make it available for your larger team. What do you do?
In most cases there are two possible approaches:
You ask your IT team to create and maintain the report, but you need to wait months.
You create the report, but then you're responsible for maintaining it and ensuring it works correctly whenever required.
What if you can combine the business knowledge superpower of citizen developers with IT maintainability best practices?
You can address this by defining a new usage scenario that merges these two approaches. We can call it the semi-self-service Power BI implementation model.
The background
Before jumping on this implementation model description we need to give some background. We will see together the two standard scenarios defined above:
IT report implementation
Business report implementation
And the roles and responsibilities.
Roles and responsibilities
Let’s outline the roles and responsibilities involved in these scenarios:
Business team: this team uses the tools provided to create value for the company. The business team, in this case, can be divided into three roles:
The Report Requestor/Creator: the person who first thought about building a report to fulfil a business process. This is normally the person who develops the report in the case of the business report implementation.
The Business Testers: the group that tests the report before it gets published to the production workspace. It normally comprehends the report requestor/creator and a few close colleagues.
The Report users: the final users of the report. Normally other internal teams that consume the data managed by the report creator’s team.
IT team: this team professionally implements technical solutions to enable value creation for the company. Normally IT teams have technical constraints when building a new platform, e.g. Version control, CI/CD and monitoring should always be in place before any release.
The IT team, in this case, can be divided into four roles:
The Power BI Developer: the person who professionally creates Power BI reports and applies development best practices.
The Business Analyst/Requirement Engineer: the person who collects the business requirements from the report requestor and transforms them into technical requirements for the Power BI Developer.
The Development Team Tester: the person who professionally tests the report before it goes to user testing.
The Production Support team: the team responsible for all the IT operations and deployments into the production workspace. This team will also ensure that all the reports always work.
Different organizations might have different roles and responsibilities. In the end, it means having the responsibilities just reshuffled among the available roles. In this case, it is important to have this background to better understand the scenarios.
IT report implementation
In this scenario, the IT team is responsible for the complete technical implementation of the new report. Often the development lifecycle is composed of these steps:
The Report Requestor defines the report requirements upfront and communicates them to the Business Analyst
The Business Analyst writes down the requirements
The Power BI Developer develops the report and publishes it into the IT development workspace
The Development Team Tester tests the report in the IT Development workspace
If all is ok, the Power BI Developer publishes the report into the User testing workspace
The Business Testers test the report
If the Business Testers are happy, the Production Support team deploys the report into the Production workspace.
The Report users use the report
The main challenges I saw with this model are:
long time to get the report developed often due to the busy technical team book of work and the non-negotiable steps of the development process.
lower than possible final impact created. This is normally due to the challenge of getting the requirements correct up front. But also because of the corporate low flexibility in changing the requirements once the report is in the development phase.
Business report implementation
In this scenario, the business team is responsible for the complete implementation of the new report. Here the development lifecycle is normally composed of these steps:
The Report Creator defines the report requirements
The Report Creator publishes the report to the “My workspace” or to the team workspace
The Report users use the report
In this case, the challenges are mostly on the maintenance side of the report. In my experience, I saw the following challenges:
Report maintainability takes a lot of time because of missing automation and development best practices (access management, data refresh failures, changes in the data sources that are often Excel files)
If the Report Creator leaves the team, no one knows how to maintain the report or how to make changes.
The semi-self-service Power BI report development
With this implementation model, you get the best of the two worlds but also you lose some of their advantages.
In this scenario, the report developer role is played by the Report Requestor, but the deployment process and the maintenance best practices are implemented by the IT team. Let’s see in detail how it looks like:
The Report Creator develops the report in a “business workspace”
The Report Creator improves the report after a few feedback iterations with potential users
The Report Creator provides the report to the Power BI Developer
The Power BI Developer reviews the PBIX file by applying the relevant best practices/platform requirements and publishes it to the “User testing workspace”.
The Business Testers test the report
If the Business Testers are happy, the Production Support team deploys the report into the “Production workspace”
The Report users use the report
The main characteristics of this approach are:
The Report Requestor develops the report
The IT team still maintains the report when in production
The IT team applies best practices to improve report quality and maintainability
This approach eliminates the miscommunication between the Report Requestor and the IT team and allows for direct and frequent iterations with the Report users. In the end, we ensure that the final report is created by those who need it.
Why a Business workspace?
In the context of this model, we introduced the concept of ‘Business workspace’. This is nothing else than a workspace fully managed by the business team.
Here, the business team can publish and test the reports, grant access to report users, delete everything, etc… in a few words they are Admins or Members in the Power BI workspace.
In this model, the IT team supports the business colleagues by providing data sources (such as semantic models or dataflows) and/or offering guidance during the development. The IT team will not have more powerful rights than the ‘Contributor’ role here nor will have any responsibility for the content published or the accesses granted.
The business workspace is the development playground of the business team.
This is required for two reasons:
The business team can play around with Power BI with fewer constraints than in the IT-managed workspaces.
The IT team doesn’t have to maintain and monitor an additional workspace.
In any case, after the report is published in the business workspace, the IT team perform some checks on the PBIX file. But what checks and best practices does the Power BI Developer implement to the report before publishing it to the “User testing workspace”?
IT report checks and best practices
The goal of the technical team is to improve the report maintainability, security and user experience.
On the other side, we do not want the technical team to spend too much time in changing the report without adding value. What does it mean?
The technical team should put effort only when it makes sense. We should not be perfect. We should build something useful.
Let’s see with one example.
The data model is poorly structured, but it works, and it doesn’t create performance or maintenance issues? We accept it.
The data model is poorly structured, the report will use a large amount of data and will go through several changes? Unacceptable. We improve it
Before starting we need to know:
Data sources
Data confidentiality
Target users
Data refresh requirements
Amount of data
If the semantic model/dataflow will be used for other reports
Access management requirements
Once we understand these points we start with the following checks and implementations.
Maintainability:
Ensuring that the connection to the data source is established and stable:
Give the technical account access to the data source
Set up the required data gateways and make sure they are stable
Communicate to the data sources’ owners that now we are a consumer and that we expect clear communication if something changes on their side
Set up the automatic refresh according to user needs
Apply incremental refresh in Power BI where it makes sense
Preventing possible issues impacting the data refresh:
Modify Power Query steps to be less prone to refresh failures.
If the source is made of files, explain to the files’ owner the best practices to avoid breaking the refresh. E.g. do not change column names or lock specific field changes in Excel.
Use steps in Power Query where only the required columns are affected, e.g. no automatic data type for all the columns.
Improving report performance and making it ready for changes:
If you expect frequent changes to the report or a large amount of data: improve the data model and reduce the model size.
Make sure to have the code documented and to have the needed technical documentation.
Test the performance and make sure to follow minimum best practices for better performance: only use useful columns/rows, use import mode where possible, use query folding, use date table, do transformation as close as possible to the data source, and others.
Others:
Set up possible automation for the access management to the report.
Write business documentation that explains what the report does, the data sources used and who are the contact people.
Security and policies
Set up row or object-level security where needed
Make sure that the access management complies with the internal policies: e.g. respect the need-to-know principle, only temporary ‘write’ access to Admins in production, and others.
Make sure that the report is published in a workspace that respects cross-border restrictions or BYOK requirements.
User experience:
Check the usability of the report: e.g. search field in slicers, only show required visuals, others. You can find some best practices here and here.
Make sure that the report performance is good also with production data.
Try to achieve a report design familiar to the users
Check the whole end-to-end user experience: is it easy for the user to get access? To ask for help? To understand the report? To use the report? To provide feedback about the report?
I am sure there are other possible aspects to look at. If you have something in mind feel free to let me know so that we can make this newsletter better.
One last comment: before to implement any change, we need to think about where is important to invest our time.
I am paid to create the highest impact not to make perfect reports. But making better reports increases its impact. So, there is always a trade-off we need to consider.
In the end, we should invest time in making the report and its set up good enough to be useful and maintainable in the shortest time. The goal is to quickly create value from the usage of data.
Where does this model work and how to start?
Ok, great you convinced me. I want to start. What should I do?
There are a few things I suggest you look at before adopting this model.
The business team should be able and willing to develop reports with Power BI. This means that you need to plan a few trainings with the interested attendees and to provide them guidance during the development process.
The business team need to have access to the data they need.
Often the data are simply in Excel files they have access to. But in other cases, the data are stored in databases that report creators do not have access to. In such cases, the IT team needs to create semantic models or dataflows to provide clean data to the report creators or to find an alternative.Both IT and business teams should have clear in mind the rules of this model.
The IT team should accept losing control of the report development and should focus on the guidance aspect also in topics like UX, adoption and feedback cycle.The Business team should be open to welcoming the changes applied by the IT team to improve the reports and accepting the limitations given by IT policies/best practices.
This model normally works better when you work with:
A few ‘pilot’ users with a data background and willing to work with Power BI
A very specific and simple enough use case that is part of an established business process
A constant communication between ‘business’ developers and the IT team
But there are cases where the semi-self-service Power BI mode is probably not a solution. An example is when there are complex reports connecting to various ‘technical’ systems.
Or cases when this model is the correct one but you need to pay a bit of attention when using it. An example is when business Report Creators come to the IT team only when they are leaving their team. Here, IT colleagues are the last chance to give a future to the report. But IT checks might substantially change the experience for the Report users and because often the time is not enough for a proper handover.
To conclude, this model works well in specific cases but it doesn’t work every time everywhere. I suggest you think about where it might work and start implementing it keeping in mind the Minimum Viable Product approach.
Summary
Today we saw the semi-self-service Power BI implementation model that combines the business knowledge superpower of citizen developers with IT maintainability best practices.
We also saw when it makes sense to use it and some tips to implement it in your company.
If you enjoyed this newsletter and haven’t subscribed yet, it might be a good idea to
In the next newsletter, we will see different ways on which IT and business teams can collaborate using this model:
In the meantime, you can check the latest articles in the archive.
To the next one,
Francesco
📣Announcement:
I am part of the team organizing the Data Platform Conference Switzerland which will take place on the 25th October 2024 in Zurich, Switzerland.
It might sound like something I should say but… I am honestly surprised by the quality of the sessions and speakers at the conference. I have personally built a part of my career thanks to the content shared by some of them.
Just to name a few sessions:
Chris Webb: What's new in Power Query in Power BI, Fabric and Excel?
Nikola Ilic: 50 Shades of Direct Lake
Injae Park: The Cost of a Workaround - Key considerations for Power BI
…more on the schedule.
🎫If interested you can buy the tickets here
The conference is organized by a non-profit association, with the goal of making this event a reality. The idea is to support knowledge sharing about data with a focus on the Azure Data stack in Switzerland.
Do you want to give a feedback about Better at Data? 📝Do it here