Daylight Saving Time And Time Zones In M

Quite a few people (Ken Puls, Reza Rad and today Chris Koester) have blogged about how the M date/time zone functions work in Power Query/Power BI, and the related problem of turning a UTC time into the actual time in any given location taking Daylight Saving Time changes into account. I don’t have much to add to what they have written, but I did learn something new about this subject from a customer last week: it is possible to do the conversion in pure M if the time zone you’re converting to is your PC’s own local time zone.

Here’s an example. In 2017 the clocks in the UK went forward at 1am on Sunday March 26th. Given the following table with two times in it, one of which is 1pm on Saturday March 25th and one of which is 1pm on Sunday March 26th:


…if you assume that these times are both UTC, here’s a query that returns the correct equivalent local time for me (because my PC is set to UK time) for both of them:

    //Read data from Excel table
    Source = 
    //Change the type of the column to datetime
    ChangeType1 = 
		{{"Time", type datetime}}),
    //Turn this into a UTC time
    ConvertToUTC = 
		each DateTime.AddZone([Time],0)),
    //Change the data type to datetimezone
    ChangeType2 = 
		{{"UTC", type datetimezone}}),
    //Convert to local PC time
    ConvertToLocal = 
		each DateTimeZone.ToLocal([UTC])),
    //Change type to datetimezone
    ChangeType3 = 
		ConvertToLocal ,
		{{"Local", type datetimezone}})


Here’s a brief explanation of what the query does:

  • First it reads the times from the Excel table and sets the Time column to be datetime data type
  • It then creates a new column called UTC and then takes the values in the Time column and converts them to datetimezone values, using the DateTime.AddZone() function to add a time zone offset of 0 hours, making them UTC times
  • Finally it creates a column called Local and converts the UTC times to my PC’s local time zone using the DateTimeZone.ToLocal() function

As you can see from the output table, the DateTimeZone.ToLocal() function has correctly handled the UK Daylight Saving Time change by converting the UTC time 13:00 on March 25th 2017 to 13:00 UK time, and converting the UTC time 13:00 on March 26th 2017 to 14:00 UK time.

Frustratingly there doesn’t seem to be a way to convert a UTC time to the correct local time in any given time zone in M – the DateTimeZone.SwitchZone() function just adds/removes hours without taking Daylight Saving Time into account, so it’s not really very useful. Apart from all the blog posts linked to at the start of this post, when I posted this question on the Power Query forum I got a very helpful answer from Marcel Beug with a completely different approach to solving the problem here, although it’s still not a straightforward one. If anyone from Microsoft is reading, it would be great to have a new M function to handle this requirement!

Quick Tip: Working With Multiple Power BI Subscriptions/Accounts

As a consultant I frequently work with customers who want to add me to their Power BI tenant so I can publish reports, monitor data refresh and so on. However, this means I end up with many different Microsoft “Work or School” accounts, one for each customer, and signing in and out of each one can be a real pain. I use Chrome as my main browser so instead I create one profile in Chrome for each customer – it makes it very easy to switch between Power BI accounts without having to sign in and out.

For more details, see the Google documentation here:

…and here’s the blog post by James Callaghan on using this feature with multiple Office 365 subscriptions that I got the idea from:

It looks like you can do something similar with other browsers too.

SSAS 2016 Locking Improvements

I first became aware of the server-wide lock taken out by SSAS when processing finishes – and the issues that this can cause – from this blog post by Andrew Calvett back in 2009. More information on how locking works in SSAS can be found in chapter 26 of “Microsoft SQL Server 2008 Analysis Services Unleashed”, while the most comprehensive discussion of this topic can be found in this post by Jason Howell:

Over the years I’ve worked with several customers who have run into locking problems as a result of users querying while processing or synchronisation are taking place, so as a result I was interested to read the following paragraph in the white paper on “Automated Partition Management For Analysis Services Tabular Models” that was published a few months ago:

Note that commit operations have been optimized considerably for tabular models in SQL Server 2016. This has caused noticeable improvements in locking and blocking for some customers with near-real time processing requirements. Database write-commit locks are required to safely complete tasks such as merging pending changes, persisting files to disk, clearing some cached state, deletion of old files, etc. In previous versions of Analysis Services, a server-level write commit lock was taken while most of these tasks were performed. With SQL Server 2016, the server-level locks are far more limited; they are only taken while producing the delta of transaction updates, and are then immediately released.

This is very good news, and in fact the improvements apply to SSAS Multidimensional 2016 as well as SSAS Tabular 2016. The ever-helpful Akshai Mirchandani of the dev team has given me more details on the changes, so here’s a summary of what happens during a commit operation and what’s new in SSAS 2016:

  • First of all, a database read-commit lock is taken to analyse all the pending changes.
  • Next a database write-commit lock is taken so that the transaction can be committed safely. This is the lock that can be blocked by long-running queries, and this is where the ForceCommitTimeout property comes into play with the result that these long-running queries may get cancelled.
  • This lock is held while the pending changes are merged together.
  • At this point SSAS is ready to do the commit, and where it takes a server-level write-commit lock. This is also the point where the improvements in SSAS 2016 have been made.
    • In previous versions SSAS would update the master.vmp file in place and hold the server-level write-commit lock while that happens and while some other, potentially time-consuming things like clearing cached state and deleting all the old files take place. This could in some cases result in the server-level write-commit lock being held for an extended period.
    • Instead in SSAS 2016 a delta of all the transaction updates are written to a .txn file, and after that the server commit lock is released. The time-consuming tasks mentioned in the previous bullet still take place but after the server-level write-commit lock has been released. This means the server-level write-commit lock is now held for a very short amount of time, and what’s more that amount of time is quite consistent.
  • Finally, all remaining locks such as the database write-commit lock are released.

