Press "Enter" to skip to content

Category: Tools

Version Control for Power BI Datasets

Published 2023-04-27 by Kevin Feasel

Richard Swinbank improves on a prior version control system:

In the previous post, I outlined a possible workflow for Power BI development, and implemented an Azure DevOps pipeline to show how steps in such a workflow could be automated. To build the pipeline I stored an entire .pbix report file – data and all – in version control, which is a problem for at least two reasons:

  • storing large report files in a version control system won’t scale well
  • datasets may contain confidential or sensitive data which must be kept out of version control.

In this post I’ll look at separating a report’s dataset from its visuals, version controlling the standalone dataset (without data), and deploying the dataset automatically to Power BI.

Read on for the process.

Comments closed

ScriptDOM Now Open Source

Published 2023-04-26 by Kevin Feasel

Drew Skwiers-Koballa has great news for us:

ScriptDOM is a powerful .NET library for code parsing, generating an abstract syntax tree (AST) that can be leveraged to apply code formatting, detect antipatterns, and more. We are thrilled to announce that the source code for ScriptDOM has been released into open source under the MIT license and is available on GitHub.  In addition, ScriptDOM is now distributed by Microsoft as a standalone NuGet package.

This is big and good news. We’ve been able to use ScriptDOM for quite a while, but now that we can extend and improve the library, that’s great.

Comments closed

Thinking about Execution Plan Icons

Published 2023-04-19 by Kevin Feasel

Hugo Kornelis polls the community:

Fast forward to today. More than five years have passed since I published the first pages of the SQL Server Execution Plan Reference. The world has changed, in many ways. But the icons on the reference pages have not. One might wonder whether the choices I made over five years ago are still relevant today. Or rather, I know that they are not all relevant anymore, I know that there is very good reason to rethink those choices. But this time, I prefer not to do this on my own. This time I am asking your feedback.

Click through for the entirety of Hugo’s request. My recommendation would be to keep the SSMS/ADS icon set but not old SSMS or SQL Sentry Plan Explorer. I like and still use Plan Explorer (and it’s the only third-party SQL Server execution plan reader I regularly use) but don’t need to see that icon set in Hugo’s documentation to understand what he’s covering.

Comments closed

Migration Tool for Cosmos DB

Published 2023-04-12 by Kevin Feasel

Hasan Savran reviews a tool:

Migrating databases to a new database server is a big job. You need to have the right tools to make this process easy for everyone. Cosmos DB had a Data Migration Tool to move data from a bunch of sources. It was a great, free tool with an easy user interface. You didn’t have to be a developer to use this tool. Here is a screenshot of what it used to look like.

     Unfortunately, the tool got old and did not get updated with upcoming SDK changes. It worked only in Windows environments too.

Click through for Hasan’s thoughts. I had a behind-the-scenes look at everything getting put together, in that I was supposed to help, got busy, and slinked away as really sharp people like Carey Payette and John Bowen drove the project to completion.

Comments closed

April Tools Day

Published 2023-04-03 by Kevin Feasel

Erin Stellato dispels some myths:

Myth #1 Azure Data Studio is the only standalone solution now that SSMS is deprecated.

SQL Server Management Studio (SSMS) is not deprecated.  We thought about writing that in ALL CAPS, but figured bold is sufficient.  SSMS has not been deprecated, and we are not planning on deprecating it.  You will see new functionality being added to Azure Data Studio, but we have a fair number of things lined up for SSMS, including migration to the Visual Studio 2022 shell, which brings 64-bit support.

Bold plus all caps might have been a bit too much, yeah.

Click through to see what’s happening in the world of SQL Server tooling from Microsoft.

Comments closed

Databricks Power Tools in VS Code

Published 2023-03-21 by Kevin Feasel

Gerhard Brueckl has some tools for us:

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.

Click through to read more in the announcement and some of the things which have changed as a result of version 2.0.

Comments closed

The Importance of Monitoring Tools

Published 2023-03-08 by Kevin Feasel

Louis Davidson talks turkey about tooling:

When I was a DBA involved with the management of a large number of database servers, I didn’t have many third-party tools to help me do my job. For the most part, I relied on scripts that I found or wrote. I enjoyed writing scripts to manage the servers, as it taught me a lot about the internals of SQL Server. Many of these scripts were eventually automated using SQL Server’s agent to run and save data on the different servers so we could review the results, looking for issues.

Some of these tools written over 20 years ago still run to this day. We captured tons of data about everything we wanted to know about the server in case there were issues. Loads and loads of data. We had some processes that would scan that data and send emails when obvious errors occurred, but it was hard to keep synchronized over many different servers.

Click through for Louis’s thoughts. I believe good tools can make a DBA’s life a lot easier, though mediocre tools might make it worse: you become the proverbial drunk looking for his keys under a streetlamp because that’s where the light is.

Comments closed

Handling Optional T-SQL Code via SQLCMD

Published 2023-03-07 by Kevin Feasel

Louis Davidson shows off a bit of SQL Server Management Studio’s SQLCMD mode:

In the creating a database script, there are quite a few parameters you will want as part of your script. The database name, the settings, whether or not to drop the database or not. Built into SQL Server Management Studio is a cool tool called SQLCMD mode. Scripts using this mode get a few scripting tools that are really useful. These tools allow you to do things like insert other script files, set environment variables, and one I particularly like, make sure you don’t accidentally execute a script if no code is highlighted using EXIT to start your script ().

Where it is really lacking is in the area of control of flow language. In this blog I am going to share a few techniques I have used to get around this when building scripts that need to optionally execute different bits of code.

Click through to see how you can use it. Going one step further, you can directly script against sqlcmd.exe, which can be useful for automating deployments.

Comments closed

Capabilities (and Limitations) of Power BI Migration Tools

Published 2023-02-20 by Kevin Feasel

Chris Webb talks tools:

I have so many customers wanting to migrate from legacy BI tools to Power BI. They are concerned that their current BI tool has an uncertain future. Licence renewals are looming and in the current economic climate organisations are looking to save money. Power BI is not only a lot cheaper than other BI tools, it’s a better tool overall and since Microsoft continues to make big investments in it then migration is clearly a no-brainer.

As a Power BI consultancy owner I have a problem though: I don’t have enough skilled people working for me to keep up with all this demand. What’s the answer? I know! Let’s build a tool that can help migrate all these legacy reports to Power BI!

The result is that, so far this year, I’ve seen or heard of five or six different Power BI migration tools built by various consultancies. That’s great and here at Microsoft we’re naturally supportive of our partners and want as many people to use Power BI as possible. I have reservations about some of these tools though, and these reservations fall into two categories.

Read on for Chris’s take on the topic.

Comments closed

Diagnosing VSCode + R Integration Problems

Published 2023-02-07 by Kevin Feasel

John MacKintosh troubleshoots an issue:

I recently updated my R installation, and then realised that I’d broken my VSCode/ R set up in the process – I could not launch an R terminal either directly or via radian.

I have a repo where I’ve collated various blog posts relating to setting up VSCode for R, but that didn’t solve all my problems.

I did get it resolved eventually, and here’s how.

Read on for that resolution. H/T R-Bloggers.

Comments closed