Category: Blog

T-SQL Tuesday #75–Power BI

This month’s host is Jorge Seggara, the @sqlchicken, who works for Microsoft. A busy schedule caused a slight delay, so we’re posting the third Tuesday of this month, but that is OK. This is a great topic for T-SQL Tuesday.

Power BI Data

While Power BI is a great visualization tool, you can’t do anything without data. That means you need to find data, which is both easy and hard. Easy if you’re working within your own organization on a specific project. Slightly more complex if you want to look at data out in the world.

However I saw this in a talk last year and I was amazed. This is the type of thing I’ve written before, and it’s cumbersome and problematic. I would think that SSIS would have made things this simple years ago.

I love sports, and wanted to play with some sports statistics awhile back. Finding good data is tough, at least in a format like CSV, that you can easily import. However Power BI makes this easy. Start up the desktop and you’ll see this:

Right away Power BI wants to get data. Click on this and the Get Data dialog opens, with lots of choices.

However if you pick “Other”, you’ll see one more that I love. Web.

Click this. You get asked for a URL. Any URL.

I happen to have one handy. After the win for Denver in Super Bowl 50, I thought I’d look back at Mr. Manning’s career.

I take that URL and drop it in the dialog.

Once I click OK, this will analyze the URL for tables of data. In this case, I get quite a few.

Now, I can click each one to see what data this is. This isn’t what I want

But this is.

I now click “Edit” at the bottom to clean my data. I could just load it, but there are a few issues.

I see all the data in the designer, and I have lots of options for working with this data.

First, since I’m going to do a comparison, let me rename the table.

Next, I see the steps below the name. I’ll add more steps, but I’ll do this in the designer GUI. First, let me remove the last row, which is a career summary.

In this case, I’m only removing one row.

Now, I want to remove a couple columns. In my case, I don’t care about a few of the data items, so I’ll pull them away. I can right click a column or choose “Remove Colums” in the ribbon. Either way, I get rid of QBR and Team.

Now I’ve got a nice year by year summary of Peyton Manning’s career. When I close and apply the query, my data is loaded into a data set for use by my Dashboard. I can then repeat this, and I’ll have two sets of data.

And, here’s my PowerBI Dashboard. It’s not terribly useful, or interactive, but it’s got data from the web that I didn’t have to copy or move.

https://app.powerbi.com/view?r=eyJrIjoiMWNmYzBiYjUtMTU3Yi00NWFhLWFiZjQtNTY0NzY4NDRkZTJmIiwidCI6IjY2NjBkOGZkLTJjNmItNDg0Mi1iZmZmLTcxOTY1YzE2NTczYSIsImMiOjN9

February 16, 2016
Quick Tests–Function Returns
I ran across a neat piece of code recently from Gail Shaw. She answered a question on returning the base path from a path in a string. Meaning if I had this string:

c:\Users\Sjones\Documents\text.txt

I’d want to return this:

c:\Users\Sjones\Documents

Her code looked like this, which is a nice, simple, elegant way of finding the path, no matter how many backslashes.

LEFT(@FullPath, LEN(@fullpath) – CHARINDEX(‘\’, REVERSE(@fullpath)))

Of course, you can easily add the last backslash with a slight change to the math.

However I wanted to add some tests. Does this really work? What if I don’t have a backslash? I thought the best way to do this was with a few tSQLt tests, which I quickly built. The entire process was 5-10 minutes, which isn’t a lot longer than if I had been running random tests myself with a variety of strings.

The advantage of tests is that if I come up with a new case, or another potential bug, I copy the test over, change the string and I have a new test, plus all the regressions. I’m not depending on my memory to run the test cases.

I first put the code in a function, which makes it easier to test.

CREATE FUNCTION GetParentPath
( @fullpath VARCHAR(4000)
)
RETURNS varchar(4000)
AS
BEGIN
RETURN LEFT(@FullPath, LEN(@fullpath) – CHARINDEX(‘\’, REVERSE(@fullpath)))
END

