Press "Enter" to skip to content

Month: April 2021

Data-Driven Subscriptions in Power BI

Patrick LeBlanc shows us how to build data-driven subscriptions using Power BI and Power Automate:

Automate data driven subscriptions with the Power Platform using Power BI and Power Automate! Patrick shows you how to quickly setup a report bursting option for your reports.

Click through for the video. There are a few more steps compared to what you’d do in Power BI Reporting Services, but it’s still pretty straightforward.


Arithmetic Operations on DATETIME Data Types

Eitan Bluman shows off some math skills:

Mathematical addition and subtraction can be performed between two datetime data types:

SET @d2 = '1900-03-30 18:00'SELECT@d1 + @d2 -- result: 1900-04-01 10:15:15.900, @d1 - @d2 -- result: 1899-10-05 22:15:15.900, @d2 - @d1 -- result: 1900-03-29 01:44:44.100

This means that we can have basic datetime arithmetics in SQL server. We can use subtraction to find an accurate difference between two dates, and use addition to add an accurate interval to a datetime column or variable.

This is one of those things you can do, but I’m not very fond of. First of all, as Eitan points out, you can’t do these in the (in all ways superior) DATETIME2 data type. Secondly, it adds some confusion to the code, as you don’t always get what you expect.

Comments closed

Sequences and Filters in XPath

Barney Lawrence continues a series on XML processing in SQL Server:

This post looks at a problem that can come up in XML messages that have perhaps not been thought out as well as we’d like and where we can’t uniquely identify one instance of a repeating element. The example we’ve used so far avoids this problem so we’ll switch to a sample that illustrates this problem.

Read on for the crux of the problem, as well as solutions.

Comments closed

Using Logic Apps to Send Multiple Attachments

Rayis Imayev has a project:

In my real project, I need to build a Logic App to send email messages with a set of files attached from my Azure Storage Account. I was able to find similar examples from other power platform developers, however, they lacked a critical part that I needed: my set of files had to be dynamic: 2 files, or 102 files –  the Logic App should be able to support this.

So, here, I would like to share my brief journey in creating such Azure Logic App:

Read on to see how Rayis solved this.

Comments closed

Managing Azure DevOps via Azure Logic Apps

Stuart Ainsworth has a process:

A big part of my job these days is looking for opportunities to improve workflow. Automation of software is great, but identifying areas to speed up human processes can be incredibly beneficial to value delivery to customers. Here’s the situation I recently figured out how to do:

1. My SRE team uses a different Azure DevOps project than our development team. This protects the “separation of duties” concept that auditors love, while still letting us transfer items back and forth.
2. The two projects are in the same organization.
3. The two projects use different templates, with different required fields.
4. Our workflow process requires two phases of triage for bugs in the wild: a technical phase (provided by my team), and a business prioritization (provided by our Business Analyst).
5. Moving a card between projects is simple, but there were several manual changes that had to be made:
– Assigning to a Business Analyst (BA)
– Changing the status to Proposed from Active
– Changing the Iteration and Area
– Moving the card.

To automate this, I decided to use Azure Logic Apps

Read on to see how Stuart did this.

Comments closed

Cost Management Updates in Azure

Michael Flanakin gives us a few updates on Azure billing:

Understanding your cost patterns over time and investigating specific charges often requires drilling into and selecting specific dates. You’ve always been able to select from one day up to one year in cost analysis, but you’ve told us that selecting those dates isn’t as easy as it could be. As we started building out a new platform for analytics and insights, we took this feedback to heart and completely redesigned the date selection. What you see today is an early peek at that.

This month, you’ll find a new option to select a custom date range in the cost analysis preview. You can pick a single month, a range of months, or start and end dates for a range of days, making it easier than ever to fine-tune your reporting to the dates you need. 

The virtue and downfall of cloud systems like AWS and Azure is that they’re very clear about how much things cost, but only if you know exactly the resources something uses. It’s not as simple as “I want to use a database,” but there are all of those other charges around data egress, networking, log management, etc. which can add up. Many of those costs are negligible (fortunately), but try walking through a pricing scenario for Azure Synapse Analytics sometime with someone new to the product and figure out at what point that person gives up trying to calculate the cost. My money says right around the time you get to the integration runtime costs.

Comments closed

The Equals Sign in Powershell

Kenneth Fisher avoids overloading:

In SQL Server both the set and equality functions are handled by the equals sign (=). 


However, in some other languages that’s not how it works. In PowerShell for example the equals sign is always a set operation. 

Click through to learn the difference, as well as what Powershell uses for equality operations. I think my favorite language for this is Scala, where I jokingly say = means “is equal to,” == means “is really equal to,” and === means “is equal to and I totally mean it.”

Comments closed

Quick Insertion into SQL Server from a Spreadsheet

Kevin Wilkie gives a quick way to load data from Excel (or any other spreadsheet):

One of the items I do before creating the table in the database is to review all of the data that is in the spreadsheet to make sure that:

1. I understand the data that is going into the database table.
2. Nothing that is just obviously wrong is trying to be pushed into the database. For example, the data I was talking about earlier that was one column over from what it should have been. If you see data that is all 0’s and 1’s up until a certain row, then you have descriptions or names – you probably have some bad data.

The other important part of pushing the data into the database from a spreadsheet is working with the CONCATENATE function of Excel. Let’s go into that now.

Click through for the process, as well as additional explanation.

Comments closed