BI Starter Pack (1/4) : The Alternative to Visual Studio

In today’s post I will show you a Business Intelligence tool that will be of great help when creating Analysis Models.

Last September, I had the opportunity to attend the data event held every year in Madrid, the SQL Saturday. In one of the sessions, specifically that of becoming Masters of Power BI with DAX Studio and more, I discovered the Tabular Editor.

Tabular Editor is an editor alternative to SSDT that is capable of creating tabular models for Analysis Services.

Therefore, Tabular Editor, is an open source program that allows you to edit a BIM file without accessing any data. This offline capability makes any change quickly applied to the BIM file, especially when creating measurements, calculated columns, viewing folders, perspectives and translations. In addition, it supports 1200 or higher compatibility level (JSON format) and gives you the option to download the executable and source code from GitHub.

Then, I explain step by step how to implement a tabular model from scratch with Tabular Editor so that you can appreciate the advantages that it entails with respect to Visual Studio.

1.Data Source Creation

First, we will create a Data Source. To do this, we right click on Data Sources – New Data Source (Legacy).

Secondly, we select Connection String and a form will open for us to enter the connection data to the database

2.Import Tables and Create Relations

To import the tables we need from the DW simply click on Tables. When you click, a window will appear for us to select the Data Source that we have previously created.

In the next window, we will see the selector of tables and views on the left. On the right, we find the column selector with the preview of the data.

We click on import and it will load all the tables instantly, since it does not matter data within the model.

To create relations, we select the column we want to relate and the target column. If the relation were not valid, the error would appear detailed when deploying the model. Also, there is the Reverse direction option that multidimensional projects had.

For the Updating of the Table Structure, Tabular Editor has the Refresh Metada option, which indicates all the updates that have been made in a table and if we want to implement them in the Model.

Within the Tables options we can find Show Dependencies that can be very useful.

3.Creation of Measures, Calculated Columns and Hierarchies

To create a Measure, we click, as we can see in the following image or, we can find a faster alternative using the keyboard shortcut Alt + 1.

Once created, the box will appear so that we enter the code in DAX and below all the options. We can also validate if the formula is correct and format the code.

It would be nice if they implemented a self-complete code option, but for that, there is already the DAX Studio application that is the one with which we should make the most complex measures.

Apart from the auto completed option, there are also other options that can help us see how to improve the performance of calculated measurements and columns.

4.Control Panel

As we can see, Tabular Editor doesn’t implement a colorful or friendly display but it is useful and agile.

4.1. Look For

Unlike Visual Studio, where the search button only works within the syntax of a calculated measure or column, when we write a word inside the search button text box of Tabular Editor, it will return any object that contains this word. As we see in the image, the relationships, tables, columns, measures or hierarchies that contain the word Prod appear.

4.2. Column Information and Object Sorting

With these options, we can visualize all the information of our model in an infinitely faster way than in Visual Studio. The following image shows us all the objects arranged alphabetically and all the detailed description of the objects.

4.3. Rename

Surely some customer has made you rename a group of measures.

With Tabular Editor you will haven’t to go one by one as with Visual Studio.

Aquí os dejo un ejemplo:

4.4. Version Backup

With Tabular Editor we have the option to make a backup of the model every time we deploy it. To configure this option, we simply go to File-> Preferences and mark the two checks in the Deployment Options section. Next, we select the path where we want the backups to be saved.