Creating a table of minutely activity from a status log within the model

[masplin]

I hav been landed with a pbix written by a consultant who is in the wind. Part of the project he wrote a query that created a log of every user for every minute of every day what they were doing.  This table is now 15mio rows and growing rapidly.  There also seem to be some mistakes in the data it is creating.  Below is his code which means nothing to me as I'm an Excel guy come to power BI not an SQL guy

= Sql.Database("softphone.secondary.database.windows.net", "softphone", [Query="declare @dstvalue int  #(lf)declare @timenow datetime#(lf)#(lf)select @dstvalue = case when is_currently_dst = 1 then 1 else 0 end from sys.time_zone_info where name = 'GMT Standard Time'#(lf)select @timenow =DATEADD(hour,@dstvalue,getdate())#(lf)#(lf)#(lf)declare @datetable table ([date] date)#(lf)declare @minutetable table ([time] time)#(lf)#(lf)create table #dateminuteperiod  ([datetime] datetime, [datetimeend] datetime )#(lf)create clustered index i1 ON #dateminuteperiod  ([datetime]);#(lf)create index i2 ON #dateminuteperiod  ([datetimeend]);#(lf)#(lf)insert into @datetable #(lf)([date])#(lf)select #(lf)distinct convert(date,AddedOn) #(lf)from [dbo].[StatusLog] #(lf)WHERE [AddedOn] >= '20190101 00:00:00' #(lf)#(lf)DECLARE @StartDate smalldatetime#(lf)DECLARE @EndDate smalldatetime#(lf)#(lf)SET @StartDate = '20090101 00:00' #(lf)SET @EndDate = '20090102 00:00'#(lf)#(lf)SET @StartDate = DATEADD(minute,-DATEPART(minute,@StartDate),@StartDate)#(lf)SET @EndDate = DATEADD(minute,-DATEPART(minute,@EndDate),@EndDate)#(lf)#(lf)#(lf); WITH DateIntervalsCTE AS#(lf)(#(lf)SELECT 0 i, @startdate AS Date#(lf)UNION ALL#(lf)SELECT i + 1, DATEADD(minute, i, @startdate )#(lf)FROM DateIntervalsCTE #(lf)WHERE DATEADD(minute, i, @startdate ) <= @enddate#(lf))#(lf)#(lf)insert into @minutetable #(lf)([time])#(lf)SELECT DISTINCT #(lf)convert(time,Date) #(lf)#(lf)FROM DateIntervalsCTE#(lf)OPTION (MAXRECURSION 0);#(lf)#(lf)#(lf)insert into #dateminuteperiod ([datetime],[datetimeend] )#(lf)select #(lf)CAST(CONCAT([date], ' ', [Time]) AS datetime2)#(lf),dateadd(minute,1,  CAST(CONCAT([date], ' ', [Time]) AS datetime2))#(lf)from @datetable d #(lf)cross apply @minutetable h #(lf)#(lf)SELECT #(lf)#(tab)s.[Id]#(lf)#(tab),s.[PhoneUserId]#(lf)#(tab),s.[AddedOn]  AS StatusStartDT#(lf)#(tab),CONVERT(Date,s.[AddedOn]) AS StatusDate#(lf)#(tab),LEAD(s.AddedOn,1,@timenow) OVER (PARTITION BY s.[PhoneUserId] ORDER  BY s.AddedOn)  AS StatusEndDT#(lf)#(tab),CASE WHEN s.StatusReasonId = 3 AND s.[AddedOn] < '2019-06-12 12:00'  THEN 'Exceptions' ELSE  COALESCE(sr.[Reason],[Status])  END AS [Status]#(lf)into #(lf)#(tab)[#StatusLog] #(lf)FROM #(lf)#(tab)[dbo].[StatusLog] s#(lf)LEFT JOIN #(lf)#(tab)[dbo].[StatusReason] sr on sr.[Id]= s.StatusReasonID#(lf)WHERE #(lf)#(tab)s.[AddedOn] >= '20190101 00:00:00'#(lf)#(lf)create index i3 on [#StatusLog] (StatusStartDT)#(lf)#(lf)select #(lf)#(tab)h.datetime as StatusDateTime#(lf)#(tab),cte.PhoneUserid#(lf)#(tab),datediff(second,#(lf)case#(tab)when cte.StatusStartDT < h.[datetime]  then h.[datetime] else StatusStartDT end,#(lf)#(tab)case#(tab)when cte.StatusEndDT BETWEEN  h.[datetime] AND h.datetimeend then  StatusEndDT      #(lf)#(tab)#(tab)#(tab)when cte.StatusEndDT > h.datetimeend    then  h.datetimeend else StatusEndDT END) AS DurationSec#(lf)#(tab),[Status]#(lf)from#(lf)#(tab)#dateminuteperiod  h#(lf)join#(lf)#(tab)#StatusLog cte on cte.StatusStartDT < h.[datetime] and cte.StatusEndDT >= h.[datetime] #(lf)where #(lf)#(tab)[Status] NOT IN ('OfflineOverride','Offline')#(lf)#(lf)union all #(lf)#(lf)select #(lf)#(tab)h.datetime as StatusDateTime#(lf)#(tab),cte.PhoneUserid#(lf)#(tab),datediff(second,cte.StatusStartDT,case when StatusEndDT >= h.[datetimeend] then h.[datetimeend]  else StatusEndDT end)#(lf)#(tab),[Status]#(lf)from#(lf)#(tab)#dateminuteperiod  h#(lf)join#(lf)#(tab)[#StatusLog]  cte on cte.StatusStartDT >= h.[datetime] and cte.StatusStartDT <=h.[datetimeend]#(lf)where #(lf)#(tab)[Status] NOT IN ('OfflineOverride','Offline')#(lf)#(lf)drop table  #dateminuteperiod#(lf)drop table  #StatusLog#(lf)", CreateNavigationProperties=false])

The table this produces looks like this

The data for this comes from this Status log

My questions are:

1. Is it possible to use  DAX or M to create the same table rather than using an SQL query?  If so any clues how to do it?
2. If it was possible is the sql version going to be superior in termsof performance? We are already running into issues with the power bi service refresh failing. The Status log contains 500,000 rows instead of 15mio so substantially smaller to query.  Just wondering if building the table inside the model woudl reduce the load on the Microsoft bit of thep rocess and shift it to the local machine.

Thanks for any advice

Mike

[v-chuncz-msft]

@masplin 

You may take a look at the links below.

• https://www.sqlbi.com/?s=SQL+to+DAX
• Best practices for building fast and reliable reports
• https://www.sqlbi.com/topics/optimization/

[masplin]

That's not a very helpful answer. The question real is can you built a table like this directly from the source log using M or DAX or is that just not possible?  I can't work out what is wrong with the SQL query as don't understnad any of the code. If i can replace this with a DAX or M version I hopefully will be able to see why it is creating some incorrect data.

Wether this is better or worse speed wise is really a secondary question.