I haven’t had a chance to test these changes in a production system yet but it sounds like anyone that needs to process or synchronise regularly throughout the day will benefit from upgrading to SSAS 2016.

Thoughts On Visio And How It Could Integrate With Power BI

The recent release of Visio Online (which means most people with an Office 365 subscription can now see Visio diagrams in the browser) and updates for Visio Pro for Office 365 (the desktop version of Visio, needed for creating diagrams, available on subscription but annoyingly not included in any of the Office 365 Enterprise plans) made me wonder if there’s anything new for BI pros in the latest version of Visio. This is a subject I come back to every few years – the last time was in back in 2013 – but I’m still a bit disappointed by some aspects of Visio when it comes to working with data even though there’s a massive amount of potential there.

To recap, there are actually two features of Visio that are interesting for BI purposes. First, you can import data into Visio and then link rows of data to shapes in your diagram (see here for a basic intro; this presentation has a lot more detail and is a must-read) to create data-linked diagrams; if your data source is an Excel workbook stored in SharePoint Online/OneDrive for Business or a SharePoint Online list then you can even refresh the data in Visio Online in the browser. While it doesn’t do exactly the same thing, the fact that the Synoptic Panel custom visual for Power BI is so popular shows that people really like the idea of seeing their data visualised in diagram form. If you know what you’re doing, you can do some really amazing stuff with Visio data-linked diagrams: not just change shape colours but also their formatting, geometry, size and more.

Secondly there are Pivot Diagrams, the Visio equivalent of Excel’s PivotTables. This blog post by David Parker is a really good guide to what they can do; like PivotTables they can even be linked direct to SSAS. The enduring popularity of the old Proclarity Decomposition Tree and the fact that so many people want something similar to be added to Power BI shows the appeal that this way of analysing data has for BI pros and Visio Pivot Diagrams are much more powerful than any custom visual the Power BI team could hope to develop.

So where does Visio fall short and what could be done about it? Here are some thoughts:

  • The data import options are limited, both in terms of where you can get data from and what you can do with it after that. These are all problems that Excel has solved in recent years with Power Pivot and Power Query, so why not borrow what Excel have implemented? Power Query/Get & Transform functionality for data loading and transformation, and a Visio Data Model (like the Excel Data Model/Power Pivot) would solve all these problems at a stroke and mean that anyone who had experience with Excel or Power BI would be able to move over to using Visio for BI easily.
  • Publishing to Office 365 is all well and good, but publishing to Power BI would also be useful. This might involve:
    • The ability to publish Visio diagrams to Power BI in the same way we can publish Excel workbooks to Power BI today.
    • A Visio equivalent to “Analyze in Excel” so you could create Pivot Diagrams connected to Power BI datasets. This should be straightforward to implement because Pivot Diagrams already work with SSAS, so all you would need to do is fire the same MDX at Power BI.
    • Once published, the ability to pin Visio diagrams to a Power BI dashboard.
    • The ability to schedule data refresh in the same way you can today with Power BI datasets and Excel workbooks.
  • The separate and relatively pricey ($13USD per month) subscription needed for Visio on the desktop suggests that Microsoft now thinks of it as a niche product, not something that even a top-of-the-range Office 365 E5 subscriber gets by default. I guess they’re allowed to make that assumption but it will be very hard for a BI pro to justify that expense for a product that isn’t essential for their job. Adding Visio into the top Office 365 SKUs would expose it to a much wider audience including BI pros. Incidentally the same argument can be made about Visio’s newly re-acquired ability to create diagrams from relational databases: it looks nice, but how many DBAs will be able to justify the cost of a subscription just for it?

In summary, while I know that I’m guilty of looking at Visio from a BI pro point of view and that data visualisation isn’t necessarily Visio’s primary purpose, I do think both Visio and Power BI could both benefit a lot from closer integration just as Excel and Power BI have done in recent years. With a bit of investment from both teams we could have another killer feature added to Power BI, one that none of Microsoft’s competitors could begin to match.

Power Query/Get & Transform: The Future Of Data Loading In Excel

Last week, Dominic Petri shared a link on Twitter that contained some important news from Microsoft: as of the March release of the Office 365 click-to-run version of Excel 2016, the old methods of loading data into Excel will be replaced by the newer “Get & Transform” functionality that is better known as Power Query. All the details are here:

Up to now Excel 2016 users have had to deal with a rather confusing compromise whereby the older data loading functionality existed side-by-side with the new “Get & Transform” functionality on the Data tab of the ribbon. From the March update onwards though the Data tab will look like this:

Get & Transform options on the Data tab

There are also several other cool new features being introduced, such as the ability to export Power Query queries to .odc files so they can be shared between workbooks, and new UI for managing connections and queries. You’ll still be able to get the old data loading functionality back via the Options dialog if you want.

If you’re a regular reader of this blog you’ll know that I’m a massive fan of Power Query, so naturally I’m really happy to see this happen. I believe it’s a big step forward for Excel in terms of its BI and reporting capabilities and the fact that this functionality is almost identical to the data loading functionality in Power BI means that Microsoft’s overall BI story becomes even more compelling: the skills you learn in Excel are transferable to Power BI and vice versa. On the other hand I can guess this is going to cause confusion for some users – similar to what happened when the ribbon first appeared in Office 2007 – but I don’t think this can be avoided. Ultimately the benefits will outweigh any disruption caused as users have to learn the new way of doing things.

%d bloggers like this: