SQLBI - Marco Russo : BISMhttp://sqlblog.com/blogs/marco_russo/archive/tags/BISM/default.aspxTags: BISMenCommunityServer 2.1 SP2 (Build: 61129.1)Basket Analysis with #dax in #powerpivot and #ssas #tabularhttp://sqlblog.com/blogs/marco_russo/archive/2014/06/12/basket-analysis-with-dax-in-powerpivot-and-ssas-tabular.aspxThu, 12 Jun 2014 07:25:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:54129Marco Russo (SQLBI)0http://sqlblog.com/blogs/marco_russo/comments/54129.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=54129http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=54129<p>A few days ago I published a new article on <strong>DAX Patterns</strong> web site describing how to implement <a href="http://www.daxpatterns.com/basket-analysis"><strong>Basket Analysis</strong></a> in DAX. This topic is a very classical one and is also covered in the many-to-many revolution white paper. It has been also discussed in several blog posts, listed here in historical order:</p> <ul> <li><a href="http://cwebbbi.wordpress.com/2010/10/20/simple-basket-analysis-in-dax/">Simple Basket Analysis in DAX</a> by Chris Webb</li> <li><a href="http://sqlblog.com/blogs/alberto_ferrari/archive/2011/05/19/powerpivot-basket-analysis-and-the-hidden-many-to-many.aspx">PowerPivot, basket analysis and the hidden many to many</a> by Alberto Ferrari</li> <li><a href="http://blog.gbrueckl.at/2014/02/applied-basket-analysis-in-power-pivot-using-dax/">Applied Basket Analysis in Power Pivot using DAX</a> by Gerhard Brueckl</li> </ul> <p>As usual, in DAX Patterns we try to present the required DAX formulas in a way that is easy to adapt to specific models. We also try to show a good implementation from a performance point of view. Further optimizations are always possible in DAX. However, in order to keep the model simple to adapt in different scenarios, we avoid presenting optimizations that would require particular assumptions or restrictions on the data model.</p> <p>I hope you will find the <a href="http://www.daxpatterns.com/basket-analysis">Basket Analysis</a> pattern useful. Even if you do not need it today, reading the DAX formula is a good exercise to check your knowledge of evaluation contexts in DAX. For example, describing how does it work the following expression is not a trivial task!</p> <font face="Consolas"><font size="3"><font color="#333333">[Orders with Both Products] :=</font> <br /><span class="Keyword" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#0070ff">CALCULATE</font></span><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#d0d0d0"> (</font></span> <br /></font></font><span class="indent4" style="list-style-type:disc;white-space:nowrap;text-align:left;display:inline-block;line-height:26px;"><font color="#333333" size="3" face="Consolas">&#160;&#160;&#160; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#0070ff">DISTINCTCOUNT</font></span><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#d0d0d0"> (</font></span><font color="#333333"> Sales[SalesOrderNumber] </font><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#d0d0d0">)</font></span></font></font><font color="#333333" size="3" face="Consolas">, <br /></font><span class="indent4" style="list-style-type:disc;white-space:nowrap;text-align:left;display:inline-block;line-height:26px;"><font color="#333333" size="3" face="Consolas">&#160;&#160;&#160; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#0070ff">CALCULATETABLE</font></span><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#d0d0d0"> (</font></span> <br /></font></font><span class="indent8" style="list-style-type:disc;white-space:nowrap;text-align:left;display:inline-block;line-height:26px;margin-right:6px;"><font color="#333333" size="3" face="Consolas">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#0070ff">SUMMARIZE</font></span><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#d0d0d0"> (</font></span><font color="#333333"> Sales, Sales[SalesOrderNumber] </font><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#d0d0d0">)</font></span></font></font><font color="#333333" size="3" face="Consolas">, <br /></font><span class="indent8" style="list-style-type:disc;white-space:nowrap;text-align:left;display:inline-block;line-height:26px;margin-right:6px;"><font color="#333333" size="3" face="Consolas">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#0070ff">ALL</font></span><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#d0d0d0"> (</font></span><font color="#333333"> Product </font><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#d0d0d0">)</font></span></font></font><font color="#333333" size="3" face="Consolas">, <br /></font><span class="indent8" style="list-style-type:disc;white-space:nowrap;text-align:left;display:inline-block;line-height:26px;margin-right:6px;"><font color="#333333" size="3" face="Consolas">&#160;&#160;&#160;&#160;&#160;&#160;&#160; </font></span><font face="Consolas"><font size="3"><span class="Keyword" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#0070ff">USERELATIONSHIP</font></span><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#d0d0d0"> (</font></span><font color="#333333"> Sales[ProductCode], 'Filter Product'[Filter ProductCode] </font><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;"><font color="#d0d0d0">)</font></span> <br /></font></font><span class="indent4" style="list-style-type:disc;white-space:nowrap;text-align:left;display:inline-block;line-height:26px;"><font color="#333333" size="3" face="Consolas">&#160;&#160;&#160; </font></span><font face="Consolas"><font color="#d0d0d0"><font size="3"><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;">)</span> <br /><span class="Parenthesis" style="list-style-type:disc;white-space:nowrap;color:;text-align:left;line-height:26px;">)</span></font></font></font> <p>The good news is that you can use the patterns even if you do not really understand all the details of the DAX formulas you are using! <br />Any feedback on this new pattern is very welcome.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=54129" width="1" height="1">SSASAnalysis ServicesPowerPivotDAXBISMTabularPerformance Tuning of #tabular models in Analysis Services 2012 #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/08/05/performance-tuning-of-tabular-models-in-analysis-services-2012-ssas.aspxMon, 05 Aug 2013 12:37:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:50382Marco Russo (SQLBI)0http://sqlblog.com/blogs/marco_russo/comments/50382.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=50382http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=50382<p>In the last weeks the Microsoft BI buzzword has been <a href="http://office.microsoft.com/en-us/excel/power-bi-FX104080667.aspx">Power BI</a> for Office 365, which will be available soon in preview. I read a few comments about the fact that there is a constant discussion about the future but a lack of information and discussion about what is really used and required today in the real world. I think it is a good news that Microsoft released the first important white paper about performance in Tabular: <a href="http://download.microsoft.com/download/D/2/0/D20E1C5F-72EA-4505-9F26-FEF9550EFD44/Performance%20Tuning%20of%20Tabular%20Models%20in%20SQL%20Server%202012%20Analysis%20Services.docx">Performance Tuning of Tabular Models in SQL Server 2012 Analysis Services</a>.</p> <p>I suggest the reading of this white paper to all of you using Analysis Services Tabular and also to Power Pivot modelers. If you already have our <a href="http://www.sqlbi.com/books/microsoft-sql-server-2012-analysis-services-the-bism-tabular-model/">SSAS Tabular book</a>, you will find some more details in the white paper in many areas. And remember the repository we have for DAX Query Plan analysis on SQLBI: <a href="http://www.sqlbi.com/topics/query-plans/"><font color="#0066cc">http://www.sqlbi.com/topics/query-plans/</font></a> – we hopefully will add more content in the next months, after the DAX Query Plans <a href="http://www.sqlbi.com/topics/query-plans/">white paper</a> written by Alberto Ferrari.</p> <p>The performance optimization in Analysis Services Tabular is a brand new topic: it’s different from the past, it’s not like SQL and it’s not like MDX. We still don’t have the right tools and the proper knowledge and experience written in books, but I think we’re on the right track to fill the gap.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=50382" width="1" height="1">SSASAnalysis ServicesPowerPivotBISMTabularCampaign for #PowerPivot and #Tabular textual modeling language #msbi #ssashttp://sqlblog.com/blogs/marco_russo/archive/2013/06/04/campaign.aspxMon, 03 Jun 2013 22:14:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:49331Marco Russo (SQLBI)4http://sqlblog.com/blogs/marco_russo/comments/49331.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=49331http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=49331<p>If you think PowerPivot and Tabular are good tools but sometime the design tools are not fast/complete/flexible enough, please consider voting <a href="https://connect.microsoft.com/SQLServer/feedback/details/789392/use-a-textual-representation-dsl-of-tabular-or-powerpivot-data-model">this suggestion on Connect to request a textual representation Tabular or PowerPivot Data Models</a>.</p>
<p>This is the simplest way to improve the Productivity of PowerPivot and Tabular users/analysts/designers.&nbsp;</p>
<p>I would like having a&nbsp;simple textual representation of a BISM model, that can be translated back and forth to a XAML representation of the data model (this could be then integrated within Visual Studio, but other development tools might use this language as well).<br>I’d like to write (for Tabular) something like:<br>&nbsp;<br>TABLE Customers (<br>&nbsp;&nbsp;&nbsp; CustomerKey INTEGER,<br>&nbsp;&nbsp;&nbsp; Name STRING,<br>&nbsp;&nbsp;&nbsp; Weight FLOAT<br>)<br>&nbsp;<br>TABLE Sales (<br>&nbsp;&nbsp;&nbsp; CustomerKey INTEGER ref Customers[CustomerKey],<br>&nbsp;&nbsp;&nbsp; Quantity INTEGER,<br>&nbsp;&nbsp;&nbsp; Amount CURRENCY<br>)<br>&nbsp;<br>This would be the syntax to keep the functional-style, even if I would prefer a more declarative language such as:<br>&nbsp;<br>TABLE Customers <br>&nbsp;&nbsp;&nbsp; CustomerKey INTEGER<br>&nbsp;&nbsp;&nbsp; Name STRING<br>&nbsp;&nbsp;&nbsp; Weight FLOAT<br>END TABLE<br>&nbsp;<br>TABLE Sales <br>&nbsp;&nbsp;&nbsp; CustomerKey INTEGER ref Customers[CustomerKey]<br>&nbsp;&nbsp;&nbsp; Quantity INTEGER<br>&nbsp;&nbsp;&nbsp; Amount CURRENCY<br>END TABLE<br>&nbsp;<br>We can discuss about the syntax, but we all know that this can be done, especially for Tabular which has a smaller number of properties to handle if compared to Multidimensional (which could benefit from the same approach, too).<br>The implementation of tools enabling the translation of models to/from this language would be cheaper that writing/improving advanced graphical editors and many other features that you have for free once you have a text file:</p>
<ul>
<li>Source control</li>
<li>Revision history</li>
<li>Version comparison</li>
<li>Team development</li>
</ul>
<p>This is the single feature for Tabular/PowerPivot with the best ROI that I can imagine.<br>And if Microsoft does not time to implement it… well, at least they could publish a document such as “Tabular Language Draft 1.0” and somebody in the community will implement the necessary translation tools. Having an "official" Language is important, because in this way nobody would spend time on something that could change too much in the future.<br>&nbsp;<br>Are you convinced? Nice! <br>So please, <strong>help me</strong> in this campaign for a <strong>PowerPivot and Tabular textual modeling language</strong>.<br>This is the call for action:</p>
<ol>
<li>Vote <a href="https://connect.microsoft.com/SQLServer/feedback/details/789392/use-a-textual-representation-dsl-of-tabular-or-powerpivot-data-model">this item on Connect</a></li>
<li>Spread the word&nbsp;as loud as you can!</li>
</ol>
<p>Don't wait, do it now!</p><img src="http://sqlblog.com/aggbug.aspx?PostID=49331" width="1" height="1">SSASAnalysis ServicesPowerPivotBISMTabularWorkshop and Events in Europe next month #ssas #tabular #powerpivothttp://sqlblog.com/blogs/marco_russo/archive/2013/02/21/workshop-and-events-in-europe-next-month-ssas-tabular-powerpivot.aspxThu, 21 Feb 2013 09:41:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47828Marco Russo (SQLBI)0http://sqlblog.com/blogs/marco_russo/comments/47828.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=47828http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=47828<p>As I <a href="http://sqlblog.com/blogs/marco_russo/archive/2013/02/06/march-2013-events-and-workshops-in-aarhus-dk-and-utrecht-nl-ssas-powerpivot.aspx">mentioned</a> a couple of weeks ago, I and Alberto are starting to travel again, delivering SSAS Tabular Workshop and other free community events around Europe. This is the updated list of our next dates, including community events:</p> <ul> <li><strong>Online – <a href="http://www.sqlbi.com/courses/ssas-tabular-workshop-online-feb2013/">SSAS Tabular Workshop on February 27-28, 2013</a></strong> (17-24 CET)</li> <ul> <li>This workshop is delivered online – you can attend wherever you are</li> <li>Timeslot is late afternoon-evening for Europeans, more appropriate office time for US market</li> </ul> <li><strong>Aarhus, Denmark</strong> – <strong><a href="http://www.sqlbi.com/courses/ssas-tabular-workshop-aarhus-mar2013/">SSAS Tabular Workshop on March 12-13, 2013</a></strong> (9-17 CET)</li> <ul> <li>Alberto Ferrari will be the teacher in these dates</li> <li>You can still <a href="http://www.sqlbi.com/courses/ssas-tabular-workshop-aarhus-mar2013/">register here</a></li> </ul> <li><strong>Aarhus, Denmark</strong> – Excel 2013 PowerPivot in Action on March 12, 2013 (17:30-19:30 CET)</li> <ul> <li>Free community event where Alberto will show the capabilities available in Excel 2013 using PowerPivot and Power View</li> <li>More <a href="http://msbip.dk/events/60/msbip-mode-nr-12/">info here</a></li> </ul> <li><strong>Utrecht, The Netherlands</strong> – <strong><a href="http://www.sqlbi.com/courses/ssas-workshop-utrecht-mar2012/">SSAS Tabular Workshop on March 25-26, 2013</a></strong> (9-17 CET)</li> <ul> <li>Marco Russo (me!) will be the teacher in these dates</li> <li>You can <a href="http://www.sqlbi.com/courses/ssas-workshop-utrecht-mar2012/">register here</a> (early bird expires on March 11, 2013)</li> </ul> <li><strong>Veenendaal, The Netherlands</strong> - Querying and Optimizing DAX on March 25, 2013 (18-21 CET)</li> <ul> <li>Free community event where Marco Russo (yes, always me!) will show how to write and optimize queries in DAX</li> <li>More <a href="http://www.sqlpass.nl/2013/02/pass-nederland-bijeenkomst-met-marco-russo-op-25-maart-te-veenendaal/">info here</a> and <a href="http://passnl-marcorusso2013.eventbrite.com/#">free registration here</a></li> </ul> <li><strong>Oslo, Norway</strong> – <strong><a href="http://www.sqlbi.com/courses/ssas-workshop-oslo-ap2012/">SSAS Tabular Workshop on April 8-9, 2013</a></strong> (9-17 CET)</li> <ul> <li>Alberto Ferrari will be the teacher in these dates</li> <li>You can <a href="http://www.sqlbi.com/courses/ssas-workshop-oslo-ap2012/">register here</a></li> </ul> <li><strong>Milan, Italy </strong>– <a href="http://www.sqlbi.com/courses/powerpivot-workshop-full/"><strong>PowerPivot Workshop Full on April 17-19, 2013</strong></a> (9-17 CET)</li> <ul> <li>Alberto will be the teacher in these dates</li> <li>Be careful: this workshop will be <em>delivered in Italian language</em> – a good excuse to practice your Italian visiting Italy for work reasons!</li> <li>You can <a href="http://www.sqlbi.com/courses/powerpivot-workshop-full/">register here</a></li> </ul> </ul> <p>Other workshops will follow in May and June in Finland, UK, Belgium and Sweden – a complete list of <a href="http://www.sqlbi.com/training/">Tabular Workshop here</a> and <a href="http://www.powerpivotworkshop.com/">PowerPivot Workshops here</a>.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=47828" width="1" height="1">Analysis ServicesBISMTabularSQLBits XI in May 2013 – many reasons to attend! #sqlbits #dax #tabularhttp://sqlblog.com/blogs/marco_russo/archive/2013/02/20/sqlbits-xi-in-may-2013-many-reasons-to-attend-sqlbits-dax-tabular.aspxWed, 20 Feb 2013 09:33:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:47803Marco Russo (SQLBI)0http://sqlblog.com/blogs/marco_russo/comments/47803.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=47803http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=47803<p>I and <a href="http://sqlblog.com/blogs/alberto_ferrari">Alberto Ferrari</a> will speak at <a href="http://www.sqlbits.com">SQLBits XI</a> (Nottingham, UK – May 2-4, 2013) and there are many good reasons to join us there, especially if you are interested to DAX!</p> <p>Here are our sessions:</p> <ul> <li><strong>Thursday, 02 May 2013 </strong>– Training Day - <strong><a href="http://www.sqlbits.com/Sessions/Event11/From_0_to_DAX2">From 0 to DAX</a></strong></li> <ul> <li>The entire day is dedicated to learning DAX, starting from the syntax and going forward with more complex syntaxes for both expressions and queries in DAX.</li> <li>Technical level: the goal is to be introductive, explaining the important concepts in DAX (such as filter context, row context, context transitions) and covering the usage of the most common DAX functions.</li> <li>Target audience: Information Workers interested in exploiting the many interesting features of PowerPivot for Excel 2013 and BI developers who want to learn the DAX language.</li> <li>In practice: if you started using DAX two years ago and created several projects in Tabular and/or PowerPivot with complex calculations, this is not the workshop for you (read about an <a href="http://www.sqlbi.com/courses/dax-advanced-workshop-london-may2012/">Advanced DAX Workshop</a> later in this post). But if you just started your first Tabular or PowerPivot projects and you are still wondering what CALCULATE is all about, then this is definitely the right training for you!</li> </ul> <li><strong>Friday, 03 May 2013</strong> – 10:50am – <strong><a href="http://www.sqlbits.com/Sessions/Event11/Modern_Data_Warehousing_Strategy">Modern Data Warehousing Strategy</a></strong></li> <ul> <li>How to design a Data Warehouse in 2013? Should we change something considering the introduction of in-memory technologies such as xVelocity? Is Self-Service BI affecting the way we design and implement a Corporate Data Warehouse?</li> <li>This is not a session about a specific feature of a product, but features and products have their effects on the data warehouse design. We’ll stop one hour trying to understand what changes in the big picture, affecting also our day-by-day job.</li> </ul> <li><strong>Friday, 03 May 2013</strong> – 2:40pm – <strong><a href="http://www.sqlbits.com/Sessions/Event11/Inside_xVelocity_InMemory_Engine_VertiPaq_">Inside xVelocity InMemory Engine (VertiPaq)</a></strong></li> <ul> <li>Do you use PowerPivot and/or Analysis Services Tabular? They use the same engine (xVelocity/VertiPaq) and you should know how it works.</li> <li>This session will show how xVelocity/VertiPaq works, how it compresses and stores data and why it is so fast answering to your queries.</li> <li>Why this is important? Because it is geeky! And because this knowledge will help you optimizing storage and DAX queries.</li> </ul> <li><strong>Saturday, 04 May 2013 </strong>– 10:50am – <strong><a href="http://www.sqlbits.com/Sessions/Event11/DAX_Query_Engine_Internals">DAX Query Engine Internals</a></strong></li> <ul> <li>DAX is amazingly fast. But some query might be not fast enough.</li> <li>Do you want to know how to optimize your DAX query? This session is for you.</li> <li>Knowing the internals of xVelocity InMemory engine is just the first step (see Friday’s session). Then you need to apply this knowledge to DAX and understand why different DAX syntaxes for the same result might have different performance.</li> <li>Understanding cross-filtering is a key to master DAX optimization. The more you know DAX, the more you will appreciate this session!</li> </ul> </ul> <p>As I said before, the training day is an introductive course. If you already know DAX and want to improve your skills, there is a <a href="http://sqlblog.com/blogs/marco_russo/archive/2013/02/07/first-dax-advanced-workshop-in-london-may-2013-dax-tabular-ssas.aspx"><strong>3-day Advanced DAX Workshop</strong></a> I will teach in London on May 13-15, 2013. It could be also a good reason to visit London if you come from other countries!</p> <p>Now that we are almost done with the <a href="http://www.sqlbi.com/books/excel-2013-building-data-models-with-powerpivot/">PowerPivot for Excel 2013</a> book, we will write <a href="http://www.sqlbi.com/articles/">more articles</a> – stay tuned!</p><img src="http://sqlblog.com/aggbug.aspx?PostID=47803" width="1" height="1">Conference CommunitiesConferenceCommunitiesAnalysis ServicesDAXBISMTabularxVelocity engines compared: VertiPaq vs ColumnStore #ssas #vertipaq #xvelocity #sql #tabularhttp://sqlblog.com/blogs/marco_russo/archive/2012/08/22/xvelocity-engines-compared-vertipaq-vs-columnstore-ssas-vertipaq-xvelocity-sql-tabular.aspxWed, 22 Aug 2012 11:56:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44767Marco Russo (SQLBI)4http://sqlblog.com/blogs/marco_russo/comments/44767.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=44767http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=44767<p>During the last months I and Alberto worked in several projects using Analysis Services Tabular and we had to face real world issues, such as complex queries, large data volume, frequent data updates and so on. Sometime we faced the challenge of comparing Tabular performance with SQL Server. It seemed a non-sense, because even if the same core xVelocity technology is implemented in both products (SQL Server 2012 uses ColumnStore indexes, whereas Analysis Services 2012 uses VertiPaq), we initially assumed that the better optimization for the in-memory engine used by Analysis Services would have been always better than SQL Server.</p> <p>However, we discovered several important things:</p> <ul> <li>Processing time might be different and having data on SQL Server could make ColumnStore way faster for processing.</li> <li>Partitioning in SQL Server might be much more effective for query performance than Analysis Services.</li> <li>A single query can scale easily on more processor on SQL Server, whereas in Analysis Services the formula engine is single-threaded and could be a bottleneck for certain queries.</li> <li>In case of a large workload with many concurrent users, storage engine cache in Analysis Services could be a big advantage over SQL Server, especially for scalability</li> </ul> <p>As you can see, these considerations are not always obvious and you might be tempted to make other assumptions based on these information. Well, don’t do that. Before anything else, read the whitepaper <a href="http://www.sqlbi.com/articles/vertipaq-vs-columnstore/">VertiPaq vs ColumnStore Comparison</a> written by Alberto Ferrari. Then, measure your workload. Finally, make some conclusion. But don’t make too many assumptions. You might be wrong, as we did at the beginning of this journey.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=44767" width="1" height="1">SSASAnalysis ServicesBISMTabularFinally in stock: Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model #ssas #tabularhttp://sqlblog.com/blogs/marco_russo/archive/2012/07/26/finally-in-stock-microsoft-sql-server-2012-analysis-services-the-bism-tabular-model-ssas-tabular.aspxThu, 26 Jul 2012 10:32:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44442Marco Russo (SQLBI)6http://sqlblog.com/blogs/marco_russo/comments/44442.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=44442http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=44442<p>It has been a long wait, but finally our book about Analysis Services Tabular is available!</p> <p><span id="btAsinTitle"><strong><a href="http://www.amazon.com/gp/product/0735658188/?tag=se04-20"><img title="bismtabularbook" 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="bismtabularbook" align="left" src="http://sqlblog.com/blogs/marco_russo/bismtabularbook_58BD0945.png" width="208" height="266" /></a>Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model</a></strong></span></p> <p>Amazon is already shipping it in US and it should be available soon also in Europe. However, the Kindle edition is already available everywhere! If you like to order it on Amazon, here are the links for all the shops:</p> <ul> <li>Amazon.com: <a href="http://www.amazon.com/gp/product/0735658188/?tag=se04-20">hardcopy</a> – <a href="http://www.amazon.com/gp/product/B008LYQ056/?tag=se04-20">kindle</a> </li> <li>Amazon.ca: <a href="http://www.amazon.ca/gp/product/0735658188/?tag=s087a1-20">hardcopy</a> </li> <li>Amazon.co.uk: <a href="http://www.amazon.co.uk/gp/product/0735658188/?tag=wwwsqlbicom08-21">hardcopy</a> – <a href="http://www.amazon.co.uk/gp/product/B008LYQ056/?tag=wwwsqlbicom08-21">kindle</a> </li> <li>Amazon.de: <a href="http://www.amazon.de/gp/product/0735658188/?tag=wwwsqlbicom00-21">hardcopy</a> – <a href="http://www.amazon.de/gp/product/B008LYQ056/?tag=wwwsqlbicom00-21">kindle</a> </li> <li>Amazon.es: <a href="http://www.amazon.es/gp/product/0735658188/?tag=wwwsqlbicom0f-21">hardcopy</a> – <a href="http://www.amazon.es/gp/product/B008LYQ056/?tag=wwwsqlbicom0f-21">kindle</a> </li> <li>Amazon.fr: <a href="http://www.amazon.fr/gp/product/0735658188/?tag=wwwsqlbicom06-21">hardcopy</a> – <a href="http://www.amazon.fr/gp/product/B008LYQ056/?tag=wwwsqlbicom06-21">kindle</a> </li> <li>Amazon.it: <a href="http://www.amazon.it/gp/product/0735658188/?tag=wwwsqlbicom-21">hardcopy</a> – <a href="http://www.amazon.it/gp/product/B008LYQ056/?tag=wwwsqlbicom-21">kindle</a> </li> </ul> <p>You can <a href="http://www.amazon.com/gp/product/B008LYQ056/?tag=se04-20#reader_B008LYQ056">follow this link</a> to take a look at the Table of Contents, at the Introduction and some content of other chapters. I suggest you to read the introduction and the &quot;Who Should Read This Book” section before buying the book, so you will be sure you are getting the right one. Well, if you read this blog, you should be in the right target audience, but double check is not bad.</p> <p>For your convenience, this is the list of the chapters:</p> <ul> <li>Chapter 1 Introducing the Tabular Model </li> <li>Chapter 2 Getting Started with the Tabular Model </li> <li>Chapter 3 Loading Data Inside Tabular </li> <li>Chapter 4 DAX Basics </li> <li>Chapter 5 Understanding Evaluation Context </li> <li>Chapter 6 Querying Tabular </li> <li>Chapter 7 DAX Advanced </li> <li>Chapter 8 Understanding Time Intelligence in DAX </li> <li>Chapter 9 Understanding xVelocity and DirectQuery </li> <li>Chapter 10 Building Hierarchies </li> <li>Chapter 11 Data Modeling in Tabular </li> <li>Chapter 12 Using Advanced Tabular Relationships </li> <li>Chapter 13 The Tabular Presentation Layer </li> <li>Chapter 14 Tabular and PowerPivot </li> <li>Chapter 15 Security </li> <li>Chapter 16 Interfacing with Tabular </li> <li>Chapter 17 Tabular Deployment </li> <li>Chapter 18 Optimizations and Monitoring </li> <li>Appendix A DAX Functions Reference </li> </ul> <p>We already received a good review but I hope to see many others – of course we all like the good ones, but we always look carefully at every comment so that we can improve the next one!</p> <p>I’d like to give a big thank you to all the people who helped us reaching this goal. I know we set an ambitious target, trying to cover a brand new engine and development environment in a very deep way despite its infancy on the market. I think that this is just the first step in this new world, Tabular will evolve and we’ll gain more experience working with it on real word projects. But I hope that many BI developers will find it easier to start a new Tabular project with the guidance of this book. We have found that Tabular is good not only for “simple” models, but also for the complex ones, when Multidimensional faced some limitations in term of leaf-level calculation. I still don’t use Tabular for any new project, there are still many scenarios in which Multidimensional is a better choice, but don’t wait too much to start playing with DAX and Tabular. You might miss some real opportunities.</p> <p>I hope you will enjoy the reading!</p><img src="http://sqlblog.com/aggbug.aspx?PostID=44442" width="1" height="1">SSASBookBISMTabularWatch @marcorus and @ferrarialberto sessions online #teched #msteched #tee2012http://sqlblog.com/blogs/marco_russo/archive/2012/07/07/watch-marcorus-and-ferrarialberto-sessions-online-teched-msteched-tee2012.aspxSat, 07 Jul 2012 05:41:30 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:44221Marco Russo (SQLBI)0http://sqlblog.com/blogs/marco_russo/comments/44221.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=44221http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=44221<p>In June I participated to two TechEd editions (<a href="http://northamerica.msteched.com">North America</a> and <a href="http://europe.msteched.com/">Europe</a>). I and Alberto delivered a Pre Conference and two sessions about Tabular. Both conferences provides recorded sessions freely available on Channel 9 so that you can compare which one has been delivered in the best way! If you have to choose between the two versions, consider that in North America we receive more questions during and after the session (still recording), increasing the interaction, whereas in Europe questions usually comes after the session finished (so no recording available). If you’re curious, watch both and let me know which version you prefer, especially for Multidimensional vs Tabular!</p> <ul> <li><a href="http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI319">BISM: Multidimensional vs. Tabular</a> (TechEd North America 2012)</li> <li><a href="http://channel9.msdn.com/Events/TechEd/Europe/2012/DBI319">BISM: Multidimensional vs. Tabular</a> (TechEd Europe 2012)</li> <li><a href="http://channel9.msdn.com/Events/TechEd/NorthAmerica/2012/DBI413">Many-to-Many Relationships in BISM Tabular</a> (TechEd North America 2012)</li> <li><a href="http://channel9.msdn.com/Events/TechEd/Europe/2012/DBI413">Many-to-Many Relationships in BISM Tabular</a> (TechEd Europe 2012)</li> </ul> <p>If you are interested to learn SSAS Tabular, don’t miss the next <a href="http://www.sqlbi.com/courses/ssas-workshop-online-sep2012/">SSAS Tabular Workshop online on September 3-4, 2012</a>. We are also planning dates for another roadshow in Europe this fall and I’m happy to announce we’ll have two dates in Germany, too. More updates in the coming weeks.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=44221" width="1" height="1">Analysis ServicesBISMTabularTechEdOptimize Many-to-Many with SUMMARIZE and Other Techniqueshttp://sqlblog.com/blogs/marco_russo/archive/2012/06/01/optimize-many-to-many-with-summarize-and-other-techniques.aspxFri, 01 Jun 2012 12:12:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:43669Marco Russo (SQLBI)0http://sqlblog.com/blogs/marco_russo/comments/43669.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=43669http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=43669<p>We are still in the early days of DAX and even if I have been using it since 2 years ago, there is still a lot to learn on that.</p> <p>One of the topics that historically interests me (and many of the readers here, probably) is the many-to-many relationships between dimensions in a dimensional data model. When I and Alberto wrote the <a href="http://www.sqlbi.com/articles/many2many/">The Many to Many Revolution 2.0</a> we discovered the SUMMARIZE based pattern very late in the whitepaper writing. It is very important for performance optimization and it should be always used. In the last month, <a href="http://gbrueckl.wordpress.com/2012/05/08/resolving-many-to-many-relationships-leveraging-dax-cross-table-filtering">Gerhard Brueckl</a> also presented an approach based on cross table filtering behavior that simplify the syntax involved, even if it’s harder to explain how it works internally.</p> <p>I published a short article titled <a href="http://www.sqlbi.com/articles/optimize-many-to-many-calculation-in-dax-with-summarize-and-cross-table-filtering/">Optimize Many-to-Many Calculation in DAX with SUMMARIZE and Cross Table Filtering</a> on <a href="http://www.sqlbi.com/">SQLBI</a> website just to provide a quick reference to the three patterns available. A further study is still required to compare performance between SUMMARIZE and Cross Table Filtering patterns. Up to now, I haven’t observed big differences between them, even if their execution plans might be not identical and this suggest me that depending on other conditions you might favor one over the other.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=43669" width="1" height="1">M2MAnalysis ServicesPowePivotBISMTabularOrder of Evaluation in CALCULATE and Clever Hierarchy Handling in #daxhttp://sqlblog.com/blogs/marco_russo/archive/2012/05/02/order-of-evaluation-in-calculate-and-clever-hierarchy-handling-in-dax.aspxWed, 02 May 2012 04:36:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:43129Marco Russo (SQLBI)0http://sqlblog.com/blogs/marco_russo/comments/43129.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=43129http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=43129<p>After the first two editions of the <a href="http://www.ssasworkshop.com/">SSAS Tabular Workshop</a>, I and Alberto had some time to flush some of the pending activities. We published two articles:</p> <ul> <li><a href="http://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/">Clever Hierarchy Handling in DAX</a>: DAX is not a hierarchy-friendly language, especially if you have a MDX background. If you have to implement hierarchy-based calculation, MDX is still better. But if you need to do that in DAX (for example, because you need this in a DAX measure), then you have to read <a href="http://www.sqlbi.com/articles/clever-hierarchy-handling-in-dax/">this article</a> written by Alberto Ferrari that explains how to correctly calculate the classical “ratio to parent” pattern in several different scenarios.</li> <li><a href="http://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/">Order of Evaluation in CALCULATE Parameters</a>: a common mistake when you start working with DAX is understanding the evaluation order of CALCULATE parameters and what happens when you have nested CALCULATE statements. The topic can be very dense, so I tried to write a <a href="http://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/">simple article</a> that explain the basic concepts and based on the feedback I’ll receive, I’ll consider writing other articles on this subject. So feedback is welcome!</li> </ul> <p>We have other ideas and material that I hope we’ll be able to write and publish in the upcoming weeks. In the meantime, there are plenty of other editions of the <a href="http://www.ssasworkshop.com/">workshop</a> this month, starting from the online one next week that is <a href="http://www.sqlbi.com/courses/ssas-workshop-online-may2012/">arranged for US time zone</a>. Don’t miss it!</p><img src="http://sqlblog.com/aggbug.aspx?PostID=43129" width="1" height="1">DAXBISMTabularLast minute update #ssas #tabular community event in Amsterdam on April 16http://sqlblog.com/blogs/marco_russo/archive/2012/04/14/last-minute-update-ssas-tabular-community-event-in-amsterdam-on-april-16.aspxFri, 13 Apr 2012 23:45:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:42823Marco Russo (SQLBI)0http://sqlblog.com/blogs/marco_russo/comments/42823.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=42823http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=42823<p>We finally have the registration website for the community event about Tabular in Analysis Services in Amsterdam on April 16 evening that I introduced in my previous <a href="http://sqlblog.com/blogs/marco_russopost/">post</a>&nbsp;- don't miss it, the registration are open (and limited!) at:</p><p><a href="http://passnl-april2012.eventbrite.com/"><strong>http://passnl-april2012.eventbrite.com/</strong></a></p><p>This is really a "last-minute" event, but we're pretty confident the room will be full anyway! See you there!</p><img src="http://sqlblog.com/aggbug.aspx?PostID=42823" width="1" height="1">SSASAnalysis ServicesBISMTabular#SSAS #Tabular Workshop and Community Events in Netherlands and Denmarkhttp://sqlblog.com/blogs/marco_russo/archive/2012/04/12/ssas-tabular-workshop-and-community-events-in-netherlands-and-denmark.aspxThu, 12 Apr 2012 11:04:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:42744Marco Russo (SQLBI)1http://sqlblog.com/blogs/marco_russo/comments/42744.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=42744http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=42744<p>Next week I will finally start the roadshow of the <a href="http://www.ssasworkshop.com/">SSAS Tabular Workshop</a>, a 2-day seminar about the new BISM Tabular model for Analysis Services that has been introduced in SQL Server 2012. During these roadshows, we always try to arrange some speeches at local community events in the evening&nbsp;- we&nbsp;already defined for&nbsp;Copenhagen, we have some logistic issue in Amsterdam that we're trying to solve.&nbsp;Here is the timetable:</p> <ul> <li>Netherlands</li> <ul> <li>SSAS Workshop in Amsterdam, NL – April 16-17, 2012</li> <ul> <li>2-day seminar, I and Alberto will be the trainers for this event, register <a href="http://www.sqlbi.com/courses/ssas-workshop-amsterdam-apr2012/">here</a></li> </ul> <li>We're trying to manage a Community event but we still don't have&nbsp;a confirmation, stay tuned</li><ul><li><strong>UPDATE: the community event has been comfirmed, please register here: <span style="font-size:12pt;mso-fareast-font-family:Calibri;mso-fareast-theme-font:minor-latin;mso-ansi-language:IT;mso-fareast-language:IT;mso-bidi-language:AR-SA;"><a href="http://passnl-april2012.eventbrite.com/"><font color="#0000ff">http://passnl-april2012.eventbrite.com/</font></a></span></strong></li></ul>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; </ul> <li>Denmark</li> <ul> <li>SSAS Workshop in Copenhagen,&nbsp;DK – April 26-27, 2012</li> <ul> <li>2-day seminar, I and Alberto will be the trainers for this event, register <a href="http://www.sqlbi.com/courses/ssas-workshop-copenhagen-apr2012/">here</a></li> </ul> <li>Community event on April 26, 2012</li> <ul> <li>This event will run in Hellerup, at Microsoft venue</li> <li>All details available here: <a href="http://msbip.dk/events/26/msbip-mode-nr-5/">http://msbip.dk/events/26/msbip-mode-nr-5/</a></li> <li>People from Sweden are welcome! Just <a href="http://www.linkedin.com/groups?gid=4388818">register to this private group</a> on LinkedIn in order to announce your presence, so we’ll know how many people will attend</li> </ul> </ul> </ul> <p>In community events we’ll deliver two speeches – here are the descriptions:</p> <blockquote> <p><strong>Inside xVelocity (VertiPaq) </strong> <br>PowerPivot and BISM Tabular models in Analysis Services share a great columnar-based database engine called xVelocity in-memory analytics engine (VertiPaq). If you want to improve performance and optimize memory used, you have to understand some basic principles about how this engine works, how data is compressed, and how you can design a data model for better optimization. Prepare yourself to change your mind. xVelocity optimization techniques might seem counterintuitive and are absolutely different than OLAP and SQL ones!</p> <p><strong>Choosing between Tabular and Multidimensional</strong> <br>You have a new project and you have to make an important decision upfront. Should you use Tabular or Multidimensional? It is not easy to answer, because sometime there is a clear choice, but most of the times both decisions might be correct, at least at the beginning. In this session we’ll help you making an informed decision, correctly evaluating pros and cons of each one according to common scenarios, considering both short-term and long-term consequences of your choice.</p> </blockquote> <p>I hope to meet many people in this first dates. We have <a href="http://www.ssasworkshop.com/">many other events coming in May and June</a>, including an <a href="http://www.sqlbi.com/courses/ssas-workshop-online-may2012/">online event</a> (for US time zones), and you can also attend our PreCon Day at <a href="http://northamerica.msteched.com/preconferenceseminars">TechEd US in Orland (PRC06)</a> or <a href="http://europe.msteched.com/PreCons">TechEd Europe</a> in Amsterdam. I’ll be a good customer for airline companies in the next three months!</p> <p>I’m just sorry that I hadn’t time to write other articles in the last month, but I’m accumulating material that I will need to write down during some flight – stay tuned…</p><img src="http://sqlblog.com/aggbug.aspx?PostID=42744" width="1" height="1">CommunitiesAnalysis ServicesBISMTabularDistinct Count of Customers in a SCD Type 2 in #DAXhttp://sqlblog.com/blogs/marco_russo/archive/2012/04/09/distinct-count-of-customers-in-a-scd-type-2-in-dax.aspxMon, 09 Apr 2012 20:55:25 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:42750Marco Russo (SQLBI)3http://sqlblog.com/blogs/marco_russo/comments/42750.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=42750http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=42750<p>If you have a Slowly Changing Dimension (SCD) Type 2 for your customer and you want to calculate the number of distinct customers that bought a product, you cannot use the simple formula:</p> <p>Customers := DISTINCTCOUNT( FactTable[Customer Id] ) )</p> <p>because it would return the number of distinct versions of customers. What you really want to do is to calculate the number of distinct application keys of the customers, that could be a lower number than the number you’ve got with the previous formula. Assuming that a Customer Code column in the Customers dimension contains the application key, you should use the following DAX formula:</p> <p>Customers := COUNTROWS( SUMMARIZE( FactTable, Customers[Customer Code] ) )</p> <p>Be careful: only the version above is really fast, because it is solved by xVelocity (formerly known as VertiPaq) engine. Other formulas involving nested calculations might be more complex and move computation to the formula engine, resulting in slower query.</p> <p>This is absolutely an interesting pattern and I have to say it’s a killer feature. Try to do the same in Multidimensional…</p><img src="http://sqlblog.com/aggbug.aspx?PostID=42750" width="1" height="1">Analysis ServicesDAXBISMTabularMicrosoft SQL Server 2012 Analysis Services – The BISM Tabular Model #ssas #tabular #bismhttp://sqlblog.com/blogs/marco_russo/archive/2012/03/28/microsoft-sql-server-2012-analysis-services-the-bism-tabular-model-ssas-tabular-bism.aspxWed, 28 Mar 2012 08:26:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:42528Marco Russo (SQLBI)0http://sqlblog.com/blogs/marco_russo/comments/42528.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=42528http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=42528<p>I, <a href="http://sqlblog.com/blogs/alberto_ferrari/">Alberto</a> and <a href="http://cwebbbi.wordpress.com">Chris</a> spent many months (many nights, holidays and also working days of the last months) writing <a href="http://www.amazon.com/gp/product/0735658188/?tag=se04-20">the book</a> we would have liked to read when we started working with Analysis Services Tabular. A book that explains how to use Tabular, how to model data with Tabular, how Tabular internally works and how to optimize a Tabular model. All those things you need to start on a real project in order to make an happy customer. You know, we’re all consultants after all, so customer satisfaction is really important to be paid for our job!</p> <p>Now the book writing is finished, we’re in the final stage of editing and reviews and we look forward to get our print copy. Its title is very long: <a href="http://www.amazon.com/gp/product/0735658188/?tag=se04-20">Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model</a>. But the important thing is that you can already (pre)order it.</p> <p>This is the list of chapters:</p> <ul> <li>01. BISM Architecture </li> <li>02. Guided Tour on Tabular</li> <li></td></tr><tr><td>03. Loading Data Inside Tabular</li> <li></td></tr><tr><td>04. DAX Basics</li> <li></td></tr><tr><td>05. Understanding Evaluation Contexts</li> <li></td></tr><tr><td>06. Querying Tabular</li> <li></td></tr><tr><td>07. DAX Advanced</li> <li></td></tr><tr><td>08. Understanding Time Intelligence in DAX</li> <li></td></tr><tr><td>09. Vertipaq Engine</li> <li></td></tr><tr><td>10. Using Tabular Hierarchies</li> <li></td></tr><tr><td>11. Data modeling in Tabular</li> <li></td></tr><tr><td>12. Using Advanced Tabular Relationships</li> <li></td></tr><tr><td>13. Tabular Presentation Layer</li> <li></td></tr><tr><td>14. Tabular and PowerPivot for Excel</li> <li></td></tr><tr><td>15. Tabular Security</li> <li></td></tr><tr><td>16. Interfacing with Tabular</li> <li></td></tr><tr><td>17. Tabular Deployment</li> <li></td></tr><tr><td>18. Optimization and Monitoring</td></tr></li> </ul> <p>And this is the book cover – have a good read!</p> <p><img border="0" src="http://akamaicovers.oreilly.com/images/0790145322197/lrg.jpg" width="201" height="245" /></p><img src="http://sqlblog.com/aggbug.aspx?PostID=42528" width="1" height="1">Analysis ServicesBookBISMTabularInvestigating on xVelocity (VertiPaq) column sizehttp://sqlblog.com/blogs/marco_russo/archive/2012/03/19/investigating-on-xvelocity-vertipaq-column-size.aspxMon, 19 Mar 2012 11:15:00 GMT21093a07-8b3d-42db-8cbf-3350fcbf5496:42376Marco Russo (SQLBI)2http://sqlblog.com/blogs/marco_russo/comments/42376.aspxhttp://sqlblog.com/blogs/marco_russo/commentrss.aspx?PostID=42376http://sqlblog.com/blogs/marco_russo/rsscomments.aspx?PostID=42376<p>&#160;</p> <p>In January I published an article about <a href="http://www.sqlbi.com/articles/optimizing-high-cardinality-columns-in-vertipaq/">how to optimize high cardinality columns in VertiPaq</a>. In the meantime, VertiPaq has been rebranded to xVelocity: the official name is now “xVelocity in-memory analytics engine (VertiPaq)” but using xVelocity and VertiPaq when we talk about Analysis Services has the same meaning. In this post I’ll show how to investigate on columns size of an existing Tabular database so that you can find the most important columns to be optimized.</p> <p>A first approach can be looking in the DataDir of Analysis Services and look for the folder containing the database. Then, look for the biggest files in all subfolders and you will find the name of a file that contains the name of the most expensive column. However, this heuristic process is not very optimized.</p> <p>A better approach is using a DMV that provides the exact information. For example, by using the following query (open SSMS, open an MDX query on the database you are interested to and execute it) you will see all database objects sorted by used size in a descending way.</p> <p>SELECT * <br />FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS <br />ORDER BY used_size DESC</p> <p>You can look at the first rows in order to understand what are the most expensive columns in your tabular model. The interesting data provided are:</p> <ul> <li>TABLE_ID: it is the name of the object – it can be also a dictionary or an index</li> <li>COLUMN_ID: it is the column name the object belongs to – you can also see ID_TO_POS and POS_TO_ID in case they refer to internal indexes</li> <li>RECORDS_COUNT: it is the number of rows in the column</li> <li>USED_SIZE: it is the used memory for the object</li> </ul> <p>By looking at the ration between USED_SIZE and RECORDS_COUNT you can understand what you can do in order to optimize your tabular model. Your options are:</p> <ul> <li><strong>Remove the column</strong>. Yes, if it contains data you will never use in a query, simply remove the column from the tabular model</li> <li><strong>Change granularity</strong>. If you are tracking time and you included milliseconds but seconds would be enough, round the data source column to the nearest second. If you have a floating point number but two decimals are good enough (i.e. the temperature), round the number to the nearest decimal is relevant to you.</li> <li><strong>Split the column</strong>. Create two or more columns that have to be combined together in order to produce the original value. This technique is described in <a href="http://www.sqlbi.com/articles/optimizing-high-cardinality-columns-in-vertipaq/">VertiPaq optimization article</a>.</li> <li><strong>Sort the table by that column</strong>. When you read the data source, you might consider sorting data by this column, so that the compression will be more efficient. However, this technique works better on columns that don’t have too many distinct values and you will probably move the problem to another column. Sorting data starting from the lower density columns (those with a few number of distinct values) and going to higher density columns (those with high cardinality) is the technique that provides the best compression ratio.</li> </ul> <p>After the optimization you should be able to reduce the used size and improve the count/size ration you measured before.</p> <p>If you are interested in a longer discussion about internal storage in VertiPaq and you want understand why this approach can save you space (and time), you can attend my 24 Hours of PASS session “<a href="http://www.sqlpass.org/24hours/spring2012/SessionsbySchedule/SessionDetails.aspx?sid=2515">VertiPaq Under the Hood</a>” on March 21 at 08:00 GMT.</p><img src="http://sqlblog.com/aggbug.aspx?PostID=42376" width="1" height="1">BISMTabular