DAX equiv needed: Historical Trending of application outages

[hxkresl]

Need a DAX equivalent for producing a dataset that can be used for Historical Trending of application outages at HOUR granularity.

I've encountered this kind of problem 2x in past 5 years, and it's one where I've needed to show whether at any given time increment (minutes, hours, days) something was on / off.  A single row in a dataset is paired with a Time table at needed grain, and then an extra column is used to track if the application was up/down or backup was running/not running.  I think it's not uncommon scenario to report on.

I solved in SQL using an OUTER APPLY.  I match the source dataset with every row in the time table (hour grain). Means a single row gets duplicated 24 times per day, so that I can then flag if application was up or down at any given hour. 

Then I can graph. Date is on X, hours 1-24 on Y, and Duration of Outage on the bars.  If 36 hour outage, first bar is 24 hours high, and second bar is 12 hours.

In SQL, I got a dataset that mapped paired each application to 24 hours of each day, and then notes whether at that hour the app was up or down. 

Now I need to do this in DAX because SQL staging isn't an option, but i did POC in SQL . Below is trend graph and it shows Napson outage <24 hrs, Shapeeze outage>24hr, StrikeOne outage >24 hrs and overlapping with Shapeeze.

 code for reproducing is shared below and pbix file shared on dropbox https://www.dropbox.com/sh/6kgldb1fhfichy4/AAAbXQhUr8VvyD65qTEOvIPFa?dl=0

 DDL shows how data is stored in source.

CREATE TABLE Alerts (
AlertID int,
AlertName varchar(100),
ApplicationName varchar(50),
IssueStartTime datetime,
ResolutionTime datetime)

INSERT INTO Alerts (AlertID,AlertName,ApplicationName,IssueStartTime,ResolutionTime)
VALUES 
(160922, 'Can''t login', 'Shapeeze', '2017-05-08 12:00:00.000', '2017-05-23 18:00:00.000'),
(150319, 'Errors Submitting Forms','Shapeeze', '2017-05-23 06:00:00.000', '2017-05-29 13:00:00.000'),
(210522, 'Server down', 'Napson', '2017-05-01 03:00:00.000','2017-05-01 23:00:00.000'),
(120311, 'Customers Being Misrouted', 'Strikeone','2017-05-15 16:00:00.000', '2017-05-19 09:00:00.000')


CREATE TABLE Alert_Application (
AlertID int,
ApplicationID int)

INSERT INTO Alert_Application
VALUES
(160922, 1001),
(150319, 1001),
(210522, 2000),
(120311, 3000)

CREATE TABLE Applications (
ApplicationID int,
AppName varchar(20))

INSERT INTO Applications 
VALUES
(1001, 'Shapeeze'),
(2000, 'Napson'),
(3000, 'StrikeOne');


CREATE TABLE Date

SQL SOLUTION

First build a time table having one row for every hour of the day, for time period I am tracking (in example, it is month of May)

--Time table
;WITH e1(n) AS(
	SELECT * FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(n)
),
e2(n) AS(
	SELECT e1.n FROM e1, e1 x
),
e4(n) AS(
	SELECT e2.n FROM e2, e2 x
),
e8(n) AS(
	SELECT e4.n FROM e4, e4 x
),
cteTally(n) AS(
	SELECT TOP 6307204 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
	FROM e8
),
Test(n) AS(
SELECT DATEADD( hh, n, DATEADD( DAY, -45, GETDATE())) 
FROM cteTally)
select DATEADD(hh,datediff(hh,0,n),0)as cte_start_date
INTO dbo.Time 
FROM Test
WHERE n <= DATEADD( MONTH, 0, GETDATE())

SQL OUTER APPLY the source dataset to time table, pairing *every* row from source with row in time table, regardless of match.  OUTER JOIN does not work here as I need to know both whether the application was out or not out.  Both pieces of information are important for the historical trending graph to work

DECLARE	@FromDate AS DATETIME,
		@ToDate AS DATETIME

	
SET		@FromDate = GETDATE()-45
SET		@ToDate = GETDATE() 

select distinct
Tool
, right('0'+ DATENAME(dd, cte_start_date),2) + '-' + DATENAME(MONTH, cte_start_date) Date
, cte_start_date
, AppDownIs1
from 
(
SELECT 
Apps.[AppName] as Tool
, CrsApp.cte_start_date
, MAX( 
	CASE 
	WHEN CrsApp.cte_start_date BETWEEN A.[issuestarttime] AND [resolutiontime]  THEN 1 
	ELSE 0  
	END  ) as AppDownIs1

FROM
alerts A
JOIN [dbo].[Alert_Application] Appset on A.alertid = Appset.alertid
JOIN [dbo].[Applications] Apps on Appset.applicationid = Apps.applicationid
OUTER APPLY (SELECT t.cte_start_date FROM Time t 
	         WHERE cte_start_date >= @FromDate  AND  cte_start_date < @ToDate) CrsApp    

GROUP BY 
Apps.[AppName], CrsApp.cte_start_date


)t
 order by cte_start_date, Tool

The resulting dataset from this query is many times larger than the original due to pairing of each row in Alerts table with 720 rows in Time table per month (24 hours X 30 days).   This sample only tracks 4 apps over 1 month, but in real life will be tracking hundreds of apps over 1 year.  I worry the dataset will be too huge.

Can this be solved in DAX?

[Eric_Zhang]

---

@hxkresl wrote:

