Without exception, every data analyst has heard about Self-service BI, especially when discussing Power BI, a tool that serves everyone from those managing businesses with Excel spreadsheets, to multinationals with Data Lakes and Data Warehouses connected to multiple sources and data types.
However, hearing about the topic clearly tells us the 'what', now we need to talk about the 'how'.
A good solution is followed not only by a complete report, with concise and useful analyses, but also by a scalable data structure, simple and easy to interpret, both for the end user consuming the data and for other teams or resources maintaining it.
And that's where self-service-oriented modeling comes in. It brings benefits in making it easier for the user to understand how to use and interpret model data, as well as helping with the handover to technical teams.
“Ahh, but that’s still not the how… 🙄”
One self-service-oriented modeling is generally suited to the realities of the project and the business. This involves available resources, including technical, human, technological, financial, etc.
However, there are some key points that we can consider when we think broadly. They are:
1 – Centralize your transformations separately from your data load.
Whenever possible, separate your transformation process, grouping it by the preferred transformation type, so that the steps are isolated and occur in a logical order.
“I don’t have a tool, just Power BI.”
Use dataflows to build your 'Data Warehouse' and always seek to reuse common tables in multiple projects, ensuring a single source of truth (SSoT).
“But I don’t have access to the Dataflows/I don’t know how to use them.”
No problem. You can create folders in your Power Query and separate them into Transformations and Loads. Ensure that all tables with common sources are referenced by the same object, also maintaining the concept of the single source of truth.
2 – Keep friendly names for tables, columns and measures.
Unlike traditional databases, in Power BI we can use user-friendly names, allowing spaces, capital letters, and even accents. This makes it easier for people who do not have technical experience to use the tool in a more effectively.
For example:
- Instead of using FT_Sales2024_BR, use Sales of the Year 2024.
- Instead of your date columns having the prefix or suffix “dt_”, call them Date, literally. Date of sale, Date of order, Date of shipment, etc.
- Instead of calling your measure sales_Jan_page_1_br, use Sales in January.
And speaking of measures…
3 – Always try to create self-service measures.
Does your measure make sense to create a new tab and plot a new chart? If not, you can improve it!
If mandatory filters are required on the screen, create conditionals so that they respect the one that comes by default (Don't forget to document this, either in the measurement description or in the project's supporting document.).
“But not all of my measurements will be visible to the end user…”
“But my DAX is already complex, I may lose performance if I add more implicit filters…”
In this case, create a new measurement table, call it – for example – “Self Service Measures” and create your measures that will be passed on to the end user team.
This will help in organizing the measures that should be used for the report to work and what should be used in the day-to-day self-service.
🏆 PRO TIP: Just as we mentioned in step 1, reference the self-service measures based on the original ones, if any. Single source of truth and such, you get it, right?
Phew, we've covered some of the main points, but let's talk about, in practice—does it work?
Of course, all of this includes a greater effort in modeling planning, architecture, and the resources involved, but it certainly WORKS!
Just keep in mind, you are not delivering a report; you are delivering a solution. A data cube. A tool that will provide autonomy, consistency and generate qualified information for its users.
“Ahh, but isn’t there a way to make a table so we can export it to Excel?”
“Ahh, but my report is different from the dash…”
“Ahh, but I needed to cross-reference this data with a local table…”
Say goodbye to these sentences. Say hello to the Democratization of Data!
Have you ever experienced situations like this? Comment below and tell me more about…
Leave a Reply