Press "Enter" to skip to content

Author: Kevin Feasel

Creating an Income Statement in Power BI

Published 2020-02-10 by Kevin Feasel

Joseph Yeates continues a series on financial statements in Power BI:

I had created a measure to populate the matrix visual. It uses =SWITCH() to return the YTD amount for the revenue and expense row headers and returns a subtotal for the net income header.

To finish building the income statement, I needed to add two more line items: retained income from the beginning and end of the year. I had already created the categories in the Power Query Editor, so I had to update the DAX statement to return logic for these lines.

Read on to see how to add these.

Comments closed

Auditing DDL with Event Notifications and Service Broker

Published 2020-02-10 by Kevin Feasel

Max Vernon wants to audit Data Definition Language events:

SQL Server doesn’t audit DDL events out of the box, aside from several major events which are captured in the Default System Trace. By “audit”, I mean there is no log of the data-definition-language statements issued against the server. So, if someone creates a table in a database, you can see the table, and when it was created, but details about who created it, and what code they used to create it isn’t saved anywhere. The code in this blog post shows how to configure SQL Server Event Notifications in tandem with SQL Server Service Broker to capture all SQL Server DDL events asynchronously to a database specially configured for the purpose.

Click through for the code and explanation of what’s happening.

Comments closed

Interleaved Execution with SQL Server

Published 2020-02-10 by Kevin Feasel

Milos Radivojevic takes us through improvements with interleaved execution in SQL Server:

As you might know, the Interleaved Execution is the member of the Intelligent Query Processing family of features. It has been introduced with SQL Server 2017 (as a part of the Adaptive Query Processing). It is designed to improve the performance of queries referencing multi-statement table-valued functions (MSTVF). Actually, it addresses currently only queries using MSTVF, but is hopefully designed for much more. The query optimizer usually has two issues with queries using MSTVF:

– MSTVF is a black-box for the optimizer; it does not know what’s inside, it cannot perform cross-statement optimization (as it is a case with inline TVFs) and it assumes it is a cheap and fast operation
– MSTVF has a fixed cardinality of 100 (prior to SQL Server 2014, it was 1)

Interleaved execution does not improve the first issue (MSTVF is still a black-box for the optimizer), but solves the cardinality issue.

Read on to understand how this second aspect has changed for the better.

Comments closed

Powershell Runspaces

Published 2020-02-10 by Kevin Feasel

Mark Wilkinson has a new blog and an itch to explain the notion of runspaces in Powershell:

So I have a problem with some of the posts I’ve read about runspaces. It all comes down to a small detail that I think makes a big difference in your understanding of them.

$Runspace = ::Create()

This code looks innocent. What does it do? You’d probably think it’s creating a new runspace, but it’s not. This code is instead creating a fresh instance of PowerShell. If you run this code and run Get-Runspace you’ll see there is still just one listed, the one attached to your current session. So what is this instance we just created?

Click through for an example and a step-by-step breakout of that example.

Comments closed

Rebuilding Rowstore Indexes Online on Tables with Columnstore Indexes

Published 2020-02-07 by Kevin Feasel

Niko Neugebauer explains something about rebuilding rowstore indexes:

This blogpost will be about older SQL Server versions (2016, 2017) and some implications that I have found that people rather do not understand, until they hit the problems in productions – the ONLINE creation & rebuild operations for the Rowstore indexes when having Columnstore Indexes on their tables.
As you should know by now – SQL Server 2017 & SQL Server 2019 respectively brought the ONLINE features support for the Columnstore Indexes (Nonclustered(2017) & Clustered(2019)) and I have blogged about those news in Columnstore Indexes – part 96 (“Nonclustered Columnstore Index Online Rebuild”) in 2017 and in Columnstore Indexes – part 123 (“Clustered Columnstore Index Online Rebuild”) in 2018.

What I honestly did not expect is the amount of the same question I have faced as in the last 3 months regarding the rebuild operations for the the ONLINE creation & rebuild operations for the Rowstore indexes when having Columnstore Indexes.

Read on to see the demonstration for when you have a clustered columnstore index and a nonclustered columnstore index.

Comments closed

Row-Level Security in Power BI Reports

Published 2020-02-07 by Kevin Feasel

Drew Skwiers-Koballa shows how to use an embed token to implement row-level security with Power BI:

To present a PowerBI report user or consumer with a securely pre-filtered dataset, row level security must be used. In a PowerBI embedded architecture where “app owns data”, implementing row level security (RLS) requires a modification to the token generation request. By specifying a role and user in the token request, we can generate an embed token specific to the user’s data access.

Click through for the instructions.

Comments closed

Implementing Soft Deletes in SQL Server

Published 2020-02-07 by Kevin Feasel

Brent Ozar shows how to use soft deletes in SQL Server:

Normally when you run a DELETE statement in a database, the data’s gone.

With the soft delete design pattern, you add a bit column like IsDeleted, IsActive, or IsArchived to the table, and instead of deleting rows, you flip the bit column. This can buy you a few advantages:

– Easier/faster undeletes
– History tracking (keeping deleted rows around for auditing purposes, although the method I’m going to show here doesn’t do anything fancy like track who did the delete)
– Easier reconciliation during disaster recovery failovers (refer to the Senior DBA class module on recovering from failovers for more info)
– Lower workload for Availability Groups secondaries (neat scenario from Aaron Bertrand)

Read on to see how and what the costs of this are.

Comments closed

Delta Lake and ACID Properties

Published 2020-02-06 by Kevin Feasel

Kundan Kumarr notes that Spark’s Delta Lake allows for ACID transactions:

DeltaLog is the crux of Delta Lake which ensures atomicity, consistency, isolation, and durability of user-initiated transactions. DeltaLog is an ordered record of transactions. Every transaction performed since the inception of Delta Lake Table, has an entry in the DeltaLog (also known as the Delta Lake transaction log). It acts as a single source of truth, giving users access to the last version of a DeltaTable’s state. It provides serializability, the strongest level of isolation level. Let’s see how DeltaLog ensures ACID Transactions.

Click through for the explanation.

Comments closed