custom build step - Page 345

Other Voices: Why Scripting Is Evil, Part 1

Scripting is easy to understand and a natural step, but scripting also has serious problems Scripts - Languages - Programming - Macintosh - AppleScript

Function Folding in #PowerQuery

Originally posted on: http://geekswithblogs.net/darrengosbell/archive/2014/05/16/function-folding-in-powerquery.aspxLooking at a typical Power Query query you will noticed that it's made up of a number of small steps. As an example take a look at the query I did in my previous post about joining a fact table to a slowly changing dimension. It was roughly built up of the following steps: Get all records from the fact table Get all records from the dimension table do an outer join between these two tables on the business key (resulting in an increase in the row count as there are multiple records in the dimension table for each business key) Filter out the excess rows introduced in step 3 remove extra columns that are not required in the final result set. If Power Query was to execute a query like this literally, following the same steps in the same order it would not be overly efficient. Particularly if your two source tables were quite large. However Power Query has a feature called function folding where it can take a number of these small steps and push them down to the data source. The degree of function folding that can be performed depends on the data source, As you might expect, relational data sources like SQL Server, Oracle and Teradata support folding, but so do some of the other sources like OData, Exchange and Active Directory. To explore how this works I took the data from my previous post and loaded it into a SQL database. Then I converted my Power Query expression to source it's data from that database. Below is the resulting Power Query which I edited by hand so that the whole thing can be shown in a single expression: let SqlSource = Sql.Database("localhost", "PowerQueryTest"), BU = SqlSource{[Schema="dbo",Item="BU"]}[Data], Fact = SqlSource{[Schema="dbo",Item="fact"]}[Data], Source = Table.NestedJoin(Fact,{"BU_Code"},BU,{"BU_Code"},"NewColumn"), LeftJoin = Table.ExpandTableColumn(Source, "NewColumn" , {"BU_Key", "StartDate", "EndDate"} , {"BU_Key", "StartDate", "EndDate"}), BetweenFilter = Table.SelectRows(LeftJoin, each (([Date] >= [StartDate]) and ([Date] <= [EndDate])) ), RemovedColumns = Table.RemoveColumns(BetweenFilter,{"StartDate", "EndDate"}) in RemovedColumns If the above query was run step by step in a literal fashion you would expect it to run two queries against the SQL database doing "SELECT * …" from both tables. However a profiler trace shows just the following single SQL query: select [_].[BU_Code], [_].[Date], [_].[Amount], [_].[BU_Key] from ( select [$Outer].[BU_Code], [$Outer].[Date], [$Outer].[Amount], [$Inner].[BU_Key], [$Inner].[StartDate], [$Inner].[EndDate] from [dbo].[fact] as [$Outer] left outer join ( select [_].[BU_Key] as [BU_Key], [_].[BU_Code] as [BU_Code2], [_].[BU_Name] as [BU_Name], [_].[StartDate] as [StartDate], [_].[EndDate] as [EndDate] from [dbo].[BU] as [_] ) as [$Inner] on ([$Outer].[BU_Code] = [$Inner].[BU_Code2] or [$Outer].[BU_Code] is null and [$Inner].[BU_Code2] is null) ) as [_] where [_].[Date] >= [_].[StartDate] and [_].[Date] <= [_].[EndDate] The resulting query is a little strange, you can probably tell that it was generated programmatically. But if you look closely you'll notice that every single part of the Power Query formula has been pushed down to SQL Server. Power Query itself ends up just constructing the query and passing the results back to Excel, it does not do any of the data transformation steps itself. So now you can feel a bit more comfortable showing Power Query to your less technical Colleagues knowing that the tool will do it's best fold all the small steps in Power Query down the most efficient query that it can against the source systems.

Search Results

Search found 35201 results on 1409 pages for 'custom build step'.

Page 345/1409 | < Previous Page | 341 342 343 344 345 346 347 348 349 350 351 352 | Next Page >

- by Darren Gosbell

- by joe

- by Cesar Downs

- by Kikaimaru

- by volker.eckardt(at)oracle.com

- by Javier Puerta

- by The Old Toxophilist

- by CyberShadow

- by Chris George

- by udinnet

- by user89797

- by Mad

- by Steve

- by John Paul Cook

- by user359650

- by Bob Dole

- by Rei Miyasaka

- by Nick

- by Ike

- by Xsteen

- by Rajesh

< Previous Page | 341 342 343 344 345 346 347 348 349 350 351 352 | Next Page >