SQL Saturday #796 – Minnesota, 2018

First, many thanks to the SQL Saturday and MNPass team for putting on another great event and letting me participate.

I co-presented with Joshuha Owen (@JoshSQL) in a precon on Modern Enterprise Data Warehousing on Azure. Thanks to those who attended and participated in the conversations around changing the way we implement data warehouse capabilities in Azure. Josh and I will be talking more about this in the future.

Now, those of you who attended my Saturday presentation on Consumption Based Architecture, I wanted to get you the slide deck and reference materials here. Thanks again for attending.

The slide deck from the session is here.

I also have blog post around this topic: Consumption Based Architecture for Modern Data Analytics. Feel free to join the conversation there around this.

ERPs and the Consumption Based Architecture Conversation

One of the key topics that came up during the session was related to handling ERPs with minimal change. The key issue surrounding ERP solutions is with the data structure in those systems. Whether you work with SAP, JackHenry, or Dynamics, you have a situation where the data model is very complex and definitely not user friendly. In Consumption Based Architecture, we try to minimize data transformation and reshaping, but ERP solutions are by nature cryptic and complex. By definition, they are not consumable. So in the consumable space, we typically recommend using the vendor supplied solutions such as JHKnow, SAP BW and so on. These solutions provide a vendor managed interpretation of the data in the ERP for reporting and other solutions.

Security in this Architecture

The question was raised during the session around how to secure this. This does not have a simple answer. Each solution may have implemented security differently. For instance, an Oracle database may use user names and not have AD integration. This means that you need to determine how to secure your consumable space. For instance, if you pick Azure Active Directory, you would move data to AAD compliant structures in Azure such as Azure SQL Database, Azure SQL Datawarehouse, and Azure Databricks. This means you might need to use a tool like Goldengate with CDC to update a SQL DB which you can apply security to. This will allow you to centralize security for your consumable data. You will need to plan for security in whatever you do and create what you need to support it.

Thanks again everyone for joining us at SQL Saturday.

 

 

SQL Saturday – Dallas – May 2018

I was able to present at SQL Saturday Dallas this year. Thanks to those of you who were able to attend. As I noted in the meeting you can find details related to Power BI Data Security in the following posts on my site.

Power BI Is Finally in the Azure Trust Center

Power BI Data Security – Sharing in Email

Power BI Data Security – Sharing

Power BI and Data Security – App Workspaces and Power BI Apps

Power BI and Data Security – Free User’s Cannot Share, Read Only in Premium

Power BI and Data Security – Row Level Security (RLS)

Power BI and Data Security – Data Classification and Privacy Levels

Power BI and Data Security – On-premises Data Gateway

Power BI and Data Security – Sharing Data

Power BI and Data Security – Compliance and Encryption

I have also added the presentation here if you want to review it as well.

Thanks again for joining me in Dallas.

T-SQL Tuesday #87 – Fixing Old Problems with Shiny New Toys: STRING_SPLIT

Thanks to Matt Gordon (@atsqlspeed) for hosting this T-SQL Tuesday.

Splitting Strings in SQL

A problem that has plagued SQL developers through the years is splitting strings. Many techniques have been used as more capabilities were added to SQL Server including XML datatypes, recursive CTEs and even CLR. I have used XML datatype methods to solve the problem most often. So, without further ado…

T-SQL Function: STRING_SPLIT

I have previously highlighted this function in a webinar with Pragmatic Works as a Hidden Gem in SQL Server 2016. It was not announced with great fanfare, but once discovered, solves a very common problem.

Syntax

STRING_SPLIT(string, delimiter)

The STRING_SPLIT function will return a single column result set. The column name is “value”. The datatype will be NVARCHAR for strings that are NCHAR or NVARCHAR. VARCHAR is used for strings that are CHAR or VARCHAR types.

Example

DECLARE @csvString AS VARCHAR(100)
SET @csvString = 'Monday, Tuesday, Wednesday, Thursday, Friday'
SELECT value AS WorkDayOfTheWeek 
FROM STRING_SPLIT (@csvString, ',');

The initial example returns the follow results:#tsql2sday

