Press "Enter" to skip to content

Month: August 2020

Checking that Power BI Security Roles are Correct

Published 2020-08-28 by Kevin Feasel

Fred Kaffenberger poses a question:

If you can ask, how do we know that we are improving, you should also be able to ask how do we know that the security roles are implemented correctly. Data culture is not just for the business, but for the reporting team as well. I haven’t seen much discussion of auditing security roles in Power BI circles, so I’m genuinely curious about how others tackle this issue. Does everyone simply work hard and hope for the best? Or do you restrict everything at the database level and use different apps for different groups instead? There may even be regulatory reasons which require you to restrict it at the database level. But even if you do restrict everything at the database level, you still need to validate that security as well.

Read on for a verification technique.

Comments closed

Passing Power Query Parameters to Stored Procedures

Published 2020-08-28 by Kevin Feasel

Soheil Bakhshi shows how we can take an input from Power Query and pass it to a stored porcedure:

This is the fourth one in the form of Quick Tips. Here is the scenario. One of my customers had a requirement to get data from a Stored Procedure from SQL Server. She required to pass the values from a Query Parameter back to SQL Server and get the results in Power BI.

The solution is somewhat easy.

If you’re familiar with SQL Server Reporting Services, the solution instantly makes sense.

Comments closed

Preventing Bruce Force Attacks in SQL Server

Published 2020-08-28 by Kevin Feasel

Raul Gonzalez walks us through some security tips and shows how to lock accounts after a certain number of failures:

SQL Server provides two different forms of authenticating the users that connect to the database server: Windows Authentication, which is the default and preferred method, and SQL Server Authentication, which needs to be explicitly enabled.

There are reasons you might need to enable SQL Server authentication and, although advertised as less secure than Windows Authentication, there are still a few things we can do to minimise the risks.

Read on for those tips.

Comments closed

A Quick Demo: Kafka to Spark Streaming to Cassandra

Published 2020-08-27 by Kevin Feasel

Kundan Kumarr walks us through a simple data pipeline:

Spark Structured Streaming is a component of Apache Spark framework that enables scalable, high throughput, fault tolerant processing of data streams.
Apache Kafka is a scalable, high performance, low latency platform that allows reading and writing streams of data like a messaging system.
Apache Cassandra is a distributed and wide-column NoSQL data store.

As I’m reading through this, I enjoyed just how straightforward the whole process was.

Comments closed

Using Calculation Groups for Dynamic Measure Formatting

Published 2020-08-27 by Kevin Feasel

Matt Allington wants to solve a problem:

Using a switch measure to toggle results is a mature and common technique used in Power BI and Power Pivot for Excel. For example, a switch measure can be used to toggle what appears on a chart so that the end user can easily switch the data being visualised (see image below).

This technique uses a disconnected table, a slicer to receive the user selection, and a switch measure to change the result of the measure based on user input. When you select an option on the slicer you can have your chart update to show the result you want to see in the chart. I blogged about this in 2014 here. 

The switch measure has a drawback, though: you can only have one number format, so Matt has a new solution using calculation groups.

Comments closed

Parsing SQL Agent Job Step Output

Published 2020-08-27 by Kevin Feasel

Thomas Rushton has a function for us:

(This follows on from my previous script about emailing SQL Job ouput.)

There are times when a SQL server scheduled task needs to look at the output from the previous step, and make decisions based on that – particularly when parsing output from noisy/chatty scripts.

I have, therefore, put together a quick and dirty script to check the output from a SQL Server job step, and pull that into a string, so that it can be examined and a script can then make decisions about what to do next.

Read on for the function definition as well as a few notes on its usage.

Comments closed

Dynamic Date Column Headers in Power BI

Published 2020-08-27 by Kevin Feasel

Gilbert Quevauvilliers shows how we can display the last several days as column headers:

I find I do love a good challenge and I find that I get them frequently. I also enjoy sharing what I have learnt with others.

In this blog post I am going to show you how I found a way to create Dynamic Date Column Headers for Tables or Matrixes in Power BI.

Below is what it looks like once completed. What I did was to create a matrix with the last 5 Days (Showing each date for the last 5 days) and then along with this an MTD calculation which shows not only the header of MTD but it includes the current months values.

This is an interesting one for sure. Read on to see how Gilbert solved the problem.

Comments closed

Recursion in T-SQL

Published 2020-08-27 by Kevin Feasel

Bert Wagner takes us through writing recursive statements in T-SQL:

Recursive queries are fun to plan and write. They can be frustrating too depending on the complexity of the problem you are trying to solve.

This post shows one solution for finding all records that are related, either directly or via intermediate records, using recursive queries in SQL Server.

When you know the data size will be fairly small and performance isn’t critical, recursion can be an elegant solution to a data access problem.

Comments closed