Blog

Creating a Report and distributed Alerts for PowerBI Scheduled Refreshes in your organization (starring Powershell, PowerBi & Microsoft Flow)

By Emanuele Meazzo

In PowerBI, powershell

6 Min read

C

The Issue If you ever tried to manage a PowerBI Tenant, you may have come across this problem: A part the PowerBI success comes from the ability for BI people to self-serve the data it needs, you just provide them with the datasources that they need and they can go around creating all the reports that they want; this is also an issue when trying to manage the tenant, because by default the owner...

Read onRead laterAdd comment

How to implement a Linear Regression Prediction in a PowerBI report using Python

By Emanuele Meazzo

In General, PowerBI

7 Min read

H

Machine learning is the buzzword of the moment, so I wanted to talk about that sweet ML here too. For a project that I’m working on, I’ve been experimenting on basic ML prediction algorithms, in this instance I’ll show you a basic POC on how ML can be integrated right into PowerBI in a serviceable manner. It’s ugly but it works The data I just needed some whatever data to...

Read onRead later1 comment

Get faster performance and lower network usage in SQL Server Loops by avoiding the “DONE Token” overload

By Emanuele Meazzo

In Performance, Tips&Tricks

4 Min read

G

FYI: You can get the Notebook for this article on my github and experiment yourself (opens with Azure Data Studio). Preamble Everybody knows that using loops in SQL Server is not efficient, if you’re able to write that same logic in a set-based statement it’s guaranteed to be faster.Still, devs can’t be helped, you just can’t seem to nail down the set-equivalent statement...

Read onRead later1 comment

SQL Notebooks: On the right track to enhance Data Analysis on SQL Server

By Emanuele Meazzo

In General, powershell

4 Min read

S

Since about a couple of versions ago, Azure Data Studio introduced Jupyter Notebooks support for SQL Server (and with SQL Server 2019 “Big Data Clusters” on the horizon, it was about time) What are Notebooks? “Notebooks” are a tool that our data science/machine learning colleagues know, love and are at their disposal since some time (Python, Spark, etc…); A notebook...

Read onRead later1 comment

How to identify the unit of measure of Extended Events durations

By Emanuele Meazzo

In Tips&Tricks, Troubleshooting

1 Min read

H

Even if I use Extended Events almost every day, I always forget the unit of measure of each duration counter, since they’re basically arbitrary; Seconds, milliseconds, microseconds? Whatever, it depends on the dev that implemented that specific counter. That’s why I’ve added to Tsql.tech Github repository the following code that extracts the descriptions from XE DMVs in order to...

Read onRead later1 comment

Quickly move data between instances for testing using only PowerShell

By Emanuele Meazzo

In powershell, Tips&Tricks, Uncategorized

2 Min read

Q

Quick tip, how many times did a dev ask you to move the PROD/QA data to DEV to test a feature? Of course, not the whole DB, which would be too easy via a scripted backup/restore, but only a single table, or subset of table data? How do you deal with it usually? With the SSMS Import data Wizard? (ugh..) Exporting to flat file and reimporting? Linked Servers? External tools? Restore the whole...

Read onRead later2 comments

Cast any string directly to XML with auto-escaping characters in SQL Server

By Emanuele Meazzo

In General, Tips&Tricks

2 Min read

C

Recently I had to look up the definition for a bunch of SQL objects and didn’t want to manually retrieve them manually in SSMS (with Create Scripts) or Visual Studio (by searching the object name in my TFS repository). Since lazyness and automation are the basis of a well done engineering work, I wanted to create a list, where I could basically click on the object that I needed and see the...

Read onRead later4 comments

How to Show Better Execution Plans in Azure Data Studio

By Emanuele Meazzo

In Tips&Tricks

1 Min read

H

Azure Data Studio is starting to mature and some neat features are coming to the vscode-based (and now multidatabase and multiplatform) tool. We got query plans in the app since some time, but to be frank they’re not very pretty and have some issues with dark themes: Phantom Lines in plans with Dark Theme The new way Phil Scott built the Queryplan.show extension for Azure Data Studio, which...

Read onRead laterAdd comment

Load data directly in SQL Server from a xlsx file, without OLEDB or Excel, using Powershell

By Emanuele Meazzo

In powershell, Tips&Tricks

3 Min read

L

I don’t know you, but people from other BU are old fashioned guys that do everything by hand, and then send you an Excel file to “put this data in the database” or “find info related to this codes”. This is an hassle, especially if you don’t have the Microsoft.ACE.OLEDB provider installed in your machine (or for some reason SSMS doesn’t recognize it or...

Read onRead laterAdd comment

A SQL Server Permission Report in PowerBI

By Emanuele Meazzo

In PowerBI, Troubleshooting

6 Min read

A

What it is and how to get it When working with SQL Instances that have lots of databases, sensitive data and equally lots of users/applications that accesses those, it can get pretty troublesome to keep track of every user, for every database, down to the single column/action.That’s why I developed this report, in orde to be able to quickly determine the security status of each object or...

Read onRead laterAdd comment