Quick Lunchtime Tutorial (5-10 minutes) CTEs make PowerBI E-Z

Introduction:
This tutorial assumes you have PowerBI and SQL Server Management Studio (SSMS)

Using CTEs to create a view can be a lot easier, quicker and more organized than creating conditional logic in PowerBI. By creating the logic within SSMS you are able to re-use, reference and modify the data PowerBI is using with ease.

This tutorial will take you through the steps you need to create a series of CTEs that make up a view used in a simple PowerBI chart.

After you run all of the code to create the view, take a closer look at the t-sql used build the CTEs. There you will see how this methodology could help save you time and keep your PowerBI visuals organized.

Tutorial Start: For this tutorial you need to create some data. First create a new database in SQL server management studio. If you do not have permissions or just don’t want to create a new database, make sure you have the database selected where you want to add the sample table and view created below.

Start by creating a DB.

CREATE DATABASE ABC_PowerBI

Then select the database you created before running the next code.

Now run the code below to create some random date data.

 

CREATE TABLE dbo.TestTableSize

(

 MyKeyField VARCHAR(10) NOT NULL,

 Gender VARCHAR(2) NOT NULL,

 Challenge1CompleteDate DATETIME NOT NULL,

 Challenge2CompleteDate DATETIME NOT NULL,

 Challenge3CompleteDate DATETIME NOT NULL,

 Challenge4CompleteDate DATETIME NOT NULL,

 Challenge5CompleteDate DATETIME NOT NULL

)

/**************************************/

/*POPULATE TABLE***********************/

/**************************************/

DECLARE @RowCount INT

DECLARE @RowString VARCHAR(10)

DECLARE @Random INT

DECLARE @Upper INT

DECLARE @Lower INT

DECLARE @InsertDate DATETIME

SET @Lower = -730

SET @Upper = -1

SET @RowCount = 0

WHILE @RowCount < 3000

BEGIN

 SET @RowString = CAST(@RowCount AS VARCHAR(10))

 SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)

 SET @InsertDate = DATEADD(dd, @Random, GETDATE())

 

 INSERT INTO TestTableSize

(MyKeyField

,Gender

,Challenge1CompleteDate

,Challenge2CompleteDate

,Challenge3CompleteDate

,Challenge4CompleteDate

,Challenge5CompleteDate)

 VALUES

(REPLICATE('0', 10 - DATALENGTH(@RowString)) + @RowString

, CASE WHEN @Random % 2 = 0 then 'F' else 'M' end

, @InsertDate

,DATEADD(dd, 1, @InsertDate)

,DATEADD(dd, 2, @InsertDate)

,DATEADD(dd, 3, @InsertDate)

,DATEADD(dd, 4, @InsertDate))

 SET @RowCount = @RowCount + 1

END;

From here we will use CTE’s to query the data and the CREATE VIEW command that can be used by PowerBI or other self-service BI tools.

CREATE VIEW dbo.PowerBIexample AS

/*******Steps Taken in View**********************************/

/*1. Add Ranking by Year and Gender */

/*2. Adding Medals based off of rank from CTE above*/

/*3. Data Bands to make calculations easier in PowerBI*/

/*4. When you relize the order is not correct in PowerBI */

/*5. Bring all the CTEs Together */

/*************************************************************/

WITH
/*Add Ranking by Year and Gender */

AddRankingByYear AS (

SELECT *, DENSE_RANK() OVER (PARTITION BY YEAR(Challenge5CompleteDate), Gender ORDER BY Challenge5CompleteDate ASC) AS MRank

FROM dbo.TestTableSize

)

/*Adding Medals based off of rank from CTE above*/

, AddMedals AS (

SELECT *,

CASE WHEN MRank = 1 THEN 'Gold'

 WHEN MRank = 2 THEN 'Silver'

WHEN MRank = 3 THEN 'Bronze'

ELSE 'No-Medal'

END AS Medal

FROM AddRankingByYear

)