Here’s my base test:

EXEC tsqlt.NewTestClass ‘StringTests’;
go
CREATE PROCEDURE [StringTests].[test simple path with one backslash]
AS
BEGIN
— Assemble
DECLARE @input VARCHAR(4000) = ‘c:\myfile.txt’
, @expected VARCHAR(4000) = ‘c:’
, @actual VARCHAR(4000)

— Assert
EXEC @actual = dbo.GetParentPath
@fullpath = @input

— Assert
EXEC tsqlt.AssertEquals
@Expected = @expected
, @Actual = @actual
, @Message = N’Incorrect Path’
END
GO

I can easily copy this and add new inputs with different paths, and matchout outputs, to test new cases. For example, my first cut produced five tests for these inputs:
- c:\myfile.txt
- c:\
- c:
- c:\Documents\myfile.txt
- c:\Users\sjones\Documents\myfile.txt
There are certainly other tests, but this 5-10 minutes of work gives me repeatable testing, and if I needed to include this function in a larger project, I already have a series of tests that can be run in my CI process.

What’s more, if I replaced this with a CLR function, such as something with SQL#, I could still use these tests.
February 15, 2016
Limiting Database Permissions for DLM Dashboard
I was talking with some of our support people recently about permissions on DLM Dashboard. A client was having issues, and we weren’t sure what was wrong. As a result, I decided to dig in a bit and see how limited I could be with permissions for the login/user that is used to track changes.

My first step was to create a new login in SQL Server, giving the public server role and then granting very limited permissions in master and the Redgate database. Those permissions were:
- master – VIEW ANY DEFINITION
- master – execute on dbo.RG_SQLLighthouse_ReadEvents
- RedGate – SELECT ON SQLLighthouse.DDL_Events
That’s a nice, limited set of permissions. You do need sysadmin for setup, but after that, you can set these permissions for the user that you’ve configured in DLM Dashboard. The permissins are documented on the DLM Dashboard documentation site.

In my case, I have a login/user, DLMDashUser, configured in the tool.

I then went to add a new database on my local instance. However since this login isn’t mapped to a user, nor has any high server privileges, I got an error.

To fix this, I connected to my instance and modified the user. Scripting is a better way to do this, and in my case, I used this script:

USE Puzzles
GO
CREATE USER DLMDashUser FROM LOGIN DLMDashUser;
GO
GRANT SELECT ON sys.sql_expression_dependencies TO DLMDashUser
GO

This grants the necessary permissions to a new user in this database. You can save this script, which is especially handy for production systems where we don’t want monitoring tools to have elevated permissions.

Now when I go to add the database, I click add and it works.

And I can then see the database in my monitoring dashboard.

The principle of least privilege should apply everywhere, certainly in production, but also in development. If you limit permissions in development, you might cause a few headaches, but you’ll understand the issues and solve them early on. More importantly, if you have security flaws, they aren’t in production systems where data is exposed.

SQL Server security isn’t that hard, but it can be cumbersome. Set it up properly in development, keep your scripts (even from the GUI), and then use those scripts for your production systems.

NOTE: Typically I’d create a role for this system, which is perhaps what I should do. Having a role like this would make switching users in DLM Dashboard at some point much easier.

CREATE ROLE Monitoring
GO

GRANT SELECT ON sys.sql_expression_dependencies TO Monitoring

GO
ALTER ROLE Monitoring ADD MEMBER DLMDashUser

In fact, I just changed to use this role, and added the role to the other databases so that my dev system is propery set up.
February 11, 2016

CROSS APPLY v InLine Functions

While working on the Advent of Code problems in SQL, I ran across something interesting. Day 4 involves hashing, which is done with the HASHBYTES function in SQL Server. This is a computation and given the problem, there is no good way to do this without brute force. The problem says

hash a specific string + an integer.
If the leftmost digits are 0 (5 or 6 of them), stop
increment the integer
repeat

