Hi guys,
This week I have delivered together with Mohammad Ali, from the Power BI dataflows team, the session named "Microsoft Power BI: Enterprise-grade BI with Power BI dataflows" (aka "What's new in Power BI Dataflows"). The session video is available here. It also includes the session's slides, downloadable from here (in the page of the session video they are listed under "Other resources").
For convenience, I'm embedding the video here (in this blog post) as well:
-
Hi all,
The enhanced engine of the (Premium) Power BI dataflows, mentioned in the calculator at http://aka.ms/dfparams, uses M query rewrites to target the source of the entities to be the cached entities in the hosted SQL engine.
For example, if we have two entities (and their corresponding M expressions):
Name M Expression Orders let
Source = OData.Feed("https://services.odata.org/V2/Northwind/Northwind.svc/", null, [Implementation = "2.0"]),
Navigation = Source{[Name = "Orders", Signature = "table"]}[Data],
Result = Table.RemoveColumns(Navigation, {"Customer", "Employee", "Order_Details", "Shipper"})
in
ResultAggregatedOrders let
Source = Orders,
#"Grouped rows" = Table.Group(Source, {"ShipCountry"}, {{"TotalFreight", each List.Sum([Freight]), type number}})
in
#"Grouped rows"
During the refresh operation of the workflow, if the enhanced engine is not enabled, the queries are rewritten to:
Name M Expression Orders let
Source = PowerBI.Dataflows([TransactionId="<transaction id>", WorkspaceId="<workspace id>", DataflowId="<dataflow id>", TenantId="<tenant id>", IsInternalRewrite=true]),
Workspace = Source{{[workspaceId="<workspace id>"]}}[Data],
Dataflow = Workspace{{[dataflowId="<dataflow id>"]}}[Data],
Entity = Dataflow{{[entity="Orders"]}}[Data]
in
EntityAggregatedOrders <as above (unmodified)>
Essentially, the Orders table is rewritten to point to the Orders entity just processed - using, actually, the dataflow M connector (with some internal options - like, for example, the transaction id, such that the data of this current transaction be observed). Without this rewrite, the Orders table will go "through" to the original data source, re-retrieving all of the orders. This would have the following implications:
- Performance impact - going to the original data source, and applying all the transforms up the chain, again, can be - in many cases - way more expensive than simply use the already processed result of the upstream M transforms.
- Consistency - going, again, and reexecuting the query could, potentially, give a different result than what the immediate upstream entity has observed during its evaluation (e.g. if new rows got added/updated in the table). Observing the cached result would not have this behavior.
Such rewrites are available only in Power BI Premium not in Power BI shared.For data ingestion, the (somewhat contradictory) recommendations are:- Avoid complex transforms - especially against SaaS services (like Salesforce or Dynamics). Get the data in ADLS Gen 2 as fast as you can (usually the equivalent of a "select *" against the data source.
- However, if certain filters or operations would significantly reduce the volume of data from the source to the destination entity, you should consider pushing these operations in the import transform. M will try to translate those operations as operations against the data source. However, M is not always able to translate all of the M query to the underlying dialect of the data source (such a push/translation of the M script to the underlying language is called "folding"). In order to guarantee folding, one should/could use the native query operation when designing the entity in Power Query Online.
When to chose the "select *" and when to chose to merge some operations in the import flow (using, potentially, the native query capability) is a bit of a conundrum whose solution (for best results) depends on the actual problem at hand.Now, with the enhanced engine, the folding discussion applies to downstream entities as well. [Such discussion is not relevant for downstream entities in the absence of the enhanced engine because the physical support - aka data source - in that case is an ADLS Gen2 file and nothing can be "pushed/folded" against the file as a file is not a service that can process any query]For the enhanced engine, the rewrite is:In the rewrite above, localhost appears as we host a SQL Server instance in the dedicated Power BI capacity (the enhanced engine is, though, not available in the A1, A2, EM1 and EM2 skus). For each workspace we have a database and for each dataflow we have a schema - as you can infer from the rewrite above.
Name M Expression Orders let
Source = Sql.Database("localhost", "db$<workspace id>", [CommandTimeout=#duration(1,0,0,0)]),
Entity = Source{[Schema="schema$<dataflow id>", Item="Orders"]}[Data]
in
EntityAggregatedOrders <as above (unmodified)>
So you see, now, that, when AggregateOrders is executed, it'll observe a SQL table in the place of its Orders reference. Therefore, the M engine will try to fold the transforms in the AggregateOrders against SQL. However, not all transforms are supported - in that case, the transform will be partially folded and the rest will be executed inside the M engine. This is still better than the case when the rewrite is used against ADLS Gen2 (because, in that case, nothing gets folded - here at least some part gets folded; also reading from SQL can be, generally, faster). As a note, the tables stored in the SQL Server instance of the enhanced engine are columnar indexes (stored in Vertipaq format).
To help you be guided through what transforms are folded and what are not, I have put here a table of transforms and their behavior (full folding/not full folding). The table is not exhaustive. The columns of the tables are:
- Index - the current row number
- Name - formatted as family:name. For example, Conversion:Conversion_Double_DateTime represents the case of an M transform from the data types conversion family.
- Folds - yes (green)/no (red) - an indication if full folding happens.
- Mashup/Sql - the M code and the total/partial SQL folding. The minimal folding is select
from as SQL is the source of data in the rewrite so some query will be pushed to SQL.
One other mention: you could figure out what the capabilities of folding are if you'd build a direct query dataset (in Power BI desktop) on top of a sql table and try the dataflow dowstream transform. Each of the steps would be presented to the user if folding is successful or not (it'll ask you to change the transform or enable loading).
Finally, all foldable operations should happening earlier rather than later in the script (if a mixture of foldable/not foldable operations happen). The M engine can reorder the steps if safe - but it's certainly not always done.
The current table of (some of the) transforms and their folding is below:
Index Name Folds Mashup/Sql 1 Conversion:
Conversion_Double_DateTimeNo shared RESULT = let
QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type datetime}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[String] as [String],
[$Table].[Int] as [Int],
[$Table].[Double] as [Double],
[$Table].[Date] as [Date],
[$Table].[IntAsString] as [IntAsString],
[$Table].[DoubleAsString] as [DoubleAsString],
[$Table].[DateTeAsString] as [DateTeAsString]
from [dbo].[Conversion] as [$Table]2 Conversion:
Conversion_Double_IntNo shared RESULT = let
QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[String] as [String],
[_].[Int] as [Int],
[_].[Double] as [Double],
[_].[Date] as [Date],
[_].[IntAsString] as [IntAsString],
[_].[DoubleAsString] as [DoubleAsString],
[_].[DateTeAsString] as [DateTeAsString]
from [dbo].[Conversion] as [_]'3 Conversion:
Conversion_Double_StringYes shared RESULT = let
QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type text}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[String] as [String],
convert(nvarchar(max), [_].[Int]) as [Int],
[_].[Double] as [Double],
[_].[Date] as [Date],
[_].[IntAsString] as [IntAsString],
[_].[DoubleAsString] as [DoubleAsString],
[_].[DateTeAsString] as [DateTeAsString]
from [dbo].[Conversion] as [_]'4 Conversion:
Conversion_Int_DateTimeNo shared RESULT = let
QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type datetime}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[String] as [String],
[$Table].[Int] as [Int],
[$Table].[Double] as [Double],
[$Table].[Date] as [Date],
[$Table].[IntAsString] as [IntAsString],
[$Table].[DoubleAsString] as [DoubleAsString],
[$Table].[DateTeAsString] as [DateTeAsString]
from [dbo].[Conversion] as [$Table]5 Conversion:
Conversion_Int_DoubleNo shared RESULT = let
QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[String] as [String],
[_].[Int] as [Int],
[_].[Double] as [Double],
[_].[Date] as [Date],
[_].[IntAsString] as [IntAsString],
[_].[DoubleAsString] as [DoubleAsString],
[_].[DateTeAsString] as [DateTeAsString]
from [dbo].[Conversion] as [_]'6 Conversion:
Conversion_Int_StringYes shared RESULT = let
QueryResult = Table.TransformColumnTypes(Conversion,{{"Int", type text}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[String] as [String],
convert(nvarchar(max), [_].[Int]) as [Int],
[_].[Double] as [Double],
[_].[Date] as [Date],
[_].[IntAsString] as [IntAsString],
[_].[DoubleAsString] as [DoubleAsString],
[_].[DateTeAsString] as [DateTeAsString]
from [dbo].[Conversion] as [_]'7 Conversion:
Conversion_String_DateTimeYes shared RESULT = let
QueryResult = Table.TransformColumnTypes(Conversion,{{"DateTeAsString", type datetime}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[String] as [String],
[$Table].[Int] as [Int],
[$Table].[Double] as [Double],
[$Table].[Date] as [Date],
[$Table].[IntAsString] as [IntAsString],
[$Table].[DoubleAsString] as [DoubleAsString],
[$Table].[DateTeAsString] as [DateTeAsString]
from [dbo].[Conversion] as [$Table]8 Conversion:
Conversion_String_DoubleYes shared RESULT = let
QueryResult = Table.TransformColumnTypes(Conversion,{{"DoubleAsString", type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[String] as [String],
[$Table].[Int] as [Int],
[$Table].[Double] as [Double],
[$Table].[Date] as [Date],
[$Table].[IntAsString] as [IntAsString],
[$Table].[DoubleAsString] as [DoubleAsString],
[$Table].[DateTeAsString] as [DateTeAsString]
from [dbo].[Conversion] as [$Table]9 Conversion:
Conversion_String_IntNo shared RESULT = let
QueryResult = Table.TransformColumnTypes(Conversion,{{"IntAsString", Int64.Type}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[String] as [String],
[$Table].[Int] as [Int],
[$Table].[Double] as [Double],
[$Table].[Date] as [Date],
[$Table].[IntAsString] as [IntAsString],
[$Table].[DoubleAsString] as [DoubleAsString],
[$Table].[DateTeAsString] as [DateTeAsString]
from [dbo].[Conversion] as [$Table]10 GroupBy:
GroupBy_2ColumnsCountDoubleYes shared RESULT = let
QueryResult = Table.Group(Customers, {"Age", "Country"}, {{"row_count", each Table.RowCount(_), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[Age] as [Age],
[rows].[Country] as [Country],
count(1) as [row_count]
from [dbo].[Customers] as [rows]
group by [Age],
[Country]'11 GroupBy:
GroupBy_2ColumnsCountIntYes shared RESULT = let
QueryResult = Table.Group(Customers, {"ZipCode", "Country"}, {{"row_count", each Table.RowCount(_), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],
[rows].[Country] as [Country],
count(1) as [row_count]
from [dbo].[Customers] as [rows]
group by [ZipCode],
[Country]'12 GroupBy:
GroupBy_AverageDoubleYes shared RESULT = let
QueryResult = Table.Group(Customers, {"Age"}, {{"Age-Avg", each List.Average([Id]), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[Age] as [Age],
avg(convert(float, [rows].[Id])) as [Age-Avg]
from [dbo].[Customers] as [rows]
group by [Age]'13 GroupBy:
GroupBy_AverageIntYes shared RESULT = let
QueryResult = Table.Group(Customers, {"ZipCode"}, {{"ZipCode-Avg", each List.Average([ZipCode]), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],
avg(convert(float, [rows].[ZipCode])) as [ZipCode-Avg]
from [dbo].[Customers] as [rows]
group by [ZipCode]'14 GroupBy:
GroupBy_CountDistinctRowsDoubleYes shared RESULT = let
Source = Customers,
QueryResult = Table.Group(Source, {"Age"}, {{"Count", each Table.RowCount(Table.Distinct(Source)), type number}})
in
QueryResult;
select count_big(1) as [$Item]
from
(
select distinct [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]
) as [$Table]15 GroupBy:
GroupBy_CountDistinctRowsIntYes shared RESULT = let
Source = Customers,
QueryResult = Table.Group(Source, {"ZipCode"}, {{"Count", each Table.RowCount(Table.Distinct(Source)), type number}})
in
QueryResult;
select count_big(1) as [$Item]
from
(
select distinct [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]
) as [$Table]16 GroupBy:
GroupBy_CountDistinctRowsStringYes shared RESULT = let
Source = Customers,
QueryResult = Table.Group(Source, {"Country"}, {{"Count", each Table.RowCount(Table.Distinct(Source)), type number}})
in
QueryResult;
select count_big(1) as [$Item]
from
(
select distinct [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]
) as [$Table]17 GroupBy:
GroupBy_CountRowsDoubleYes shared RESULT = let
QueryResult = Table.Group(Customers, {"Age"}, {{"count-rows", each Table.RowCount(_), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[Age] as [Age],
count(1) as [count-rows]
from [dbo].[Customers] as [rows]
group by [Age]'18 GroupBy:
GroupBy_CountRowsIntYes shared RESULT = let
QueryResult = Table.Group(Customers, {"ZipCode"}, {{"count-rows", each Table.RowCount(_), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],
count(1) as [count-rows]
from [dbo].[Customers] as [rows]
group by [ZipCode]'19 GroupBy:
GroupBy_CountRowsStringYes shared RESULT = let
QueryResult = Table.Group(Customers, {"Country"}, {{"count-rows", each Table.RowCount(_), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[Country] as [Country],
count(1) as [count-rows]
from [dbo].[Customers] as [rows]
group by [Country]'20 GroupBy:
GroupBy_MaxDoubleYes shared RESULT = let
QueryResult = Table.Group(Customers, {"Age"}, {{"Age-Max", each List.Max([Age]), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[Age] as [Age],
max([rows].[Age]) as [Age-Max]
from [dbo].[Customers] as [rows]
group by [Age]'21 GroupBy:
GroupBy_MaxIntYes shared RESULT = let
QueryResult = Table.Group(Customers, {"ZipCode"}, {{"ZipCode-Max", each List.Max([ZipCode]), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],
max([rows].[ZipCode]) as [ZipCode-Max]
from [dbo].[Customers] as [rows]
group by [ZipCode]'22 GroupBy:
GroupBy_MedianDoubleNo shared RESULT = let
QueryResult = Table.Group(Customers, {"Age"}, {{"Age-Median", each List.Median([Age]), type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]23 GroupBy:
GroupBy_MedianIntNo shared RESULT = let
QueryResult = Table.Group(Customers, {"ZipCode"}, {{"ZipCode-Median", each List.Median([ZipCode]), type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]24 GroupBy:
GroupBy_MinDoubleYes shared RESULT = let
QueryResult = Table.Group(Customers, {"Age"}, {{"Age-Min", each List.Min([Age]), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[Age] as [Age],
min([rows].[Age]) as [Age-Min]
from [dbo].[Customers] as [rows]
group by [Age]'25 GroupBy:
GroupBy_MinIntYes shared RESULT = let
QueryResult = Table.Group(Customers, {"ZipCode"}, {{"ZipCode-Min", each List.Min([ZipCode]), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],
min([rows].[ZipCode]) as [ZipCode-Min]
from [dbo].[Customers] as [rows]
group by [ZipCode]'26 GroupBy:
GroupBy_SumDoubleYes shared RESULT = let
QueryResult = Table.Group(Customers, {"Age"}, {{"Age-Sum", each List.Sum([Age]), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[Age] as [Age],
sum([rows].[Age]) as [Age-Sum]
from [dbo].[Customers] as [rows]
group by [Age]'27 GroupBy:
GroupBy_SumIntYes shared RESULT = let
QueryResult = Table.Group(Customers, {"ZipCode"}, {{"ZipCode-Sum", each List.Sum([ZipCode]), type number}})
in
QueryResult;
execute sp_executesql N'select [rows].[ZipCode] as [ZipCode],
sum([rows].[ZipCode]) as [ZipCode-Sum]
from [dbo].[Customers] as [rows]
group by [ZipCode]'28 Join:
Join_FullOuterYes shared RESULT = let
CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.FullOuter)
in
CustomerJoinOrder;
execute sp_executesql N'select [$Outer].[Id],
[$Outer].[Name],
[$Outer].[Age],
[$Outer].[ZipCode],
[$Outer].[Country],
[$Inner].[OrderId],
[$Inner].[CustomerId],
[$Inner].[OrderDate],
[$Inner].[Amount]
from [dbo].[Customers] as [$Outer]
full outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'29 Join:
Join_InnerYes shared RESULT = let
CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.Inner)
in
CustomerJoinOrder;
execute sp_executesql N'select [$Outer].[Id],
[$Outer].[Name],
[$Outer].[Age],
[$Outer].[ZipCode],
[$Outer].[Country],
[$Inner].[OrderId],
[$Inner].[CustomerId],
[$Inner].[OrderDate],
[$Inner].[Amount]
from [dbo].[Customers] as [$Outer]
inner join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'30 Join:
Join_LeftAntiNo shared RESULT = let
CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.LeftAnti)
in
CustomerJoinOrder;
select [$Table].[OrderId] as [OrderId],
[$Table].[CustomerId] as [CustomerId],
[$Table].[OrderDate] as [OrderDate],
[$Table].[Amount] as [Amount]
from [dbo].[Orders] as [$Table]31 Join:
Join_LeftOuterYes shared RESULT = let
CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.LeftOuter)
in
CustomerJoinOrder;
execute sp_executesql N'select [$Outer].[Id],
[$Outer].[Name],
[$Outer].[Age],
[$Outer].[ZipCode],
[$Outer].[Country],
[$Inner].[OrderId],
[$Inner].[CustomerId],
[$Inner].[OrderDate],
[$Inner].[Amount]
from [dbo].[Customers] as [$Outer]
left outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'32 Join:
Join_RightAntiNo shared RESULT = let
CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.RightAnti)
in
CustomerJoinOrder;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]33 Join:
Join_RightOuterYes shared RESULT = let
CustomerJoinOrder = Table.Join(Customers, "Id", Orders, "CustomerId", JoinKind.RightOuter)
in
CustomerJoinOrder;
execute sp_executesql N'select [$Outer].[Id],
[$Outer].[Name],
[$Outer].[Age],
[$Outer].[ZipCode],
[$Outer].[Country],
[$Inner].[OrderId],
[$Inner].[CustomerId],
[$Inner].[OrderDate],
[$Inner].[Amount]
from [dbo].[Customers] as [$Outer]
right outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'34 ManageColumns:
ManageColumns_AddColumnYes shared RESULT = let
QueryResult = Table.AddColumn(Customers, "Custom", each if [Name] = "a" then "x" else if [Name] = "b" then "y" else if [Name] = "c" then "z" else null, type text)
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country],
case
when [_].[Name] = ''a'' and [_].[Name] is not null
then ''x''
when [_].[Name] = ''b'' and [_].[Name] is not null
then ''y''
when [_].[Name] = ''c'' and [_].[Name] is not null
then ''z''
else null
end as [Custom]
from [dbo].[Customers] as [_]'35 ManageColumns:
ManageColumns_DuplicateColumnsYes shared RESULT = let
Cust = Customers,
QueryResult = Table.DuplicateColumn(Cust, "Id", "Id-Copy")
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country],
[_].[Id] as [Id-Copy]
from [dbo].[Customers] as [_]'36 ManageColumns:
ManageColumns_RenameColumnsYes shared RESULT = let
QueryResult = Table.RenameColumns(Customers, {"Id", "CustId"})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [CustId],
[_].[Name] as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'37 ManageColumns:
ManageColumns_ReorderColumnsYes shared RESULT = let
QueryResult = Table.ReorderColumns(Customers, {"ZipCode", "Age", "Id"})
in
QueryResult;
execute sp_executesql N'select [ZipCode],
[Name],
[Age],
[Id],
[Country]
from [dbo].[Customers] as [$Table]'38 ManageColumns:
ManageColumns_SelectColumnsYes shared RESULT = let
QueryResult = Table.SelectColumns(Customers, {"Id"})
in
QueryResult;
execute sp_executesql N'select [Id]
from [dbo].[Customers] as [$Table]'39 ManageColumns:
ManageColumns_SplitColumn_ByDelimiterNo shared RESULT = let
QueryResult = Table.SplitColumn(Customers, "Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Name.1", "Name.2"})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]40 ManageColumns:
ManageColumns_SplitColumn_ByNumCharactersNo shared RESULT = let
QueryResult = Table.SplitColumn(Customers, "Name", Splitter.SplitTextByRepeatedLengths(1), {"Name.1", "Name.2"})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]41 ManageColumns:
ManageColumns_UnpivotColumnsYes shared RESULT = let
QueryResult = Table.UnpivotOtherColumns(Customers, {"Id", "Name", "Age", "ZipCode"}, "Attribute", "Value")
in
QueryResult;
execute sp_executesql N'select [$Table].[Id],
[$Table].[Name],
[$Table].[Age],
[$Table].[ZipCode],
[$Table].[Attribute],
[$Table].[Value]
from [dbo].[Customers] [$Pivot] unpivot ([Value] for [Attribute] in ([Country])) as [$Table]'42 NestedJoin:
NestedJoin_FullOuterYes shared RESULT = let
JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.FullOuter),
QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"})
in
QueryResult;
execute sp_executesql N'select [$Outer].[Id] as [Id],
[$Outer].[Name] as [Name],
[$Outer].[Age] as [Age],
[$Outer].[ZipCode] as [ZipCode],
[$Outer].[Country] as [Country],
[$Inner].[OrderId] as [C2.OrderId],
[$Inner].[OrderDate] as [C2.OrderDate]
from [dbo].[Customers] as [$Outer]
full outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'43 NestedJoin:
NestedJoin_InnerYes shared RESULT = let
JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.Inner),
QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"})
in
QueryResult;
execute sp_executesql N'select [$Outer].[Id] as [Id],
[$Outer].[Name] as [Name],
[$Outer].[Age] as [Age],
[$Outer].[ZipCode] as [ZipCode],
[$Outer].[Country] as [Country],
[$Inner].[OrderId] as [C2.OrderId],
[$Inner].[OrderDate] as [C2.OrderDate]
from [dbo].[Customers] as [$Outer]
inner join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'44 NestedJoin:
NestedJoin_LeftAntiNo shared RESULT = let
JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.LeftAnti),
QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"})
in
QueryResult;
select [$Table].[OrderId] as [OrderId],
[$Table].[CustomerId] as [CustomerId],
[$Table].[OrderDate] as [OrderDate],
[$Table].[Amount] as [Amount]
from [dbo].[Orders] as [$Table]45 NestedJoin:
NestedJoin_LeftOuterYes shared RESULT = let
JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.LeftOuter),
QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"})
in
QueryResult;
execute sp_executesql N'select [$Outer].[Id] as [Id],
[$Outer].[Name] as [Name],
[$Outer].[Age] as [Age],
[$Outer].[ZipCode] as [ZipCode],
[$Outer].[Country] as [Country],
[$Inner].[OrderId] as [C2.OrderId],
[$Inner].[OrderDate] as [C2.OrderDate]
from [dbo].[Customers] as [$Outer]
left outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'46 NestedJoin:
NestedJoin_RightAntiNo shared RESULT = let
JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.RightAnti),
QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]47 NestedJoin:
NestedJoin_RightOuterYes shared RESULT = let
JoinTable = Table.NestedJoin(Customers, {"Id"}, Orders, {"CustomerId"}, "C2", JoinKind.RightOuter),
QueryResult = Table.ExpandTableColumn(JoinTable, "C2", {"OrderId", "OrderDate"}, {"C2.OrderId", "C2.OrderDate"})
in
QueryResult;
execute sp_executesql N'select [$Outer].[Id] as [Id],
[$Outer].[Name] as [Name],
[$Outer].[Age] as [Age],
[$Outer].[ZipCode] as [ZipCode],
[$Outer].[Country] as [Country],
[$Inner].[OrderId] as [C2.OrderId],
[$Inner].[OrderDate] as [C2.OrderDate]
from [dbo].[Customers] as [$Outer]
right outer join [dbo].[Orders] as [$Inner] on ([$Outer].[Id] = [$Inner].[CustomerId] or [$Outer].[Id] is null and [$Inner].[CustomerId] is null)'48 Other:
ListDistinctYes shared RESULT = let
Source = Customers,
DistinctCustomers = List.Distinct(Source[Name])
in
DistinctCustomers;
select distinct [Name]
from [dbo].[Customers] as [$Table]49 RowManipulation:
RowManipulation_FilterYes shared RESULT = let
QueryResult = Table.SelectRows(Customers, each not Text.Contains([Name], "42"))
in
QueryResult;
execute sp_executesql N'select [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]
where not ([_].[Name] like ''%42%'')'50 RowManipulation:
RowManipulation_FilterStringYes shared RESULT = let
QueryResult = Table.SelectRows(Customers, each not Text.Contains([Name], "42"))
in
QueryResult;
execute sp_executesql N'select [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]
where not ([_].[Name] like ''%42%'')'51 RowManipulation:
RowManipulation_KeepBottomRowsNo shared RESULT = let
QueryResult = Table.LastN(Customers, 42)
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]52 RowManipulation:
RowManipulation_KeepDuplicatesYes shared RESULT = let
QueryResult = let columnNames = {"Country"}, addCount = Table.Group(Customers, columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(Customers, columnNames, removeCount, columnNames, JoinKind.Inner)
in
QueryResult;
execute sp_executesql N'select [$Outer].[Id],
[$Outer].[Name],
[$Outer].[Age],
[$Outer].[ZipCode],
[$Inner].[Country]
from [dbo].[Customers] as [$Outer]
inner join
(
select [_].[Country]
from
(
select [rows].[Country] as [Country],
count(1) as [Count]
from [dbo].[Customers] as [rows]
group by [Country]
) as [_]
where [_].[Count] > 1
) as [$Inner] on ([$Outer].[Country] = [$Inner].[Country] or [$Outer].[Country] is null and [$Inner].[Country] is null)'53 RowManipulation:
RowManipulation_KeepErrorsNo shared RESULT = let
QueryResult = Table.SelectRowsWithErrors(Customers, {"Name"})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]54 RowManipulation:
RowManipulation_KeepRangeRowsNo shared RESULT = let
QueryResult = Table.Range(Customers, 10, 42)
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]55 RowManipulation:
RowManipulation_KeepTopRowsYes shared RESULT = let
QueryResult = Table.FirstN(Customers, 42)
in
QueryResult;
execute sp_executesql N'select top 42
[$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]'56 RowManipulation:
RowManipulation_RemoveAlternateRowsNo shared RESULT = let
QueryResult = Table.AlternateRows(Customers, 1, 1, 2)
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]57 RowManipulation:
RowManipulation_RemoveBlankQueriesNo shared RESULT = let
QueryResult = Table.SelectRows(Customers, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]58 RowManipulation:
RowManipulation_RemoveBottomRowsNo shared RESULT = let
QueryResult = Table.RemoveLastN(Customers, 42)
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]59 RowManipulation:
RowManipulation_RemoveDuplicatesNo shared RESULT = let
QueryResult = Table.Distinct(Customers, {"ZipCode"})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]60 RowManipulation:
RowManipulation_RemoveEmptyYes shared RESULT = let
QueryResult = Table.SelectRows(Customers, each [Name] <> null and [Name] <> "")
in
QueryResult;
execute sp_executesql N'select [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]
where [_].[Name] is not null and ([_].[Name] <> '''' or [_].[Name] is null)'61 RowManipulation:
RowManipulation_RemoveErrorsNo shared RESULT = let
QueryResult = Table.RemoveRowsWithErrors(Customers, {"Age"})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]62 RowManipulation:
RowManipulation_RemoveTopRowsNo shared RESULT = let
QueryResult = Table.Skip(Customers, 42)
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]63 Sort:
Sort_TableIntAscYes shared RESULT = let
QueryResult = Table.Sort(Customers,{{"ZipCode", Order.Ascending}})
in
QueryResult;
execute sp_executesql N'select [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]
order by [_].[ZipCode]'64 Sort:
Sort_TableIntDescYes shared RESULT = let
QueryResult = Table.Sort(Customers,{{"ZipCode", Order.Descending}})
in
QueryResult;
execute sp_executesql N'select [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]
order by [_].[ZipCode] desc'65 Sort:
Sort_TableMultipleAscYes shared RESULT = let
QueryResult = Table.Sort(Customers,{{"ZipCode", Order.Ascending}, {"Country", Order.Descending}})
in
QueryResult;
execute sp_executesql N'select [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]
order by [_].[ZipCode],
[_].[Country] desc'66 Sort:
Sort_TableStringAscYes shared RESULT = let
QueryResult = Table.Sort(Customers,{{"Country", Order.Ascending}})
in
QueryResult;
execute sp_executesql N'select [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]
order by [_].[Country]'67 Sort:
Sort_TableStringDescYes shared RESULT = let
QueryResult = Table.Sort(Customers,{{"Country", Order.Descending}})
in
QueryResult;
execute sp_executesql N'select [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]
order by [_].[Country] desc'68 TableOperations:
TableOperations_AppendTableYes shared RESULT = let
Table1 = Table.SelectColumns(Customers, {"Id"}),
Table2 = Table.SelectColumns(Orders, {"CustomerId"}),
QueryResult = Table.Combine({Table1, Table2})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
null as [CustomerId]
from
(
select [Id]
from [dbo].[Customers] as [$Table]
) as [_]
union all select null as [Id],
[_].[CustomerId] as [CustomerId]
from
(
select [CustomerId]
from [dbo].[Orders] as [$Table]
) as [_]'69 TableOperations:
TableOperations_FilldownNo shared RESULT = let
QueryResult = Table.FillDown(Customers,{"Name"})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]70 TableOperations:
TableOperations_FillupNo shared RESULT = let
QueryResult = Table.FillUp(Customers,{"Name"})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]71 TableOperations:
TableOperations_PivotNo shared RESULT = let
Source = Customers,
QueryResult = Table.Pivot(Source, List.Distinct(Source[Name]), "Name", "Id")
in
QueryResult;
select distinct [Name]
from [dbo].[Customers] as [$Table]72 TableOperations:
TableOperations_PromoteHeaderYes shared RESULT = let
QueryResult = Table.PromoteHeaders(Customers, [PromoteAllScalars=true])
in
QueryResult;
select top 1
[$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]73 TableOperations:
TableOperations_ReplaceErrorsNo shared RESULT = let
QueryResult = Table.ReplaceErrorValues(Customers, {{"Name", "a"}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]74 TableOperations:
TableOperations_ReplaceValues_NumberYes shared RESULT = let
QueryResult = Table.ReplaceValue(Customers,2,7,Replacer.ReplaceValue,{"Age"})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
case
when [_].[Age] = 2 and [_].[Age] is not null
then 7
else [_].[Age]
end as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'75 TableOperations:
TableOperations_ReplaceValues_StringNo shared RESULT = let
QueryResult = Table.ReplaceValue(Customers,"a","s",Replacer.ReplaceText,{"Name"})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]76 TableOperations:
TableOperations_TableDistinctYes shared RESULT = let
Source = Customers,
QueryResult = Table.Distinct(Source)
in
QueryResult;
select distinct [_].[Id],
[_].[Name],
[_].[Age],
[_].[ZipCode],
[_].[Country]
from [dbo].[Customers] as [_]77 TableOperations:
TableOperations_TableRowCountYes shared RESULT = let
QueryResult = Table.RowCount(Customers)
in
QueryResult;
select count_big(1) as [$Item]
from [dbo].[Customers] as [$Table]78 TransformColumn:
TransformColumn_Number_AbsYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Abs, type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
abs([_].[Age]) as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'79 TransformColumn:
TransformColumn_Number_CombinationsNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", each Number.Combinations(_, 2), type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]80 TransformColumn:
TransformColumn_Number_ExpYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Exp, type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
exp([_].[Age]) as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'81 TransformColumn:
TransformColumn_Number_FactorialNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Factorial, type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]82 TransformColumn:
TransformColumn_Number_IntegerDivideNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"ZipCode", each Number.IntegerDivide(_, 2), type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]83 TransformColumn:
TransformColumn_Number_IsEvenNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.IsEven, type logical}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]84 TransformColumn:
TransformColumn_Number_IsNaNNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.IsNaN, type logical}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]85 TransformColumn:
TransformColumn_Number_IsOddNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.IsOdd, type logical}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]86 TransformColumn:
TransformColumn_Number_LnNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Ln, type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]87 TransformColumn:
TransformColumn_Number_LogYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"ZipCode", Number.Log, type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
[_].[Age] as [Age],
log([_].[ZipCode]) as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'88 TransformColumn:
TransformColumn_Number_Log10Yes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"ZipCode", Number.Log10, type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
[_].[Age] as [Age],
log10([_].[ZipCode]) as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'89 TransformColumn:
TransformColumn_Number_ModYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"ZipCode", each Number.Mod(_, 2), type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
[_].[Age] as [Age],
[_].[ZipCode] % 2 as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'90 TransformColumn:
TransformColumn_Number_PermutationsNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", each Number.Permutations(_, 2), type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]91 TransformColumn:
TransformColumn_Number_PowerYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", each Number.Power(_, 2), type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
power([_].[Age], 2) as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'92 TransformColumn:
TransformColumn_Number_RoundYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Round, type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
round([_].[Age], 0) as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'93 TransformColumn:
TransformColumn_Number_RoundAwayFromZeroNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.RoundAwayFromZero, type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]94 TransformColumn:
TransformColumn_Number_RoundDownYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.RoundDown, type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
floor([_].[Age]) as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'95 TransformColumn:
TransformColumn_Number_RoundTowardZeroNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.RoundTowardZero, type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]96 TransformColumn:
TransformColumn_Number_RoundUpYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.RoundUp, type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
ceiling([_].[Age]) as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'97 TransformColumn:
TransformColumn_Number_SignNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Sign, type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]98 TransformColumn:
TransformColumn_Number_SqrtYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Age", Number.Sqrt, type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
sqrt([_].[Age]) as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'99 TransformColumn:
TransformColumn_Text_AfterDelimiterNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.AfterDelimiter(_, "a"), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]100 TransformColumn:
TransformColumn_Text_AtNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.At(_, 2), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]101 TransformColumn:
TransformColumn_Text_BeforeDelimiterNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.BeforeDelimiter(_, "a"), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]102 TransformColumn:
TransformColumn_Text_BetweenDelimitersNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.BetweenDelimiters(_, "a", "b"), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]103 TransformColumn:
TransformColumn_Text_CleanNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Clean, type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]104 TransformColumn:
TransformColumn_Text_ContainsYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Contains(_, "a"), type logical}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
case
when [_].[Name] like ''%a%''
then 1
else 0
end as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'105 TransformColumn:
TransformColumn_Text_EndNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.End(_, 2), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]106 TransformColumn:
TransformColumn_Text_EndsWithYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.EndsWith(_, "a"), type logical}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
case
when right([_].[Name], len(replace(''a'', '' '', ''*''))) = ''a''
then 1
else 0
end as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'107 TransformColumn:
TransformColumn_Text_FromYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", Text.From, type text}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
convert(nvarchar(max), [_].[Name]) as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'108 TransformColumn:
TransformColumn_Text_FromNumberNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"ZipCode", Character.FromNumber, type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]109 TransformColumn:
TransformColumn_Text_FromTextNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", Value.FromText, type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]110 TransformColumn:
TransformColumn_Text_InsertNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Insert(_, 2, "a"), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]111 TransformColumn:
TransformColumn_Text_LengthYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Length, type text}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
len(replace([_].[Name], '' '', ''*'')) as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'112 TransformColumn:
TransformColumn_Text_LowerYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Lower, type text}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
lower([_].[Name]) as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'113 TransformColumn:
TransformColumn_Text_MiddleNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Middle(_, 2, 3), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]114 TransformColumn:
TransformColumn_Text_PadEndNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.PadEnd(_, 2), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]115 TransformColumn:
TransformColumn_Text_PadStartNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.PadStart(_, 2), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]116 TransformColumn:
TransformColumn_Text_PositionOfNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.PositionOf(_, "a"), type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]117 TransformColumn:
TransformColumn_Text_PositionOfAnyNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.PositionOfAny(_, {"a"}), type number}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]118 TransformColumn:
TransformColumn_Text_ProperNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Proper, type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]119 TransformColumn:
TransformColumn_Text_RangeNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Range(_, 2), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]120 TransformColumn:
TransformColumn_Text_RemoveNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Remove(_, "a"), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]121 TransformColumn:
TransformColumn_Text_RemoveRangeNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.RemoveRange(_, 2, 3), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]122 TransformColumn:
TransformColumn_Text_RepeatNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Repeat(_, 2), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]123 TransformColumn:
TransformColumn_Text_ReplaceNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Replace(_, "a", "b"), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]124 TransformColumn:
TransformColumn_Text_ReplaceRangeNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.ReplaceRange(_, 2, 3, "a"), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]125 TransformColumn:
TransformColumn_Text_ReverseNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Reverse, type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]126 TransformColumn:
TransformColumn_Text_StartNo shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.Start(_, 2), type text}})
in
QueryResult;
select [$Table].[Id] as [Id],
[$Table].[Name] as [Name],
[$Table].[Age] as [Age],
[$Table].[ZipCode] as [ZipCode],
[$Table].[Country] as [Country]
from [dbo].[Customers] as [$Table]127 TransformColumn:
TransformColumn_Text_StartsWithYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", each Text.StartsWith(_, "a"), type logical}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
case
when [_].[Name] like ''a%''
then 1
else 0
end as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'128 TransformColumn:
TransformColumn_Text_ToNumberYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"ZipCode", each Character.ToNumber("42"), type number}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
[_].[Name] as [Name],
[_].[Age] as [Age],
52 as [ZipCode],
[_].[Country] as [Country]
from
(
select [Id],
[Name],
[Age],
[Country]
from [dbo].[Customers] as [$Table]
) as [_]'129 TransformColumn:
TransformColumn_Text_TrimYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Trim, type text}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
trim(concat(nchar(32), nchar(160), nchar(5760), nchar(8192), nchar(8193), nchar(8194), nchar(8195), nchar(8196), nchar(8197), nchar(8198), nchar(8199), nchar(8200), nchar(8201), nchar(8202), nchar(8239), nchar(8287), nchar(12288), nchar(9), nchar(10), nchar(11), nchar(12), nchar(13), nchar(133), nchar(8232), nchar(8233)) from [_].[Name]) as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'130 TransformColumn:
TransformColumn_Text_TrimEndYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", Text.TrimEnd, type text}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
rtrim([_].[Name]) as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'131 TransformColumn:
TransformColumn_Text_TrimStartYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", Text.TrimStart, type text}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
ltrim([_].[Name]) as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'132 TransformColumn:
TransformColumn_Text_UpperYes shared RESULT = let
QueryResult = Table.TransformColumns(Customers,{{"Name", Text.Upper, type text}})
in
QueryResult;
execute sp_executesql N'select [_].[Id] as [Id],
upper([_].[Name]) as [Name],
[_].[Age] as [Age],
[_].[ZipCode] as [ZipCode],
[_].[Country] as [Country]
from [dbo].[Customers] as [_]'
2View comments
-
Hello friends,
I have not written a post in quite some time (this is the understatement of the year). This post is a calculator for the Power BI Premium Dataflows workload and a brief explanation of those parameters. The parameters are grouped in few categories:
- Parameters that can't be changed by the user - left section in the calculator (you can change these parameter in the calculator app at the bottom of the post for educational purposes only - the value of these parameters is not in the control of the user):
- Container TTL [min] - this represents the time the M container survives (in minutes). The container might survive more, in case it's in the middle of a query. Once a query is finished, the "time bomb" is evaluated and, if exceeded, the container is disposed. The container is also disposed if it's idle and the TTL has passed (the assessment is not done only at the end of a query). The value for this parameter is 20 minutes.
- Memory Usage Factor [%] - this represents the memory usage factor for M. Out of all the memory granted by the infrastructure, the dataflows engine needs to retain a bit for its own operation (orchestration of the ETL process, transaction management etc.). Everything else (pretty much everything) is used for the actual ETL engine (M engine). The value for this parameter is 95% (i.e. only 5% is used by the dataflows engine for internal operations, 95% goes to ETL).
- CPU Usage Factor [multiplier] - the M engine has the ability to execute multiple ETL core data processes (all the transforms that lead to a dataflow entity to be created) in parallel. Moreover, each query is executed in a separate process - named mashup container (it's literally a process whose name is MashupContainer.exe - it also has nothing to do with any containerization technology, it's simply a name that uses the same term). The dataflows engine limits the number of containers of the mashup engine based on a number of criteria. Each container can run only a single query at a given moment. If the number of concurrent queries reaches the number of containers that the dataflow engine is willing to spend, queries are serialized - the number of containers represents, therefore, the degree of parallelism for the dataflow engine. One of the criteria used to limit, is the CPU available - ETL processes being a CPU "consumer", we do not want to create too many context switches as it has performance implications. To avoid unnecessary context switching, one should not have more tasks than the number of cores available. However, threads that are blocked on IO operations (e.g. reading data from the data source) provide good parallelism - as the CPU is not used during that time. To complicate matter further, it is very hard to know apriori if a query is going to use a lot of CPU (e.g. expensive in memory join or sort algorithm) or not at all (e.g. it's going to simply read the data, without any transforms, from a remote data source). Further, given that, in parallel to the dataflows engine, other workloads (e.g. Analysis Services) exist on the same dedicated capacity, this clouds the decision further. Based on experience, we set the value of the multiplier to 2. That means that, from the point of view of CPU utilization alone, we could use 2x the number of cores (e.g. the P1 sku has 4 [backend, i.e. capacity] cores - then we could have 8 containers, from the point of view of CPU restrictions). Other factors weigh in and the system will use the smallest of the computed values based on these different criteria.
- WSS 2nd Container [MB] - [wss stands for working set size] for the case when the Enhanced (Dataflows) Engine is enabled, the ETL core pipeline (per entity) is split into two parts:
- read the data and make the necessary transforms (the "ET" part of ETL) - this uses the "main" pool of containers mentioned earlier.
- write the data into SQL and underlying storage (blob or ADLS Gen2) - given that this is, simply, streaming some data, albeit using the same M engine, it does not need a lot of memory for the container, therefore, we enabled a secondary pool of containers (same number of secondary containers as are main containers) with different, much smaller, working set size. This parameter specifies the working set size for each of the secondary containers. The value is 64 MB.
- Max Cache Container [MB] - represents the maximum cache size per container in MB. Each data source can define a caching strategy such that it doesn't go over and over, across evaluations, to the data from the original location (e.g. cache an Excel file). The value of this parameter is 2048 MB.
- Max Temp Size Container [MB] - represents the maximum temporary size per container in MB. This space is used by the data firewall of the M engine (the privacy rules). When the mashup contains a combination of data sources of different privacy boundaries and the query plan cannot guarantee that the data can be flown only in the secure direction (e.g. public => private), the M engine will first cache the necessary data in this temporary space (e.g. the public data in the example just considered in this statement). The temporary space needs to be large enough to cache the entire data needed for the query that cannot, otherwise, be safely processed. The value of this parameter is currently 2048 MB.
- Sql Memory Usage Factor [%] - If the enhanced compute engine is enabled (see below), some memory has to be accounted to the SQL engine that is hosted on the dedicated capacity within the dataflow workload. The value of this parameter is 30%.
- Sql Data Streaming Enabled - inside the engine, the flow can be decomposed into reading and transforming the data and, then, stream the data to both storage (blob or ADLS Gen2) and SQL database (using the secondary containers - described above). The other alternative is to read and transform the data while writing it at the same time (in the same M query) and, then, upload the data from the blob into the SQL database. The value of this parameter is true (we chose the first strategy described here, the one that uses the secondary containers).
- Parameters that can be changed in the UX by the administrator of the capacity in the admin portal/capacities section - middle section in the calculator. The UX looks like this:
- WSS Container [MB] - (labeled "Container Size (Mb)" in the UX) represents the working set size of the mashup container. The minimum value is 700 MB, and so is its default value. It is recommended to increase it to 1.5 GB-2 GB if there are complex transforms being involved. You would be able to see, using the calculator, the impact of such a choice (less parallelism - but each operation might finish earlier because more memory is assigned to each respective operation). The result of increasing the container size is less parallelism as dividing the same amount of memory to larger quantity (to get the maximum number of containers based on the memory criteria) would result in a smaller number of containers being able to be allocated while ensuring more memory for each of them. Upgrading to higher skus can allow you to increase the memory per operation while adding more parallelism (as the higher end skus have more memory). Experimentation can also help to establish which of the parameters is critical for the project (increase sku, add more memory, add parallelism etc).
- Max Memory [%] - represents the quota (out of the physical memory of the dedicated capacity) given to the dataflows workloads. As a note, the quotas of all of the workloads can be overcommited (can add up to more than 100%) - as a matter of fact, the Analysis Services (datasets) workload has always allocated 100% of the memory so, as a whole, the workloads are, indeed, overcommited. However, no workload can have by itself more than 100% of allocation. There is also an absolute minimum limit of about 1200 MB for the dataflows workload. This is not visible per se in the UX but you will get an error if you put a percentage that would end up with an absolute memory smaller than these 1200 MB. For the P1 sku, this translate to the fact that the smallest percentage you can assign is 5%.
- Enhanced Engine Enabled - (labeled "Enhanced Dataflows Compute Engine" in the UX) represents the choice of the customer to direct the dataflow workload to use and manage an internal SQL engine that caches the data in order to deliver better performance (by rewriting queries that, otherwise, would use the Azure storage and redirect them to use that cached data in SQL - but more about that in another post).
- Parameters that depend on the hardware choice (dedicated capacity instantiated) - middle section in the calculator:
- Sku - represents a designated moniker for the capacity that was instantiated, from P1 to P5. P1-P3 are mainstream capacities that can be instantiated in every region without customer needing to contact us. P4 and P5 are super-large capacities that can be instantiated only by specific deals with Microsoft.
- Hardware Cores - not a parameter per se, it is driven by the selection of the sku. One note: the hardware cores are different than the v-cores. For example, P1 has 8 v-cores: 4 frontend, 4 backend (dedicated). These 4 backend cores are the hardware cores the calculator considers for its internal projections - this is why P1 shows 4 cores and not 8 cores (which include the 4 shared frontend cores).
- Hardware Memory - again, not a parameter per se, but a projection of the selction of the sku - it represents the size of memory of the dedicated capacity.
The results are computed and presented in the right section of the calculator:
- Max Containers [#] - represents the max number of M containers (and, therefore, ETL dop). This is calculated once the memory and CPU constraints are taken into account. This number is for the main container pool (not related to the secondary container pool described above).
- Container Max WSS [MB] - represents the actual maximum working set size for each of the main containers.
- Has Secondary Containers - represents an indication if the engine will use a secondary pool of M container. This pool (if used) will have less memory per container (equal to WSS 2nd Container [MB] parameter) and the number of containers will be equal to the number of containers in the main pool.
The calculator is embedded in the page below for your convenience (direct link is here):
5View comments
View comments