To work with an Azure Data Platform efficiently there is many tools that one should be aware of. However, installing them and keeping them current is a tedious job. In this post, I will describe which tools are needed for developer of a standard data platform on Azure with a focus on Data Warehousing. Moreover, I will explain how to install and keep everything updated with Chocolatey.
On Linux packages managers like
dpkg have been around since the 90s, on Windows they are presently less common.
Chocolatey is a software that allows users to manage software on their Windows machines.
Supported software can be installed with a single command
choco install <name> and updates for all installed software can be applied with a single
choco upgrade all --confirm.
Microsoft has recently introduced their own package manager called winget. However, as of today the winget repository does not offer everything needed for Azure data projects yet -- validate if your requirements are met at winget.run.
sql-server-management-studio-- SQL Server Management Studio has been the standard tool to manage SQL Servers of Microsoft for a very long time. Today it is still powerful and can connect to SQL endpoints of Azure SQL Database or Azure Synapse. It can also connect to XMLA endpoints of Power BI premium workspaces.
visualstudio2019-workload-data-- SQL Server Data Tools are the tools to manage database projects. It allows to ensure that everything is consistent and easily refactor (e.g. rename columns) without breaking dependencies. It can generate
dacpacand thus is part of deployment processes.
daxstudio-- Whenever one has to look into performance of a Power BI data model there is no way around Dax Studio. It does an amazing job to show the details of query execution.
tabular-editor-- Another indispensible tool, when working with Analysis Services tabular projects and for some time past also just Power BI. Tabular Editor allows you to easily batch modify existing measures, adhere to best practice, and automate changes to the model.
azure-data-studio-- Azure Data Studio is the next generation of SQL Server Management Studio. It is platform independent, has a plugin system and is under very active development. One can, for example, store queries as notebooks and easily bookmark connections.
powerbi-- Power BI has become the tool for all data analysis in the Microsoft data world. One cannot just use it to visualize business data, I am often using it to visualize Azure consumption details as well.
git-- For years there has not been a project without version control anymore. Git has become the standard of version control everywhere. Microsoft is using this as the default backend in Azure DevOps and GitHub is broadly used. In case you are not using version control yet, start now.
giton the command line is not intuitive. TortoiseGit comes to the rescue here, it nicely embeds status icons into the Windows Explorer and shows for example which elements have changes or are not tracked.
microsoftazurestorageexplorer-- Working with storage accounts through the portal or with just the command line, comes with limitations. Azure Storage Explorer is the tool that allows to nicely drag&drop file system elements from your local system to the cloud and back. Moreover, it allows you to manage ACLs.
azure-cli-- Creating Azure resources through the Azure Portal is one option, however to do it a reproducible fashion Azure CLI is a very good start. One can easily setup a demo environment with it (see my previous post) or one can configure Azure services.
keepassxc-- Managing many strong passwords becomes a pain. I have been using different KeePass based password managers for years. KeepassXC is under active development and works on all platforms. There is even a client for the mobile phones, e.g. KeePassium.
greenshot-- taking screenshots is essential for any consultant. Greenshot allows to take a screenshot of just parts of the screens and one can easily label elements on that screenshot.
notepadplusplus-- Notepad++ is a very versatile editor that allows to sort lines and highlight code in many languages.
vscode-- Visual Studio Code is another very powerful development environment. Often I use this to write documentation in Markdown and use the integrated source control.
To install Chocolatey, have a look at their installation instructions. You should be able to get it installed with the following PowerShell snippet:
Set-ExecutionPolicy Bypass -Scope Process -Force; ` [System.Net.ServicePointManager]::SecurityProtocol = [System.Net.ServicePointManager]::SecurityProtocol -bor 3072; ` iex ((New-Object System.Net.WebClient).DownloadString('https://community.chocolatey.org/install.ps1'))
To install everything with chocolatey, just execute the following:
choco install --confirm --acceptlicense ` sql-server-management-studio visualstudio2019professional visualstudio2019-workload-data daxstudio ` tabular-editor azure-data-studio powerbi git tortoisegit microsoftazurestorageexplorer azure-cli ` keepassxc greenshot notepadplusplus vscode
Chocolatey is a tremendous help when getting a developer machine ready. I hope with the above explanations and suggestions you will be able to get started faster the next time.
I am looking forward to your feedback, what other tools do you usually use for your data projects?