Have you ever used the SSAS out of the box functionality to log queries? This can be used to see how many queries are run, how long each query runs, and which users are running the queries. If not, check out this blog for a description of some dashboards that can be created from this log table. If so, have you ever noticed that the time SSAS attaches to each query is in UTC?
Here is the create statement for the default query logging table created by SSAS when logging is turned on:
CREATE TABLE [dbo].[OlapQueryLog](
[MSOLAP_Database] [nvarchar](255) NULL,
[MSOLAP_ObjectPath] [nvarchar](4000) NULL,
[MSOLAP_User] [nvarchar](255) NULL,
[Dataset] [nvarchar](4000) NULL,
[StartTime] [datetime] NULL,
[Duration] [bigint] NULL
) ON [PRIMARY]
For this blog, the important field to note is the [StartTime]. As each query is logged, the starting time for the query is inserted into the OlapQueryLog table along with all the other related information (the cube, the user, the actual query, and the duration). We wanted to build a cube off of this table in order to analyze the peak and low usage Hours of the day for the system. This would allow us to help plan maintenance windows and system downtime for migrations (if needed). However, we soon found out that the usage patterns seemed to be way off, by about a quarter of a day.
Digging into this more, we soon found out that the SSAS query log table logs all activity in UTC (as far as I know this can’t be changed). For this reason, we needed a way to modify the dates as we pulled the data into our fact table, which luckily enough can be done with a simple SQL statement.
In fact, since our servers and main user base sits in the Eastern Time zone, we know we need 5 hours subtracted from the time listed in the log table (as shown in the screenshot above). We can easily just subtract 5 hours, but we can also make the SQL dynamic so that it can be used by any server in any time zone:
Select StartTime,
DATEDIFF([hour], GetUTCDate(), GETDATE()) AS [Difference],
DATEPART(HH, DATEADD([hour], DATEDIFF([hour], GetUTCDate(), GETDATE()), StartTime)) As NewStartTime
FROM dbo.OlapQueryLog
As you can see in the screenshot, I have returned three fields. StartTime being the date returned from the query log table, Difference being the delta in hours between the UTC time (“GetUTCDate()”) and the server time (“GETDATE()”), and NewStartTime being the new hour formatted in the servers local time.
We can now easily browse the cube we created by Hour and get an accurate representation of how the cube is actually being used!
Here is the create statement for the default query logging table created by SSAS when logging is turned on:
CREATE TABLE [dbo].[OlapQueryLog](
[MSOLAP_Database] [nvarchar](255) NULL,
[MSOLAP_ObjectPath] [nvarchar](4000) NULL,
[MSOLAP_User] [nvarchar](255) NULL,
[Dataset] [nvarchar](4000) NULL,
[StartTime] [datetime] NULL,
[Duration] [bigint] NULL
) ON [PRIMARY]
For this blog, the important field to note is the [StartTime]. As each query is logged, the starting time for the query is inserted into the OlapQueryLog table along with all the other related information (the cube, the user, the actual query, and the duration). We wanted to build a cube off of this table in order to analyze the peak and low usage Hours of the day for the system. This would allow us to help plan maintenance windows and system downtime for migrations (if needed). However, we soon found out that the usage patterns seemed to be way off, by about a quarter of a day.
Digging into this more, we soon found out that the SSAS query log table logs all activity in UTC (as far as I know this can’t be changed). For this reason, we needed a way to modify the dates as we pulled the data into our fact table, which luckily enough can be done with a simple SQL statement.
In fact, since our servers and main user base sits in the Eastern Time zone, we know we need 5 hours subtracted from the time listed in the log table (as shown in the screenshot above). We can easily just subtract 5 hours, but we can also make the SQL dynamic so that it can be used by any server in any time zone:
Select StartTime,
DATEDIFF([hour], GetUTCDate(), GETDATE()) AS [Difference],
DATEPART(HH, DATEADD([hour], DATEDIFF([hour], GetUTCDate(), GETDATE()), StartTime)) As NewStartTime
FROM dbo.OlapQueryLog
As you can see in the screenshot, I have returned three fields. StartTime being the date returned from the query log table, Difference being the delta in hours between the UTC time (“GetUTCDate()”) and the server time (“GETDATE()”), and NewStartTime being the new hour formatted in the servers local time.
We can now easily browse the cube we created by Hour and get an accurate representation of how the cube is actually being used!