Since a hash doesn’t lend itself to a pattern, you can’t start with 100,000 and determine if the integer you need is higher or lower. Instead you need to work through the integers.

I decided to try this with a tally table and hashing with TOP 1. BTW, TOP 1 makes a huge difference.

However, my structure was to query my tally table like this:

SELECT n
, HASHBYTES(‘MD5’, ‘iwrupvqb’ + CONVERT(VARCHAR(15), n))
FROM cteTally

This was in a second CTE, and in the main query I then use a WHERE clause to filter the list down to the entry with leading zeros. When I ran this, I noticed it was rather slow at first, at least, what I considered slow. I checked with a few other people that had solved the problem, and I found their times were faster than mine.

I wasn’t sure the brute force technique would benefit from a TOP clause, but I added a TOP 1 to the outer query. This made the entire process run much quicker, which is interesting. Apparently the filtering is collapsed across the tally table join with the hash computation and as soon as a valid match is found, this ends the calculations. My average went down by a factor of 10.

However, I wondered if moving the calculation to a join, with CROSS APPLY, would be quicker. I couldn’t imagine why, but I decided to try this. I moved the calcuation by changing the HASHBYTES calculation to a SELECT statement in a derived table for the CROSS APPLY and then taking the result of that as part of my column list. This changed my CTE to this:

SELECT n
, hb.hashvalue
FROM cteTally
CROSS APPLY (SELECT HASHBYTES(‘MD5’, ‘iwrupvqb’ + CONVERT(VARCHAR(15), n))) AS hb(hashvalue)

That resulted in a slightly faster query time. When I added a TOP to this, the times improved slightly from using HASHBYTES in the column list with a TOP. Intuitively this doens’t make sense, as it would seem the same number of function calls need to be completed, but the CROSS APPLY handles them a bit more efficiently. I’m sure someone has a much more in-depth understanding of the query optimizer here, and I won’t try to explain things myself. The times are close enough that I suspect some minor optimization from CROSS APPLY.

As a comparison, I also ran a brute force loop, with this code, that calculates the values sequentially until the result is determine. This should be equivalent to the results from TOP 1, and we find that they aren’t. The tally table solution with CROSS APPLY is much quicker.

DECLARE @t BIT = 1;
DECLARE @i INT = 0;
DECLARE @start DATETIME = GETDATE();
WHILE @t = 1
BEGIN
   IF LEFT( CONVERT(VARCHAR(50), HASHBYTES(‘MD5’, ‘iwrupvqb’ + CAST(@i AS VARCHAR(10))), 2), 6) = ‘000000’
     BEGIN
       SELECT @i
       SELECT @t = 0
     end
   SELECT @i = @i + 1
   –IF @i > 10000000
   — SELECT @t = 0
END
SELECT starttime = @start
     , seconds = DATEDIFF(SECOND, @start, GETDATE())
;

Here’s a summary of the code timings (averaged across 5 executions), for the second part of the puzzle, which looks for 6 leading zeros and has a result in the 9million range.

Query	Timings (sec)
Hashbytes in column list, no TOP	185.6
CROSS APPLY, no TOP	182.3
Hasbytes in columns list, TOP	17.8
CROSS APPLY with TOP	16.0
Brute Force, WHILE loop	33.8

Conclusion

The conclusion I’d take here is that CROSS APPLY ought to be a tool you keep in the front of your toolbox and use when you must execute a function for each row of a set of tables. This is one of the T-SQL techniques that I never learned early in my career (it wasn’t available), and I haven’t used much outside of looking for execution plans, but it’s a join capability I will certainly look to use in the future.

However, if you are using UDFs instead of system functions, I’d certainly recommend you read Adam Machanic’s post on Scalar Functions and CROSS APPLY, and perhaps you can change to ITVFs and get some great performance gains.

February 10, 2016

Category: Blog

T-SQL Tuesday #75–Power BI

Power BI Data

Quick Tests–Function Returns

Limiting Database Permissions for DLM Dashboard

CROSS APPLY v InLine Functions

Conclusion