Power BI Examples (NCAA Grad Rates)

A nice sample of PowerBI using NCAA Data about graduation rates. I built this using a simple metrics but several ways to view that sample metric. 

NCAA Graduation Rates

Querying Database for Column Names (version 2.0)

I created a previous blog on how to query the Database for column names are returning the tables. This has been very effective in reverse engineering a client database that is based on Microsoft SQL Server. I have added an update to his query that also returns the number of rows with the table.







In this example, we are looking for a column with the name "staff" in the column name.

------ --------------------------------------------

----- FIND TABLES With a specific Column Name ----
-------------------------------------------------
---- Change the Variable @FindColumn with  ------
-----the Column name your are looking for  ------
----- in the Database                      ------
-------------------------------------------------

DECLARE @FindColumn VARCHAR(64)

SET @FindColumn = '%staff%';

IF OBJECT_ID('tempdb..#counts') IS NOT NULL DROP TABLE #counts

IF OBJECT_ID('tempdb..#table_row_count') IS NOT NULL DROP TABLE #table_row_count

CREATE TABLE #counts (table_name VARCHAR(255),row_count INT )

 

EXEC sp_MSForEachTable @command1 = 'INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'

SELECT LEFT(RIGHT(table_name, LEN(table_name) - 7), LEN(RIGHT(table_name, LEN(table_name) - 7)) - 1) table_name  ,row_count

INTO #table_row_count FROM #counts ORDER BY 1;

WITH RESULTS

AS (

       SELECT t.NAME AS TableName

              ,[type_desc] [Type]
              ,SCHEMA_NAME(schema_id) AS [Schema]

              ,c.NAME AS ColumnName

       FROM sys.tables AS t

       INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

       WHERE c.NAME LIKE @FindColumn      

   )

SELECT RS.TableName

       ,RS.[Type]

       ,RS.ColumnName

       ,TC.row_count

FROM RESULTS RS

LEFT JOIN #table_row_count TC ON TC.table_name collate SQL_Latin1_General_CP1_CI_AS = RS.TableName ORDER BY [Schema]    ,TableName

YTD and Previous YTD calculations

Regardless of what reasons you develop a OLAP DB, you almost always are going to have to deal with creating a YTD and/or previous YTD Calculation. I don't have to do these calculations a lot, which means I need to often scramble for the proper code and syntax to make this work. Having just recently did this for SSAS 2013, Here is the process for you and I.


For starters, make sure you have created the proper date dimensions with at the minimum (for best effect) a date and year column. Yes, this seems obviously but still it needs to be said. Now that we have that out of the way, the steps are as follows:

1. Add Calendar hierarchy (Works best this way)
2. Make sure Date Dimension is specified as Time in Dimension Properties
3. Add Attribute Relationships (see my previous blog post on this)

Now to create YTD use the following (I know there is a YTD function but I have more success with this method) MDX calculation:

 AGGREGATE
(
 PERIODSTODATE
  (
    [Dim Date].[Calendar].[Year],
   [Dim Date].[Calendar].CurrentMember
  ),
([Measures].[Amount])
)
In my example I have a Date hierarchy called DimDate.  In the above code, I used  called this Measure YTD, not using that same calculation, I added it to a ParalellPeriod function to get the Previous year. The code is as follows:

SUM
(
ParallelPeriod
([Dim Date].[Calendar].[Year],
1,
[Dim Date].[Calendar].CurrentMember),[Measures].[YTD]
)


This should work as long as you have process the YTD measure and then created the Previous YTD calculation. Also, this requires the Measure to be aligned with an Year filter (you will see NULLs otherwise) to work.


Again, a common calculation but commonly done incorrectly. Hopefully this helps you.

Power BI DAX Calculations

I use Power BI a lot. Particularly lately I’ve been using the Power BI Desktop that Microsoft seems to be updating almost weekly (Just kidding, but it is on about a monthly update schedule).  When creating calculations, I find some of the most impactful calculations are YTD, Same Period Last Year, and % of Total.

To save you some time, here are the Measure Calculations.... 

·     % of a Total

 

% of Total  = SUM(DailySales[Net Sales]) / CALCULATE(SUM(DailySales[Net Sales]),ALLSELECTED()) 

·     Year to Date

Basically, we need to make sure the Date is format as a normal Date. 

Net Sales YTD = TOTALYTD(SUM(DailySales[Net Sales]),Dates[Date])

·     

            Sales of the Previous Year

