Monday, December 31, 2018

Adding Date Period to SSAS Tabular Models


Using time intelligence is one of the great features of using SSAS.  I often find that it’s useful to use data periods with dashboard objects instead of simple data ranges. For example, if you want to filter the data by the last 7 days, you often have to specific the current date and the last seven days. This would be try as well for current month. To simplify this process, I would suggest creating a date period for common date selections. For the date period as follows...


  • Current Month
  • Last Two Months
  • Last Three Months
  • Last Six Months
  • All Periods
In this example, the date column is Fiscal Week (Typically this would be Date Column) and the Date is specific as FiscalDate. The ‘Fiscal Week’[FiscalDate] columns would need to be change to whatever is correct in your SSAS model (i.e ‘Date’[Date]’).

You would first simply create a Calculated Table in your Tabular Model with the following DAX Query: 

= UNION (

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Fiscal Week' , DATESBETWEEN('Fiscal Week'[FiscalDate], EOMONTH(TODAY(),-1)+1,TODAY())  ), 'Fiscal Week'[FiscalDate]),"Period","Current Month") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Fiscal Week' , DATESBETWEEN('Fiscal Week'[FiscalDate], EOMONTH(TODAY(),-2)+1 ,TODAY())  ), 'Fiscal Week'[FiscalDate]),"Period","Last Two Months") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Fiscal Week' , DATESBETWEEN('Fiscal Week'[FiscalDate], EOMONTH(TODAY(),-3)+1 ,TODAY())  ), 'Fiscal Week'[FiscalDate]),"Period","Last Three Months") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Fiscal Week' , DATESBETWEEN('Fiscal Week'[FiscalDate], EOMONTH(TODAY(),-6)+1 ,TODAY())  ), 'Fiscal Week'[FiscalDate]),"Period","Last Six Months") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Fiscal Week'), 'Fiscal Week'[FiscalDate]),"Period","Overall") )


 In this example, we could use data periods by days as well…

  • Last 7 Days
  • Last 14 days
  • Last 30 days
  • Last 90 days
  • All Periods 
= UNION (

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Fiscal Week' , DATESBETWEEN('Fiscal Week'[FiscalDate],today()-07+1,today()) ), 'Fiscal Week'[FiscalDate]),"Period","Last 07 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Fiscal Week' , DATESBETWEEN('Fiscal Week'[FiscalDate],today()-14+1,today()) ), 'Fiscal Week'[FiscalDate]),"Period","Last 14 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Fiscal Week' , DATESBETWEEN('Fiscal Week'[FiscalDate],today()-30+1,today()) ), 'Fiscal Week'[FiscalDate]),"Period","Last 30 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Fiscal Week' , DATESBETWEEN('Fiscal Week'[FiscalDate],today()-90+1,today()) ), 'Fiscal Week'[FiscalDate]),"Period","Last 90 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Fiscal Week'), 'Fiscal Week'[FiscalDate]),"Period","Overall") )

 Once you’ve created the Calculated Table, you must add a relationship to the Date Table in your model. In this example, we create the relationship with the Date Period calculated table in the a Table called Fiscal Week. We simple use the default Cardinality: One to Many for the Filter Direction, we use Both Tables.

  


Select OK to save and after deploying the model you should be able to filter by periods.

Friday, January 26, 2018

Dynamic Update of Date Timestamps

At time I often stumble on existing Data Warehouses where the tables do not include an existing timestamp of when the data has been added. Instead of totally recreating the table and adding logic to add timestamps for inserts, here is a sample query that will add the new column and then update it when new updates table place.


-- Add columname and insert default time when added
 
ALTER TABLE dbo.TableName ADD NewColumnName datetime
ALTER TABLE dbo.TableName ADD   DEFAULT (getdate()) FOR NewColumnName
 


The simple update can save you a lot of time when simply troubleshooting table updates.

Sunday, January 21, 2018

Create Dynamic Date Periods in DAX

