Search results matching tag 'Excel 2013'http://sqlblog.com/search/SearchResults.aspx?o=DateDescending&tag=Excel+2013&orTags=0Search results matching tag 'Excel 2013'en-USCommunityServer 2.1 SP2 (Build: 61129.1)Possible SWITCH Optimization in DAX – #powerpivot #dax #tabularhttp://sqlblog.com/blogs/marco_russo/archive/2014/08/18/possible-switch-optimization-in-dax-powerpivot-dax-tabular.aspxMon, 18 Aug 2014 10:54:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:54843sqlbi
<p>In one of the <a href="http://www.sqlbi.com/training/dax-workshop/" mce_href="http://www.sqlbi.com/training/dax-workshop/">Advanced DAX Workshop</a> I taught this year, I had an interesting discussion about how to optimize a SWITCH statement (which could be frequently used checking a slicer, like in the <a href="http://www.daxpatterns.com/parameter-table/" mce_href="http://www.daxpatterns.com/parameter-table/">Parameter Table pattern</a>).</p>
<p>Let’s start with the problem. What happen when you have such a statement?</p>
<font face="Consolas"><font size="3"><font color="#333333">Sales :=</font> <br></font></font><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">&nbsp;&nbsp;&nbsp; SWITCH</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">VALUES</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span><font color="#333333"> Period[Period] </font><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span></font></font><font color="#333333" face="Consolas" size="3">, <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="StringLiteral" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d93124" face="Consolas" size="3">"Current"</font></span><font color="#333333" face="Consolas" size="3">, [Internet Total Sales], <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="StringLiteral" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d93124" face="Consolas" size="3">"MTD"</font></span><font color="#333333" face="Consolas" size="3">, [MTD Sales], <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="StringLiteral" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d93124" face="Consolas" size="3">"QTD"</font></span><font color="#333333" face="Consolas" size="3">, [QTD Sales], <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="StringLiteral" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d93124" face="Consolas" size="3">"YTD"</font></span><font color="#333333" face="Consolas" size="3">, [YTD Sales], <br></font><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BLANK</font></span><font color="#d0d0d0"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"> (</span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;">)</span></font> <br></font></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0" face="Consolas" size="3">)</font></span>
<p>The SWITCH statement is in reality just syntax sugar for a nested IF statement. When you place such a measure in a pivot table, for every cell of the pivot table the IF options are evaluated. In order to optimize performance, the DAX engine usually does not compute cell-by-cell, but tries to compute the values in bulk-mode. However, if a measure contains an IF statement, every cell might have a different execution path, so the current implementation might evaluate all the possible IF branches in bulk-mode, so that for every cell the result from one of the branches will be already available in a pre-calculated dataset.</p>
<p>The price for that could be high. If you consider the previous Sales measure, the YTD Sales measure could be evaluated for all the cells where it’s not required, and also when YTD is not selected at all in a Pivot Table. The actual optimization made by the DAX engine could be different in every build, and I expect newer builds of Tabular and Power Pivot to be better than older ones. However, we still don’t live in an ideal world, so it could be better trying to help the engine finding a better execution plan.</p>
<p>One student (<a href="http://www.linkedin.com/pub/niek-de-wit/16/8b6/451" mce_href="http://www.linkedin.com/pub/niek-de-wit/16/8b6/451">Niek de Wit</a>) proposed this approach:</p>
<p><font face="Consolas"><font size="3"><font color="#333333">Selection :=</font> <br><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">IF</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">HASONEVALUE</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span><font color="#333333"> Period[Period] </font><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span></font></font><font color="#333333" face="Consolas" size="3">, <br></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">VALUES</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span><font color="#333333"> Period[Period] </font></font><font size="3"><font color="#d0d0d0"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;">)</span> <br></font><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span></font></font></p>
<p><font face="Consolas"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"></span></font><font face="Consolas"><font size="3"><font color="#333333">Sales :=</font> <br><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">CALCULATE</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">[Internet Total Sales], <br></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">FILTER</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">VALUES</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span><font color="#333333"> 'Internet Sales'[Order Quantity] </font><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span></font></font><font color="#333333" face="Consolas" size="3">, <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">'Internet Sales'[Order Quantity] <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#333333">= </font><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">IF</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#333333">[Selection] = </font><span class="StringLiteral" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d93124">"Current"</font></span></font></font><font color="#333333" face="Consolas" size="3">, <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">'Internet Sales'[Order Quantity], <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Number" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#ee7f18">-1</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span> <br></font></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#d0d0d0"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;">)</span> <br><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;">)</span></font> <br></font></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#333333">+ </font><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">CALCULATE</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">[MTD Sales], <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">FILTER</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">VALUES</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span><font color="#333333"> 'Internet Sales'[Order Quantity] </font><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span></font></font><font color="#333333" face="Consolas" size="3">, <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">'Internet Sales'[Order Quantity] <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#333333">= </font><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">IF</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#333333">[Selection] = </font><span class="StringLiteral" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d93124">"MTD"</font></span></font></font><font color="#333333" face="Consolas" size="3">, <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">'Internet Sales'[Order Quantity], <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Number" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#ee7f18">-1</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span> <br></font></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span> <br></font></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#333333">+ </font><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">CALCULATE</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">[QTD Sales], <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">FILTER</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">VALUES</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span><font color="#333333"> 'Internet Sales'[Order Quantity] </font><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span></font></font><font color="#333333" face="Consolas" size="3">, <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">'Internet Sales'[Order Quantity] <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#333333">= </font><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">IF</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#333333">[Selection] = </font><span class="StringLiteral" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d93124">"QTD"</font></span></font></font><font color="#333333" face="Consolas" size="3">, <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">'Internet Sales'[Order Quantity], <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Number" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#ee7f18">-1</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span> <br></font></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span> <br></font></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#333333">+ </font><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">CALCULATE</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">[YTD Sales], <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">FILTER</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">VALUES</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span><font color="#333333"> 'Internet Sales'[Order Quantity] </font><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span></font></font><font color="#333333" face="Consolas" size="3">, <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">'Internet Sales'[Order Quantity] <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#333333">= </font><span class="Keyword" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#0070ff">IF</font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0"> (</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><font color="#333333">[Selection] = </font><span class="StringLiteral" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d93124">"YTD"</font></span></font></font><font color="#333333" face="Consolas" size="3">, <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font color="#333333" face="Consolas" size="3">'Internet Sales'[Order Quantity], <br></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Number" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#ee7f18">-1</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span> <br></font></font><span class="indent8" style="text-align:left;line-height:26px;margin-right:6px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </font></span><font face="Consolas"><font size="3"><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font color="#d0d0d0">)</font></span> <br></font></font><span class="indent4" style="text-align:left;line-height:26px;display:inline-block;list-style-type:disc;white-space:nowrap;"><font color="#333333" face="Consolas" size="3">&nbsp;&nbsp;&nbsp; </font></span><span class="Parenthesis" style="text-align:left;line-height:26px;list-style-type:disc;white-space:nowrap;"><font face="Consolas"><font color="#d0d0d0" size="3">)</font></font></span></p>
<p>At first sight, you might think it’s impossible that this approach could be faster. However, if you examine with the profiler what happens, there is a different story. Every original IF’s execution branch is now a separate CALCULATE statement, which applies a filter that does not execute the required measure calculation if the result of the FILTER is empty. I used the ‘Internet Sales’[Order Quantity] column in this example just because in Adventure Works it has only one value (every row has 1): in the real world, you should use a column that has a very low number of distinct values, or use a column that has always the same value for every row (so it will be compressed very well!). Because the value –1 is never used in this column, the IF comparison in the filter discharge all the values iterated in the filter if the selection does not match with the desired value.</p>
<p>I hope to have time in the future to write a longer article about this optimization technique, but in the meantime I’ve seen this optimization has been useful in many other implementations. Please write your feedback if you find scenarios (in both Power Pivot and Tabular) where you obtain performance improvements using this technique!</p>DIVIDE vs division operator in #daxhttp://sqlblog.com/blogs/marco_russo/archive/2014/07/24/divide-vs-division-operator-in-dax.aspxThu, 24 Jul 2014 05:22:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:54363sqlbi<p>Alberto Ferrari wrote an interesting article about <a href="http://www.sqlbi.com/articles/divide-performance" mce_href="http://www.sqlbi.com/articles/divide-performance">DIVIDE performance</a> in DAX. This new function has been introduced in SQL Server Analysis Services 2012 SP1, so it is available also in Excel 2013 (which still doesn’t have other features/fixes introduced by following Cumulative Updates…). The idea that instead of writing:</p> <p>IF ( Sales[Quantity] &lt;&gt; 0, Sales[Amount] / Sales[Quantity], BLANK () )</p> <p>you can write:</p> <p>DIVIDE ( Sales[Amount], Sales[Quantity] )</p> <p>There is a third optional argument in DIVIDE that defines the result in case the denominator (second argument) is zero, and by default its value is BLANK, so I omitted the third argument in my example.</p> <p>Using DIVIDE is very important, especially when you use a measure in MDX (for example in an Excel PivotTable) because it raise the chance that the non empty evaluation for the result is evaluated in bulk mode instead of cell-by-cell. However, from a DAX point of view, you might find it’s better to use the standard division operator removing the IF statement. I suggest you to read Alberto’s article, because you will find that an expression applying a filter using FILTER is faster than using CALCULATE, which is against any rule of thumb you might have read until now!</p> <p>Again, this is not always true, and depends on many conditions – trying to simplify, we might say that for a simple calculation, the query plan generated by FILTER could be more efficient – but, as usual, it depends, and 90% of the times using FILTER instead of CALCULATE produces slower performance. Do not take anything for granted, and always check the query plan when performance are your first issue!</p>Calculate New, Returning, Lost, and Recovered Customers in #daxhttp://sqlblog.com/blogs/marco_russo/archive/2014/06/26/calculate-new-returning-lost-and-recovered-customers-in-dax.aspxThu, 26 Jun 2014 07:13:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:54272sqlbi<p>Calculating the number of new and returning customers is a recurring question. I would say this is a “classical” Business Intelligence problem, very common in marketing department. I worked on these problems with many customers, with small and large datasets, and I wrote a DAX Pattern “<a href="http://www.daxpatterns.com/new-and-returning-customers/">New and Returning Customers</a>” showing how to calculate:</p> <ul> <li><strong>New Customers</strong>: customers who never made any purchase</li> <li><strong>Returning Customers</strong>: customers who bought something in the past</li> <li><strong>Lost Customers</strong>: customers who bought something but did not buy in the last N days days</li> <li><strong>Recovered Customers</strong>: previously “lost customers” who made a new purchase</li> </ul> <p>This is not a brand new topic, you can find many other blog posts on this topic (<a href="http://cwebbbi.wordpress.com/2010/05/03/counting-returning-customers-in-dax/">Chris Webb</a>, <a href="http://javierguillen.wordpress.com/2012/08/24/determining-customer-retention-in-dax/">Javier Guillén</a>, <a href="http://blog.gbrueckl.at/lost-customers/">Gerhard Brueckl</a>, <a href="http://www.powerpivotpro.com/2013/01/new-customers-per-day-technique-by-david-hager/">David Hager</a>, <a href="http://www.powerpivotpro.com/2013/01/new-customers-per-day-generalized-to-new-customers-per-month-etc/">Rob Collie</a>), so my goal was to show very generic formulas that were generally the best solution in term of performance. This make the formula less readable, such as the following:</p> <p><code>[Returning Customers] := <br /></code><code>COUNTROWS</code> <code>( <br /></code><code>&#160;&#160;&#160; CALCULATETABLE</code> <code>( <br /></code><code>&#160;&#160;&#160;&#160;&#160;&#160;&#160; VALUES</code> <code>(</code> <code>&lt;customer_key_column&gt; </code><code>)</code><code>, <br /></code><code>&#160;&#160;&#160;&#160;&#160;&#160;&#160; VALUES</code> <code>(</code> <code>&lt;customer_key_column&gt; </code><code>)</code><code>, <br /></code><code>&#160;&#160;&#160;&#160;&#160;&#160;&#160; FILTER</code> <code>( <br /></code><code>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; ALL</code> <code>(</code> <code>&lt;date_column&gt; </code><code>)</code><code>, <br /></code><code>&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160;&#160; &lt;date_column&gt; &lt; </code><code>MIN</code> <code>(</code> <code>&lt;date_column&gt; </code><code>) <br /></code><code>&#160;&#160;&#160;&#160;&#160;&#160;&#160; ) <br /></code><code>&#160;&#160;&#160; ) <br /></code><code>)</code></p> <p>As you see, using CALCULATETABLE ( VALUES ( table[column] ), VALUES ( table[column] ), … ) seems a useless thing. Why counting the rows returned by VALUES and passing it also as a filter argument? This is a not so intuitive behavior of CALCULATE. The first argument is an expression that will be evaluated in a modified filter context. The third argument is a FILTER on the date column, which extends the range of dates considered, including all the past sales transactions. At this point, the first VALUES would return any customers who made a purchase in the past, but the second argument will only considered those that made a purchase in the <em>current selection</em> of time. The final result is an AND condition between two sets of customers (the intersection of the two sets), which is faster than trying to calculate the number of past transactions of each customer who made a purchase in the current selection of time, filtering only those that results in zero transactions.</p> <p>In general, I prefer using more readable DAX formulas, also in DAX patterns, optimizing them only when necessary. But in this case the performance might be important (visible to the user) also with a few thousands of customers. As usual, any feedback on the <a href="http://www.daxpatterns.com/new-and-returning-customers/">New and Returning Customers</a> pattern will be very welcome!</p>Connecting to #powerpivot from an external program (such as #Tableau)http://sqlblog.com/blogs/marco_russo/archive/2014/02/26/connecting-to-powerpivot-from-an-external-program-such-as-tableau.aspxWed, 26 Feb 2014 07:59:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:52975sqlbi<p>Many people requested me how to connect to Power Pivot from an external program, without publishing the workbook on SharePoint or on Analysis Services Tabular. I always said it is not possible (for both technical and licensing reasons), but someone observed that Tableau is able to extract data from a Power Pivot data model connecting directly to the xlsx file. I wanted to investigate how they solved the limitations that exists.</p> <p>From a technical point of view, you have to install a Tableau Add-In for Power Pivot Excel (it’s <a href="http://www.tableausoftware.com/support/drivers" mce_href="http://www.tableausoftware.com/support/drivers">available</a> for both 32 and 64 bit). Then, you connect using the Tableau Desktop software selecting the Microsoft Power Pivot connection. You choose a local Excel file and click Connect. The list of perspective appears in a combo box.</p> <p><a href="http://sqlblog.com/blogs/marco_russo/connect_664AFBDA.png" mce_href="http://sqlblog.com/blogs/marco_russo/connect_664AFBDA.png"><img width="398" height="430" title="connect" style="border:0px currentColor;border-image:none;padding-top:0px;padding-right:0px;padding-left:0px;display:inline;background-image:none;" alt="connect" src="http://sqlblog.com/blogs/marco_russo/connect_thumb_63A4D0DA.png" border="0"></a></p> <p>You click ok and you navigate into the Power Pivot data model. But what’s happening? Tableau runs an Excel instance (probably by using Excel object model) and then connects through the Tableau Add-In for Power Pivot that you installed before. Probably this add-in acts as a bridge between the process running Excel and the process running Tableau. This solve the technical problem, and it would be interesting to know how to use the same add-in from other programs without having to write the same add-in again. I know many ISVs that would love to do that!</p> <p>But before starting your project in Visual Studio to do the same (after all, it shouldn’t be rocket science writing such a connector), consider the license agreement (EULA) of Office. It says that “<i>Except for the permitted use described under "Remote Access" below, this license is for direct use of the software only through the input mechanisms of the licensed computer, such as a keyboard, mouse, or touchscreen. It does not give permission for installation of the software on a server or for use by or through other computers or devices connected to the server over an internal or external network.</i>”. It seems we are in gray area here. The access to Excel is not direct. But at the same time, it is not made on another computer, and technically you are using keyboard, mouse and/or touchscreen when you are using Tableau Desktop.</p> <p>This is certainly an unsupported scenario (and if the background Excel process hangs for any reason, you have to kill it in Task Manager). But if the licensing allows that, or if Microsoft tolerate this, probably many companies writing software (I have a long list of requests I received…) could be interested in doing the same.</p> <p>I would love to hear some official word on this topic…</p>Using Added Columns in #DAX Querieshttp://sqlblog.com/blogs/marco_russo/archive/2014/01/21/using-added-columns-in-dax-queries.aspxTue, 21 Jan 2014 14:08:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:52622sqlbi<p>I had an issue in a query that reminded me a few details naming columns created by ADDCOLUMNS.</p> <p>When you specify a simple name in ADDCOLUMNS, you can use it later, for example in a SUMMARIZE or in a FILTER expression, such as in the following example (see the Value column):</p> <p><font face="Lucida Console"><span class="Keyword">EVALUATE</span> <br /><span class="Keyword">CALCULATETABLE</span><span class="Parenthesis"> (</span> <br /><span class="indent4">&#160;&#160;&#160; </span><span class="Keyword">ADDCOLUMNS</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Keyword">ADDCOLUMNS</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="Keyword">VALUES</span><span class="Parenthesis"> (</span> 'Date'[Date] <span class="Parenthesis">)</span>, <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="StringLiteral">&quot;<font style="background-color:#ffff00;">Value</font>&quot;</span>, [Internet Total Sales] <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Parenthesis">)</span>, <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="StringLiteral">&quot;Frequency&quot;</span>, <span class="Keyword">COUNTROWS</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="Keyword">FILTER</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Keyword">VALUES</span><span class="Parenthesis"> (</span> 'Date'[Date] <span class="Parenthesis">)</span>, <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span>[Internet Total Sales] &lt;= <font style="background-color:#ffff00;">[Value]</font> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="Parenthesis">)</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Parenthesis">)</span> <br /><span class="indent4">&#160;&#160;&#160; </span><span class="Parenthesis">)</span>, <br /><span class="indent4">&#160;&#160;&#160; </span>'Date'[Calendar Year] = <span class="Number">2006</span>, <br /><span class="indent4">&#160;&#160;&#160; </span>'Date'[Month] = <span class="Number">7</span> <br /><span class="Parenthesis">)</span></font></p> <p>Naming an added column in this way makes it accessible only through its name, using the “measure” syntax, which doesn’t have a table name before the name of the column (please, remind that this is not a best practice – always use the table name before the column name when you reference a column and always omit the table name when you reference a measure!).</p> <p>So, a better way to write the previous expression could be the following one:</p> <p><font face="Lucida Console"><span class="Keyword">EVALUATE</span> <br /><span class="Keyword">CALCULATETABLE</span><span class="Parenthesis"> (</span> <br /><span class="indent4">&#160;&#160;&#160; </span><span class="Keyword">ADDCOLUMNS</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Keyword">ADDCOLUMNS</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="Keyword">VALUES</span><span class="Parenthesis"> (</span> 'Date'[Date] <span class="Parenthesis">)</span>, <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="StringLiteral">&quot;<font style="background-color:#ffff00;">’Date’[Value]</font>&quot;</span>, [Internet Total Sales] <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Parenthesis">)</span>, <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="StringLiteral">&quot;Frequency&quot;</span>, <span class="Keyword">COUNTROWS</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="Keyword">FILTER</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Keyword">VALUES</span><span class="Parenthesis"> (</span> 'Date'[Date] <span class="Parenthesis">)</span>, <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span>[Internet Total Sales] &lt;= <font style="background-color:#ffff00;">‘Date’[Value] <br /></font><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="Parenthesis">)</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Parenthesis">)</span> <br /><span class="indent4">&#160;&#160;&#160; </span><span class="Parenthesis">)</span>, <br /><span class="indent4">&#160;&#160;&#160; </span>'Date'[Calendar Year] = <span class="Number">2006</span>, <br /><span class="indent4">&#160;&#160;&#160; </span>'Date'[Month] = <span class="Number">7</span> <br /><span class="Parenthesis">)</span></font></p> <p>Until now, there is nothing really new. It could be just a good reminder. Now, for some reason a few days ago I wrote this:</p> <p><font face="Lucida Console"><span class="Keyword">EVALUATE</span> <br /><span class="Keyword">CALCULATETABLE</span><span class="Parenthesis"> (</span> <br /><span class="indent4">&#160;&#160;&#160; </span><span class="Keyword">ADDCOLUMNS</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Keyword">ADDCOLUMNS</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="Keyword">VALUES</span><span class="Parenthesis"> (</span> 'Date'[Date] <span class="Parenthesis">)</span>, <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="StringLiteral">&quot;<font style="background-color:#ffff00;">[Value]</font>&quot;</span>, [Internet Total Sales] <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Parenthesis">)</span>, <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="StringLiteral">&quot;Frequency&quot;</span>, <span class="Keyword">COUNTROWS</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="Keyword">FILTER</span><span class="Parenthesis"> (</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Keyword">VALUES</span><span class="Parenthesis"> (</span> 'Date'[Date] <span class="Parenthesis">)</span>, <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span>[Internet Total Sales] &lt;= <font style="background-color:#ffff00;">[Value] <br /></font><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="indent4">&#160;&#160;&#160; </span><span class="Parenthesis">)</span> <br /><span class="indent8">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </span><span class="Parenthesis">)</span> <br /><span class="indent4">&#160;&#160;&#160; </span><span class="Parenthesis">)</span>, <br /><span class="indent4">&#160;&#160;&#160; </span>'Date'[Calendar Year] = <span class="Number">2006</span>, <br /><span class="indent4">&#160;&#160;&#160; </span>'Date'[Month] = <span class="Number">7</span> <br /><span class="Parenthesis">)</span></font></p> <p>In this case executing the query you receive an error that says that the Value column is not found. The problem is that I used <span class="StringLiteral"><font style="background-color:#ffffff;" face="Lucida Console">&quot;[Value]&quot;</font></span> instead of&#160; <span class="StringLiteral"><font face="Lucida Console">&quot;<font style="background-color:#ffffff;">Value</font>&quot;</font></span>. So the lesson is: be careful when you name a column, don’t use square brackets unless you want to include the table name, which is a good idea so you will not confuse its semantic with a measure when you reference such a column later in your expression.</p>Power Query now imports relationships #powerquery #powerpivothttp://sqlblog.com/blogs/marco_russo/archive/2013/12/17/power-query-now-imports-relationships-powerquery-powerpivot.aspxTue, 17 Dec 2013 13:42:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:52138sqlbi<p>The December release of Power Query preview (version 2.9.3502.261) introduced an interesting feature: when you import multiple tables in the data model, relationships existing between tables in the data source are automatically detected and added to the data model as well. This is similar to the behavior you have in Power Pivot, but there is an important difference. When you import one or more tables in a data model where you already imported other tables before, the relationships are detected also between tables already imported and new tables selected for import. As you might know, Power Pivot detects relationships only between tables imported at once, not between tables imported and other tables already in the data model, even when they comes from the same data source.</p> <p>The detection in Power Query doesn’t work between tables imported in data model directly by Power Pivot. It only works between tables that have been imported using Power Query.</p> <p>I didn’t test the performance (I can imagine there is some price to pay using Power Query instead of a direct connection), but it’s clear that in the long term Power Query should be THE tool to import data in Power Pivot, or better in Excel, removing the overriding with existing functionalities that do similar things. I really like the progress that Power Query is doing, and I hope that the best is yet to come…</p>Dynamic Calculation of Inventory with #dax in #powerpivot and #ssas #tabularhttp://sqlblog.com/blogs/marco_russo/archive/2013/08/27/dynamic-calculation-of-inventory-with-dax-in-powerpivot-and-ssas-tabular.aspxTue, 27 Aug 2013 12:18:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50505sqlbi<p>I recently wrote an <a href="http://www.sqlbi.com/articles/inventory-in-power-pivot-and-dax-snapshot-vs-dynamic-calculation">article on SQLBI</a> describing the dynamic calculation of stock inventory in DAX. With this technique, you can avoid creating a snapshot fact table just to store the inventory stock for every day and every product, creating a table that in certain business is huge and expensive, especially if you have to load it in memory.</p> <p>I also performed some performance comparisons between the two approaches. I have to say that I would not suggest one option against the other, unless I can do some test on a real set of data. The problem is that the real convenience depends on data distribution and density. Moreover, performance differences also depends on the query, and I have found that the traditional snapshot-based approach is faster when performance are always good (queries below one second), but becomes slower when there are queries returning large result set. I still don’t have a clear explanation of the reason why there are these differences, so if you have any feedback based on experience on different data sets, you are more than welcome if you will share it here!</p> <p>You can read the article and download the sample workbooks (for both Excel 2010 and Excel 2013) following <a href="http://www.sqlbi.com/articles/inventory-in-power-pivot-and-dax-snapshot-vs-dynamic-calculation">this link</a>.</p>Excel 2013 stand-alone now includes #powerpivothttp://sqlblog.com/blogs/marco_russo/archive/2013/08/19/excel-2013-stand-alone-now-includes-powerpivot.aspxMon, 19 Aug 2013 06:49:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50508sqlbi
<p>This time Microsoft listened to the many complaints received since Excel 2013 release and the stand-alone version of Excel now includes Power Pivot and Power View! This is a very important news because there were customer who didn’t upgrade from Excel 2010 to Excel 2013 just because they would have lost the Power Pivot add-in!</p>
<p>This news outdates my <a mce_href="http://sqlblog.com/blogs/marco_russo/archive/2013/02/18/the-right-version-of-excel-2013-for-using-powerpivot-powerpivot-excel.aspx" href="http://sqlblog.com/blogs/marco_russo/archive/2013/02/18/the-right-version-of-excel-2013-for-using-powerpivot-powerpivot-excel.aspx">previous</a> <a mce_href="http://sqlblog.com/blogs/marco_russo/archive/2013/02/27/update-to-the-powerpivot-for-excel-2013-licensing-powerpivot-excel.aspx" href="http://sqlblog.com/blogs/marco_russo/archive/2013/02/27/update-to-the-powerpivot-for-excel-2013-licensing-powerpivot-excel.aspx">posts</a> written in February and I am very happy for that!</p>
<p>Microsoft updated <a mce_href="http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2013/02/27/unable-to-use-excel-2010-powerpivot-workbooks-in-excel-2013.aspx" href="http://blogs.technet.com/b/the_microsoft_excel_support_team_blog/archive/2013/02/27/unable-to-use-excel-2010-powerpivot-workbooks-in-excel-2013.aspx">this blog post</a> describing the SKUs that includes Power Pivot and Power View, and now the list includes the Excel stand-alone version that anyone can buy (for example <a mce_href="http://www.amazon.com/dp/B009SPJ3HY/?tag=se04-20" href="http://www.amazon.com/dp/B009SPJ3HY/?tag=se04-20">on Amazon</a>). If you already have Excel 2013 and do not see Power Pivot active, check you have <a href="http://support.microsoft.com/kb/2817425">this upgrade</a>.</p>
<p>As a side note, Power Pivot (formerly known as PowerPivot) has now an official name that is made by two words and is no longer a single word. The reason is that Microsoft announced <a mce_href="http://office.microsoft.com/en-us/excel/power-bi-FX104080667.aspx" href="http://office.microsoft.com/en-us/excel/power-bi-FX104080667.aspx">Power BI</a>, which includes Power Pivot, Power View, Power Query (code named Data Explorer), Power Map (code named GeoFlow) and much more. For a long time, we will see this name written in both ways, I think…</p>DAX Studio for Excel 2013 finally available! #dax #excel #powerpivot #ssas #tabularhttp://sqlblog.com/blogs/marco_russo/archive/2013/05/04/dax-studio-for-excel-2013-finally-available-dax-excel-powerpivot-ssas-tabular.aspxSat, 04 May 2013 05:49:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48963sqlbi<p>I'm so happy that <a href="http://www.sqlbi.com/tools/dax-studio/">DAX Studio</a> finally supports Excel 2013! As <a href="http://geekswithblogs.net/darrengosbell/archive/2013/05/04/new-release---dax-studio-1.2-with-excel-2013-support.aspx">Darren Gosbell described in his blog</a>, this release has a few internal changes that will better support future enhancements. I will port the code to capture the query plan for a query in this new release, but unfortunately it will require some weeks because I'm traveling a lot in these days.</p><p>If you write DAX formulas and queries for PowerPivot or Analysis Services Tabular, DAX Studio is a must have tool: do you really want to live without a DAX Editor? There are a lot of possible improvements and I hope other contributors will give their help to <a href="http://daxstudio.codeplex.com/">this Codeplex project</a>.</p>PowerPivot Workbook Size Optimizer #powerpivot #tabularhttp://sqlblog.com/blogs/marco_russo/archive/2013/04/30/powerpivot-workbook-size-optimizer-powerpivot-tabular.aspxTue, 30 Apr 2013 10:50:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:48929sqlbi<p>Microsoft released the <a href="http://www.microsoft.com/download/details.aspx?id=38793">Workbook Size Optimizer for Excel</a>, the first version of an Excel add-in for <strong>Excel 2013</strong> that inspects the data model and suggest possible optimizations. Fundamentally, it tries to apply the best practices descripted in a <a href="http://sqlblog.com/blogs/marco_russo/archive/2013/04/03/optimize-memory-in-powerpivot-and-ssas-tabular.aspx">white paper I mentioned</a> a few weeks ago, removing useless columns and changing granularity to those that could reduce the overall memory cost of a table.</p> <p><a href="http://sqlblog.com/blogs/marco_russo/image_600F7B61.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;float:left;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" align="left" src="http://sqlblog.com/blogs/marco_russo/image_thumb_6D095B72.png" width="96" height="97" /></a>There are different setup available in the <a href="http://www.microsoft.com/download/details.aspx?id=38793">download page</a>, depending on operating system (Windows 7 or Windows 8) and on Office version (32 or 64 bit). Once installed, you have a new tab in the Excel ribbon, called Workbook Size Optimizer, showing a single button that starts a wizard.</p> <p>I tried to run the optimizer with a workbook where I imported several tables from Adventure Works Data Warehouse sample database. The first page shows a few information about the workbook size and the option of automatic detection or manual choice of rules. The latter is an option you can request also later, so I started with the default.</p> <p><a href="http://sqlblog.com/blogs/marco_russo/image_24DB9F96.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/marco_russo/image_thumb_5CADE3B9.png" width="620" height="423" /></a></p> <p>After a short analysis, I received three smart suggestions (considered the model I have). We might wonder that removing UnitCost is a smart thing, because it could be required in order to perform calculations and rounding the value might be not correct for our analysis. </p> <p><a href="http://sqlblog.com/blogs/marco_russo/image_575EFD08.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/marco_russo/image_thumb_763570E6.png" width="620" height="423" /></a></p> <p>Since I requested to apply some changes, I have the option of changing which rules to apply. This corresponds to the choice you have if you choose “Let me choose the rules myself” in the first screen of the wizard.</p> <p><a href="http://sqlblog.com/blogs/marco_russo/image_2E07B50A.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/marco_russo/image_thumb_4CDE28E8.png" width="620" height="423" /></a></p> <p>I kept all the rules and after I click Next I had to wait several seconds in order to complete the optimization process. The result shows a few information about the result of the job.</p> <p><a href="http://sqlblog.com/blogs/marco_russo/image_3DC74A0E.png"><img title="image" style="border-top:0px;border-right:0px;background-image:none;border-bottom:0px;padding-top:0px;padding-left:0px;border-left:0px;display:inline;padding-right:0px;" border="0" alt="image" src="http://sqlblog.com/blogs/marco_russo/image_thumb_0A8B10A5.png" width="620" height="423" /></a></p> <p>This is a good starting point. Don’t blindly trust any suggestion and try to consider carefully the rules to apply in order to avoid losing important data for your analysis. Moreover, you might have a better knowledge of your data model than a wizard and consider the deletion of many useless columns (for your analysis) that are not identified by the wizard. My article <a href="http://www.sqlbi.com/articles/checklist-for-memory-optimizations-in-powerpivot-and-tabular-models/">Checklist for Memory Optimizations in PowerPivot and Tabular Models</a> contains several best practices that you can apply to your data model.</p>