value
Monday
Tuesday
Wednesday
Thursday
Friday

As you can see in the example, the results returned a leading space which was in the original string. The following example trims leading and trailing spaces.

DECLARE @csvString AS VARCHAR(100)
SET @csvString = 'Monday, Tuesday, Wednesday, Thursday, Friday'
SELECT LTRIM(RTRIM(value)) AS WorkDayOfTheWeek 
FROM STRING_SPLIT (@csvString, ',');

The cleaned example returns the follow results:

value
Monday
Tuesday
Wednesday
Thursday
Friday

Thanks again Matt for this opportunity to share an underrated, but really useful shiny new tool in SQL Server 2016.

SQL Saturday #492 Follow Up – A Window into Your Data

Thanks for attending my session on window functions in TSQL. I hope you learned something you can take back and use in your projects or at your work. You will find an link to the session and code I used below. If you have any questions about the session post them in comments and I will try to get you the answers.

Questions and Comments

1. Does RATIO_TO_REPORT exist in SQL Server? It is in Oracle.
   • Currently this function is not available in SQL Server
   • Here is the equivalent functionality using existing functions in SQL Server:

     • OrderAmt / SUM(OrderAmt) OVER (PARTITION BY OrderDate)

     • This example can use the source code I have referenced below. It uses the current value as the numerator and the sum by partition as the denominator. While not a simple function, the equivalent is still fairly simple using window functions to help.
2. Demo issues with Azure SQL Database
   • During the session I ran into an issue with Azure SQL Database. It turns out that the following two functions are not supported there.
     • PERCENTILE_CONT
     • PERCENTILE_DISC

Slides, Code, and Follow Up Posts

The presentation can be found here: A Window into Your Data

The code was put into a Word document that you can get here: TSQL Window Function Code

This session is also backed by an existing blog series I have written.

T-SQL Window Functions – Part 1- The OVER() Clause

T-SQL Window Functions – Part 2- Ranking Functions

T-SQL Window Functions – Part 3: Aggregate Functions

T-SQL Window Functions – Part 4- Analytic Functions

Microsoft Resources:

• OVER Clause: http://msdn.microsoft.com/en-us/library/ms189461(v=SQL.110).aspx
• Analytic Functions: http://msdn.microsoft.com/en-us/library/hh213234(v=sql.110).aspx

SQL Saturday #486 Richmond – A Window Into Your Data

 

Thanks for attending my session on window functions in TSQL. I hope you learned something you can take back and use in your projects or at your work. You will find an link to the session and code I used below. If you have any questions about the session post them in comments and I will try to get you the answers.

Questions

1. Can an OVER clause be used in the WHERE clause?
   • No. The OVER clause can only be used in SELECT and ORDER BY clauses.
2. Some follow up on ROWS and RANGE with context to CURRENT ROW.
   • We had a lot of discussion around this. In our examples below, RANGE aggregated all the data that fit into the ORDER BY clause. ROWS only referenced the row it was in. So, RANGE looks at everything that meets the criteria established by the PARTITION BY and ORDER BY clauses. ROWS is bound to the physical row.
   • Code examples:

     • OVER (PARTITION BY CustomerName ORDER BY OrderDate RANGE CURRENT ROW)

       • Summed two rows of data for the customer with the date. Both rows had the same date.

     • OVER (PARTITION BY CustomerName ORDER BY OrderDate ROWS CURRENT ROW)

       • Each row only contained the data for the row it was in.

Slides, Code, and Follow Up Posts

The presentation can be found here: A Window into Your Data

The code was put into a Word document that you can get here: TSQL Window Function Code

This session is also backed by an existing blog series I have written.

T-SQL Window Functions – Part 1- The OVER() Clause

T-SQL Window Functions – Part 2- Ranking Functions

T-SQL Window Functions – Part 3: Aggregate Functions

T-SQL Window Functions – Part 4- Analytic Functions

Microsoft Resources:

• OVER Clause: http://msdn.microsoft.com/en-us/library/ms189461(v=SQL.110).aspx
• Analytic Functions: http://msdn.microsoft.com/en-us/library/hh213234(v=sql.110).aspx