Date filters are a very common dimension in dashboards and here is a simple way to create dynamic date filters. This saves times as it prevents you from having to pull dates from other sources.

In other tables, I would add a DateKey as follows...

DateKey = Format('Sales[DATE],"YYYYMMDD")


Then the following...
DATES


DATE = ADDCOLUMNS( CALENDAR(DATE(2017,1,1), DATE(2020,12,31)) ,"DateKey", FORMAT ( [Date], "YYYYMMDD" ), //NumericDate
"Date2", FORMAT ( [Date], "M/d/yyyy" ),

"Year", YEAR([Date]),

"FiscalYear","FY" & IF (MONTH([Date]) <=5 ,Year([Date]),Year([Date]) +1) ,

"Quarter", "Q " & FORMAT( [Date], "Q"),

"MonthNameLong" , FORMAT ( [Date], "mmmm" ) ,

"MonthNameShort" , FORMAT ( [Date], "mmm" ) ,"Month Key", FORMAT ( [Date], "YYYYMM" ),

"MonthNumber", MONTH([Date]),

"MonthYear", FORMAT ( [Date], "mmm " ) & YEAR([Date]), "Month Year 2", FORMAT ( [Date], "mmm " ) & RIGHT(YEAR([Date]),2),

"DayName", FORMAT ( [Date], "dddd" ), //Name for Each day of the week

"DayShortName", FORMAT ( [Date], "ddd" ),

"DayNumber" , WEEKDAY ( [Date] ) )

DATE PERIOD


 DatePeriod = 

UNION (

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-07+1,today()) ), 'Date'[Date]),"Period","Last 07 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-14+1,today()) ), 'Date'[Date]),"Period","Last 14 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-30+1,today()) ), 'Date'[Date]),"Period","Last 30 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-90+1,today()) ), 'Date'[Date]),"Period","Last 90 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date'), 'Date'[Date]),"Period","Overall") )




 Once you create both date and date period tables, you can need to join these together using Manage Relationships. Make sure you specific to Both for Cross Filter Directions.









Thursday, December 7, 2017

Get the SQL Jobs Activity

Here is a great query the generates metrics on SQL JOB execution.


use [msdb]

Declare @weekDay Table
(
mask int
, maskValue varchar(32)
);

Insert Into @weekDay
Select 1, 'Sunday' UNION ALL
Select 2, 'Monday' UNION ALL
Select 4, 'Tuesday' UNION ALL
Select 8, 'Wednesday' UNION ALL
Select 16, 'Thursday' UNION ALL
Select 32, 'Friday' UNION ALL
Select 64, 'Saturday';


