{"id":195,"date":"2009-11-07T22:17:52","date_gmt":"2009-11-08T03:17:52","guid":{"rendered":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/?p=195"},"modified":"2010-10-20T06:42:23","modified_gmt":"2010-10-20T11:42:23","slug":"my-tweets-from-sql-pass-summit-2009-ssas-powerpivot-dax-and-more","status":"publish","type":"post","link":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/195_my-tweets-from-sql-pass-summit-2009-ssas-powerpivot-dax-and-more","title":{"rendered":"My tweets from SQL PASS Summit 2009 &#8211; SSAS, PowerPivot, DAX and more"},"content":{"rendered":"<p>I just came back from SQL PASS Summit 2009. About a week before that I started to use Twitter and I tried to &#8220;tweet&#8221; during the conference as much as I can. My Twitter ID is: <a href=\"http:\/\/twitter.com\/VidasM\">http:\/\/twitter.com\/VidasM<\/a>. I decided that some of my posts could be interesting to people who do not use Twitter, so I cleaned them up a bit and posted them here.<\/p>\n<p><!--more--><\/p>\n<ul>\n<li>Avoid &#8220;All in one&#8221; cubes<\/li>\n<li>Keep cube data size small &#8211; 300-400GB cube, that is 5-6TB source relational data.<\/li>\n<li>SQL CAT call cube large when its size is\u00a02-3TB!<\/li>\n<li>After bunch of process updates, if you can do sometimes process full. This might reduce &#8220;fragmentation&#8221; of data<\/li>\n<li>Big dimension-anything over 10 &#8211; 20mln members, but depends on number of attributes, relationships<\/li>\n<li>On\u00a0Windows 2003 consider &lt;PreaAllocate&gt; setting in ini file, to reduce memory fragmentation. Could be big performance increase.Win2008-no need to use it.<\/li>\n<li>During SSAS\u00a0processing there are a lots of small random I\/Os , RAID-5 is OK.<\/li>\n<li>on source DB for SSAS use compression, eliminate locking.<\/li>\n<li>money type is about 10-20% faster than decimal.<\/li>\n<li>if optimize SSAS, keep in mind SQL will optimize too and create many parallel queries. Maybe need MAXDOP=1 for processing, if all optimization is done for SSAS (many partitions, data source connections, etc)<\/li>\n<li>consider 20% aggregations, should be up to 100 ideally<\/li>\n<\/ul>\n<p>Here are my tweets from the session\u00a0&#8220;Top customer support issues in Analysis Services 2005\/2008 and how to resolve them&#8221;, by John Sirmon (Microsoft CSS)<\/p>\n<ul>\n<li>use native provided for data source, don&#8217;t use managed provider<\/li>\n<li>when SQL&amp;SSAS same box, use shared memory data source: lpc:MachineName.!!!!!! Also change packet size to 32757.<\/li>\n<li>#SSAS\u00a0or instead of lpc: could also use tcp:&lt;machine name&gt;<\/li>\n<li>#SSAS\u00a0test packet size, depends on hardware. Sometimes smaller value is better.<\/li>\n<li>there is Performance Analyzer Logs, not available for SSAS yet, but good.<\/li>\n<li>when SSAS uses multiple data sources, then other data is accessed through OpenRowset &#8211; that is not efficient.<\/li>\n<li>John Sirmon said: best book every done on SSAS &#8211; <a href=\"http:\/\/www.ssas-info.com\/announcements\/1653-july-21-2009-new-book-released-expert-cube-development-with-microsoft-sql-server-2008-analysis-services\" target=\"_blank\">Expert cube development with SSAS<\/a><\/li>\n<\/ul>\n<p>Here are my tweets from the session\u00a0&#8220;SQLCAT: A Preview of PowerPivot Best Practices&#8221;. Presenters are Denny Lee and Dave Wickert:<\/p>\n<ul>\n<li>#PowerPivot\u00a0spreadsheet is stored in the SQL database when deployed to SharePoint<\/li>\n<li>#powerPivot\u00a0uses AS engine &#8211; in memory column based store w\/VertiPaq. All calculations are local after import.<\/li>\n<li>Excel has it&#8217;s own local in process SSAS engine. New#powerPivot\u00a0functions &#8211; Data Access eXpressions (DAX)<\/li>\n<li>#powerPivot\u00a0compression 10:1 (approx) and depends&#8230;<\/li>\n<li>More info about\u00a0#PowerPivot:VertiPaq does compression about 10:1, then SSAS does on top compression 1.5:1 or 2:1.<\/li>\n<li>MOLAP stores multiple records in the grain of fact table, so some records could be rolled up<\/li>\n<li>Vertipaq has a row for each source data, so different from MOLAP.<\/li>\n<li>There is on disk structure for\u00a0#PowerPivot\u00a0that looks similar to SSAS. c:\\local user\\&#8230; Additional info: <a href=\"http:\/\/powerpivot-info.com\/post\/32-understanding-why-an-excel-powerpivot-workbook-is-so-large\" target=\"_blank\">Denny Lee just blogged about this with more details<\/a>.<\/li>\n<li>Sub-folder customdata has file with backup of SSAS database for\u00a0#PowerPivot.<\/li>\n<li>Showing demo of silverlight gallery of\u00a0#PowerPivot\u00a0reports in SharePoint<\/li>\n<li>#PowerPivot\u00a0&#8211; when open workbook in Excel services, no interaction with SSAS first<\/li>\n<li>when click on slicer, excel services \/#PowerPivot service talks to AS service engine. So on demand loading of SSAS DB.<\/li>\n<li>first initialization could take time &#8211; depends on traffic, etc. Same published\u00a0#PowerPivot\u00a0can serve many people.<\/li>\n<li>Need to optimize SharePoint for\u00a0#PowerPivot\u00a0setup.<\/li>\n<li>Capacity planning-need memory for any in use database and buffer 10-20% for auto detection(?).<\/li>\n<li>#PowerPivot\u00a0keeps cache of detached databases. in the SSAS backup folder<\/li>\n<li>SSAS servers will be 64-128GB and more for\u00a0#PowerPivot. Will work with 8GB, but most likely you will want more.<\/li>\n<li>#PowerPivot\u00a0capacity planning depends &#8211;\u00a0#users\u00a0total\/concurrent, # of files, max file size, data refresh options<\/li>\n<li>in SharePoint content db workbooks are stored as blobs . Could use Remote Blob store in FileStream. Overhead 2-3%<\/li>\n<li>but better handle higher concurrency scenarios (cont..)<\/li>\n<li>different topology options &#8211; one sharepoint server, multiple servers with different services on them, etc&#8230;<\/li>\n<li>Currently SharePoint\u00a0requires Kerberos security all the way<\/li>\n<li>in SharePoint 2010 &#8211; new &#8220;claims tokens&#8221;, so no need for Kerberos for\u00a0#PowerPivot<\/li>\n<li>Kerberos still might be required, but there is reduction in cases when you need it for\u00a0#PowerPivot\u00a0and SharePoint 2010.<\/li>\n<li>#PowerPivot\u00a0&#8211; before installing YOU MUST READ MANUAL! That is important.<\/li>\n<li>upgrade SharePOInt 2007 to 2010 is very complex for#PowerPivot. Cannot upgrade CTP2-&gt;CTP3. Not clear CTP3-&gt;RTM.<\/li>\n<li>SharePoint is optimized for download, so there are concerns for upload\u00a0#PowerPivot\u00a0(large files)<\/li>\n<li>power pivot max size 2GB (SharePoint limit). But you can create &gt;2GB\u00a0#PowerPivot\u00a0files, just not upload to Sharepoint. Additional info: Actual max publish size could be 1.8GB. Although it is limitation, it will affect very few users, as majority of them will not have such large files, so there is no need to worry about this<\/li>\n<li>in sharepoint LargeChunkFileSize parameter is useless &#8211; don&#8217;t touch it for\u00a0#PowerPivot.<\/li>\n<li>to upload\u00a0#PowerPivot\u00a0to SharePoint you need very good network connection! Maybe first copy to SharePoint server &#8211; faster.<\/li>\n<li>troubleshooting &#8211; error messages very en-cryptic for#PowerPivot.<\/li>\n<li>use ULS logs (Bing or google for more info) from SharePoint to troubleshoot#PowerPivot.<\/li>\n<li>from ULS logs use correlationID to track down log data for one event. Log files very large, filter by time too.\u00a0#powerpivot<br \/>\nthere is no separate\u00a0#PowerPivot\u00a0log. Error could be in#PowerPivot, Excel services, etc., so ULS one option.<\/li>\n<li>You can use SQL Profiler to troubleshoot#PowerPivot\u00a0SSAS instance! Good news!<\/li>\n<li>so to troubleshoot\u00a0#PowerPivot\u00a0you need to know SharePoint and SSAS and use tools! Consultant will be still busy&#8230;<\/li>\n<li>session almost done, time for lunch. See you latter tonight for\u00a0#PowerPivot\u00a0DAX session!<\/li>\n<li>create a view with fewer records and create#PowerPivot, deploy to Sharepoint, then update view to include all records. Faster.<\/li>\n<li>#PowerPivot\u00a0tip: Rename *.xlsx file to *.zip and inside you will see data file that you can rename to *.abf and\u00a0restore to SSAS server that runs in PowerPivot integrated mode<\/li>\n<li>this was the best session so far at\u00a0\u00a0&#8211; Best Practice on#PowerPivot\u00a0by @dennylee\u00a0and Dave Wickert<\/li>\n<\/ul>\n<p>My tweets from the session &#8220;DAX in\u00a0#PowerPivot\u00a0for Excel 2010&#8221; by Howie Dickerman.<\/p>\n<ul>\n<li>Data Analysis Expressions = DAX.<\/li>\n<li>DAX lets user do multidimensional analysis without user knowing that this is multidimensional analysis.<\/li>\n<li>sample DAX: =[Qty]*[Price] &#8211; syntax just like Excel.<\/li>\n<li>DAX uses Excel functions, but no notion of addressing indv cells or ranges &#8211; instead columns in the data<\/li>\n<li>DAX is not replacement for MDX<\/li>\n<li>DAX provides functions that implement relations database concepts: Filter tables, aggregates, follow relationships<\/li>\n<li>DAX one to many function sample: =SUMX(RELATEDTABLE([Sales], Sales[Amount])<\/li>\n<li>DAX\u00a0has functions to assist with dynamic aggregations of measures:<\/li>\n<li>DAX\u00a0dynamic sample: =VALUES(Time[year]) &amp; &#8220;.&#8221; &amp; VALUES(Product[ProductID])<\/li>\n<li>DAX sample: =IF(VALUES(Time[Year])=2008,&#8221;Baseline&#8221;,&#8221;normal&#8221;)<\/li>\n<li>DAX calc expression in 2 places &#8211; calc columns (full materialize) and measures (eval for each cell dynamically)<\/li>\n<li>More than 80 Excel functions in DAX<\/li>\n<li>CTP3 has new FORMAT function to allow to convert any number to string\u00a0#DAX<\/li>\n<li>DAX sample: [salesAmt]\/[SalesAmt](All(Product)) &#8211; use measure as function.<\/li>\n<li>DAX = Sales[SalesAmt]\/CALCULATE(Sales[SalesAmt], ALL(Product)) more complex syntax<\/li>\n<li>CTP3 DAX has 35 Time Intelligence Functions &#8211; require date column in the data. Column type Date<\/li>\n<li>DAX this version does not have custom time periods and weeks. Works just with Yr, Qtr,Mth,Day<\/li>\n<li>DAX function samples: FirstDate, LastDate, FirstNonBlank, StartOfMonth, StartOfQtr, EndOfYear<\/li>\n<li>DAX: DateAdd, DatesBetween, DatesInperiod &#8211; 26 function that return a table of dates<\/li>\n<li>DAX: PreviousDay, Nextmonth, DatesMTD, DatesYTD, TotalMTD, OpeningBalanceQuarter, ClosingBalanceYear, etc &lt;-more functions<\/li>\n<li>Year over year growth: =Sales[SalesAmtt]-Sales[SalesAmth)(DateAdd(Time[Date],-1,Year].All(Time))<\/li>\n<li>AllTime &#8211; need to add now in CTP3, not in RTM(will be under cover). So that first year has data.<\/li>\n<li>DAX sample:\u00a0QTD Sales =TotalQTD(Sales[SalesAmt],Time[Date], All(Time))<\/li>\n<li>DAX yearAgo:=Sales[SalesAmt)(ParallelPeriod(Time[Date],-12, Month).All(Time))<\/li>\n<li>I missed single quotes in same functions prev. If table name contains spaces, need to put single quote around names.<\/li>\n<li>DAX\u00a0autocomplete in CTP3 add single quotes, but they are optional.<\/li>\n<\/ul>\n<p>Additional tweets from the conference:<\/p>\n<ul>\n<li>Microsoft just announced BI resource center on Technet: <a href=\"http:\/\/technet.microsoft.com\/en-ca\/bi\/default.aspx\" target=\"_blank\">http:\/\/technet.microsoft.com\/en-ca\/bi\/default.aspx<\/a><\/li>\n<li>Microsoft SQL Server 2008R2 &amp; PowerPivot\u00a0CTP3 will be released in November<\/li>\n<li>Microsoft will release SQL Server 2008R2 in the first half of 2010<\/li>\n<li>Just noticed-Mosha released new version of MDX Studio few weeks ago:\u00a0<a href=\"http:\/\/www.ssas-info.com\/forum\/viewtopic.php?f=3&amp;t=624\" target=\"_blank\">http:\/\/www.ssas-info.com\/forum\/viewtopic.php?f=3&amp;t=624<\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>I just came back from SQL PASS Summit 2009. About a week before that I started to use Twitter and I tried to &#8220;tweet&#8221; during the conference as much as I can. My Twitter ID is: http:\/\/twitter.com\/VidasM. I decided that some of my posts could be interesting to people who do not use Twitter, so I cleaned them up a bit and posted them here.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[17,7],"tags":[],"aioseo_notices":[],"_links":{"self":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/195"}],"collection":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/comments?post=195"}],"version-history":[{"count":7,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/195\/revisions"}],"predecessor-version":[{"id":201,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/posts\/195\/revisions\/201"}],"wp:attachment":[{"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/media?parent=195"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/categories?post=195"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.ssas-info.com\/VidasMatelisBlog\/wp-json\/wp\/v2\/tags?post=195"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}