"Bravo is particularly valuable for newer or less technical Power BI users who want to create more sophisticated semantic models."
Although Power BI is very powerful as a stand-alone solution, users can enhance their experience even further by leveraging external tools that integrate with Power BI. Bravo, the external tool that receives the spotlight in this article, is for the Power BI user that seeks a well designed model but find the other tools too advanced. It is a free open-source tool to analyse and improve semantic models through a simple, intuitive user interface (see Figure 1).
Figure 1: Screenshot Bravo
Bravo is one of many powerful tools have been developed by the Power BI community. Six external tools that any Power BI developer needs to know, will be introduced in this series of articles. These tools are:
Bravo
Power BI Helper
Dax Studio
Measure Killer
ALM Toolkit
and Tabular Editor
While advanced users may benefit from more complex and technical tools, like DAX studio or Tabular Editor, Bravo is ideal for users who prefer simplicity over complexity. Bravo is perfect for basic tasks and guides users towards semantic models that follow basic standards. The intuitive user interface makes Bravo particularly accessible and an ideal tool for beginners and non-technical users. This article introduces Bravo, how you should use it and shows an example use case.
Bravo, an external tool for business users
Bravo is a Power BI sidekick for those business users that do not have a technical background but still want a clean and to the point semantic model. Bravo is an open-source tool developed by SQLBI, a company known for other tools like Tabular Editor and DAX Formatter.
Bravo has four functions for which it can be used:
Analyse the model: It allows the users to quickly analyse which columns consume the most memory and gives insights into which columns can be removed to optimise memory usage.
Format measures: Formatting DAX (Data Analysis Expressions**)** according to good practices increases the readability of the semantic model and improves collaboration with others. For this, Bravo has a formatter service that can be used to preview and format measures with the DAX formatter of SQLBI.
Create a date table: Bravo has calendar templates built in with different options, languages and holidays as well as custom date templates. Besides creating the calendar table, Bravo can also add DAX measures that implement the common time intelligence calculations.
Export data: For reviewing the semantic model and checking its correctness, a business user might prefer to do some calculations in Excel. These users can use Bravo to easily export the model to Excel spreadsheets or CSV files.
Installation and connection
Bravo can be downloaded via the website https://bravo.bi/. It can be installed in different versions. The recommended version is the self-contained version but due to company policies you might have to go for another version. In this article we’ll continue with the recommended self-contained version.
After installing Bravo, it can be opened via the tab External Tools in Power BI which connects Bravo to the local Power BI Desktop file. Alternatively, you can open Bravo from your machine and connect to a local Power BI file or to a model on the Power BI service. Note that the XMLA (eXtensible Markup Language for Analysis) endpoint needs to be enabled and a premium license is required to connect to a model on the service.
Example use case
Imagine working for a company that is in the American air traffic business and getting assigned a Power BI semantic model and report. The report analyses flights and delays of American air traffic in 2015. The model is designed by an analyst that left the company and you need to take over the maintenance and further development.
One of these developments is to include all available years, a change that can have an impact on the performance of the semantic model. The star schema of the model is fairly simple with the flight list serving as a fact table and a few dimensions (Airline, Departure airports and Arrival airports) and a measure table (Figure 2).
Figure 2: Schema of Power BI semantic model
So far, the analyst built one page with a high level view (see Figure 3). You plan to team up with a business analyst and a few subject matter experts to understand better the performance and the requirements. To prepare yourself, you deep dive into the semantic model with the help of Bravo.
Figure 3: Power BI report
Analyse the model
The tab ‘Analyze Model’ shows technical information of the columns in the model, see Figure 4 for a print screen of the tab for the example use case. In the heading, general information is given about the model: the total size, the number of columns and the number of columns that are not referenced.
Figure 4: Tab ‘Analyze model’
In the body details per column are given: the data type of the column, the name and the table to which the column belongs, the cardinality (number of unique values), size (in MB) and weight or relative size. This tab can be used to find columns that are expensive (in memory usage) and not needed. It gives an indication which columns are referenced in measures, relationships, etc.
Other items on this tab that are worth mentioning are:
The highlights in the table with the technical details; columns that are not referenced in the model are highlighted. These columns are not used in other areas of the semantic model.
It is possible to group the columns by table and show the cardinality, size and weight of each table.
The page allows the user to search for columns.
The user can save the information into an external VPAX file a file that can be further analysed by Bravo, DAX Studio, Tabular Editor, and VertiPaq Analyzer.
Note that Bravo only checks that columns are referenced in the model. It cannot verify whether a column is used in a report. Columns should only be removed after verifying that they are not used by any report. This can be done manually or with another external tool like Power BI helper.
Analysing the semantic model of the example use case shows that the column FLIGHT_NUMBER is a heavy column taking about 8% of the weight. After some discussion with the business and the subject matter expert, it is concluded that the FLIGHT_NUMBER is a technical number that is only relevant in the operational system and can be removed without any impact.
Removing and changing the semantic model is not possible in Bravo, the changes have to be made in Power BI.
Another heavy column, TAIL_NUMBER, contains valuable information and might be used in a measure later to filter out special flights.
Formatting the measures
The second tab in Bravo is the DAX formatter. Bravo sends the measures to the DAX formatter service managed by SQLBI over a secured connection. In the tab, Bravo shows the number of measures, how many with errors, how many to format and how many to analyse. Analysing the model is the first step to take. Afterwards Bravo shows the measures with errors and the measures to format as in the screenshot in Figure 5. The user can then review the changes and push them to the dataset.
Figure 5: format DAX pane
The tab has, on the left, the list of measures. Measures that needs formatting are highlighted. On the right, the measure is shown in its current form and the proposal from the DAX formatter service. After reviewing, the user can select the measures to format and can format all of the measures in one click.
Note that, when an update is made in Power BI, Bravo is out of sync and the measures have to be analysed again to align with the semantic model.
Adding the date table
A date dimension is one of the most common dimensions created in dimensional modelling. While some companies have a centralised and standardised date dimension available in their warehouse, it is still common that the date dimension is created in each semantic model. In Bravo, the tab Manage Dates can be used to create a date dimension from templates making it easy to create the table you need for your report, see Figure 6.
Figure 6: Manage Dates pane
When a date table is already available, the tables will be updated and the relationships will remain intact. Each template has parameters that allow the user to adapt the date dimension to their need. The standard template gives a date dimension with the most common fields while the weekly template is the most detailed. For reporting purposes that need information on the fiscal year, a version is also available.
On the interval tab, Figure 7, the user can select the first and last year of the date dimension or Bravo sets the information automatically based on a scan of the model. The Dates tab allows to select the region format, the name of the date dimension and the name of the date definition table.
Figure 7: Manage Dates pane, the interval settings
A list of holidays based on a region can be added. This is a standard list that unfortunately cannot be customised. The information is created with two tables, the Holidays Table and the Holidays Definition Table, for which the user can define the names. The definition table includes hard-coded holidays for each country, allowing the developer to add additional holidays retrospectively if required.
The last tab is the Time Intelligence tab where the user can select the measures to which time intelligence functions have to be applied. Bravo will add all the time intelligence measures when the date table is created or updated. Applying the time intelligence to all the measures might be a bit much.
When the user has configured the date table, the changed can be previewed with the Preview Changes button. This will show all the tables that are modified. After reviewing the changes can be applied and the semantic model is updated.
Power BI standard has the auto date/time setting activated. To create a date dimension with Bravo, this should be deactivated (Settings > Current File > Data Load). It is recommended to deactivate the auto date/time by default. The auto date/time will create a date table for each date field in the semantic model (even when there is no need) and spans all the years present in the columns. This will consume a lot of memory compared to a single date table or multiple date tables crafted carefully by the developer. The default setting can be found in Settings > Global > Data Load.
Validating the semantic model with exports
The last feature to show is the export feature. The export pane in Bravo allows to export the data in the semantic model. It is a useful addition for validating the information via another tool like Excel. Another use for the export is to have a snapshot of the semantic model at a given time. Figure 8 shows a screenshot of the Export Data tab.
Figure 8: Export Data pane, export to Excel
Unlike the export option on the visuals, the export pane in Bravo lets the user export the tables itself and not a calculated table. The export format is either an Excel file or CSV files. Exporting to an Excel file is limited to one million rows but allows to combine the tables into one workbook. In addition to the requested tables, a summary of the export is added with information on the number of rows and the status of the export (see Figure 9).
Figure 9: Summary of export to an Excel file
When Exporting to CSV, see Figure 10 for a screenshot, a single file is made for each table. The user can tweak the format like the delimiter, using quotes, creating the files in a subfolder, etc.
Figure 10: Export Data pane, export to CSV
As many business users are very familiar with Excel, this feature is definitely a good addition to the toolbox.
Final thoughts
Using Bravo, we identified and removed unnecessary columns that were consuming excessive memory, effectively streamlining the semantic model. The formatter tab helped improve the model's readability and standardisation. We enhanced performance and usability by implementing a template-based date dimension and disabling the auto date/time feature.
Bravo is particularly valuable for newer or less technical Power BI users who want to create more sophisticated semantic models or analyse them more deeply than Power BI allows. While experienced users might prefer other tools, and features like 'Manage Dates' may be handled through a centralised data warehouse, Bravo's intuitive interface and straightforward functionalities make it a worthy addition to the recommended external tools list.
Commenti