With SCHED as (
Select sched.name As 'scheduleName'
, sched.schedule_id
, jobsched.job_id as job_id
, Case
When sched.freq_type = 1
Then 'Once'
When sched.freq_type = 4 And sched.freq_interval = 1
Then 'Daily'
When sched.freq_type = 4
Then 'Every ' + Cast(sched.freq_interval As varchar(5)) + ' days'
When sched.freq_type = 8
Then Replace( Replace( Replace((
Select maskValue
From @weekDay As x
Where sched.freq_interval & x.mask <> 0
Order By mask For XML Raw)
, '"/><row maskValue="', ', '), '<row maskValue="', ''), '"/>', '')
+ Case When sched.freq_recurrence_factor <> 0
And sched.freq_recurrence_factor = 1
Then '; weekly'
When sched.freq_recurrence_factor <> 0
Then '; every '
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ' weeks'
End
When sched.freq_type = 16
Then 'On day '
+ Cast(sched.freq_interval As varchar(10)) + ' of every '
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ' months'
When sched.freq_type = 32
Then Case
When sched.freq_relative_interval = 1
Then 'First'
When sched.freq_relative_interval = 2
Then 'Second'
When sched.freq_relative_interval = 4
Then 'Third'
When sched.freq_relative_interval = 8
Then 'Fourth'
When sched.freq_relative_interval = 16
Then 'Last'
End +
Case
When sched.freq_interval = 1
Then ' Sunday'
When sched.freq_interval = 2
Then ' Monday'
When sched.freq_interval = 3
Then ' Tuesday'
When sched.freq_interval = 4
Then ' Wednesday'
When sched.freq_interval = 5
Then ' Thursday'
When sched.freq_interval = 6
Then ' Friday'
When sched.freq_interval = 7
Then ' Saturday'
When sched.freq_interval = 8
Then ' Day'
When sched.freq_interval = 9
Then ' Weekday'
When sched.freq_interval = 10
Then ' Weekend'
End
+
Case
When sched.freq_recurrence_factor <> 0
And sched.freq_recurrence_factor = 1
Then '; monthly'
When sched.freq_recurrence_factor <> 0
Then '; every '
+ Cast(sched.freq_recurrence_factor As varchar(10)) + ' months'
End
When sched.freq_type = 64
Then 'StartUp'
When sched.freq_type = 128
Then 'Idle'
End As 'frequency'
, IsNull('Every ' + Cast(sched.freq_subday_interval As varchar(10)) +
Case
When sched.freq_subday_type = 2
Then ' seconds'
When sched.freq_subday_type = 4
Then ' minutes'
When sched.freq_subday_type = 8
Then ' hours'
End, 'Once') As 'subFrequency'

,[Start_time] =
CASE LEN(sched.active_start_time)
WHEN 1 THEN CAST('00:00:0' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 2 THEN CAST('00:00:' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 3 THEN CAST('00:0'
+ LEFT(RIGHT(sched.active_start_time, 3), 1)
+ ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 4 THEN CAST('00:'
+ LEFT(RIGHT(sched.active_start_time, 4), 2)
+ ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 5 THEN CAST('0'
+ LEFT(RIGHT(sched.active_start_time, 5), 1)
+ ':' + LEFT(RIGHT(sched.active_start_time, 4), 2)
+ ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
WHEN 6 THEN CAST(LEFT(RIGHT(sched.active_start_time, 6), 2)
+ ':' + LEFT(RIGHT(sched.active_start_time, 4), 2)
+ ':' + RIGHT(sched.active_start_time, 2) AS CHAR(8))
END
,[End_time] =
CASE LEN(sched.active_end_time)
WHEN 1 THEN CAST('00:00:0' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
WHEN 2 THEN CAST('00:00:' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
WHEN 3 THEN CAST('00:0'
+ LEFT(RIGHT(sched.active_end_time, 3), 1)
+ ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
WHEN 4 THEN CAST('00:'
+ LEFT(RIGHT(sched.active_end_time, 4), 2)
+ ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
WHEN 5 THEN CAST('0'
+ LEFT(RIGHT(sched.active_end_time, 5), 1)
+ ':' + LEFT(RIGHT(sched.active_end_time, 4), 2)
+ ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
WHEN 6 THEN CAST(LEFT(RIGHT(sched.active_end_time, 6), 2)
+ ':' + LEFT(RIGHT(sched.active_end_time, 4), 2)
+ ':' + RIGHT(sched.active_end_time, 2) AS CHAR(8))
END
, Replicate('0', 6 - Len(jobsched.next_run_time))
+ Cast(jobsched.next_run_time As varchar(6)) As 'nextRunTime'
, Cast(jobsched.next_run_date As char(8)) As 'nextRunDate'
From msdb.dbo.sysschedules As sched
Join msdb.dbo.sysjobschedules As jobsched
On sched.schedule_id = jobsched.schedule_id),
JOB as (
SELECT
[job_id] = job.job_id
,[Job_Name] = job.Name
,[Job_Enabled] =
CASE job.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
,[Sched_ID] = sched.schedule_id
,[Sched_Enabled] =
CASE sched.enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END
,[Sched_Frequency] =
CASE sched.freq_type
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Daily'
WHEN 8 THEN 'Weekly'
WHEN 16 THEN 'Monthly'
WHEN 32 THEN 'Monthly relative'
WHEN 64 THEN 'When SQLServer Agent starts'
END
,[Next_Run_Date] =
CASE next_run_date
WHEN 0 THEN NULL
ELSE SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 1, 4) + '/' +
SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 5, 2) + '/' +
SUBSTRING(CONVERT(VARCHAR(15), next_run_date), 7, 2)
END
,[Next_Run_Time] =
CASE LEN(next_run_time)
WHEN 1 THEN CAST('00:00:0' + RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 2 THEN CAST('00:00:' + RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 3 THEN CAST('00:0'
+ LEFT(RIGHT(next_run_time, 3), 1)
+ ':' + RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 4 THEN CAST('00:'
+ LEFT(RIGHT(next_run_time, 4), 2)
+ ':' + RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 5 THEN CAST('0'
+ LEFT(RIGHT(next_run_time, 5), 1)
+ ':' + LEFT(RIGHT(next_run_time, 4), 2)
+ ':' + RIGHT(next_run_time, 2) AS CHAR(8))
WHEN 6 THEN CAST(LEFT(RIGHT(next_run_time, 6), 2)
+ ':' + LEFT(RIGHT(next_run_time, 4), 2)
+ ':' + RIGHT(next_run_time, 2) AS CHAR(8))
END
,[Max_Duration] =
CASE LEN(max_run_duration)
WHEN 1 THEN CAST('00:00:0'
+ CAST(max_run_duration AS CHAR) AS CHAR(8))
WHEN 2 THEN CAST('00:00:'
+ CAST(max_run_duration AS CHAR) AS CHAR(8))
WHEN 3 THEN CAST('00:0'
+ LEFT(RIGHT(max_run_duration, 3), 1)
+ ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
WHEN 4 THEN CAST('00:'
+ LEFT(RIGHT(max_run_duration, 4), 2)
+ ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
WHEN 5 THEN CAST('0'
+ LEFT(RIGHT(max_run_duration, 5), 1)
+ ':' + LEFT(RIGHT(max_run_duration, 4), 2)
+ ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
WHEN 6 THEN CAST(LEFT(RIGHT(max_run_duration, 6), 2)
+ ':' + LEFT(RIGHT(max_run_duration, 4), 2)
+ ':' + RIGHT(max_run_duration, 2) AS CHAR(8))
END
,[Min_Duration] =
CASE LEN(min_run_duration)
WHEN 1 THEN CAST('00:00:0'
+ CAST(min_run_duration AS CHAR) AS CHAR(8))
WHEN 2 THEN CAST('00:00:'
+ CAST(min_run_duration AS CHAR) AS CHAR(8))
WHEN 3 THEN CAST('00:0'
+ LEFT(RIGHT(min_run_duration, 3), 1)
+ ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
WHEN 4 THEN CAST('00:'
+ LEFT(RIGHT(min_run_duration, 4), 2)
+ ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
WHEN 5 THEN CAST('0'
+ LEFT(RIGHT(min_run_duration, 5), 1)
+ ':' + LEFT(RIGHT(min_run_duration, 4), 2)
+ ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
WHEN 6 THEN CAST(LEFT(RIGHT(min_run_duration, 6), 2)
+ ':' + LEFT(RIGHT(min_run_duration, 4), 2)
+ ':' + RIGHT(min_run_duration, 2) AS CHAR(8))
END
,[Avg_Duration] =
CASE LEN(avg_run_duration)
WHEN 1 THEN CAST('00:00:0'
+ CAST(avg_run_duration AS CHAR) AS CHAR(8))
WHEN 2 THEN CAST('00:00:'
+ CAST(avg_run_duration AS CHAR) AS CHAR(8))
WHEN 3 THEN CAST('00:0'
+ LEFT(RIGHT(avg_run_duration, 3), 1)
+ ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
WHEN 4 THEN CAST('00:'
+ LEFT(RIGHT(avg_run_duration, 4), 2)
+ ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
WHEN 5 THEN CAST('0'
+ LEFT(RIGHT(avg_run_duration, 5), 1)
+ ':' + LEFT(RIGHT(avg_run_duration, 4), 2)
+ ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
WHEN 6 THEN CAST(LEFT(RIGHT(avg_run_duration, 6), 2)
+ ':' + LEFT(RIGHT(avg_run_duration, 4), 2)
+ ':' + RIGHT(avg_run_duration, 2) AS CHAR(8))
END
,[Subday_Frequency] =
CASE (sched.freq_subday_interval)
WHEN 0 THEN 'Once'
ELSE CAST('Every '
+ RIGHT(sched.freq_subday_interval, 2)
+ ' '
+ CASE (sched.freq_subday_type)
WHEN 1 THEN 'Once'
WHEN 4 THEN 'Minutes'
WHEN 8 THEN 'Hours'
END AS CHAR(16))
END
,[Sched_End Date] = sched.active_end_date
,[Sched_End Time] = sched.active_end_time
,[Fail_Notify_Name] =
CASE
WHEN oper.enabled = 0 THEN 'Disabled: '
ELSE ''
END + oper.name
,[Fail_Notify_Email] = oper.email_address
,server

FROM dbo.sysjobs job
LEFT JOIN (SELECT

job_schd.job_id
,sys_schd.enabled
,sys_schd.schedule_id
,sys_schd.freq_type
,sys_schd.freq_subday_type
,sys_schd.freq_subday_interval
,next_run_date =
CASE
WHEN job_schd.next_run_date = 0 THEN sys_schd.active_start_date
ELSE job_schd.next_run_date
END
,next_run_time =
CASE
WHEN job_schd.next_run_date = 0 THEN sys_schd.active_start_time
ELSE job_schd.next_run_time
END
,active_end_date = NULLIF(sys_schd.active_end_date, '99991231')
,active_end_time = NULLIF(sys_schd.active_end_time, '235959')

FROM dbo.sysjobschedules job_schd
LEFT JOIN dbo.sysschedules sys_schd
ON job_schd.schedule_id = sys_schd.schedule_id) sched
ON job.job_id = sched.job_id
LEFT OUTER JOIN (SELECT
job_id, server
,MAX(job_his.run_duration) AS max_run_duration
,MIN(job_his.run_duration) AS min_run_duration
,AVG(job_his.run_duration) AS avg_run_duration
FROM dbo.sysjobhistory job_his
GROUP BY job_id, server) Q1
ON job.job_id = Q1.job_id
LEFT JOIN sysoperators oper
ON job.notify_email_operator_id = oper.id)

SELECT isnull(b.server,convert(varchar(max),SERVERPROPERTY('ServerName'))), b.job_name, b.job_enabled, isnull(b.sched_enabled,'No') as sched_enabled,
isnull(a.scheduleName, 'None') as scheduleName, isnull(a.frequency,'Not scheduled') as frequency,
isnull(a.subFrequency, 'None') as subFrequency, isnull(a.start_time,'-') as start_time, isnull(a.end_Time,'-') as end_time,
isnull(b.Next_Run_Date, '-') as Next_Run_Date, isnull(b.Next_Run_Time, '-') as Next_Run_Time,
isnull(b.Max_Duration, '-') as Max_Duration, isnull(b.Min_Duration, '-') as Min_Duration,
isnull(b.Avg_Duration, '-') as Avg_Duration, isnull(b.Fail_Notify_Name, 'None') as Fail_Notify_Name,
isnull(b.Fail_Notify_Email, 'None') as Fail_Notify_Email
FROM SCHED a RIGHT OUTER JOIN JOB b
ON a.job_id = b.job_id
ORDER BY job_name