Need a DAX equivalent for producing a dataset that can be used for Historical Trending of application outages at HOUR granularity.

I've encountered this kind of problem 2x in past 5 years, and it's one where I've needed to show whether at any given time increment (minutes, hours, days) something was on / off.  A single row in a dataset is paired with a Time table at needed grain, and then an extra column is used to track if the application was up/down or backup was running/not running.  I think it's not uncommon scenario to report on.

I solved in SQL using an OUTER APPLY.  I match the source dataset with every row in the time table (hour grain). Means a single row gets duplicated 24 times per day, so that I can then flag if application was up or down at any given hour. 

Then I can graph. Date is on X, hours 1-24 on Y, and Duration of Outage on the bars.  If 36 hour outage, first bar is 24 hours high, and second bar is 12 hours.

In SQL, I got a dataset that mapped paired each application to 24 hours of each day, and then notes whether at that hour the app was up or down. 

Now I need to do this in DAX because SQL staging isn't an option, but i did POC in SQL . Below is trend graph and it shows Napson outage <24 hrs, Shapeeze outage>24hr, StrikeOne outage >24 hrs and overlapping with Shapeeze.

 code for reproducing is shared below and pbix file shared on dropbox https://www.dropbox.com/sh/6kgldb1fhfichy4/AAAbXQhUr8VvyD65qTEOvIPFa?dl=0

 DDL shows how data is stored in source.

CREATE TABLE Alerts (
AlertID int,
AlertName varchar(100),
ApplicationName varchar(50),
IssueStartTime datetime,
ResolutionTime datetime)

INSERT INTO Alerts (AlertID,AlertName,ApplicationName,IssueStartTime,ResolutionTime)
VALUES 
(160922, 'Can''t login', 'Shapeeze', '2017-05-08 12:00:00.000', '2017-05-23 18:00:00.000'),
(150319, 'Errors Submitting Forms','Shapeeze', '2017-05-23 06:00:00.000', '2017-05-29 13:00:00.000'),
(210522, 'Server down', 'Napson', '2017-05-01 03:00:00.000','2017-05-01 23:00:00.000'),
(120311, 'Customers Being Misrouted', 'Strikeone','2017-05-15 16:00:00.000', '2017-05-19 09:00:00.000')


CREATE TABLE Alert_Application (
AlertID int,
ApplicationID int)

INSERT INTO Alert_Application
VALUES
(160922, 1001),
(150319, 1001),
(210522, 2000),
(120311, 3000)

CREATE TABLE Applications (
ApplicationID int,
AppName varchar(20))

INSERT INTO Applications 
VALUES
(1001, 'Shapeeze'),
(2000, 'Napson'),
(3000, 'StrikeOne');


CREATE TABLE Date

SQL SOLUTION

First build a time table having one row for every hour of the day, for time period I am tracking (in example, it is month of May)

--Time table
;WITH e1(n) AS(
	SELECT * FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(n)
),
e2(n) AS(
	SELECT e1.n FROM e1, e1 x
),
e4(n) AS(
	SELECT e2.n FROM e2, e2 x
),
e8(n) AS(
	SELECT e4.n FROM e4, e4 x
),
cteTally(n) AS(
	SELECT TOP 6307204 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1
	FROM e8
),
Test(n) AS(
SELECT DATEADD( hh, n, DATEADD( DAY, -45, GETDATE())) 
FROM cteTally)
select DATEADD(hh,datediff(hh,0,n),0)as cte_start_date
INTO dbo.Time 
FROM Test
WHERE n <= DATEADD( MONTH, 0, GETDATE())

SQL OUTER APPLY the source dataset to time table, pairing *every* row from source with row in time table, regardless of match.  OUTER JOIN does not work here as I need to know both whether the application was out or not out.  Both pieces of information are important for the historical trending graph to work

DECLARE	@FromDate AS DATETIME,
		@ToDate AS DATETIME

	
SET		@FromDate = GETDATE()-45
SET		@ToDate = GETDATE() 

select distinct
Tool
, right('0'+ DATENAME(dd, cte_start_date),2) + '-' + DATENAME(MONTH, cte_start_date) Date
, cte_start_date
, AppDownIs1
from 
(
SELECT 
Apps.[AppName] as Tool
, CrsApp.cte_start_date
, MAX( 
	CASE 
	WHEN CrsApp.cte_start_date BETWEEN A.[issuestarttime] AND [resolutiontime]  THEN 1 
	ELSE 0  
	END  ) as AppDownIs1

FROM
alerts A
JOIN [dbo].[Alert_Application] Appset on A.alertid = Appset.alertid
JOIN [dbo].[Applications] Apps on Appset.applicationid = Apps.applicationid
OUTER APPLY (SELECT t.cte_start_date FROM Time t 
	         WHERE cte_start_date >= @FromDate  AND  cte_start_date < @ToDate) CrsApp    

GROUP BY 
Apps.[AppName], CrsApp.cte_start_date


)t
 order by cte_start_date, Tool

The resulting dataset from this query is many times larger than the original due to pairing of each row in Alerts table with 720 rows in Time table per month (24 hours X 30 days).   This sample only tracks 4 apps over 1 month, but in real life will be tracking hundreds of apps over 1 year.  I worry the dataset will be too huge.

Can this be solved in DAX?

---

Instead of 720 rows for each row, have you considered to 30 (only days) for each? You only take the down hours in start day and resolution day into account, for the days during thoses two dates, the downhours are just 24, right?