Monthly Archives: May 2019

Power Query Is No Longer DAX’s Little Brother

I’ve talked before about the difference between the Power Query Formula language, or M, and the DAX language.

I would describe Power Query as the intern you pay minimum wage or the sous chef, and DAX as the $35 per hour analyst or the head chef. This wasn’t to be mean but instead was just because Power Query was all about automating repetitive data manipulations. It handled the less exciting, less complicated work.

Last week, however, I presented on Power Query, and I had to update the slide about where it’s available. I used to say that wherever DAX is, Power Query was not very far behind. Doing all of the grunt work so that DAX could shine. But this time I had to update my slides because Power Query is starting to take center stage.

Now instead of just being available in Excel, Power BI and SSAS, Power Query is available in Microsoft Flow, SSIS and ADF! At the time this post was published, these are all in preview. But it’s really exciting to see Power Query no longer trailing behind DAX, ready to take center stage.

T-SQL Tuesday #114 – An Unsolved SQL Puzzle

This week’s T-SQL Tuesday invitation is all about puzzles. I’ve got an accidental puzzle that I’ve never quite solved, from one of my demos. I’m sure the answer will be a “Well duh!” moment.

I give presentations on SQL Server execution plans. As part of that, I like to show that if you pull a single row from a heap, it has to read everything. As part of that demo, I try to push everything out of memory by disabling readahead reads, taking a checkpoint, and dropping clean buffers. But for some reason… it never quite works!

IF NOT EXISTS ( SELECT  *
FROM    sys.schemas
WHERE   name = N'Demo' )
EXEC('CREATE SCHEMA [Demo] AUTHORIZATION [dbo]');
GO

IF OBJECT_ID('Adventureworks2014.Demo.Person', 'U') IS NOT NULL
BEGIN
DROP TABLE AdventureWorks2014.Demo.Person;
END

SET STATISTICS IO ON

SELECT *
INTO [AdventureWorks2014].[Demo].[Person]
FROM [AdventureWorks2014].[Person].[Person]

DBCC TRACEON (652,-1);

CHECKPOINT
DBCC DROPCLEANBUFFERS

SELECT * FROM [AdventureWorks2014].[Demo].[Person]
WHERE BusinessEntityID = 25

You can see here that it shows 3,808 logical reads, but 5 physical reads.

Screen shot of statistics IO showing the number of reads.

I’m sure there is some simple way to force it to do all of the physical reads, but I have yet to figure it out. Or it may be that I’m misunderstanding something and physical reads are the only pages used. But when I look at the execution plan, it says it read all of the rows.

I’d love to get an answer to this puzzle. I’m sure it’s something simple.

Update: Andy G. asked if maybe the issue is I’m not using all the rows. Here I tried a heap of a single row, and I get 1 logical read and 0 physical reads.

Table 'Person'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Sync Your Outlook Calendar to Google Calendar with Microsoft Flow

Sometimes you may want your Outlook Calendar events synced to Google Calendar. This can be done with a handful of clicks and Microsoft Flow. Additionally, this is completely free if you have Office 365.

What is Microsoft Flow?

Microsoft flow is the third piece of the Microsoft Power Platform:

  1. Power BI – Interactive analytics.
  2. PowerApps – Low-code mobile and web applications.
  3. Flow – User-friendly event integrations.

The Power Platform is a set of tools aimed at business users that want capabilities that were originally limited to professional coders or BI developers.

Out of the three, Microsoft Flow is the weirdest because it’s so granular. The unit of measure for Power BI is the report, the unit of measure for PowerApps is the application, and the unit of measure for Microsoft Flow is the flow or event trigger. And event triggers are really, really tiny.

Essentially, a flow is a trigger and then a series of actions, much like you might map out with a flow chart. It functions similarly to IFTTT or Zapier. I think of it as the glue or connective tissue between different applications.

In this post, we are going to glue together our Outlook Calendar to our Google Calendar.

Why connect calendars?

Back when I worked a normal job, I had two calendars: Office 365 for work and Google for home. Now that I work for myself, that’s a lot more complicated. Sometimes a customer will create an account for me in their network. Sometimes I’ll partner with other consultants and work as part of their team. And of course, I’ve got my own work email at eugene@sqlgene.com.

I need all of these calendars to consolidate to one place. My natural inclination and personal preference is to put it all into Google. Now, there are sync apps available, but this sort of problem is a perfect use case. A calendar event is created in outlook, a flow is triggered, and that information is transferred to Google.

Using Flow

To use Flow, I simply went to https://flow.microsoft.com and searched for Google Calendar. The template search for Flow sorts by popularity, and unsurprisingly the top result was exactly what I wanted.

image

Once I selected the template, then I needed to log into my Google account.
image

Then I just needed to select the calendars from both accounts that I wanted to sync.
image

And that’s it! I was pleasantly surprised how easy it was to do, and I’m interested to see where else I can use Microsoft Flow.