Category Archives: VSCode

Release of Power BI Studio v2.0 (VSCode extension)

Posted on by — 6 Comments ↓

Due to the great feedback I have received for the first version of my VSCode extension to mange Power BI objects from within VSCode I decided to continue working on it and am finally happy to share that I am releasing a new version – v2.0!

If you already had the previous version installed in VSCode, you do not have to do anything as it will update automatically. If you are a new user, you can install it from the gallery or search for “Power BI Studio” in the VSCode extensions tab.

Besides adding some new features I also changed the name to “Power BI Studio” to make it more accessible and ease communication. While it is technically still a VSCode extension and still requires VSCode (desktop client or vscode.dev) to run, I think “Power BI Studio” is a much better term and also refers to other established Power BI tools like DAX Studio and SQL Server Management Studio which a lot of users are already familiar with.

But there are also a lot of new features – here are the most important ones:

  • Integration with External Tools in Power BI Desktop
  • Show Memory Statistics for Power BI Datasets
  • new config powerbi.workspaceFilter to filter workspaces
  • support for Fabric APIs in Power BI notebooks
  • a lot of bug fixes and minor improvements on existing features

Integration with External Tools in Power BI Desktop

You can now start any of your External Tools you have configured for Power BI Desktop directly from VSCode to automatically connect to an online dataset. (Seriously, how often did you have to search for the proper connection string when you wanted to used DAX Studio or Tabular Editor?!)

Besides the direct integration with Power BI Desktop External Tools you can also simply copy the connectionstring from the context menu of a dataset!

Show Memory Statistics for Power BI Datasets

When analyzing the performance of a Power BI Datasets it is very crucial to understand the memory footprint of your model. To support admins and developers alike to investigate in memory issues, I integrated the DAX queries from my fellow Microsoft Data Platform MVP Hariharan Rajendran that exposes the memory consumption into a pre-defined notebook which can easily be opened from the context menu of the dataset:

The scripts/DAX queries will be updated constantly whenever new functionality becomes available. If you have any ideas/queries you want to have included, please reach out to me!

Other features

The new config setting powerbi.workspaceFilter allows you to use a Regular Expression (RegEx) to filter the workspaces you want to be shown in the UI. This can be very useful if you are working in a large enterprise with many workspaces and you only want to work with a small subset of them. As it is a RegEx, it allows very fine-grained and also very modular filters and you can use | (=RegEx OR) to chain different conditions. To filter for all workspaces that contain Finance or are production workspaces (suffix [PROD]) you could use “Finance|\[PROD\]”

Using Power BI notebooks you can now also query Fabric REST API endpoints.as they are using the very same authentication in the background. To do so, you need to specify the full URL of the Fabric API you want to call or use the command SET API_PATH = https://api.fabric.microsoft.com upfront. As of now the Fabric API does not come with autocompletion so you need to know which endpoints you want to call. Please refer to the official Fabric REST API documentation for more details.

GET https://api.fabric.microsoft.com/v1/workspaces

There will be some more integration with Fabric in the very near future so please stay tuned!

For all other changes/bugfixes/improvements please refer to the official documentation and changelog.

Power BI Studio will still be developed as Open-Source Software (OSS) and contributors are very welcome. Also if you have any other feedback, feature requests or simply found a bug, please file a ticket in the repository.

Release of Power BI-VSCode Extension

Posted on by — 12 Comments ↓

Download from VSCode Gallery

I am working a lot with Power BI in my daily business and there have always been a couple of things that bothered me since the very beginning. Most of this is related to the web UI and its usability, mainly that you need too many clicks to get to where you want (e.g. viewing Datasets refreshes) but also that some features are simply not exposed in the UI that are possible with the Power BI REST APIs (e.g. rebinding a report to another dataset). So I thought there must be some better way to do this and make management and usability of Power BI easier and I came up with the idea for a Visual Studio Code extension for Power BI to close this gap.

As you may know, I have already written another VSCode extension for Databricks (Databricks Power Tools) which is basically also “just” a wrapper around the various Databricks APIs but makes various features of Databricks much more accessible, especially for people that spend most of their time in a local IDE anyway and are already used to it. At this point I also want to thank my company paiqo for supporting this engagements and making all this possible!

For about a year now I have been developing the Power BI VSCode extension and it finally reached a state where I want to release it. It has been in the VSCode market place for quite some time now but was never officially released by a blog post like this. To stay up-to-date I highly recommend to follow the repository which will always be updated to include the latest features and documentation.

So what is this Power BI VSCode extension all about and how can it help me in my daily work? There are currently three core components included which all serve different purposes:

  • Workspace browser
  • Notebooks to run arbitrary API calls and DAX
  • TMDL editor (!)

The workspace browser allows you to access all artifacts that you have access to and run the most common API calls directly from the VSCode UI. Besides features that are also available in the web UI like taking over an artifact, triggering a refresh/viewing the history or changing parameters, this includes additional features like Rebind, Clone, Configuring Query Scale Out, Update Report Content, etc. For some features you can also use Drag&Drop instead of the context menu. For example, if you drag a report and drop it on a dataset, a popup will ask you whether you want to rebind the report to that dataset or clone the report and link the clone to the dataset!

Besides the workspace browser there is also a dedicated one for Deployment Pipelines which allows you to configure Power BI deployment pipelines and also run selective deployments directly from VSCode!

