July 1, 2022 at 7:26 am
Hi
I have a tabular model and have been asked to retrieve the last cube refresh time. However when I look in $SYSTEM.MDSCHEMA_CUBES the time is different to when I right click on the model database and look at the properties. The time in the properties is correct but in $SYSTEM.MDSCHEMA_CUBES it is showing in GMT rather than BST (1 hour behind) where we are now.
Anyone else notice this or may know how to resolve it?
Thank you
July 1, 2022 at 8:45 pm
The times are definitely stored in UTC. The right click method is clearly adjusting based on time zone, but I can't see any way to get the current time zone information from Analysis Services.
The solution might be to get the time zone offset from sql and adjust the time from SSAS. How do you need to present the information? If it is a report then you could query the last update date and query the time zone offset from sql server and create an expression field to show the converted time.
An alternative might be to query the tabular model from SQL through a linked server and then make use of SQL Server time zone functions to adjust the time. I don't have access to a Tabular linked server, only MDIM and the same syntax didn't work (or I did something else wrong).
I know it is possible to use PowerShell to return attributes from cubes. We use PS to return the current list of partitions so we know whether to create a new one or not, so I imagine the last update date could be retrieved and inserted into a table. This sounds like overkill and might be a steep learning curve (assuming it's not blocked as a security risk anyway).
I think the solution depends on how you need to report the information and what version of SQL Server you are using. I tried using CURRENT_TIMEZONE (), but I'm on SQL 2017 so computer said no.
July 4, 2022 at 9:05 am
Hi Ed B
So what I do is extract the data that the DMV retrieves into a SQL table and this then gets added to reports. From what you are saying looks like I need to apply a function to offset the time in my SQL query. I suspected this might be the case but wanted to check if I had missed anything obvious.
Thank you for your response.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply