In the realm of business intelligence, Power BI has emerged as a powerful tool for data visualisation and analysis. With its user-friendly interface and wide range of features, Power BI allows users to transform raw data into interactive dashboards and reports. One of the most critical steps in creating a Power BI model is defining and configuring the underlying data structure, which is where Tabular Editor and its script functionality come into play.
Tabular Editor is a powerful tool that provides advanced capabilities for managing and editing Power BI data models. By utilising the script functionality in Tabular Editor, users can automate the initial generation of a Power BI model from platform metadata. The script functionality in Tabular Editor allows users to write and execute custom scripts in C# or Visual Basic.
In this blog article, I demonstrate how to:
- Create a calculation group
- Use C# scripts
- Utilise the BPA tool
- Implement object-level security using Tabular Editor.
Enhancing Capabilities with C# Scripts
The use of C# scripts in Tabular Editor further enhances the capabilities for generating the Power BI model. C# scripts offer more flexibility and advanced functionality compared to Visual Basic scripts. Users can leverage the power of C# to perform complex calculations, manipulate data, and integrate with external systems and APIs. This opens a wide range of possibilities for customising and extending the auto generation process.
With the ability to write C# scripts in Tabular Editor, users are not limited to just creating the initial Power BI model from platform metadata. They can also incorporate additional logic and business rules, perform data transformations and cleansing, and even integrate with external data sources to enriDemo Task: Power BI Model Generation
Moreover, consistency and adherence to best practices are assured when scripts are used in Tabular Editor. To construct high-quality Power BI models, users can make use of the Microsoft best-practice toolbox for Power BI scripts, which offers guidelines and recommendations. This toolkit includes the Best Practice Analyzer, which helps identify and fix common issues in Power BI models.
Benefits of Using Tabular Editor:
1) Time Efficiency: Tabular Editor allows you to create and manage measures and other objects in bulk. This capability is especially beneficial for handling repetitive tasks, as it speeds up the process and reduces manual effort.
2) Consistency and Reusability: By defining measures in Tabular Editor, you ensure consistency across different reports and models. You can easily reuse and update these measures as needed, which helps maintain uniformity in your calculations.
3) Enhanced Performance: Tabular Editor provides a streamlined interface for managing DAX expressions and other model objects, which can lead to better performance and easier optimisation of your Power BI models.
Three advantages of assigning BPA rules within Tabular Editor are:
1) Automated Quality Checks: BPA rules help in automatically checking the model against best practices. This ensures that the model adheres to the recommended standards and practices, reducing the likelihood of errors.
2) Identifying Issues: The rules can quickly identify missing elements or issues that need to be updated. This makes it easier to spot and rectify potential problems early in the development process.
3) Improved Maintainability: With BPA rules in place, maintaining and updating the model becomes more straightforward. The rules provide clear guidelines on what needs to be fixed or improved, making the process of maintaining the model more efficient.
Object-level security (OLS)
Securing row-level data in Power BI is a straightforward process that has been in the product for quite a while. However, what about securing data at the object level? Using Tabular Editor, we can specify security at the object level. As with calculation groups, Tabular Editor must be installed and working. Security can be configured in either version of Tabular Editor.
Object-level security (OLS) enables model authors to secure specific tables or columns from report viewers. For example, a column that includes personal data can be restricted so that only certain viewers can see and interact with it. In addition, you can also restrict object names and metadata. This added layer of security prevents users without the appropriate access levels from discovering business critical or sensitive personal information like employee or financial records. For viewers that don’t have the required permission, it’s as if the secured tables or columns don’t exist.
Demo Task
In the demo below, I created several tasks in Power BI, including various measures. Additionally, I have used Tabular Editor to create measures, which significantly reduces the time spent on repetitive tasks and enhances the overall speed and efficiency of the development process.
- Created Time Intelligence Measure in Power BI and Tabular Editor:
Start by right clicking tables, then selecting create → Calculation Group.
After naming your new table, right click Calculation Items and select Create > Calculation Item.
In the screenshot below, you can see Power BI. Each calculation must be created individually. In Tabular Editor, one calculation group can link with all the measures.
Here’s where you will define your different variations and calculations. For this example, we’re going to create four versions of sales.
Start by creating four calculation items named Current, MTD, QTD and YTD. For each of these, define the correct DAX expression.
- Using Advanced Scripting in Tabular Editor:
Created total sum measure, time intelligence, and more for selected measures in a single instance.
- Using the BPA Tool in Tabular Editor:
Demonstrated how to apply and benefit from BPA rules.
- Object-level Security (OLS):
The first step is to create the roles that will be used to define security. In Power BI go to the Modeling ribbon, then click on Manage roles.
Next, launch Tabular Editor from External tools. Under the Model view > Roles, you will see the new US role created:
For each of the tables we wish to secure, we can click on the table and change it from Default to None or Read. Here we’ve chosen to set two tables, Dynamic Measure and Report Pages, to None.
Save and refresh the dataset in Power BI.
Conclusion
Data modeling consistency and productivity can be greatly increased by using Tabular Editor’s script functionality to autogenerate Power BI models from platform metadata. By automating repetitive tasks, ensuring adherence to best practices, and leveraging the advanced capabilities of C# scripting, users can create efficient, maintainable, and scalable Power BI models. This method minimises time and reduces errors. Using these scripting features will undoubtedly lead to more intelligent and resilient Power BI applications.
When used with Power BI, Tabular Editor simplifies the generation and administration of measures, resulting in time savings and consistency. Applying BPA principles also makes it easier to find problems, automate quality checks, and enhances the general maintainability of your models. This set of techniques and tools improves the dependability and efficiency of your Power BI development process.
A Power BI developer can have access to a more thorough and reliable modeling process by using an external tool such as Tabular Editor. You can use the licensed TE3 version or the free TE2 version, but adding Tabular Editor to your toolkit will expand your options tremendously!