/*Data Bands to make calculations easier in PowerBI*/

,DateDiffrenceList AS (

SELECT GETDATE() AS TodaysDate,

 /* Days Forward*/

 DATEADD(yy, 2, GETDATE()) AS TwoYearsForward,

 DATEADD(mm, 6, GETDATE()) AS SixMonthsForward,

 DATEADD(mm, 1, GETDATE()) AS OneMonthsForward,

 DATEADD(dd, 14, GETDATE()) AS TwoWeeksForward,

 DATEADD(dd, 7, GETDATE()) AS OneWeekForward,

 /* DAYS Back */

 DATEADD(yy, -2, GETDATE()) AS TwoYearsBack,

 DATEADD(mm, -6, GETDATE()) AS SixMonthsBack,

 DATEADD(mm, -1, GETDATE()) AS OneMonthsBack,

 DATEADD(dd, -14, GETDATE()) AS TwoWeeksBack,

 DATEADD(dd, -7, GETDATE()) AS OneWeekBack

)

/*When you realize the order is not correct in PowerBI */

,MedalRank AS (

SELECT AddMedals.MyKeyField,

CASE WHEN AddMedals.Medal = 'Gold' THEN '1 - Gold'

WHEN AddMedals.Medal = 'Silver' THEN '2 - Silver'

WHEN AddMedals.Medal = 'Bronze' THEN '3 - Bronze'

WHEN AddMedals.Medal = 'No-Medal' THEN '4 - None'

END AS MedalRank

FROM AddMedals

)

/*Bring all the CTEs Together */

SELECT TT.*, AddRankingByYear.MRank, AddMedals.Medal, MedalRank.MedalRank

FROM dbo.TestTableSize TT

LEFT JOIN AddRankingByYear ON AddRankingByYear.MyKeyField = TT.MyKeyField

LEFT JOIN AddMedals ONAddMedals.MyKeyField = AddRankingByYear.MyKeyField

LEFT JOIN MedalRank ONMedalRank.MyKeyField = AddMedals.MyKeyField

LEFT JOIN DateDiffrenceList ON 1 = 1

 

Some people make conditional statements in PowerBI but that logic does not carry over from report to report. By writing a CASE WHEN statement into a CTE you can easily copy the logic to a new view and then add or remove Medals, the example used in this case.  Allowing you to easily filter out the No-Medal folks in your PowerBI visual.

WITH


/*1. Add Ranking by Year and Gender */

AddRankingByYear AS (

SELECT *, DENSE_RANK() OVER (PARTITION BY YEAR(Challenge5CompleteDate), Gender ORDER BY Challenge5CompleteDate ASC) AS MRank

FROM dbo.TestTableSize


)

/*2. Adding Medals based off of rank from CTE above*/

--, AddMedals AS (

SELECT *,

CASE WHEN MRank = 1 THEN 'Gold'

 WHEN MRank = 2 THEN 'Silver'

WHEN MRank = 3 THEN 'Bronze'

ELSE 'No-Medal'

END AS Medal

FROM AddRankingByYear

Notice how I just was able to comment out the line above the second SELECT statement so you are able to see the results of just these two CTEs.

A lot of times I will re-use code for views, one commonly reused piece of code that I use are date bands. You can use these in PowerBI to make a new column and then filter that column so that it only factors in events that happened within the last six months, or any similar logic for any of the date bands provided above.

You may also find yourself wanting your key or other parts of you PowerBI visual to be in a certain order. With a CTE you can easily change the t-sql after THEN, in this example I created a new CTE to Create MedalRank.

In the end you can easily create a chart showing which day of the Month medalists finished when viewing only medalists who finished in the last 6 months. 

 

Thanks to Mitch Sellers for his blog on creating test data:

http://mitchelsellers.com/blogs/2008/09/12/creating-random-sql-server-test-data.aspx