Press "Enter" to skip to content

Curated SQL Posts

Power BI Query Reduction

Published 2017-11-09 by Kevin Feasel

Wolfgang Strasser points out a new feature in Power BI Desktop:

In some cases, sources for Direct Query models are either large (and therefore need some time to bring back the data) or slow (which could maybe be improved by some database tuning :-)).

Before the Nov2017 release, every change in a filter or slicer led to an immediate round trip to the data source which made some of those reports … let’s call it .. not perfect in the context of user experience.

Beginning with the Nov2017 release some options for Query Reduction has been introduced.

Click through for an example of how to use Query Reduction on direct query models.

Comments closed

TDE As Security Theater

Published 2017-11-09 by Kevin Feasel

Randolph West argues that Transparent Data Encryption is not security theater:

The short version is that our data, log and backup files are encrypted at rest (i.e., on the storage layer), so that an attacker cannot simply copy and attach the data and log files, or restore a backup, without having access to the master key. If backup tapes or drives are stolen, the data on those devices cannot be recovered.

We can also use what is known as a Hardware Security Module (HSM) to provide keys to secure the database. This is a dedicated physical or virtual device, separate from SQL Server, that generates keys for various services in an organization.

Read on for Randolph’s argument.  For the counter-argument, check out Simon McAuliffe’s article on the topic.  Over the past couple of years, I’ve grown much more sympathetic to the idea that TDE’s primary use case is in weeding out the rabble-rousers with nary a clue, but it’s not very helpful against a knowledgeable attacker with administrative access to your SQL Server.

Comments closed

Dynamic Row-Level Security And Power BI

Published 2017-11-09 by Kevin Feasel

Reza Rad shows how to build role-based filters in Power BI and use those to implement row-level security:

 

I have written while ago, about how to implement a dynamic row level security in Power BI. This post is an addition to that post. I’ve had a lot of inquiries that; “What If I want users to see their own data, and the Manager to see everything?”, or “How to add Manager or Director Level access to the dynamic row level security?” This post will answer this question. In this post, you will learn a scenario that you can implement a dynamic row level security. In this scenario, everyone will see their own data, but the manager will see everything. If you want to learn more about Power BI, read Power BI book from Rookie to Rock Star.

Read on to see one method of solving this dilemma.

Comments closed

Automatically Scripting Objects Using dbatools

Published 2017-11-09 by Kevin Feasel

Constantine Kokkinos shows how easy it is to use dbatools to script out database objects:

@brianszadek says: I am looking to automate the scripting of objects with PowerShell for easier source control integration.

I thought this would be a little bit easier, but it looks like the SMO way of doing this is still fairly complicated. Maybe something I can look at simplifying for the project in a bit

If you want to get your database into source control but feel like it’s going to be a time-consuming challenge, this isn’t the time-consuming part.

Comments closed

SQL On Docker And Running SQL Queries

Published 2017-11-09 by Kevin Feasel

Rob Sewell loads SQL on Windows & Linux via Docker and shows how to run a query against it using Powershell:

In only a few seconds you have a SQL 2017 instance up and running (Take a look at Andrews blog at dbafromthecold.com for a great container series with much greater detail)

Now that we have our container we need to connect to it. We need to gather the IPAddress. We can do this using docker command docker inspect but I like to make things a little more programmatical. This works for my Windows 10 machine for Windows SQL Containers. There are some errors with other machines it appears but there is an alternative below

Read the whole thing.

Comments closed

Rendering Ten Million Points With ggplot2

Published 2017-11-08 by Kevin Feasel

Antonio Sánchez Chinchón shows how to draw Clifford attractors in R:

From a technical point of view, the challenge is creating a data frame with all locations, since it must have 10 milion rows and must be populated sequentially. A very fast way to do it is using Rcpp package. To render the plot I use ggplot, which works quite well. Here you have the code to play with Clifford Attractors if you want:

Click through for the code, as well as sample output images.

Comments closed

Dynamic Pivoting In Redshift

Published 2017-11-08 by Kevin Feasel

Maria Zakourdaev is not Redshift’s biggest fan:

Several days ago I have spent a few hours of my life figuring out how to do dynamic pivot in Amazon Redshift. To tell you the truth, I have expected much more from this DBMS SQL language.

Redshift is based on Postgre SQL 8.0.2 ( which was released in 2005 !!!! )

Anything you would want for this, not too difficult task,  does not exits.  No stored procedures. No JSON datatype. No variables outside of UDF, no queries inside UDFs. “UDF can be used to calculate values but cannot be used to call SQL functions”. Python UDFs also cannot query the data, only perform calculations.

Finally I have found one useful function LISTAGG that helped me to get distinct values of all pivoted columns.

Read on to see how Maria solved this problem.  And to tell the truth, I’m not Redshift’s biggest fan either.

Comments closed

Testing Event Hub To Stream Analytics Performance

Published 2017-11-08 by Kevin Feasel

Rolf Tesmer tries a few different settings for optimizing performance when streaming data from Azure Event Hub to Azure Stream Analytics:

When you configure Azure Stream Analytics you only have 2 levers;

  • Streaming Units (SU) – Each SU is a blend of compute, memory and throughput between 1 and 48 (or more by contacting support).  The factors that impact SU are query complexity, latency, and volume of data. SU can be used to scale out a job to achieve higher throughput. Depending on query complexity and throughput required, more SU units may be necessary to achieve your performance requirements.  A level of SU6 assigns an entire Stream Analytics node.   For our test we wont change SU

  • SQL Query Design – Queries are expressed in a SQL-like query language. These queries are documented in the query language reference guide and includes several common query patterns.  The design of the query can greatly affect the job throughput, in particular if and/or how the PARTITION BY clause is used.

Rolf tests along three margins:  2 versus 16 input partitions, 2 versus 16 output partitions, and whether to partition the data or not.  Read on to see which combination was fastest.

Comments closed

Query Store And Availability Groups FAQ

Published 2017-11-08 by Kevin Feasel

Erin Stellato has a few follow-up questions from her Query Store sessions:

Q: Can you enable Query Store for a read-only replica?

A: No.  Because the replica is read-only, and Query Store inherently writes data TO the database, you cannot enable it to capture queries that are executed against that read-only copy.  I did create a Connect item for this request.  If this is of interest to you, please up-vote it: Enable Query Store for collection on a read-only replica in an Availability Group.  The more votes this has, the better the possibility that Microsoft will implement it, so feel free to share with your friends and have them vote too!

Read on for more questions and answers, and if you’re interested in it, vote on the Connect item above.

Comments closed