Net Sales PY = CALCULATE(SUM(DailySales[Net Sales]), SAMEPERIODLASTYEAR(Dates[MonthYear])) 

Using these three expression you can easily modify these to accommodate other time intelligence calculations and percentages.So if you wanted to get the % change of the Previous Year, you can use the above measure as follows:

         % ChangeVSLastYr = ((SUM(DailySales[Net Sales]) / [Net Sales PY])) - 1

Find Column Names across a SQL Database

When working against databases that you’re not particularly familiar with and you don’t have a data dictionary (or any schema information) you often need to have a quick way to find column names in a database.  

Using this query I created, you can specify a value for the @FindColumn parameter and it will look for that column using the database of your choice. In this example, I looking for the 'clientkey' across the database ProdDB.

 

-- FIND  clientkey columns in SQL Database ProdDB--

 

Use ProdDB
Declare @FindColumn varchar(32)
SET @FindColumn = 'clientkey'

 

SELECT t.Name AS TableOrViewName, [type_desc] [Type],

SCHEMA_NAME(schema_id) AS [Schema],

c.name AS ColumnName

FROM sys.tables AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE @FindColumn

UNION

SELECT v.name,type_desc, SCHEMA_NAME(schema_id) AS [Schema] , cl.name

 FROM sys.views v

 INNER JOIN sys.columns cl ON v.OBJECT_ID = cl.OBJECT_ID

 WHERE cl.name LIKE @FindColumn 

 ORDER BY [Schema], TableOrViewName

 

Common Date Calculations for Parameters and SQL variables

I’m commonly asked to make the parameters for the date start with specific months. I’m typically asked for its First Day Last Month or Last Day Previous Month. Here are the T-SQL and SSRS Expressions to create the dates:

Using T-SQL:

--First Day of Current Month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

--Last Day of Current Month

SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

 --First Day of Previous Month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

--Last Day of Previous Month

SELECT DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

 

When you need to create these directly in the SSRS Report. Use the following:

 

--First date of Current Month

=Today.AddDays(1-Today.Day)

 --last date of Current Month

=Today.AddDays(1-Today.Day).AddMonths(1).AddDays(-1)

 --First date of Current Year

=Today.AddMonths(1-Today.month).AddDays(1-Today.day)

 --Last date of Current Year

=Today.AddDays(1-Today.Day).AddMonths(13-today.month).AddDays(-1)

 --Previous Month Last Date

=DateAdd("d",-(Day(today)), Today)

--Previous Month First Date

=dateadd(dateinterval.month, -1, today().AddDays(-(today().Day-1)))

Creating Environments for Testing

I’ve said during rants about career options, building a lab to test is essential to your development as an IT professional. I‘ve pointed out where you can find a treasure-trove of IT books (http://it-ebooks.info/) and also where you can get free training (Microsoft Virtual Academy).  Using these two site resources you get the books and the training, now you can use the Microsoft Virtual Labs to get to play around with the technologies you read and learned.

Now the third part of your learning process should stem around practicing what you have learned. I’ve said in previous posts that you should establish a lab to test learned technologies so that you when you need to execute them in the real world, you’ll be more effective. Testing requires having some lab environment that mimics the real world. Unfortunately, this is where you can run into a few challenges trying to establish the appropriate environment. This is why I recommend using the Microsoft Virtual Labs - https://technet.microsoft.com/en-us/virtuallabs

Using Microsoft Virtual Labs

The main advantage is that is provides a ready-made environment so that you can practice what you learned.  You don’t have to spend any time setting up a test environment which is often the most laborious and costly side of testing. I’ve worked in support for a long time (too much in my opinion) and the need to have available environment to test customer issues that critical to understanding a resolving customer issues.

It can be costly and very time consuming to have enterprise solutions like SharePoint is testing format. Even if you are using virtual solutions (Hyper-V or VMWare), this is still an expensive technology because it takes lots of disk space and memory (RAM). This does not include the processing power needed to effectively run a “usable” installation. Sure you can get by using these methods with a simple SQL solutions or even a barebones SharePoint environment, but those are often not the best environment to test solutions that are of the enterprise variety.

One of the only downsides is that each virtual lab environment is that is expires in 90 minutes. That may not seem like a long time but it is generally sufficient to test when the all configuration has been done. The obviously con is that if you build out a lab environment with a lot of configuration that will be lost.  Therefore, it’s important that you factor that restriction in your testing. This is for testing issues that can be reproduced quickly or for learning and not for testing solutions that require hours to produce.