API tools faq
paste
Guest User

Plan with out top

a guest
Mar 6th, 2018
158
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 205.78 KB | None | 0 0
raw download clone embed print report
  1. <?xml version="1.0" encoding="utf-16"?>
  2. <ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.5" Build="13.0.4466.4" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
  3. <BatchSequence>
  4. <Batch>
  5. <Statements>
  6. <StmtSimple StatementCompId="1" StatementEstRows="309606" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="28.4287" StatementText="WITH G_DIM_STORE0 AS (&#xD;&#xA; Select L_DIM_STORE0.&quot;RETAIL_ID&quot; As &quot;RETAIL_ID&quot;, L_DIM_STORE0.&quot;SG_CHAIN_CODE&quot; As &quot;SG_CHAIN_CODE&quot;, L_DIM_STORE0.&quot;SG_CHAIN_DESC&quot; As &quot;SG_CHAIN_DESC&quot;&#xD;&#xA; From dbo.vwDim_Store L_DIM_STORE0&#xD;&#xA; Group by L_DIM_STORE0.&quot;RETAIL_ID&quot; , L_DIM_STORE0.&quot;SG_CHAIN_CODE&quot; , L_DIM_STORE0.&quot;SG_CHAIN_DESC&quot; &#xD;&#xA;)&#xD;&#xA;Select *&#xD;&#xA;Into #DIM_STORE0&#xD;&#xA;From G_DIM_STORE0" StatementType="SELECT INTO" QueryHash="0xA919CB055A6D4B93" QueryPlanHash="0xBE89CAA78CB1FA39" RetrievedFromCache="true" SecurityPolicyApplied="false">
  7. <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
  8. <QueryPlan DegreeOfParallelism="25" MemoryGrant="408" CachedPlanSize="56" CompileTime="1" CompileCPU="1" CompileMemory="768">
  9. <ThreadStat Branches="1" UsedThreads="25">
  10. <ThreadReservation NodeId="0" ReservedThreads="13" />
  11. <ThreadReservation NodeId="1" ReservedThreads="12" />
  12. </ThreadStat>
  13. <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" RequiredMemory="408" DesiredMemory="408" RequestedMemory="408" GrantWaitTime="0" GrantedMemory="408" MaxUsedMemory="408" MaxQueryMemory="24969208" />
  14. <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="536870" EstimatedPagesCached="838860" EstimatedAvailableDegreeOfParallelism="12" MaxCompileMemory="95651768" />
  15. <WaitStats>
  16. <Wait WaitType="LCK_M_S" WaitTimeMs="3" WaitCount="26" />
  17. <Wait WaitType="PAGELATCH_SH" WaitTimeMs="368" WaitCount="6705" />
  18. <Wait WaitType="PAGELATCH_UP" WaitTimeMs="763" WaitCount="11527" />
  19. <Wait WaitType="LATCH_EX" WaitTimeMs="1330" WaitCount="18804" />
  20. </WaitStats>
  21. <QueryTimeStats CpuTime="748" ElapsedTime="157" />
  22. <RelOp AvgRowSize="9" EstimateCPU="0.0608151" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="309606" LogicalOp="Gather Streams" NodeId="0" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="28.4287">
  23. <OutputList />
  24. <RunTimeInformation>
  25. <RunTimeCountersPerThread Thread="0" ActualRows="309606" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="134" ActualCPUms="24" />
  26. </RunTimeInformation>
  27. <Parallelism>
  28. <RelOp AvgRowSize="9" EstimateCPU="0.0258005" EstimateIO="1.53245" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="309606" LogicalOp="Insert" NodeId="1" Parallel="true" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="28.3679">
  29. <OutputList />
  30. <RunTimeInformation>
  31. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  32. <RunTimeCountersPerThread Thread="1" ActualRows="11320" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="128" ActualCPUms="26" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  33. <RunTimeCountersPerThread Thread="2" ActualRows="14801" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="122" ActualCPUms="29" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  34. <RunTimeCountersPerThread Thread="3" ActualRows="13020" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="131" ActualCPUms="28" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  35. <RunTimeCountersPerThread Thread="4" ActualRows="12475" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="124" ActualCPUms="25" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  36. <RunTimeCountersPerThread Thread="5" ActualRows="12536" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="115" ActualCPUms="26" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  37. <RunTimeCountersPerThread Thread="6" ActualRows="13774" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="121" ActualCPUms="26" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  38. <RunTimeCountersPerThread Thread="7" ActualRows="10419" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="125" ActualCPUms="26" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  39. <RunTimeCountersPerThread Thread="8" ActualRows="11945" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="129" ActualCPUms="27" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  40. <RunTimeCountersPerThread Thread="9" ActualRows="13966" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="111" ActualCPUms="32" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  41. <RunTimeCountersPerThread Thread="10" ActualRows="16518" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="126" ActualCPUms="26" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  42. <RunTimeCountersPerThread Thread="11" ActualRows="10379" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="122" ActualCPUms="24" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  43. <RunTimeCountersPerThread Thread="12" ActualRows="11418" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="125" ActualCPUms="27" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  44. <RunTimeCountersPerThread Thread="13" ActualRows="13326" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="126" ActualCPUms="26" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  45. <RunTimeCountersPerThread Thread="14" ActualRows="11723" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="126" ActualCPUms="25" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  46. <RunTimeCountersPerThread Thread="15" ActualRows="12768" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="120" ActualCPUms="24" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  47. <RunTimeCountersPerThread Thread="16" ActualRows="12526" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="124" ActualCPUms="25" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  48. <RunTimeCountersPerThread Thread="17" ActualRows="12660" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="124" ActualCPUms="25" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  49. <RunTimeCountersPerThread Thread="18" ActualRows="12774" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="122" ActualCPUms="27" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  50. <RunTimeCountersPerThread Thread="19" ActualRows="12294" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="126" ActualCPUms="27" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  51. <RunTimeCountersPerThread Thread="20" ActualRows="14327" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="122" ActualCPUms="26" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  52. <RunTimeCountersPerThread Thread="21" ActualRows="12575" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="128" ActualCPUms="29" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  53. <RunTimeCountersPerThread Thread="22" ActualRows="13384" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="123" ActualCPUms="26" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  54. <RunTimeCountersPerThread Thread="23" ActualRows="13659" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="124" ActualCPUms="28" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  55. <RunTimeCountersPerThread Thread="24" ActualRows="13484" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="127" ActualCPUms="26" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  56. <RunTimeCountersPerThread Thread="25" ActualRows="1535" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="109" ActualCPUms="13" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  57. </RunTimeInformation>
  58. <Update DMLRequestSort="false">
  59. <Object Table="[#DIM_STORE0]" Storage="RowStore" />
  60. <SetPredicate>
  61. <ScalarOperator ScalarString="[#DIM_STORE0].[RETAIL_ID] = [Expr1006],[#DIM_STORE0].[SG_CHAIN_CODE] = [STG_AGG].[dbo].[DIM_STORE].[SG_CHAIN_CODE],[#DIM_STORE0].[SG_CHAIN_DESC] = [STG_AGG].[dbo].[DIM_STORE].[SG_CHAIN_DESC]">
  62. <ScalarExpressionList>
  63. <ScalarOperator>
  64. <MultipleAssign>
  65. <Assign>
  66. <ColumnReference Table="[#DIM_STORE0]" Column="RETAIL_ID" />
  67. <ScalarOperator>
  68. <Identifier>
  69. <ColumnReference Column="Expr1006" />
  70. </Identifier>
  71. </ScalarOperator>
  72. </Assign>
  73. <Assign>
  74. <ColumnReference Table="[#DIM_STORE0]" Column="SG_CHAIN_CODE" />
  75. <ScalarOperator>
  76. <Identifier>
  77. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Column="SG_CHAIN_CODE" />
  78. </Identifier>
  79. </ScalarOperator>
  80. </Assign>
  81. <Assign>
  82. <ColumnReference Table="[#DIM_STORE0]" Column="SG_CHAIN_DESC" />
  83. <ScalarOperator>
  84. <Identifier>
  85. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Column="SG_CHAIN_DESC" />
  86. </Identifier>
  87. </ScalarOperator>
  88. </Assign>
  89. </MultipleAssign>
  90. </ScalarOperator>
  91. </ScalarExpressionList>
  92. </ScalarOperator>
  93. </SetPredicate>
  94. <RelOp AvgRowSize="72" EstimateCPU="0.00258005" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="309606" LogicalOp="Compute Scalar" NodeId="2" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="26.8097">
  95. <OutputList>
  96. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Column="SG_CHAIN_DESC" />
  97. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Column="SG_CHAIN_CODE" />
  98. <ColumnReference Column="Expr1006" />
  99. </OutputList>
  100. <ComputeScalar>
  101. <DefinedValues>
  102. <DefinedValue>
  103. <ColumnReference Column="Expr1006" />
  104. <ScalarOperator ScalarString="setidentity([STG_AGG].[dbo].[DIM_STORE].[RETAIL_ID],(-7),(0),N'#DIM_STORE0')">
  105. <Intrinsic FunctionName="setidentity">
  106. <ScalarOperator>
  107. <Identifier>
  108. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Column="RETAIL_ID" />
  109. </Identifier>
  110. </ScalarOperator>
  111. <ScalarOperator>
  112. <Const ConstValue="(-7)" />
  113. </ScalarOperator>
  114. <ScalarOperator>
  115. <Const ConstValue="(0)" />
  116. </ScalarOperator>
  117. <ScalarOperator>
  118. <Const ConstValue="N'#DIM_STORE0'" />
  119. </ScalarOperator>
  120. </Intrinsic>
  121. </ScalarOperator>
  122. </DefinedValue>
  123. </DefinedValues>
  124. <RelOp AvgRowSize="72" EstimateCPU="0.0283936" EstimateIO="26.7787" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="309606" EstimatedRowsRead="309606" LogicalOp="Clustered Index Scan" NodeId="3" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="26.8071" TableCardinality="309606">
  125. <OutputList>
  126. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Column="RETAIL_ID" />
  127. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Column="SG_CHAIN_DESC" />
  128. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Column="SG_CHAIN_CODE" />
  129. </OutputList>
  130. <RunTimeInformation>
  131. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="253" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  132. <RunTimeCountersPerThread Thread="1" ActualRows="11320" ActualRowsRead="11320" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="5" ActualCPUms="5" ActualScans="1" ActualLogicalReads="1386" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  133. <RunTimeCountersPerThread Thread="2" ActualRows="14801" ActualRowsRead="14801" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="7" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1812" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  134. <RunTimeCountersPerThread Thread="3" ActualRows="13020" ActualRowsRead="13020" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1588" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  135. <RunTimeCountersPerThread Thread="4" ActualRows="12475" ActualRowsRead="12475" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1513" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  136. <RunTimeCountersPerThread Thread="5" ActualRows="12536" ActualRowsRead="12536" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="5" ActualScans="1" ActualLogicalReads="1576" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  137. <RunTimeCountersPerThread Thread="6" ActualRows="13774" ActualRowsRead="13774" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1687" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  138. <RunTimeCountersPerThread Thread="7" ActualRows="10419" ActualRowsRead="10419" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="4" ActualCPUms="4" ActualScans="1" ActualLogicalReads="1275" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  139. <RunTimeCountersPerThread Thread="8" ActualRows="11945" ActualRowsRead="11945" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="5" ActualScans="1" ActualLogicalReads="1453" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  140. <RunTimeCountersPerThread Thread="9" ActualRows="13966" ActualRowsRead="13966" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="9" ActualCPUms="8" ActualScans="1" ActualLogicalReads="1719" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  141. <RunTimeCountersPerThread Thread="10" ActualRows="16518" ActualRowsRead="16518" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="2029" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  142. <RunTimeCountersPerThread Thread="11" ActualRows="10379" ActualRowsRead="10379" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="5" ActualCPUms="4" ActualScans="1" ActualLogicalReads="1271" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  143. <RunTimeCountersPerThread Thread="12" ActualRows="11418" ActualRowsRead="11418" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="5" ActualCPUms="5" ActualScans="1" ActualLogicalReads="1400" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  144. <RunTimeCountersPerThread Thread="13" ActualRows="13326" ActualRowsRead="13326" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1630" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  145. <RunTimeCountersPerThread Thread="14" ActualRows="11723" ActualRowsRead="11723" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="5" ActualScans="1" ActualLogicalReads="1454" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  146. <RunTimeCountersPerThread Thread="15" ActualRows="12768" ActualRowsRead="12768" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1576" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  147. <RunTimeCountersPerThread Thread="16" ActualRows="12526" ActualRowsRead="12526" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="5" ActualScans="1" ActualLogicalReads="1537" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  148. <RunTimeCountersPerThread Thread="17" ActualRows="12660" ActualRowsRead="12660" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1528" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  149. <RunTimeCountersPerThread Thread="18" ActualRows="12774" ActualRowsRead="12774" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1571" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  150. <RunTimeCountersPerThread Thread="19" ActualRows="12294" ActualRowsRead="12294" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1507" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  151. <RunTimeCountersPerThread Thread="20" ActualRows="14327" ActualRowsRead="14327" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1754" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  152. <RunTimeCountersPerThread Thread="21" ActualRows="12575" ActualRowsRead="12575" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="5" ActualScans="1" ActualLogicalReads="1544" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  153. <RunTimeCountersPerThread Thread="22" ActualRows="13384" ActualRowsRead="13384" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="5" ActualScans="1" ActualLogicalReads="1649" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  154. <RunTimeCountersPerThread Thread="23" ActualRows="13659" ActualRowsRead="13659" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1652" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  155. <RunTimeCountersPerThread Thread="24" ActualRows="13484" ActualRowsRead="13484" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="6" ActualCPUms="6" ActualScans="1" ActualLogicalReads="1642" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  156. <RunTimeCountersPerThread Thread="25" ActualRows="1535" ActualRowsRead="1535" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="1" ActualCPUms="0" ActualScans="1" ActualLogicalReads="194" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  157. </RunTimeInformation>
  158. <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  159. <DefinedValues>
  160. <DefinedValue>
  161. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Column="RETAIL_ID" />
  162. </DefinedValue>
  163. <DefinedValue>
  164. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Column="SG_CHAIN_DESC" />
  165. </DefinedValue>
  166. <DefinedValue>
  167. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Column="SG_CHAIN_CODE" />
  168. </DefinedValue>
  169. </DefinedValues>
  170. <Object Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_STORE]" Index="[IX_DIM_STORE]" IndexKind="Clustered" Storage="RowStore" />
  171. </IndexScan>
  172. </RelOp>
  173. </ComputeScalar>
  174. </RelOp>
  175. </Update>
  176. </RelOp>
  177. </Parallelism>
  178. </RelOp>
  179. </QueryPlan>
  180. </StmtSimple>
  181. </Statements>
  182. </Batch>
  183. <Batch>
  184. <Statements>
  185. <StmtSimple StatementCompId="3" StatementEstRows="1097" StatementId="2" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="0.538625" StatementText="WITH G_VWDIM_PROD_HIER_STORE0 AS (&#xD;&#xA; Select L_VWDIM_PROD_HIER_STORE0.&quot;BRAND_DESC&quot; As &quot;BRAND_DESC&quot;, L_VWDIM_PROD_HIER_STORE0.&quot;BRAND_ID&quot; As &quot;BRAND_ID&quot;, L_VWDIM_PROD_HIER_STORE0.&quot;PROD_HIER_ID&quot; As &quot;PROD_HIER_ID_STORE&quot;, L_VWDIM_PROD_HIER_STORE0.&quot;PROD_HIER_LEVEL&quot; As &quot;PROD_HIER_LEVEL_STORE&quot;&#xD;&#xA; From dbo.vwDIM_PROD_HIER L_VWDIM_PROD_HIER_STORE0&#xD;&#xA; Where ( L_VWDIM_PROD_HIER_STORE0.&quot;PROD_HIER_LEVEL&quot; = 3) &#xD;&#xA; Group by L_VWDIM_PROD_HIER_STORE0.&quot;BRAND_DESC&quot; , L_VWDIM_PROD_HIER_STORE0.&quot;BRAND_ID&quot; , L_VWDIM_PROD_HIER_STORE0.&quot;PROD_HIER_ID&quot; , L_VWDIM_PROD_HIER_STORE0.&quot;PROD_HIER_LEVEL&quot; &#xD;&#xA;)&#xD;&#xA;Select *&#xD;&#xA;Into #VWDIM_PROD_HIER_STORE0&#xD;&#xA;From G_VWDIM_PROD_HIER_STORE0" StatementType="SELECT INTO" QueryHash="0x54C223592B5C0B60" QueryPlanHash="0x9FBA2A05B1C112FF" RetrievedFromCache="true" SecurityPolicyApplied="false">
  186. <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
  187. <QueryPlan DegreeOfParallelism="1" CachedPlanSize="56" CompileTime="1" CompileCPU="1" CompileMemory="496">
  188. <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
  189. <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="536870" EstimatedPagesCached="838860" EstimatedAvailableDegreeOfParallelism="12" MaxCompileMemory="95651768" />
  190. <QueryTimeStats CpuTime="3" ElapsedTime="3" />
  191. <RelOp AvgRowSize="9" EstimateCPU="0.001097" EstimateIO="0.06477" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1097" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="0.538625">
  192. <OutputList />
  193. <RunTimeInformation>
  194. <RunTimeCountersPerThread Thread="0" ActualRows="1097" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="2" ActualCPUms="2" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  195. </RunTimeInformation>
  196. <Update DMLRequestSort="false">
  197. <Object Table="[#VWDIM_PROD_HIER_STORE0]" Storage="RowStore" />
  198. <SetPredicate>
  199. <ScalarOperator ScalarString="[#VWDIM_PROD_HIER_STORE0].[BRAND_DESC] = [STG_AGG].[dbo].[DIM_PROD_HIER].[BRAND_DESC],[#VWDIM_PROD_HIER_STORE0].[BRAND_ID] = [STG_AGG].[dbo].[DIM_PROD_HIER].[BRAND_ID],[#VWDIM_PROD_HIER_STORE0].[PROD_HIER_ID_STORE] = [Expr1005],[#VWDIM_PROD_HIER_STORE0].[PROD_HIER_LEVEL_STORE] = [STG_AGG].[dbo].[DIM_PROD_HIER].[PROD_HIER_LEVEL]">
  200. <ScalarExpressionList>
  201. <ScalarOperator>
  202. <MultipleAssign>
  203. <Assign>
  204. <ColumnReference Table="[#VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  205. <ScalarOperator>
  206. <Identifier>
  207. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="BRAND_DESC" />
  208. </Identifier>
  209. </ScalarOperator>
  210. </Assign>
  211. <Assign>
  212. <ColumnReference Table="[#VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  213. <ScalarOperator>
  214. <Identifier>
  215. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="BRAND_ID" />
  216. </Identifier>
  217. </ScalarOperator>
  218. </Assign>
  219. <Assign>
  220. <ColumnReference Table="[#VWDIM_PROD_HIER_STORE0]" Column="PROD_HIER_ID_STORE" />
  221. <ScalarOperator>
  222. <Identifier>
  223. <ColumnReference Column="Expr1005" />
  224. </Identifier>
  225. </ScalarOperator>
  226. </Assign>
  227. <Assign>
  228. <ColumnReference Table="[#VWDIM_PROD_HIER_STORE0]" Column="PROD_HIER_LEVEL_STORE" />
  229. <ScalarOperator>
  230. <Identifier>
  231. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="PROD_HIER_LEVEL" />
  232. </Identifier>
  233. </ScalarOperator>
  234. </Assign>
  235. </MultipleAssign>
  236. </ScalarOperator>
  237. </ScalarExpressionList>
  238. </ScalarOperator>
  239. </SetPredicate>
  240. <RelOp AvgRowSize="68" EstimateCPU="0.0001097" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1097" LogicalOp="Compute Scalar" NodeId="1" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.472758">
  241. <OutputList>
  242. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="PROD_HIER_LEVEL" />
  243. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="BRAND_ID" />
  244. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="BRAND_DESC" />
  245. <ColumnReference Column="Expr1005" />
  246. </OutputList>
  247. <ComputeScalar>
  248. <DefinedValues>
  249. <DefinedValue>
  250. <ColumnReference Column="Expr1005" />
  251. <ScalarOperator ScalarString="setidentity([STG_AGG].[dbo].[DIM_PROD_HIER].[PROD_HIER_ID],(-7),(0),N'#VWDIM_PROD_HIER_STORE0')">
  252. <Intrinsic FunctionName="setidentity">
  253. <ScalarOperator>
  254. <Identifier>
  255. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="PROD_HIER_ID" />
  256. </Identifier>
  257. </ScalarOperator>
  258. <ScalarOperator>
  259. <Const ConstValue="(-7)" />
  260. </ScalarOperator>
  261. <ScalarOperator>
  262. <Const ConstValue="(0)" />
  263. </ScalarOperator>
  264. <ScalarOperator>
  265. <Const ConstValue="N'#VWDIM_PROD_HIER_STORE0'" />
  266. </ScalarOperator>
  267. </Intrinsic>
  268. </ScalarOperator>
  269. </DefinedValue>
  270. </DefinedValues>
  271. <RelOp AvgRowSize="68" EstimateCPU="0.0247574" EstimateIO="0.443866" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1097" EstimatedRowsRead="22364" LogicalOp="Clustered Index Scan" NodeId="2" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.468623" TableCardinality="22364">
  272. <OutputList>
  273. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="PROD_HIER_ID" />
  274. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="PROD_HIER_LEVEL" />
  275. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="BRAND_ID" />
  276. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="BRAND_DESC" />
  277. </OutputList>
  278. <RunTimeInformation>
  279. <RunTimeCountersPerThread Thread="0" ActualRows="1097" ActualRowsRead="22364" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="2" ActualCPUms="2" ActualScans="1" ActualLogicalReads="598" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  280. </RunTimeInformation>
  281. <IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  282. <DefinedValues>
  283. <DefinedValue>
  284. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="PROD_HIER_ID" />
  285. </DefinedValue>
  286. <DefinedValue>
  287. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="PROD_HIER_LEVEL" />
  288. </DefinedValue>
  289. <DefinedValue>
  290. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="BRAND_ID" />
  291. </DefinedValue>
  292. <DefinedValue>
  293. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="BRAND_DESC" />
  294. </DefinedValue>
  295. </DefinedValues>
  296. <Object Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Index="[PK_DIM_PROD_HIER]" IndexKind="Clustered" Storage="RowStore" />
  297. <Predicate>
  298. <ScalarOperator ScalarString="[STG_AGG].[dbo].[DIM_PROD_HIER].[PROD_HIER_LEVEL]=(3)">
  299. <Compare CompareOp="EQ">
  300. <ScalarOperator>
  301. <Identifier>
  302. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_PROD_HIER]" Column="PROD_HIER_LEVEL" />
  303. </Identifier>
  304. </ScalarOperator>
  305. <ScalarOperator>
  306. <Const ConstValue="(3)" />
  307. </ScalarOperator>
  308. </Compare>
  309. </ScalarOperator>
  310. </Predicate>
  311. </IndexScan>
  312. </RelOp>
  313. </ComputeScalar>
  314. </RelOp>
  315. </Update>
  316. </RelOp>
  317. </QueryPlan>
  318. </StmtSimple>
  319. </Statements>
  320. </Batch>
  321. <Batch>
  322. <Statements>
  323. <StmtSimple StatementCompId="5" StatementEstRows="6.57267" StatementId="3" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="130" StatementSubTreeCost="0.0318869" StatementText="WITH G_DIM_TIME0 AS (&#xD;&#xA; Select L_DIM_TIME0.&quot;BEFORE_WEEKS&quot; As &quot;BEFORE_WEEKS&quot;, L_DIM_TIME0.&quot;PERIOD_CODE&quot; As &quot;PERIOD_CODE&quot;, L_DIM_TIME0.&quot;PERIOD_DATE&quot; As &quot;RELATIVE_WEEK&quot;&#xD;&#xA; From dbo.vwDim_Time L_DIM_TIME0&#xD;&#xA; Where ( L_DIM_TIME0.&quot;BEFORE_WEEKS&quot; = 0) &#xD;&#xA; Group by L_DIM_TIME0.&quot;BEFORE_WEEKS&quot; , L_DIM_TIME0.&quot;PERIOD_CODE&quot; , L_DIM_TIME0.&quot;PERIOD_DATE&quot; &#xD;&#xA;)&#xD;&#xA;Select *&#xD;&#xA;Into #DIM_TIME0&#xD;&#xA;From G_DIM_TIME0" StatementType="SELECT INTO" QueryHash="0x17976DBECBB56051" QueryPlanHash="0xC9EB87061BC7DE34" RetrievedFromCache="true" SecurityPolicyApplied="false">
  324. <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
  325. <QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="72" CompileTime="3" CompileCPU="3" CompileMemory="872">
  326. <MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="544" RequiredMemory="512" DesiredMemory="544" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="0" MaxQueryMemory="24969208" />
  327. <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="536870" EstimatedPagesCached="838860" EstimatedAvailableDegreeOfParallelism="12" MaxCompileMemory="95651768" />
  328. <QueryTimeStats CpuTime="1" ElapsedTime="0" />
  329. <RelOp AvgRowSize="9" EstimateCPU="6.57267E-06" EstimateIO="0.01" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6.57267" LogicalOp="Insert" NodeId="0" Parallel="false" PhysicalOp="Table Insert" EstimatedTotalSubtreeCost="0.0318869">
  330. <OutputList />
  331. <RunTimeInformation>
  332. <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  333. </RunTimeInformation>
  334. <Update DMLRequestSort="false">
  335. <Object Table="[#DIM_TIME0]" Storage="RowStore" />
  336. <SetPredicate>
  337. <ScalarOperator ScalarString="[#DIM_TIME0].[BEFORE_WEEKS] = [Expr1007],[#DIM_TIME0].[PERIOD_CODE] = [STG_AGG].[dbo].[DIM_TIME].[PERIOD_CODE] as [A].[PERIOD_CODE],[#DIM_TIME0].[RELATIVE_WEEK] = [STG_AGG].[dbo].[DIM_TIME].[PERIOD_DATE] as [A].[PERIOD_DATE]">
  338. <ScalarExpressionList>
  339. <ScalarOperator>
  340. <MultipleAssign>
  341. <Assign>
  342. <ColumnReference Table="[#DIM_TIME0]" Column="BEFORE_WEEKS" />
  343. <ScalarOperator>
  344. <Identifier>
  345. <ColumnReference Column="Expr1007" />
  346. </Identifier>
  347. </ScalarOperator>
  348. </Assign>
  349. <Assign>
  350. <ColumnReference Table="[#DIM_TIME0]" Column="PERIOD_CODE" />
  351. <ScalarOperator>
  352. <Identifier>
  353. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  354. </Identifier>
  355. </ScalarOperator>
  356. </Assign>
  357. <Assign>
  358. <ColumnReference Table="[#DIM_TIME0]" Column="RELATIVE_WEEK" />
  359. <ScalarOperator>
  360. <Identifier>
  361. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_DATE" />
  362. </Identifier>
  363. </ScalarOperator>
  364. </Assign>
  365. </MultipleAssign>
  366. </ScalarOperator>
  367. </ScalarExpressionList>
  368. </ScalarOperator>
  369. </SetPredicate>
  370. <RelOp AvgRowSize="16" EstimateCPU="0.000127869" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6.57267" LogicalOp="Distinct Sort" NodeId="1" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0218804">
  371. <OutputList>
  372. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  373. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_DATE" />
  374. <ColumnReference Column="Expr1007" />
  375. </OutputList>
  376. <MemoryFractions Input="1" Output="1" />
  377. <RunTimeInformation>
  378. <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="1024" OutputMemoryGrant="640" UsedMemoryGrant="0" />
  379. </RunTimeInformation>
  380. <Sort Distinct="true">
  381. <OrderBy>
  382. <OrderByColumn Ascending="true">
  383. <ColumnReference Column="Expr1007" />
  384. </OrderByColumn>
  385. <OrderByColumn Ascending="true">
  386. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  387. </OrderByColumn>
  388. <OrderByColumn Ascending="true">
  389. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_DATE" />
  390. </OrderByColumn>
  391. </OrderBy>
  392. <RelOp AvgRowSize="16" EstimateCPU="6.57267E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6.57267" LogicalOp="Compute Scalar" NodeId="2" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0104912">
  393. <OutputList>
  394. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  395. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_DATE" />
  396. <ColumnReference Column="Expr1007" />
  397. </OutputList>
  398. <ComputeScalar>
  399. <DefinedValues>
  400. <DefinedValue>
  401. <ColumnReference Column="Expr1007" />
  402. <ScalarOperator ScalarString="[STG_AGG].[dbo].[DIM_FACTACTIVEPERIOD].[PERIOD_CODE] as [B].[PERIOD_CODE]-[Expr1009]">
  403. <Arithmetic Operation="SUB">
  404. <ScalarOperator>
  405. <Identifier>
  406. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_FACTACTIVEPERIOD]" Alias="[B]" Column="PERIOD_CODE" />
  407. </Identifier>
  408. </ScalarOperator>
  409. <ScalarOperator>
  410. <Identifier>
  411. <ColumnReference Column="Expr1009" />
  412. </Identifier>
  413. </ScalarOperator>
  414. </Arithmetic>
  415. </ScalarOperator>
  416. </DefinedValue>
  417. </DefinedValues>
  418. <RelOp AvgRowSize="20" EstimateCPU="6.57267E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6.57267" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0104906">
  419. <OutputList>
  420. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  421. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_DATE" />
  422. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_FACTACTIVEPERIOD]" Alias="[B]" Column="PERIOD_CODE" />
  423. <ColumnReference Column="Expr1009" />
  424. </OutputList>
  425. <ComputeScalar>
  426. <DefinedValues>
  427. <DefinedValue>
  428. <ColumnReference Column="Expr1009" />
  429. <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[STG_AGG].[dbo].[DIM_TIME].[PERIOD_CODE] as [A].[PERIOD_CODE],0)">
  430. <Convert DataType="int" Style="0" Implicit="true">
  431. <ScalarOperator>
  432. <Identifier>
  433. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  434. </Identifier>
  435. </ScalarOperator>
  436. </Convert>
  437. </ScalarOperator>
  438. </DefinedValue>
  439. </DefinedValues>
  440. <RelOp AvgRowSize="16" EstimateCPU="2.74738E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6.57267" LogicalOp="Inner Join" NodeId="4" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0104899">
  441. <OutputList>
  442. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  443. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_DATE" />
  444. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_FACTACTIVEPERIOD]" Alias="[B]" Column="PERIOD_CODE" />
  445. </OutputList>
  446. <RunTimeInformation>
  447. <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  448. </RunTimeInformation>
  449. <NestedLoops Optimized="false">
  450. <OuterReferences>
  451. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_FACTACTIVEPERIOD]" Alias="[B]" Column="PERIOD_CODE" />
  452. </OuterReferences>
  453. <RelOp AvgRowSize="11" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Table Scan" NodeId="5" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1">
  454. <OutputList>
  455. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_FACTACTIVEPERIOD]" Alias="[B]" Column="PERIOD_CODE" />
  456. </OutputList>
  457. <RunTimeInformation>
  458. <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  459. </RunTimeInformation>
  460. <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  461. <DefinedValues>
  462. <DefinedValue>
  463. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_FACTACTIVEPERIOD]" Alias="[B]" Column="PERIOD_CODE" />
  464. </DefinedValue>
  465. </DefinedValues>
  466. <Object Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_FACTACTIVEPERIOD]" Alias="[B]" IndexKind="Heap" Storage="RowStore" />
  467. </TableScan>
  468. </RelOp>
  469. <RelOp AvgRowSize="12" EstimateCPU="0.0002703" EstimateIO="0.0068287" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6.57267" EstimatedRowsRead="103" LogicalOp="Inner Join" NodeId="6" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.007099" TableCardinality="120">
  470. <OutputList>
  471. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  472. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_DATE" />
  473. </OutputList>
  474. <RunTimeInformation>
  475. <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  476. </RunTimeInformation>
  477. <NestedLoops Optimized="false">
  478. <OuterReferences>
  479. <ColumnReference Column="Expr1011" />
  480. <ColumnReference Column="Expr1012" />
  481. <ColumnReference Column="Expr1010" />
  482. </OuterReferences>
  483. <RelOp AvgRowSize="15" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Compute Scalar" NodeId="7" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0">
  484. <OutputList>
  485. <ColumnReference Column="Expr1011" />
  486. <ColumnReference Column="Expr1012" />
  487. <ColumnReference Column="Expr1010" />
  488. </OutputList>
  489. <RunTimeInformation>
  490. <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  491. </RunTimeInformation>
  492. <ComputeScalar>
  493. <DefinedValues>
  494. <DefinedValue>
  495. <ValueVector>
  496. <ColumnReference Column="Expr1011" />
  497. <ColumnReference Column="Expr1012" />
  498. <ColumnReference Column="Expr1010" />
  499. </ValueVector>
  500. <ScalarOperator ScalarString="GetRangeWithMismatchedTypes(NULL,[STG_AGG].[dbo].[DIM_FACTACTIVEPERIOD].[PERIOD_CODE] as [B].[PERIOD_CODE],(42))">
  501. <Intrinsic FunctionName="GetRangeWithMismatchedTypes">
  502. <ScalarOperator>
  503. <Const ConstValue="NULL" />
  504. </ScalarOperator>
  505. <ScalarOperator>
  506. <Identifier>
  507. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_FACTACTIVEPERIOD]" Alias="[B]" Column="PERIOD_CODE" />
  508. </Identifier>
  509. </ScalarOperator>
  510. <ScalarOperator>
  511. <Const ConstValue="(42)" />
  512. </ScalarOperator>
  513. </Intrinsic>
  514. </ScalarOperator>
  515. </DefinedValue>
  516. </DefinedValues>
  517. <RelOp AvgRowSize="0" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Constant Scan" NodeId="8" Parallel="false" PhysicalOp="Constant Scan" EstimatedTotalSubtreeCost="0">
  518. <OutputList />
  519. <RunTimeInformation>
  520. <RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  521. </RunTimeInformation>
  522. <ConstantScan />
  523. </RelOp>
  524. </ComputeScalar>
  525. </RelOp>
  526. <RelOp AvgRowSize="12" EstimateCPU="0.0002703" EstimateIO="0.0068287" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="6.57267" EstimatedRowsRead="103" LogicalOp="Clustered Index Seek" NodeId="9" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.007099" TableCardinality="120">
  527. <OutputList>
  528. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  529. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_DATE" />
  530. </OutputList>
  531. <RunTimeInformation>
  532. <RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="120" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="8" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  533. </RunTimeInformation>
  534. <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  535. <DefinedValues>
  536. <DefinedValue>
  537. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  538. </DefinedValue>
  539. <DefinedValue>
  540. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_DATE" />
  541. </DefinedValue>
  542. </DefinedValues>
  543. <Object Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Index="[IX_DIM_TIME]" Alias="[A]" IndexKind="Clustered" Storage="RowStore" />
  544. <SeekPredicates>
  545. <SeekPredicateNew>
  546. <SeekKeys>
  547. <StartRange ScanType="GT">
  548. <RangeColumns>
  549. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  550. </RangeColumns>
  551. <RangeExpressions>
  552. <ScalarOperator ScalarString="[Expr1011]">
  553. <Identifier>
  554. <ColumnReference Column="Expr1011" />
  555. </Identifier>
  556. </ScalarOperator>
  557. </RangeExpressions>
  558. </StartRange>
  559. <EndRange ScanType="LT">
  560. <RangeColumns>
  561. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  562. </RangeColumns>
  563. <RangeExpressions>
  564. <ScalarOperator ScalarString="[Expr1012]">
  565. <Identifier>
  566. <ColumnReference Column="Expr1012" />
  567. </Identifier>
  568. </ScalarOperator>
  569. </RangeExpressions>
  570. </EndRange>
  571. </SeekKeys>
  572. </SeekPredicateNew>
  573. </SeekPredicates>
  574. <Predicate>
  575. <ScalarOperator ScalarString="([STG_AGG].[dbo].[DIM_FACTACTIVEPERIOD].[PERIOD_CODE] as [B].[PERIOD_CODE]-CONVERT_IMPLICIT(int,[STG_AGG].[dbo].[DIM_TIME].[PERIOD_CODE] as [A].[PERIOD_CODE],0))=(0)">
  576. <Compare CompareOp="EQ">
  577. <ScalarOperator>
  578. <Arithmetic Operation="SUB">
  579. <ScalarOperator>
  580. <Identifier>
  581. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_FACTACTIVEPERIOD]" Alias="[B]" Column="PERIOD_CODE" />
  582. </Identifier>
  583. </ScalarOperator>
  584. <ScalarOperator>
  585. <Convert DataType="int" Style="0" Implicit="true">
  586. <ScalarOperator>
  587. <Identifier>
  588. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[DIM_TIME]" Alias="[A]" Column="PERIOD_CODE" />
  589. </Identifier>
  590. </ScalarOperator>
  591. </Convert>
  592. </ScalarOperator>
  593. </Arithmetic>
  594. </ScalarOperator>
  595. <ScalarOperator>
  596. <Const ConstValue="(0)" />
  597. </ScalarOperator>
  598. </Compare>
  599. </ScalarOperator>
  600. </Predicate>
  601. </IndexScan>
  602. </RelOp>
  603. </NestedLoops>
  604. </RelOp>
  605. </NestedLoops>
  606. </RelOp>
  607. </ComputeScalar>
  608. </RelOp>
  609. </ComputeScalar>
  610. </RelOp>
  611. </Sort>
  612. </RelOp>
  613. </Update>
  614. </RelOp>
  615. </QueryPlan>
  616. </StmtSimple>
  617. </Statements>
  618. </Batch>
  619. <Batch>
  620. <Statements>
  621. <StmtSimple StatementCompId="7" StatementEstRows="764327" StatementId="4" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="11.8115" StatementText="WITH G_VWAGG_PRODHIER_STORE0 AS (&#xD;&#xA; Select L_DIM_TIME0.&quot;BEFORE_WEEKS&quot; As &quot;BEFORE_WEEKS&quot;, L_VWDIM_PROD_HIER_STORE0.&quot;BRAND_DESC&quot; As &quot;BRAND_DESC&quot;, L_VWDIM_PROD_HIER_STORE0.&quot;BRAND_ID&quot; As &quot;BRAND_ID&quot;, L_DIM_TIME0.&quot;RELATIVE_WEEK&quot; As &quot;RELATIVE_WEEK&quot;, L_VWAGG_PRODHIER_STORE0.&quot;RETAIL_ID&quot; As &quot;RETAIL_ID&quot;, L_DIM_STORE0.&quot;SG_CHAIN_CODE&quot; As &quot;SG_CHAIN_CODE&quot;, L_DIM_STORE0.&quot;SG_CHAIN_DESC&quot; As &quot;SG_CHAIN_DESC&quot;, Sum( L_VWAGG_PRODHIER_STORE0.&quot;CONSUMER_UNITS_R52&quot; ) As &quot;CONSUMER_UNITS_52W_CY&quot;, Sum( L_VWAGG_PRODHIER_STORE0.&quot;EQ_VOLUME_R52&quot; ) As &quot;EQ_VOLUME_52W_CY&quot;, Max( L_VWAGG_PRODHIER_STORE0.&quot;STORE_SELLING_R52&quot; ) As &quot;STORES_RECEIVING_PRODUCT_52W_CY&quot;, Max( L_VWAGG_PRODHIER_STORE0.&quot;SKU_COUNT_R52&quot; ) As &quot;TDP_52W_CY&quot;&#xD;&#xA; From dbo.vwAGG_PRODHIER_STORE L_VWAGG_PRODHIER_STORE0&#xD;&#xA; Inner Join #DIM_STORE0 L_DIM_STORE0 On ( L_VWAGG_PRODHIER_STORE0.&quot;RETAIL_ID&quot; = L_DIM_STORE0.&quot;RETAIL_ID&quot; ) &#xD;&#xA; Inner Join #VWDIM_PROD_HIER_STORE0 L_VWDIM_PROD_HIER_STORE0 On ( ( L_VWAGG_PRODHIER_STORE0.&quot;PROD_HIER_ID&quot; = L_VWDIM_PROD_HIER_STORE0.&quot;PROD_HIER_ID_STORE&quot; ) AND ( L_VWAGG_PRODHIER_STORE0.&quot;PROD_HIER_LEVEL&quot; = L_VWDIM_PROD_HIER_STORE0.&quot;PROD_HIER_LEVEL_STORE&quot; ) ) &#xD;&#xA; Inner Join #DIM_TIME0 L_DIM_TIME0 On ( L_VWAGG_PRODHIER_STORE0.&quot;PERIOD_CODE&quot; = L_DIM_TIME0.&quot;PERIOD_CODE&quot; ) &#xD;&#xA; Where ( ( L_VWAGG_PRODHIER_STORE0.&quot;PROD_HIER_LEVEL&quot; = 3) AND ( L_VWAGG_PRODHIER_STORE0.&quot;TRANSSHIPMENT_IND&quot; = '0') ) &#xD;&#xA; Group by L_DIM_TIME0.&quot;BEFORE_WEEKS&quot; , L_VWDIM_PROD_HIER_STORE0.&quot;BRAND_DESC&quot; , L_VWDIM_PROD_HIER_STORE0.&quot;BRAND_ID&quot; , L_DIM_TIME0.&quot;RELATIVE_WEEK&quot; , L_VWAGG_PRODHIER_STORE0.&quot;RETAIL_ID&quot; , L_DIM_STORE0.&quot;SG_CHAIN_CODE&quot; , L_DIM_STORE0.&quot;SG_CHAIN_DESC&quot; &#xD;&#xA; Having ( ( Sum( L_VWAGG_PRODHIER_STORE0.&quot;CONSUMER_UNITS_R52&quot; ) &lt;&gt; 0) OR ( Sum( L_VWAGG_PRODHIER_STORE0.&quot;EQ_VOLUME_R52&quot; ) &lt;&gt; 0) OR ( Max( L_VWAGG_PRODHIER_STORE0.&quot;STORE_SELLING_R52&quot; ) &lt;&gt; 0) OR ( Max( L_VWAGG_PRODHIER_STORE0.&quot;SKU_COUNT_R52&quot; ) &lt;&gt; 0) ) &#xD;&#xA;),&#xD;&#xA;G_VWAGG_PRODHIER_STORE1 AS (&#xD;&#xA; Select L_VWAGG_PRODHIER_STORE0.&quot;BEFORE_WEEKS&quot; As &quot;BEFORE_WEEKS&quot;, L_VWAGG_PRODHIER_STORE0.&quot;BRAND_DESC&quot; As &quot;BRAND_DESC&quot;, L_VWAGG_PRODHIER_STORE0.&quot;BRAND_ID&quot; As &quot;BRAND_ID&quot;, L_VWAGG_PRODHIER_STORE0.&quot;RELATIVE_WEEK&quot; As &quot;RELATIVE_WEEK&quot;, L_VWAGG_PRODHIER_STORE0.&quot;SG_CHAIN_CODE&quot; As &quot;SG_CHAIN_CODE&quot;, L_VWAGG_PRODHIER_STORE0.&quot;SG_CHAIN_DESC&quot; As &quot;SG_CHAIN_DESC&quot;, Sum( L_VWAGG_PRODHIER_STORE0.&quot;CONSUMER_UNITS_52W_CY&quot; ) As &quot;CONSUMER_UNITS_52W_CY&quot;, Sum( L_VWAGG_PRODHIER_STORE0.&quot;EQ_VOLUME_52W_CY&quot; ) As &quot;EQ_VOLUME_52W_CY&quot;, Sum( L_VWAGG_PRODHIER_STORE0.&quot;STORES_RECEIVING_PRODUCT_52W_CY&quot; ) As &quot;STORES_RECEIVING_PRODUCT_52W_CY&quot;, Sum( L_VWAGG_PRODHIER_STORE0.&quot;TDP_52W_CY&quot; ) As &quot;TDP_52W_CY&quot;&#xD;&#xA; From G_VWAGG_PRODHIER_STORE0 L_VWAGG_PRODHIER_STORE0&#xD;&#xA; Group by L_VWAGG_PRODHIER_STORE0.&quot;BEFORE_WEEKS&quot; , L_VWAGG_PRODHIER_STORE0.&quot;BRAND_DESC&quot; , L_VWAGG_PRODHIER_STORE0.&quot;BRAND_ID&quot; , L_VWAGG_PRODHIER_STORE0.&quot;RELATIVE_WEEK&quot; , L_VWAGG_PRODHIER_STORE0.&quot;SG_CHAIN_CODE&quot; , L_VWAGG_PRODHIER_STORE0.&quot;SG_CHAIN_DESC&quot; &#xD;&#xA; Having (( L_VWAGG_PRODHIER_STORE0.&quot;BEFORE_WEEKS&quot; is not null) AND ( L_VWAGG_PRODHIER_STORE0.&quot;BRAND_DESC&quot; is not null) AND ( L_VWAGG_PRODHIER_STORE0.&quot;BRAND_ID&quot; is not null) AND ( L_VWAGG_PRODHIER_STORE0.&quot;RELATIVE_WEEK&quot; is not null) AND ( L_VWAGG_PRODHIER_STORE0.&quot;SG_CHAIN_CODE&quot; is not null) AND ( L_VWAGG_PRODHIER_STORE0.&quot;SG_CHAIN_DESC&quot; is not null)) &#xD;&#xA;)&#xD;&#xA;Select L_VWAGG_PRODHIER_STORE0.&quot;BEFORE_WEEKS&quot; As &quot;BEFORE_WEEKS&quot;, L_VWAGG_PRODHIER_STORE0.&quot;BRAND_DESC&quot; As &quot;BRAND_DESC&quot;, L_VWAGG_PRODHIER_STORE0.&quot;BRAND_ID&quot; As &quot;BRAND_ID&quot;, L_VWAGG_PRODHIER_STORE0.&quot;RELATIVE_WEEK&quot; As &quot;RELATIVE_WEEK&quot;, L_VWAGG_PRODHIER_STORE0.&quot;SG_CHAIN_CODE&quot; As &quot;SG_CHAIN_CODE&quot;, L_VWAGG_PRODHIER_STORE0.&quot;SG_CHAIN_DESC&quot; As &quot;SG_CHAIN_DESC&quot;,case when Coalesce(L_VWAGG_PRODHIER_STORE0.&quot;STORES_RECEIVING_PRODUCT_52W_CY&quot;,0)=0 then 0 else Coalesce(L_VWAGG_PRODHIER_STORE0.&quot;TDP_52W_CY&quot;,0)*1.0/Coalesce(L_VWAGG_PRODHIER_STORE0.&quot;STORES_RECEIVING_PRODUCT_52W_CY&quot;,0)end As &quot;AVERAGE_ITEMS_PER_STORE_52W_CY&quot;, L_VWAGG_PRODHIER_STORE0.&quot;CONSUMER_UNITS_52W_CY&quot; As &quot;CONSUMER_UNITS_52W_CY&quot;, L_VWAGG_PRODHIER_STO" StatementType="SELECT" QueryHash="0x91B1AD39F5B5894C" QueryPlanHash="0xF7872D25BED4DB23" RetrievedFromCache="true" SecurityPolicyApplied="false">
  622. <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
  623. <QueryPlan DegreeOfParallelism="2" MemoryGrant="1030264" CachedPlanSize="296" CompileTime="247" CompileCPU="119" CompileMemory="3864">
  624. <ThreadStat Branches="1" UsedThreads="3">
  625. <ThreadReservation NodeId="1" ReservedThreads="4" />
  626. </ThreadStat>
  627. <MemoryGrantInfo SerialRequiredMemory="9216" SerialDesiredMemory="1020960" RequiredMemory="18520" DesiredMemory="1030264" RequestedMemory="1030264" GrantWaitTime="0" GrantedMemory="1030264" MaxUsedMemory="315392" MaxQueryMemory="24969208" />
  628. <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="536870" EstimatedPagesCached="838860" EstimatedAvailableDegreeOfParallelism="12" MaxCompileMemory="95650552" />
  629. <WaitStats>
  630. <Wait WaitType="HTBUILD" WaitTimeMs="1" WaitCount="10" />
  631. <Wait WaitType="HTDELETE" WaitTimeMs="9" WaitCount="10" />
  632. </WaitStats>
  633. <QueryTimeStats CpuTime="4712" ElapsedTime="2376" />
  634. <RelOp AvgRowSize="113" EstimateCPU="0.0764327" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="764327" LogicalOp="Compute Scalar" NodeId="0" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="11.8115">
  635. <OutputList>
  636. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  637. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  638. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  639. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  640. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  641. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  642. <ColumnReference Column="Expr1030" />
  643. <ColumnReference Column="Expr1031" />
  644. <ColumnReference Column="Expr1032" />
  645. <ColumnReference Column="Expr1033" />
  646. <ColumnReference Column="Expr1034" />
  647. </OutputList>
  648. <ComputeScalar>
  649. <DefinedValues>
  650. <DefinedValue>
  651. <ColumnReference Column="Expr1034" />
  652. <ScalarOperator ScalarString="CASE WHEN CASE WHEN [Expr1032] IS NOT NULL THEN [Expr1032] ELSE (0) END=(0) THEN (0.000000000000) ELSE CONVERT_IMPLICIT(numeric(10,0),CASE WHEN [Expr1033] IS NOT NULL THEN [Expr1033] ELSE (0) END,0)*(1.0)/CONVERT_IMPLICIT(numeric(10,0),CASE WHEN [Expr1032] IS NOT NULL THEN [Expr1032] ELSE (0) END,0) END">
  653. <IF>
  654. <Condition>
  655. <ScalarOperator>
  656. <Compare CompareOp="EQ">
  657. <ScalarOperator>
  658. <IF>
  659. <Condition>
  660. <ScalarOperator>
  661. <Compare CompareOp="IS NOT">
  662. <ScalarOperator>
  663. <Identifier>
  664. <ColumnReference Column="Expr1032" />
  665. </Identifier>
  666. </ScalarOperator>
  667. <ScalarOperator>
  668. <Const ConstValue="NULL" />
  669. </ScalarOperator>
  670. </Compare>
  671. </ScalarOperator>
  672. </Condition>
  673. <Then>
  674. <ScalarOperator>
  675. <Identifier>
  676. <ColumnReference Column="Expr1032" />
  677. </Identifier>
  678. </ScalarOperator>
  679. </Then>
  680. <Else>
  681. <ScalarOperator>
  682. <Const ConstValue="(0)" />
  683. </ScalarOperator>
  684. </Else>
  685. </IF>
  686. </ScalarOperator>
  687. <ScalarOperator>
  688. <Const ConstValue="(0)" />
  689. </ScalarOperator>
  690. </Compare>
  691. </ScalarOperator>
  692. </Condition>
  693. <Then>
  694. <ScalarOperator>
  695. <Const ConstValue="(0.000000000000)" />
  696. </ScalarOperator>
  697. </Then>
  698. <Else>
  699. <ScalarOperator>
  700. <Arithmetic Operation="DIV">
  701. <ScalarOperator>
  702. <Arithmetic Operation="MULT">
  703. <ScalarOperator>
  704. <Convert DataType="numeric" Precision="10" Scale="0" Style="0" Implicit="true">
  705. <ScalarOperator>
  706. <IF>
  707. <Condition>
  708. <ScalarOperator>
  709. <Compare CompareOp="IS NOT">
  710. <ScalarOperator>
  711. <Identifier>
  712. <ColumnReference Column="Expr1033" />
  713. </Identifier>
  714. </ScalarOperator>
  715. <ScalarOperator>
  716. <Const ConstValue="NULL" />
  717. </ScalarOperator>
  718. </Compare>
  719. </ScalarOperator>
  720. </Condition>
  721. <Then>
  722. <ScalarOperator>
  723. <Identifier>
  724. <ColumnReference Column="Expr1033" />
  725. </Identifier>
  726. </ScalarOperator>
  727. </Then>
  728. <Else>
  729. <ScalarOperator>
  730. <Const ConstValue="(0)" />
  731. </ScalarOperator>
  732. </Else>
  733. </IF>
  734. </ScalarOperator>
  735. </Convert>
  736. </ScalarOperator>
  737. <ScalarOperator>
  738. <Const ConstValue="(1.0)" />
  739. </ScalarOperator>
  740. </Arithmetic>
  741. </ScalarOperator>
  742. <ScalarOperator>
  743. <Convert DataType="numeric" Precision="10" Scale="0" Style="0" Implicit="true">
  744. <ScalarOperator>
  745. <IF>
  746. <Condition>
  747. <ScalarOperator>
  748. <Compare CompareOp="IS NOT">
  749. <ScalarOperator>
  750. <Identifier>
  751. <ColumnReference Column="Expr1032" />
  752. </Identifier>
  753. </ScalarOperator>
  754. <ScalarOperator>
  755. <Const ConstValue="NULL" />
  756. </ScalarOperator>
  757. </Compare>
  758. </ScalarOperator>
  759. </Condition>
  760. <Then>
  761. <ScalarOperator>
  762. <Identifier>
  763. <ColumnReference Column="Expr1032" />
  764. </Identifier>
  765. </ScalarOperator>
  766. </Then>
  767. <Else>
  768. <ScalarOperator>
  769. <Const ConstValue="(0)" />
  770. </ScalarOperator>
  771. </Else>
  772. </IF>
  773. </ScalarOperator>
  774. </Convert>
  775. </ScalarOperator>
  776. </Arithmetic>
  777. </ScalarOperator>
  778. </Else>
  779. </IF>
  780. </ScalarOperator>
  781. </DefinedValue>
  782. </DefinedValues>
  783. <RelOp AvgRowSize="100" EstimateCPU="0.815852" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="764327" LogicalOp="Gather Streams" NodeId="1" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="11.7351">
  784. <OutputList>
  785. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  786. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  787. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  788. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  789. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  790. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  791. <ColumnReference Column="Expr1030" />
  792. <ColumnReference Column="Expr1031" />
  793. <ColumnReference Column="Expr1032" />
  794. <ColumnReference Column="Expr1033" />
  795. </OutputList>
  796. <RunTimeInformation>
  797. <RunTimeCountersPerThread Thread="0" ActualRows="27162" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="2331" ActualCPUms="3" />
  798. </RunTimeInformation>
  799. <Parallelism>
  800. <RelOp AvgRowSize="100" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="764327" LogicalOp="Compute Scalar" NodeId="2" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="10.9186">
  801. <OutputList>
  802. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  803. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  804. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  805. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  806. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  807. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  808. <ColumnReference Column="Expr1030" />
  809. <ColumnReference Column="Expr1031" />
  810. <ColumnReference Column="Expr1032" />
  811. <ColumnReference Column="Expr1033" />
  812. </OutputList>
  813. <RunTimeInformation>
  814. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  815. <RunTimeCountersPerThread Thread="1" ActualRows="13617" Batches="16" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  816. <RunTimeCountersPerThread Thread="2" ActualRows="13545" Batches="16" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  817. </RunTimeInformation>
  818. <ComputeScalar>
  819. <DefinedValues>
  820. <DefinedValue>
  821. <ColumnReference Column="Expr1030" />
  822. <ScalarOperator ScalarString="CASE WHEN [Expr1085]=(0) THEN NULL ELSE [Expr1086] END">
  823. <IF>
  824. <Condition>
  825. <ScalarOperator>
  826. <Compare CompareOp="EQ">
  827. <ScalarOperator>
  828. <Identifier>
  829. <ColumnReference Column="Expr1085" />
  830. </Identifier>
  831. </ScalarOperator>
  832. <ScalarOperator>
  833. <Const ConstValue="(0)" />
  834. </ScalarOperator>
  835. </Compare>
  836. </ScalarOperator>
  837. </Condition>
  838. <Then>
  839. <ScalarOperator>
  840. <Const ConstValue="NULL" />
  841. </ScalarOperator>
  842. </Then>
  843. <Else>
  844. <ScalarOperator>
  845. <Identifier>
  846. <ColumnReference Column="Expr1086" />
  847. </Identifier>
  848. </ScalarOperator>
  849. </Else>
  850. </IF>
  851. </ScalarOperator>
  852. </DefinedValue>
  853. <DefinedValue>
  854. <ColumnReference Column="Expr1031" />
  855. <ScalarOperator ScalarString="CASE WHEN [Expr1087]=(0) THEN NULL ELSE [Expr1088] END">
  856. <IF>
  857. <Condition>
  858. <ScalarOperator>
  859. <Compare CompareOp="EQ">
  860. <ScalarOperator>
  861. <Identifier>
  862. <ColumnReference Column="Expr1087" />
  863. </Identifier>
  864. </ScalarOperator>
  865. <ScalarOperator>
  866. <Const ConstValue="(0)" />
  867. </ScalarOperator>
  868. </Compare>
  869. </ScalarOperator>
  870. </Condition>
  871. <Then>
  872. <ScalarOperator>
  873. <Const ConstValue="NULL" />
  874. </ScalarOperator>
  875. </Then>
  876. <Else>
  877. <ScalarOperator>
  878. <Identifier>
  879. <ColumnReference Column="Expr1088" />
  880. </Identifier>
  881. </ScalarOperator>
  882. </Else>
  883. </IF>
  884. </ScalarOperator>
  885. </DefinedValue>
  886. <DefinedValue>
  887. <ColumnReference Column="Expr1032" />
  888. <ScalarOperator ScalarString="CASE WHEN [Expr1089]=(0) THEN NULL ELSE [Expr1090] END">
  889. <IF>
  890. <Condition>
  891. <ScalarOperator>
  892. <Compare CompareOp="EQ">
  893. <ScalarOperator>
  894. <Identifier>
  895. <ColumnReference Column="Expr1089" />
  896. </Identifier>
  897. </ScalarOperator>
  898. <ScalarOperator>
  899. <Const ConstValue="(0)" />
  900. </ScalarOperator>
  901. </Compare>
  902. </ScalarOperator>
  903. </Condition>
  904. <Then>
  905. <ScalarOperator>
  906. <Const ConstValue="NULL" />
  907. </ScalarOperator>
  908. </Then>
  909. <Else>
  910. <ScalarOperator>
  911. <Identifier>
  912. <ColumnReference Column="Expr1090" />
  913. </Identifier>
  914. </ScalarOperator>
  915. </Else>
  916. </IF>
  917. </ScalarOperator>
  918. </DefinedValue>
  919. <DefinedValue>
  920. <ColumnReference Column="Expr1033" />
  921. <ScalarOperator ScalarString="CASE WHEN [Expr1091]=(0) THEN NULL ELSE [Expr1092] END">
  922. <IF>
  923. <Condition>
  924. <ScalarOperator>
  925. <Compare CompareOp="EQ">
  926. <ScalarOperator>
  927. <Identifier>
  928. <ColumnReference Column="Expr1091" />
  929. </Identifier>
  930. </ScalarOperator>
  931. <ScalarOperator>
  932. <Const ConstValue="(0)" />
  933. </ScalarOperator>
  934. </Compare>
  935. </ScalarOperator>
  936. </Condition>
  937. <Then>
  938. <ScalarOperator>
  939. <Const ConstValue="NULL" />
  940. </ScalarOperator>
  941. </Then>
  942. <Else>
  943. <ScalarOperator>
  944. <Identifier>
  945. <ColumnReference Column="Expr1092" />
  946. </Identifier>
  947. </ScalarOperator>
  948. </Else>
  949. </IF>
  950. </ScalarOperator>
  951. </DefinedValue>
  952. </DefinedValues>
  953. <RelOp AvgRowSize="100" EstimateCPU="0.949969" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="764327" LogicalOp="Aggregate" NodeId="3" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="10.9186">
  954. <OutputList>
  955. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  956. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  957. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  958. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  959. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  960. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  961. <ColumnReference Column="Expr1085" />
  962. <ColumnReference Column="Expr1086" />
  963. <ColumnReference Column="Expr1087" />
  964. <ColumnReference Column="Expr1088" />
  965. <ColumnReference Column="Expr1089" />
  966. <ColumnReference Column="Expr1090" />
  967. <ColumnReference Column="Expr1091" />
  968. <ColumnReference Column="Expr1092" />
  969. </OutputList>
  970. <MemoryFractions Input="1" Output="1" />
  971. <RunTimeInformation>
  972. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  973. <RunTimeCountersPerThread Thread="1" ActualRows="13617" Batches="16" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="320" ActualCPUms="320" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  974. <RunTimeCountersPerThread Thread="2" ActualRows="13545" Batches="16" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="300" ActualCPUms="298" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  975. </RunTimeInformation>
  976. <Hash>
  977. <DefinedValues>
  978. <DefinedValue>
  979. <ColumnReference Column="Expr1085" />
  980. <ScalarOperator ScalarString="COUNT_BIG([Expr1026])">
  981. <Aggregate AggType="COUNT_BIG" Distinct="false">
  982. <ScalarOperator>
  983. <Identifier>
  984. <ColumnReference Column="Expr1026" />
  985. </Identifier>
  986. </ScalarOperator>
  987. </Aggregate>
  988. </ScalarOperator>
  989. </DefinedValue>
  990. <DefinedValue>
  991. <ColumnReference Column="Expr1086" />
  992. <ScalarOperator ScalarString="SUM([Expr1026])">
  993. <Aggregate AggType="SUM" Distinct="false">
  994. <ScalarOperator>
  995. <Identifier>
  996. <ColumnReference Column="Expr1026" />
  997. </Identifier>
  998. </ScalarOperator>
  999. </Aggregate>
  1000. </ScalarOperator>
  1001. </DefinedValue>
  1002. <DefinedValue>
  1003. <ColumnReference Column="Expr1087" />
  1004. <ScalarOperator ScalarString="COUNT_BIG([Expr1027])">
  1005. <Aggregate AggType="COUNT_BIG" Distinct="false">
  1006. <ScalarOperator>
  1007. <Identifier>
  1008. <ColumnReference Column="Expr1027" />
  1009. </Identifier>
  1010. </ScalarOperator>
  1011. </Aggregate>
  1012. </ScalarOperator>
  1013. </DefinedValue>
  1014. <DefinedValue>
  1015. <ColumnReference Column="Expr1088" />
  1016. <ScalarOperator ScalarString="SUM([Expr1027])">
  1017. <Aggregate AggType="SUM" Distinct="false">
  1018. <ScalarOperator>
  1019. <Identifier>
  1020. <ColumnReference Column="Expr1027" />
  1021. </Identifier>
  1022. </ScalarOperator>
  1023. </Aggregate>
  1024. </ScalarOperator>
  1025. </DefinedValue>
  1026. <DefinedValue>
  1027. <ColumnReference Column="Expr1089" />
  1028. <ScalarOperator ScalarString="COUNT_BIG([Expr1028])">
  1029. <Aggregate AggType="COUNT_BIG" Distinct="false">
  1030. <ScalarOperator>
  1031. <Identifier>
  1032. <ColumnReference Column="Expr1028" />
  1033. </Identifier>
  1034. </ScalarOperator>
  1035. </Aggregate>
  1036. </ScalarOperator>
  1037. </DefinedValue>
  1038. <DefinedValue>
  1039. <ColumnReference Column="Expr1090" />
  1040. <ScalarOperator ScalarString="SUM([Expr1028])">
  1041. <Aggregate AggType="SUM" Distinct="false">
  1042. <ScalarOperator>
  1043. <Identifier>
  1044. <ColumnReference Column="Expr1028" />
  1045. </Identifier>
  1046. </ScalarOperator>
  1047. </Aggregate>
  1048. </ScalarOperator>
  1049. </DefinedValue>
  1050. <DefinedValue>
  1051. <ColumnReference Column="Expr1091" />
  1052. <ScalarOperator ScalarString="COUNT_BIG([Expr1029])">
  1053. <Aggregate AggType="COUNT_BIG" Distinct="false">
  1054. <ScalarOperator>
  1055. <Identifier>
  1056. <ColumnReference Column="Expr1029" />
  1057. </Identifier>
  1058. </ScalarOperator>
  1059. </Aggregate>
  1060. </ScalarOperator>
  1061. </DefinedValue>
  1062. <DefinedValue>
  1063. <ColumnReference Column="Expr1092" />
  1064. <ScalarOperator ScalarString="SUM([Expr1029])">
  1065. <Aggregate AggType="SUM" Distinct="false">
  1066. <ScalarOperator>
  1067. <Identifier>
  1068. <ColumnReference Column="Expr1029" />
  1069. </Identifier>
  1070. </ScalarOperator>
  1071. </Aggregate>
  1072. </ScalarOperator>
  1073. </DefinedValue>
  1074. </DefinedValues>
  1075. <HashKeysBuild>
  1076. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1077. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1078. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1079. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1080. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1081. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1082. </HashKeysBuild>
  1083. <BuildResidual>
  1084. <ScalarOperator ScalarString="[tempdb].[dbo].[#DIM_TIME0].[BEFORE_WEEKS] as [L_DIM_TIME0].[BEFORE_WEEKS] = [tempdb].[dbo].[#DIM_TIME0].[BEFORE_WEEKS] as [L_DIM_TIME0].[BEFORE_WEEKS] AND [tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[BRAND_DESC] as [L_VWDIM_PROD_HIER_STORE0].[BRAND_DESC] = [tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[BRAND_DESC] as [L_VWDIM_PROD_HIER_STORE0].[BRAND_DESC] AND [tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[BRAND_ID] as [L_VWDIM_PROD_HIER_STORE0].[BRAND_ID] = [tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[BRAND_ID] as [L_VWDIM_PROD_HIER_STORE0].[BRAND_ID] AND [tempdb].[dbo].[#DIM_TIME0].[RELATIVE_WEEK] as [L_DIM_TIME0].[RELATIVE_WEEK] = [tempdb].[dbo].[#DIM_TIME0].[RELATIVE_WEEK] as [L_DIM_TIME0].[RELATIVE_WEEK] AND [tempdb].[dbo].[#DIM_STORE0].[SG_CHAIN_CODE] as [L_DIM_STORE0].[SG_CHAIN_CODE] = [tempdb].[dbo].[#DIM_STORE0].[SG_CHAIN_CODE] as [L_DIM_STORE0].[SG_CHAIN_CODE] AND [tempdb].[dbo].[#DIM_STORE0].[SG_CHAIN_DESC] as [L_DIM_STORE0].[SG_CHAIN_DESC] = [tempdb].[dbo].[#DIM_STORE0].[SG_CHAIN_DESC] as [L_DIM_STORE0].[SG_CHAIN_DESC]">
  1085. <Logical Operation="AND">
  1086. <ScalarOperator>
  1087. <Logical Operation="AND">
  1088. <ScalarOperator>
  1089. <Logical Operation="AND">
  1090. <ScalarOperator>
  1091. <Logical Operation="AND">
  1092. <ScalarOperator>
  1093. <Logical Operation="AND">
  1094. <ScalarOperator>
  1095. <Compare CompareOp="IS">
  1096. <ScalarOperator>
  1097. <Identifier>
  1098. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1099. </Identifier>
  1100. </ScalarOperator>
  1101. <ScalarOperator>
  1102. <Identifier>
  1103. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1104. </Identifier>
  1105. </ScalarOperator>
  1106. </Compare>
  1107. </ScalarOperator>
  1108. <ScalarOperator>
  1109. <Compare CompareOp="IS">
  1110. <ScalarOperator>
  1111. <Identifier>
  1112. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1113. </Identifier>
  1114. </ScalarOperator>
  1115. <ScalarOperator>
  1116. <Identifier>
  1117. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1118. </Identifier>
  1119. </ScalarOperator>
  1120. </Compare>
  1121. </ScalarOperator>
  1122. </Logical>
  1123. </ScalarOperator>
  1124. <ScalarOperator>
  1125. <Compare CompareOp="IS">
  1126. <ScalarOperator>
  1127. <Identifier>
  1128. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1129. </Identifier>
  1130. </ScalarOperator>
  1131. <ScalarOperator>
  1132. <Identifier>
  1133. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1134. </Identifier>
  1135. </ScalarOperator>
  1136. </Compare>
  1137. </ScalarOperator>
  1138. </Logical>
  1139. </ScalarOperator>
  1140. <ScalarOperator>
  1141. <Compare CompareOp="IS">
  1142. <ScalarOperator>
  1143. <Identifier>
  1144. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1145. </Identifier>
  1146. </ScalarOperator>
  1147. <ScalarOperator>
  1148. <Identifier>
  1149. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1150. </Identifier>
  1151. </ScalarOperator>
  1152. </Compare>
  1153. </ScalarOperator>
  1154. </Logical>
  1155. </ScalarOperator>
  1156. <ScalarOperator>
  1157. <Compare CompareOp="IS">
  1158. <ScalarOperator>
  1159. <Identifier>
  1160. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1161. </Identifier>
  1162. </ScalarOperator>
  1163. <ScalarOperator>
  1164. <Identifier>
  1165. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1166. </Identifier>
  1167. </ScalarOperator>
  1168. </Compare>
  1169. </ScalarOperator>
  1170. </Logical>
  1171. </ScalarOperator>
  1172. <ScalarOperator>
  1173. <Compare CompareOp="IS">
  1174. <ScalarOperator>
  1175. <Identifier>
  1176. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1177. </Identifier>
  1178. </ScalarOperator>
  1179. <ScalarOperator>
  1180. <Identifier>
  1181. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1182. </Identifier>
  1183. </ScalarOperator>
  1184. </Compare>
  1185. </ScalarOperator>
  1186. </Logical>
  1187. </ScalarOperator>
  1188. </BuildResidual>
  1189. <RelOp AvgRowSize="98" EstimateCPU="0.00942697" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="764349" LogicalOp="Filter" NodeId="4" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="9.96864">
  1190. <OutputList>
  1191. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1192. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1193. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1194. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1195. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1196. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1197. <ColumnReference Column="Expr1026" />
  1198. <ColumnReference Column="Expr1027" />
  1199. <ColumnReference Column="Expr1028" />
  1200. <ColumnReference Column="Expr1029" />
  1201. </OutputList>
  1202. <RunTimeInformation>
  1203. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1204. <RunTimeCountersPerThread Thread="1" ActualRows="650724" Batches="898" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="11" ActualCPUms="11" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1205. <RunTimeCountersPerThread Thread="2" ActualRows="640685" Batches="885" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="11" ActualCPUms="11" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1206. </RunTimeInformation>
  1207. <Filter StartupExpression="false">
  1208. <RelOp AvgRowSize="98" EstimateCPU="0" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="764349" LogicalOp="Compute Scalar" NodeId="5" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="9.95921">
  1209. <OutputList>
  1210. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1211. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1212. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1213. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1214. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1215. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1216. <ColumnReference Column="Expr1026" />
  1217. <ColumnReference Column="Expr1027" />
  1218. <ColumnReference Column="Expr1028" />
  1219. <ColumnReference Column="Expr1029" />
  1220. </OutputList>
  1221. <RunTimeInformation>
  1222. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1223. <RunTimeCountersPerThread Thread="1" ActualRows="807784" Batches="898" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="7" ActualCPUms="7" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1224. <RunTimeCountersPerThread Thread="2" ActualRows="795948" Batches="885" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="7" ActualCPUms="7" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1225. </RunTimeInformation>
  1226. <ComputeScalar>
  1227. <DefinedValues>
  1228. <DefinedValue>
  1229. <ColumnReference Column="Expr1026" />
  1230. <ScalarOperator ScalarString="CASE WHEN [Expr1081]=(0) THEN NULL ELSE [Expr1082] END">
  1231. <IF>
  1232. <Condition>
  1233. <ScalarOperator>
  1234. <Compare CompareOp="EQ">
  1235. <ScalarOperator>
  1236. <Identifier>
  1237. <ColumnReference Column="Expr1081" />
  1238. </Identifier>
  1239. </ScalarOperator>
  1240. <ScalarOperator>
  1241. <Const ConstValue="(0)" />
  1242. </ScalarOperator>
  1243. </Compare>
  1244. </ScalarOperator>
  1245. </Condition>
  1246. <Then>
  1247. <ScalarOperator>
  1248. <Const ConstValue="NULL" />
  1249. </ScalarOperator>
  1250. </Then>
  1251. <Else>
  1252. <ScalarOperator>
  1253. <Identifier>
  1254. <ColumnReference Column="Expr1082" />
  1255. </Identifier>
  1256. </ScalarOperator>
  1257. </Else>
  1258. </IF>
  1259. </ScalarOperator>
  1260. </DefinedValue>
  1261. <DefinedValue>
  1262. <ColumnReference Column="Expr1027" />
  1263. <ScalarOperator ScalarString="CASE WHEN [Expr1083]=(0) THEN NULL ELSE [Expr1084] END">
  1264. <IF>
  1265. <Condition>
  1266. <ScalarOperator>
  1267. <Compare CompareOp="EQ">
  1268. <ScalarOperator>
  1269. <Identifier>
  1270. <ColumnReference Column="Expr1083" />
  1271. </Identifier>
  1272. </ScalarOperator>
  1273. <ScalarOperator>
  1274. <Const ConstValue="(0)" />
  1275. </ScalarOperator>
  1276. </Compare>
  1277. </ScalarOperator>
  1278. </Condition>
  1279. <Then>
  1280. <ScalarOperator>
  1281. <Const ConstValue="NULL" />
  1282. </ScalarOperator>
  1283. </Then>
  1284. <Else>
  1285. <ScalarOperator>
  1286. <Identifier>
  1287. <ColumnReference Column="Expr1084" />
  1288. </Identifier>
  1289. </ScalarOperator>
  1290. </Else>
  1291. </IF>
  1292. </ScalarOperator>
  1293. </DefinedValue>
  1294. </DefinedValues>
  1295. <RelOp AvgRowSize="98" EstimateCPU="0.913378" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="764349" LogicalOp="Aggregate" NodeId="6" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="9.95921">
  1296. <OutputList>
  1297. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1298. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1299. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1300. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1301. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1302. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1303. <ColumnReference Column="Expr1028" />
  1304. <ColumnReference Column="Expr1029" />
  1305. <ColumnReference Column="Expr1081" />
  1306. <ColumnReference Column="Expr1082" />
  1307. <ColumnReference Column="Expr1083" />
  1308. <ColumnReference Column="Expr1084" />
  1309. </OutputList>
  1310. <MemoryFractions Input="0.538049" Output="0.538049" />
  1311. <RunTimeInformation>
  1312. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1313. <RunTimeCountersPerThread Thread="1" ActualRows="807784" Batches="898" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="1594" ActualCPUms="1589" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1314. <RunTimeCountersPerThread Thread="2" ActualRows="795948" Batches="885" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="1609" ActualCPUms="1598" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1315. </RunTimeInformation>
  1316. <Hash>
  1317. <DefinedValues>
  1318. <DefinedValue>
  1319. <ColumnReference Column="Expr1081" />
  1320. <ScalarOperator ScalarString="COUNT_BIG([STG_AGG].[dbo].[AGG_PRODHIER_STORE].[CONSUMER_UNITS_R52])">
  1321. <Aggregate AggType="COUNT_BIG" Distinct="false">
  1322. <ScalarOperator>
  1323. <Identifier>
  1324. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="CONSUMER_UNITS_R52" />
  1325. </Identifier>
  1326. </ScalarOperator>
  1327. </Aggregate>
  1328. </ScalarOperator>
  1329. </DefinedValue>
  1330. <DefinedValue>
  1331. <ColumnReference Column="Expr1082" />
  1332. <ScalarOperator ScalarString="SUM([STG_AGG].[dbo].[AGG_PRODHIER_STORE].[CONSUMER_UNITS_R52])">
  1333. <Aggregate AggType="SUM" Distinct="false">
  1334. <ScalarOperator>
  1335. <Identifier>
  1336. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="CONSUMER_UNITS_R52" />
  1337. </Identifier>
  1338. </ScalarOperator>
  1339. </Aggregate>
  1340. </ScalarOperator>
  1341. </DefinedValue>
  1342. <DefinedValue>
  1343. <ColumnReference Column="Expr1083" />
  1344. <ScalarOperator ScalarString="COUNT_BIG([STG_AGG].[dbo].[AGG_PRODHIER_STORE].[EQ_VOLUME_R52])">
  1345. <Aggregate AggType="COUNT_BIG" Distinct="false">
  1346. <ScalarOperator>
  1347. <Identifier>
  1348. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="EQ_VOLUME_R52" />
  1349. </Identifier>
  1350. </ScalarOperator>
  1351. </Aggregate>
  1352. </ScalarOperator>
  1353. </DefinedValue>
  1354. <DefinedValue>
  1355. <ColumnReference Column="Expr1084" />
  1356. <ScalarOperator ScalarString="SUM([STG_AGG].[dbo].[AGG_PRODHIER_STORE].[EQ_VOLUME_R52])">
  1357. <Aggregate AggType="SUM" Distinct="false">
  1358. <ScalarOperator>
  1359. <Identifier>
  1360. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="EQ_VOLUME_R52" />
  1361. </Identifier>
  1362. </ScalarOperator>
  1363. </Aggregate>
  1364. </ScalarOperator>
  1365. </DefinedValue>
  1366. <DefinedValue>
  1367. <ColumnReference Column="Expr1028" />
  1368. <ScalarOperator ScalarString="MAX([Expr1035])">
  1369. <Aggregate AggType="MAX" Distinct="false">
  1370. <ScalarOperator>
  1371. <Identifier>
  1372. <ColumnReference Column="Expr1035" />
  1373. </Identifier>
  1374. </ScalarOperator>
  1375. </Aggregate>
  1376. </ScalarOperator>
  1377. </DefinedValue>
  1378. <DefinedValue>
  1379. <ColumnReference Column="Expr1029" />
  1380. <ScalarOperator ScalarString="MAX([STG_AGG].[dbo].[AGG_PRODHIER_STORE].[SKU_COUNT_R52])">
  1381. <Aggregate AggType="MAX" Distinct="false">
  1382. <ScalarOperator>
  1383. <Identifier>
  1384. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="SKU_COUNT_R52" />
  1385. </Identifier>
  1386. </ScalarOperator>
  1387. </Aggregate>
  1388. </ScalarOperator>
  1389. </DefinedValue>
  1390. </DefinedValues>
  1391. <HashKeysBuild>
  1392. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1393. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1394. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1395. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1396. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1397. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1398. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1399. </HashKeysBuild>
  1400. <BuildResidual>
  1401. <ScalarOperator ScalarString="[tempdb].[dbo].[#DIM_TIME0].[BEFORE_WEEKS] as [L_DIM_TIME0].[BEFORE_WEEKS] = [tempdb].[dbo].[#DIM_TIME0].[BEFORE_WEEKS] as [L_DIM_TIME0].[BEFORE_WEEKS] AND [tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[BRAND_DESC] as [L_VWDIM_PROD_HIER_STORE0].[BRAND_DESC] = [tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[BRAND_DESC] as [L_VWDIM_PROD_HIER_STORE0].[BRAND_DESC] AND [tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[BRAND_ID] as [L_VWDIM_PROD_HIER_STORE0].[BRAND_ID] = [tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[BRAND_ID] as [L_VWDIM_PROD_HIER_STORE0].[BRAND_ID] AND [tempdb].[dbo].[#DIM_TIME0].[RELATIVE_WEEK] as [L_DIM_TIME0].[RELATIVE_WEEK] = [tempdb].[dbo].[#DIM_TIME0].[RELATIVE_WEEK] as [L_DIM_TIME0].[RELATIVE_WEEK] AND [STG_AGG].[dbo].[AGG_PRODHIER_STORE].[RETAIL_ID] = [STG_AGG].[dbo].[AGG_PRODHIER_STORE].[RETAIL_ID] AND [tempdb].[dbo].[#DIM_STORE0].[SG_CHAIN_CODE] as [L_DIM_STORE0].[SG_CHAIN_CODE] = [tempdb].[dbo].[#DIM_STORE0].[SG_CHAIN_CODE] as [L_DIM_STORE0].[SG_CHAIN_CODE] AND [tempdb].[dbo].[#DIM_STORE0].[SG_CHAIN_DESC] as [L_DIM_STORE0].[SG_CHAIN_DESC] = [tempdb].[dbo].[#DIM_STORE0].[SG_CHAIN_DESC] as [L_DIM_STORE0].[SG_CHAIN_DESC]">
  1402. <Logical Operation="AND">
  1403. <ScalarOperator>
  1404. <Logical Operation="AND">
  1405. <ScalarOperator>
  1406. <Logical Operation="AND">
  1407. <ScalarOperator>
  1408. <Logical Operation="AND">
  1409. <ScalarOperator>
  1410. <Logical Operation="AND">
  1411. <ScalarOperator>
  1412. <Logical Operation="AND">
  1413. <ScalarOperator>
  1414. <Compare CompareOp="IS">
  1415. <ScalarOperator>
  1416. <Identifier>
  1417. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1418. </Identifier>
  1419. </ScalarOperator>
  1420. <ScalarOperator>
  1421. <Identifier>
  1422. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1423. </Identifier>
  1424. </ScalarOperator>
  1425. </Compare>
  1426. </ScalarOperator>
  1427. <ScalarOperator>
  1428. <Compare CompareOp="IS">
  1429. <ScalarOperator>
  1430. <Identifier>
  1431. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1432. </Identifier>
  1433. </ScalarOperator>
  1434. <ScalarOperator>
  1435. <Identifier>
  1436. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1437. </Identifier>
  1438. </ScalarOperator>
  1439. </Compare>
  1440. </ScalarOperator>
  1441. </Logical>
  1442. </ScalarOperator>
  1443. <ScalarOperator>
  1444. <Compare CompareOp="IS">
  1445. <ScalarOperator>
  1446. <Identifier>
  1447. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1448. </Identifier>
  1449. </ScalarOperator>
  1450. <ScalarOperator>
  1451. <Identifier>
  1452. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1453. </Identifier>
  1454. </ScalarOperator>
  1455. </Compare>
  1456. </ScalarOperator>
  1457. </Logical>
  1458. </ScalarOperator>
  1459. <ScalarOperator>
  1460. <Compare CompareOp="IS">
  1461. <ScalarOperator>
  1462. <Identifier>
  1463. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1464. </Identifier>
  1465. </ScalarOperator>
  1466. <ScalarOperator>
  1467. <Identifier>
  1468. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1469. </Identifier>
  1470. </ScalarOperator>
  1471. </Compare>
  1472. </ScalarOperator>
  1473. </Logical>
  1474. </ScalarOperator>
  1475. <ScalarOperator>
  1476. <Compare CompareOp="IS">
  1477. <ScalarOperator>
  1478. <Identifier>
  1479. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1480. </Identifier>
  1481. </ScalarOperator>
  1482. <ScalarOperator>
  1483. <Identifier>
  1484. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1485. </Identifier>
  1486. </ScalarOperator>
  1487. </Compare>
  1488. </ScalarOperator>
  1489. </Logical>
  1490. </ScalarOperator>
  1491. <ScalarOperator>
  1492. <Compare CompareOp="IS">
  1493. <ScalarOperator>
  1494. <Identifier>
  1495. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1496. </Identifier>
  1497. </ScalarOperator>
  1498. <ScalarOperator>
  1499. <Identifier>
  1500. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1501. </Identifier>
  1502. </ScalarOperator>
  1503. </Compare>
  1504. </ScalarOperator>
  1505. </Logical>
  1506. </ScalarOperator>
  1507. <ScalarOperator>
  1508. <Compare CompareOp="IS">
  1509. <ScalarOperator>
  1510. <Identifier>
  1511. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1512. </Identifier>
  1513. </ScalarOperator>
  1514. <ScalarOperator>
  1515. <Identifier>
  1516. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1517. </Identifier>
  1518. </ScalarOperator>
  1519. </Compare>
  1520. </ScalarOperator>
  1521. </Logical>
  1522. </ScalarOperator>
  1523. </BuildResidual>
  1524. <RelOp AvgRowSize="86" EstimateCPU="0.0801585" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="765130" LogicalOp="Inner Join" NodeId="7" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="9.04583">
  1525. <OutputList>
  1526. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1527. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="CONSUMER_UNITS_R52" />
  1528. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="EQ_VOLUME_R52" />
  1529. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="SKU_COUNT_R52" />
  1530. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1531. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1532. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1533. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1534. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1535. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1536. <ColumnReference Column="Expr1035" />
  1537. </OutputList>
  1538. <MemoryFractions Input="0.0798305" Output="0.0798305" />
  1539. <RunTimeInformation>
  1540. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1541. <RunTimeCountersPerThread Thread="1" ActualRows="2270027" Batches="15633" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="27" ActualCPUms="23" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1542. <RunTimeCountersPerThread Thread="2" ActualRows="2200589" Batches="17363" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="29" ActualCPUms="24" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1543. </RunTimeInformation>
  1544. <Hash BitmapCreator="true">
  1545. <DefinedValues>
  1546. <DefinedValue>
  1547. <ColumnReference Column="Opt_Bitmap1080" />
  1548. </DefinedValue>
  1549. </DefinedValues>
  1550. <HashKeysBuild>
  1551. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="PROD_HIER_ID_STORE" />
  1552. </HashKeysBuild>
  1553. <HashKeysProbe>
  1554. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PROD_HIER_ID" />
  1555. </HashKeysProbe>
  1556. <ProbeResidual>
  1557. <ScalarOperator ScalarString="[STG_AGG].[dbo].[AGG_PRODHIER_STORE].[PROD_HIER_ID]=[tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[PROD_HIER_ID_STORE] as [L_VWDIM_PROD_HIER_STORE0].[PROD_HIER_ID_STORE]">
  1558. <Compare CompareOp="EQ">
  1559. <ScalarOperator>
  1560. <Identifier>
  1561. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PROD_HIER_ID" />
  1562. </Identifier>
  1563. </ScalarOperator>
  1564. <ScalarOperator>
  1565. <Identifier>
  1566. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="PROD_HIER_ID_STORE" />
  1567. </Identifier>
  1568. </ScalarOperator>
  1569. </Compare>
  1570. </ScalarOperator>
  1571. </ProbeResidual>
  1572. <RelOp AvgRowSize="25" EstimateCPU="0.0001071" EstimateIO="0.00542572" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1097" EstimatedRowsRead="1097" LogicalOp="Table Scan" NodeId="8" Parallel="true" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.00553282" TableCardinality="1097">
  1573. <OutputList>
  1574. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1575. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1576. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="PROD_HIER_ID_STORE" />
  1577. </OutputList>
  1578. <RunTimeInformation>
  1579. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1580. <RunTimeCountersPerThread Thread="1" ActualRows="521" ActualRowsRead="521" Batches="1" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1581. <RunTimeCountersPerThread Thread="2" ActualRows="576" ActualRowsRead="576" Batches="1" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1582. </RunTimeInformation>
  1583. <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  1584. <DefinedValues>
  1585. <DefinedValue>
  1586. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1587. </DefinedValue>
  1588. <DefinedValue>
  1589. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1590. </DefinedValue>
  1591. <DefinedValue>
  1592. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="PROD_HIER_ID_STORE" />
  1593. </DefinedValue>
  1594. </DefinedValues>
  1595. <Object Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" IndexKind="Heap" Storage="RowStore" />
  1596. <Predicate>
  1597. <ScalarOperator ScalarString="[tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[PROD_HIER_LEVEL_STORE] as [L_VWDIM_PROD_HIER_STORE0].[PROD_HIER_LEVEL_STORE]=(3) AND [tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[BRAND_ID] as [L_VWDIM_PROD_HIER_STORE0].[BRAND_ID] IS NOT NULL AND [tempdb].[dbo].[#VWDIM_PROD_HIER_STORE0].[BRAND_DESC] as [L_VWDIM_PROD_HIER_STORE0].[BRAND_DESC] IS NOT NULL">
  1598. <Logical Operation="AND">
  1599. <ScalarOperator>
  1600. <Compare CompareOp="EQ">
  1601. <ScalarOperator>
  1602. <Identifier>
  1603. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="PROD_HIER_LEVEL_STORE" />
  1604. </Identifier>
  1605. </ScalarOperator>
  1606. <ScalarOperator>
  1607. <Const ConstValue="(3)" />
  1608. </ScalarOperator>
  1609. </Compare>
  1610. </ScalarOperator>
  1611. <ScalarOperator>
  1612. <Compare CompareOp="IS NOT">
  1613. <ScalarOperator>
  1614. <Identifier>
  1615. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_ID" />
  1616. </Identifier>
  1617. </ScalarOperator>
  1618. <ScalarOperator>
  1619. <Const ConstValue="NULL" />
  1620. </ScalarOperator>
  1621. </Compare>
  1622. </ScalarOperator>
  1623. <ScalarOperator>
  1624. <Compare CompareOp="IS NOT">
  1625. <ScalarOperator>
  1626. <Identifier>
  1627. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#VWDIM_PROD_HIER_STORE0]" Alias="[L_VWDIM_PROD_HIER_STORE0]" Column="BRAND_DESC" />
  1628. </Identifier>
  1629. </ScalarOperator>
  1630. <ScalarOperator>
  1631. <Const ConstValue="NULL" />
  1632. </ScalarOperator>
  1633. </Compare>
  1634. </ScalarOperator>
  1635. </Logical>
  1636. </ScalarOperator>
  1637. </Predicate>
  1638. </TableScan>
  1639. </RelOp>
  1640. <RelOp AvgRowSize="79" EstimateCPU="0.150304" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="500349" LogicalOp="Inner Join" NodeId="9" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="8.96005">
  1641. <OutputList>
  1642. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PROD_HIER_ID" />
  1643. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1644. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="CONSUMER_UNITS_R52" />
  1645. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="EQ_VOLUME_R52" />
  1646. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="SKU_COUNT_R52" />
  1647. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1648. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1649. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1650. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1651. <ColumnReference Column="Expr1035" />
  1652. </OutputList>
  1653. <MemoryFractions Input="0.0788105" Output="0.0788105" />
  1654. <RunTimeInformation>
  1655. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1656. <RunTimeCountersPerThread Thread="1" ActualRows="2270027" Batches="15633" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="94" ActualCPUms="90" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1657. <RunTimeCountersPerThread Thread="2" ActualRows="2200589" Batches="17363" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="95" ActualCPUms="90" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1658. </RunTimeInformation>
  1659. <Hash>
  1660. <DefinedValues />
  1661. <HashKeysBuild>
  1662. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="RETAIL_ID" />
  1663. </HashKeysBuild>
  1664. <HashKeysProbe>
  1665. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1666. </HashKeysProbe>
  1667. <ProbeResidual>
  1668. <ScalarOperator ScalarString="[tempdb].[dbo].[#DIM_STORE0].[RETAIL_ID] as [L_DIM_STORE0].[RETAIL_ID]=[STG_AGG].[dbo].[AGG_PRODHIER_STORE].[RETAIL_ID]">
  1669. <Compare CompareOp="EQ">
  1670. <ScalarOperator>
  1671. <Identifier>
  1672. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="RETAIL_ID" />
  1673. </Identifier>
  1674. </ScalarOperator>
  1675. <ScalarOperator>
  1676. <Identifier>
  1677. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1678. </Identifier>
  1679. </ScalarOperator>
  1680. </Compare>
  1681. </ScalarOperator>
  1682. </ProbeResidual>
  1683. <RelOp AvgRowSize="43" EstimateCPU="0.0283871" EstimateIO="1.31654" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="309606" EstimatedRowsRead="309606" LogicalOp="Table Scan" NodeId="10" Parallel="true" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="1.34492" TableCardinality="309606">
  1684. <OutputList>
  1685. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="RETAIL_ID" />
  1686. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1687. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1688. </OutputList>
  1689. <RunTimeInformation>
  1690. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1691. <RunTimeCountersPerThread Thread="1" ActualRows="155653" ActualRowsRead="155653" Batches="173" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="51" ActualCPUms="51" ActualScans="1" ActualLogicalReads="896" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1692. <RunTimeCountersPerThread Thread="2" ActualRows="153953" ActualRowsRead="153953" Batches="172" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="51" ActualCPUms="51" ActualScans="1" ActualLogicalReads="878" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1693. </RunTimeInformation>
  1694. <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  1695. <DefinedValues>
  1696. <DefinedValue>
  1697. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="RETAIL_ID" />
  1698. </DefinedValue>
  1699. <DefinedValue>
  1700. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1701. </DefinedValue>
  1702. <DefinedValue>
  1703. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1704. </DefinedValue>
  1705. </DefinedValues>
  1706. <Object Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" IndexKind="Heap" Storage="RowStore" />
  1707. <Predicate>
  1708. <ScalarOperator ScalarString="[tempdb].[dbo].[#DIM_STORE0].[SG_CHAIN_CODE] as [L_DIM_STORE0].[SG_CHAIN_CODE] IS NOT NULL AND [tempdb].[dbo].[#DIM_STORE0].[SG_CHAIN_DESC] as [L_DIM_STORE0].[SG_CHAIN_DESC] IS NOT NULL">
  1709. <Logical Operation="AND">
  1710. <ScalarOperator>
  1711. <Compare CompareOp="IS NOT">
  1712. <ScalarOperator>
  1713. <Identifier>
  1714. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_CODE" />
  1715. </Identifier>
  1716. </ScalarOperator>
  1717. <ScalarOperator>
  1718. <Const ConstValue="NULL" />
  1719. </ScalarOperator>
  1720. </Compare>
  1721. </ScalarOperator>
  1722. <ScalarOperator>
  1723. <Compare CompareOp="IS NOT">
  1724. <ScalarOperator>
  1725. <Identifier>
  1726. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_STORE0]" Alias="[L_DIM_STORE0]" Column="SG_CHAIN_DESC" />
  1727. </Identifier>
  1728. </ScalarOperator>
  1729. <ScalarOperator>
  1730. <Const ConstValue="NULL" />
  1731. </ScalarOperator>
  1732. </Compare>
  1733. </ScalarOperator>
  1734. </Logical>
  1735. </ScalarOperator>
  1736. </Predicate>
  1737. </TableScan>
  1738. </RelOp>
  1739. <RelOp AvgRowSize="46" EstimateCPU="0.0531202" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="447550" LogicalOp="Inner Join" NodeId="11" Parallel="true" PhysicalOp="Hash Match" EstimatedTotalSubtreeCost="7.44707">
  1740. <OutputList>
  1741. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PROD_HIER_ID" />
  1742. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1743. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="CONSUMER_UNITS_R52" />
  1744. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="EQ_VOLUME_R52" />
  1745. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="SKU_COUNT_R52" />
  1746. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1747. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1748. <ColumnReference Column="Expr1035" />
  1749. </OutputList>
  1750. <MemoryFractions Input="0.0003321" Output="0.0003321" />
  1751. <RunTimeInformation>
  1752. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1753. <RunTimeCountersPerThread Thread="1" ActualRows="2270027" Batches="15633" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="23" ActualCPUms="16" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1754. <RunTimeCountersPerThread Thread="2" ActualRows="2200589" Batches="17363" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="22" ActualCPUms="17" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1755. </RunTimeInformation>
  1756. <Hash>
  1757. <DefinedValues />
  1758. <HashKeysBuild>
  1759. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="PERIOD_CODE" />
  1760. </HashKeysBuild>
  1761. <HashKeysProbe>
  1762. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PERIOD_CODE" />
  1763. </HashKeysProbe>
  1764. <ProbeResidual>
  1765. <ScalarOperator ScalarString="[tempdb].[dbo].[#DIM_TIME0].[PERIOD_CODE] as [L_DIM_TIME0].[PERIOD_CODE]=[STG_AGG].[dbo].[AGG_PRODHIER_STORE].[PERIOD_CODE]">
  1766. <Compare CompareOp="EQ">
  1767. <ScalarOperator>
  1768. <Identifier>
  1769. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="PERIOD_CODE" />
  1770. </Identifier>
  1771. </ScalarOperator>
  1772. <ScalarOperator>
  1773. <Identifier>
  1774. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PERIOD_CODE" />
  1775. </Identifier>
  1776. </ScalarOperator>
  1777. </Compare>
  1778. </ScalarOperator>
  1779. </ProbeResidual>
  1780. <RelOp AvgRowSize="16" EstimateCPU="0.0285032" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Distribute Streams" NodeId="12" Parallel="true" PhysicalOp="Parallelism" EstimatedTotalSubtreeCost="0.0317863">
  1781. <OutputList>
  1782. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1783. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="PERIOD_CODE" />
  1784. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1785. </OutputList>
  1786. <RunTimeInformation>
  1787. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1788. <RunTimeCountersPerThread Thread="1" ActualRows="1" Batches="1" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1789. <RunTimeCountersPerThread Thread="2" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1790. </RunTimeInformation>
  1791. <Parallelism PartitioningType="RoundRobin">
  1792. <RelOp AvgRowSize="16" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Table Scan" NodeId="13" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="1">
  1793. <OutputList>
  1794. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1795. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="PERIOD_CODE" />
  1796. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1797. </OutputList>
  1798. <RunTimeInformation>
  1799. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1800. <RunTimeCountersPerThread Thread="1" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1801. </RunTimeInformation>
  1802. <TableScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
  1803. <DefinedValues>
  1804. <DefinedValue>
  1805. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1806. </DefinedValue>
  1807. <DefinedValue>
  1808. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="PERIOD_CODE" />
  1809. </DefinedValue>
  1810. <DefinedValue>
  1811. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1812. </DefinedValue>
  1813. </DefinedValues>
  1814. <Object Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" IndexKind="Heap" Storage="RowStore" />
  1815. <Predicate>
  1816. <ScalarOperator ScalarString="[tempdb].[dbo].[#DIM_TIME0].[BEFORE_WEEKS] as [L_DIM_TIME0].[BEFORE_WEEKS] IS NOT NULL AND [tempdb].[dbo].[#DIM_TIME0].[RELATIVE_WEEK] as [L_DIM_TIME0].[RELATIVE_WEEK] IS NOT NULL">
  1817. <Logical Operation="AND">
  1818. <ScalarOperator>
  1819. <Compare CompareOp="IS NOT">
  1820. <ScalarOperator>
  1821. <Identifier>
  1822. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="BEFORE_WEEKS" />
  1823. </Identifier>
  1824. </ScalarOperator>
  1825. <ScalarOperator>
  1826. <Const ConstValue="NULL" />
  1827. </ScalarOperator>
  1828. </Compare>
  1829. </ScalarOperator>
  1830. <ScalarOperator>
  1831. <Compare CompareOp="IS NOT">
  1832. <ScalarOperator>
  1833. <Identifier>
  1834. <ColumnReference Database="[tempdb]" Schema="[dbo]" Table="[#DIM_TIME0]" Alias="[L_DIM_TIME0]" Column="RELATIVE_WEEK" />
  1835. </Identifier>
  1836. </ScalarOperator>
  1837. <ScalarOperator>
  1838. <Const ConstValue="NULL" />
  1839. </ScalarOperator>
  1840. </Compare>
  1841. </ScalarOperator>
  1842. </Logical>
  1843. </ScalarOperator>
  1844. </Predicate>
  1845. </TableScan>
  1846. </RelOp>
  1847. </Parallelism>
  1848. </RelOp>
  1849. <RelOp AvgRowSize="41" EstimateCPU="0.021585" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="447550" LogicalOp="Filter" NodeId="14" Parallel="true" PhysicalOp="Filter" EstimatedTotalSubtreeCost="7.36216">
  1850. <OutputList>
  1851. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PERIOD_CODE" />
  1852. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PROD_HIER_ID" />
  1853. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1854. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="CONSUMER_UNITS_R52" />
  1855. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="EQ_VOLUME_R52" />
  1856. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="SKU_COUNT_R52" />
  1857. <ColumnReference Column="Expr1035" />
  1858. </OutputList>
  1859. <RunTimeInformation>
  1860. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1861. <RunTimeCountersPerThread Thread="1" ActualRows="2270027" Batches="15633" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="9" ActualCPUms="7" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1862. <RunTimeCountersPerThread Thread="2" ActualRows="2200589" Batches="17363" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="10" ActualCPUms="7" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1863. </RunTimeInformation>
  1864. <Filter StartupExpression="false">
  1865. <RelOp AvgRowSize="47" EstimateCPU="0.00245284" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="447550" LogicalOp="Compute Scalar" NodeId="15" Parallel="true" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="7.34058">
  1866. <OutputList>
  1867. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PERIOD_CODE" />
  1868. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PROD_HIER_ID" />
  1869. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1870. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="CONSUMER_UNITS_R52" />
  1871. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="EQ_VOLUME_R52" />
  1872. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="SKU_COUNT_R52" />
  1873. <ColumnReference Column="Expr1035" />
  1874. <ColumnReference Column="Expr1036" />
  1875. </OutputList>
  1876. <RunTimeInformation>
  1877. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1878. <RunTimeCountersPerThread Thread="1" ActualRows="2272496" Batches="15633" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="12" ActualCPUms="9" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1879. <RunTimeCountersPerThread Thread="2" ActualRows="2207665" Batches="17363" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="14" ActualCPUms="11" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
  1880. </RunTimeInformation>
  1881. <ComputeScalar>
  1882. <DefinedValues>
  1883. <DefinedValue>
  1884. <ColumnReference Column="Expr1035" />
  1885. <ScalarOperator ScalarString="CONVERT(smallint,[STG_AGG].[dbo].[AGG_PRODHIER_STORE].[STORE_SELLING_R52],0)">
  1886. <Convert DataType="smallint" Style="0" Implicit="false">
  1887. <ScalarOperator>
  1888. <Identifier>
  1889. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="STORE_SELLING_R52" />
  1890. </Identifier>
  1891. </ScalarOperator>
  1892. </Convert>
  1893. </ScalarOperator>
  1894. </DefinedValue>
  1895. <DefinedValue>
  1896. <ColumnReference Column="Expr1036" />
  1897. <ScalarOperator ScalarString="CONVERT(int,[STG_AGG].[dbo].[AGG_PRODHIER_STORE].[TRANSSHIPMENT_IND],0)">
  1898. <Convert DataType="int" Style="0" Implicit="false">
  1899. <ScalarOperator>
  1900. <Identifier>
  1901. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="TRANSSHIPMENT_IND" />
  1902. </Identifier>
  1903. </ScalarOperator>
  1904. </Convert>
  1905. </ScalarOperator>
  1906. </DefinedValue>
  1907. </DefinedValues>
  1908. <RelOp AvgRowSize="42" EstimateCPU="0.269814" EstimateIO="7.06831" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Batch" EstimateRows="447550" EstimatedRowsRead="2943410" LogicalOp="Clustered Index Scan" NodeId="16" Parallel="true" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="7.33812" TableCardinality="29434100">
  1909. <OutputList>
  1910. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PERIOD_CODE" />
  1911. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PROD_HIER_ID" />
  1912. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1913. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="TRANSSHIPMENT_IND" />
  1914. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="CONSUMER_UNITS_R52" />
  1915. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="EQ_VOLUME_R52" />
  1916. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="STORE_SELLING_R52" />
  1917. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="SKU_COUNT_R52" />
  1918. </OutputList>
  1919. <RunTimeInformation>
  1920. <RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualEndOfScans="0" ActualExecutions="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
  1921. <RunTimeCountersPerThread Thread="1" ActualRows="2272496" Batches="15633" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="162" ActualCPUms="160" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" SegmentReads="8" SegmentSkips="16" />
  1922. <RunTimeCountersPerThread Thread="2" ActualRows="2207665" Batches="17363" ActualEndOfScans="0" ActualExecutions="1" ActualExecutionMode="Batch" ActualElapsedms="161" ActualCPUms="160" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" SegmentReads="5" SegmentSkips="0" />
  1923. </RunTimeInformation>
  1924. <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="ColumnStore">
  1925. <DefinedValues>
  1926. <DefinedValue>
  1927. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PERIOD_CODE" />
  1928. </DefinedValue>
  1929. <DefinedValue>
  1930. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PROD_HIER_ID" />
  1931. </DefinedValue>
  1932. <DefinedValue>
  1933. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="RETAIL_ID" />
  1934. </DefinedValue>
  1935. <DefinedValue>
  1936. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="TRANSSHIPMENT_IND" />
  1937. </DefinedValue>
  1938. <DefinedValue>
  1939. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="CONSUMER_UNITS_R52" />
  1940. </DefinedValue>
  1941. <DefinedValue>
  1942. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="EQ_VOLUME_R52" />
  1943. </DefinedValue>
  1944. <DefinedValue>
  1945. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="STORE_SELLING_R52" />
  1946. </DefinedValue>
  1947. <DefinedValue>
  1948. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="SKU_COUNT_R52" />
  1949. </DefinedValue>
  1950. </DefinedValues>
  1951. <Object Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Index="[CCIX_AGG_PRODHIER_STORE]" IndexKind="Clustered" Storage="ColumnStore" />
  1952. <Predicate>
  1953. <ScalarOperator ScalarString="[STG_AGG].[dbo].[AGG_PRODHIER_STORE].[PROD_HIER_LEVEL]=(3) AND PROBE([Opt_Bitmap1080],[STG_AGG].[dbo].[AGG_PRODHIER_STORE].[PROD_HIER_ID])">
  1954. <Logical Operation="AND">
  1955. <ScalarOperator>
  1956. <Compare CompareOp="EQ">
  1957. <ScalarOperator>
  1958. <Identifier>
  1959. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PROD_HIER_LEVEL" />
  1960. </Identifier>
  1961. </ScalarOperator>
  1962. <ScalarOperator>
  1963. <Const ConstValue="(3)" />
  1964. </ScalarOperator>
  1965. </Compare>
  1966. </ScalarOperator>
  1967. <ScalarOperator>
  1968. <Intrinsic FunctionName="PROBE">
  1969. <ScalarOperator>
  1970. <Identifier>
  1971. <ColumnReference Column="Opt_Bitmap1080" />
  1972. </Identifier>
  1973. </ScalarOperator>
  1974. <ScalarOperator>
  1975. <Identifier>
  1976. <ColumnReference Database="[STG_AGG]" Schema="[dbo]" Table="[AGG_PRODHIER_STORE]" Column="PROD_HIER_ID" />
  1977. </Identifier>
  1978. </ScalarOperator>
  1979. </Intrinsic>
  1980. </ScalarOperator>
  1981. </Logical>
  1982. </ScalarOperator>
  1983. </Predicate>
  1984. </IndexScan>
  1985. </RelOp>
  1986. </ComputeScalar>
  1987. </RelOp>
  1988. <Predicate>
  1989. <ScalarOperator ScalarString="[Expr1036]=(0)">
  1990. <Compare CompareOp="EQ">
  1991. <ScalarOperator>
  1992. <Identifier>
  1993. <ColumnReference Column="Expr1036" />
  1994. </Identifier>
  1995. </ScalarOperator>
  1996. <ScalarOperator>
  1997. <Const ConstValue="(0)" />
  1998. </ScalarOperator>
  1999. </Compare>
  2000. </ScalarOperator>
  2001. </Predicate>
  2002. </Filter>
  2003. </RelOp>
  2004. </Hash>
  2005. </RelOp>
  2006. </Hash>
  2007. </RelOp>
  2008. </Hash>
  2009. </RelOp>
  2010. </Hash>
  2011. </RelOp>
  2012. </ComputeScalar>
  2013. </RelOp>
  2014. <Predicate>
  2015. <ScalarOperator ScalarString="[Expr1026]&lt;&gt;(0.00) OR [Expr1027]&lt;&gt;(0.00) OR [Expr1028]&lt;&gt;(0) OR [Expr1029]&lt;&gt;(0)">
  2016. <Logical Operation="OR">
  2017. <ScalarOperator>
  2018. <Compare CompareOp="NE">
  2019. <ScalarOperator>
  2020. <Identifier>
  2021. <ColumnReference Column="Expr1026" />
  2022. </Identifier>
  2023. </ScalarOperator>
  2024. <ScalarOperator>
  2025. <Const ConstValue="(0.00)" />
  2026. </ScalarOperator>
  2027. </Compare>
  2028. </ScalarOperator>
  2029. <ScalarOperator>
  2030. <Compare CompareOp="NE">
  2031. <ScalarOperator>
  2032. <Identifier>
  2033. <ColumnReference Column="Expr1027" />
  2034. </Identifier>
  2035. </ScalarOperator>
  2036. <ScalarOperator>
  2037. <Const ConstValue="(0.00)" />
  2038. </ScalarOperator>
  2039. </Compare>
  2040. </ScalarOperator>
  2041. <ScalarOperator>
  2042. <Compare CompareOp="NE">
  2043. <ScalarOperator>
  2044. <Identifier>
  2045. <ColumnReference Column="Expr1028" />
  2046. </Identifier>
  2047. </ScalarOperator>
  2048. <ScalarOperator>
  2049. <Const ConstValue="(0)" />
  2050. </ScalarOperator>
  2051. </Compare>
  2052. </ScalarOperator>
  2053. <ScalarOperator>
  2054. <Compare CompareOp="NE">
  2055. <ScalarOperator>
  2056. <Identifier>
  2057. <ColumnReference Column="Expr1029" />
  2058. </Identifier>
  2059. </ScalarOperator>
  2060. <ScalarOperator>
  2061. <Const ConstValue="(0)" />
  2062. </ScalarOperator>
  2063. </Compare>
  2064. </ScalarOperator>
  2065. </Logical>
  2066. </ScalarOperator>
  2067. </Predicate>
  2068. </Filter>
  2069. </RelOp>
  2070. </Hash>
  2071. </RelOp>
  2072. </ComputeScalar>
  2073. </RelOp>
  2074. </Parallelism>
  2075. </RelOp>
  2076. </ComputeScalar>
  2077. </RelOp>
  2078. </QueryPlan>
  2079. </StmtSimple>
  2080. </Statements>
  2081. </Batch>
  2082. </BatchSequence>
  2083. </ShowPlanXML>
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!