There are also UIs for Capacities and Gateways, but those are mainly for informational purposes and are read-only.

The second component of the extension are Power BI Notebooks which allow you to run any arbitrary API call . This is especially useful as not every API call can be built into the UI properly (e.g. due to too many parameters, etc.). Power BI Notebooks also support notebook magics like %dax or %cmd to run DAX queries or to set variables within the notebook. There is also intellisense/autocomplete which should help you a lot to discover and write your final API call. This also includes samples for more complex API calls like calling the Enhanced Refresh API.

To run a DAX statement via the Execute Queries API, you can simply use %dax in the first line of the notebook call and then start writing your DAX query:

The last – but definitely not least(!) – part is the just recently added TMDL (Tabular Model Definition Language) integration, which allows you to modify Power BI datasets using TMDL. If your dataset resides in a premium capacity and the XMLA endpoint is enabled for read/write mode, you can select “Edit TMDL” from the context menu of your dataset. This will add a new folder to your VSCode workspace that represents the TMDL structure of that dataset. You can now navigate the individual .tmdl files, change them and validate them. once you are happy with the changes, you can also publish your changes back to the online dataset. The .tmdl files only reside in memory for the time of your VSCode session and will be reloaded every time. If necessary, you can also force a manual reload at any time to the the most recent version from the Power BI service.

Besides this “online”-mode, you can also save the TMDL definition locally – e.g. if you want to check it into a Git repository. The same features as described above are also available for locally stored TMDL definitions. this also includes TMDL definitions generated by other tools like Tabular Editor or pbi-tools!

To ease debugging, there is also a [Go to Error] button if your TMDL is not valid which jumps directly to the faulty TMDL file and highlights the line with the error:

To make this all work, you need to have ASP.NET Core Runtime 7.0 or higher installed as described in the docs.

So whats next?

While I do have some new features already in the backlog, I am also eagerly looking forward to gather some feedback from the community to drive future developments. So if you have a feature that you want to have added to the extension, simply open a feature request in the repository.

Due to the open architecture of VSCode, the extension also integrates with/leverages all other extensions in the Power BI space. Though, there is not much available at the moment but I hope that his ecosystem grows and sooner or later there will be a language extension for DAX or TMDL that provides intellisense/autocomplete here too, or simply syntax highlighting for the very beginning.

As this is an open-source project, you can also contribute directly by creating pull requests. If you like the extension and make sure I don’t run out of coffee while continuously improving it you can also sponsor a cup of coffee for me to contribute to this extension.

Databricks Power Tools for VSCode

Posted on by — 19 Comments ↓

As you probably know, we at paiqo have developed our Databricks extension for VSCode over the last years and are constantly adding new features and improving user experience. The most notable features are probably the execution of local notebooks against a Databricks cluster, a nice UI to manage clusters, jobs, secrets, repos, etc. and last but not least also a browser for your workspace and DBFS to sync files locally.

In February 2023 Databricks also published its own official VSCode extension which was definitely long awaited by a lot of customers (blog, extension). It allows you to run a local file on a Databricks cluster and display the results in VSCode again. Alternatively you can also run the code as a workflow. I am sure we can expect much more features in the near future and Databricks investing in local IDE support is already a great step forward!

As you can imagine, I am working very closely with the people at Databricks and we are happy to also announce the next major release of our Databricks VSCode extension 2.0 which now also integrates with the official Databricks extension! To avoid confusion between the two extensions we also renamed ours to Databricks Power Tools so from now on you will see two Databricks icons on the very left bar in VSCode.

By introducing a new connection manager you can now leverage the configuration settings you already have in the Databricks extension and use them in the Databricks Power Tools seamlessly. All you need to do is to change the VSCode configuration to use the new Databricks Extension connection manager as shown below.

This is also the new default so if you have both extensions installed, the Databricks Power Tools will automatically pick up the configuration settings from the Databricks extension to establish a connection to your Databricks workspace.

If we detect that the Databricks extension is installed already, we also automatically create a new Notebook Kernel for you that allows you to run notebooks against the cluster that you configured in the Databricks extension. To change the cluster where the code runs, you can use the Cluster Manager from the Databricks Power Tools.

Besides that new integration, there are also a lot of other new features that made it into this major release:

  • File system integrations: you can now mount your Databricks workspace or DBFS directly into your VSCode workspace. This also allows you to easily drag & drop items between your local filesystem, Databricks workspace (notebooks) and DBFS!
  • A new Azure Connection Manager to automatically load the Databricks Workspaces that you have access to
  • Support for Widgets in Notebooks similar to Databricks using <strong>dbutils.widgets</strong> library
  • Added support for <strong>_sql_df variable in notebooks when mixing Python and SQL cells
  • Added support for <strong>%run</strong> and <strong>dbutils.notebook.run()</strong>
  • Preparations to make the whole extension also run via vscode.dev but there also need to be some changes made on the Databricks side for this to finally work
  • A lot of bug fixes and minor other features

All these improvements together allow you to run most of the code that you currently have in Databricks also from VSCode without any changes! Also the sync between your local files and the Databricks workspace is super easy. You can almost start a new Databricks project without ever opening the Databricks web UI!

Here is a little demo to demonstrate what the Databricks Power Tools can do for you!

We finally got your attention? Great!
Make sure to download the new Databricks Power Tools and try them on your own!
Any feedback is very welcome and if you are as passionate about it as we are you might also want to contribute to the project!