Future MSBI-Power Pivot ‘N’ DAX

Here are Vidas comments on Power Pivot and DAX
- from the session ”SQLCAT: A Preview of PowerPivot Best Practices”. Presenters are Denny Lee and Dave Wickert:
• #PowerPivot spreadsheet is stored in the SQL database when deployed to SharePoint
• #powerPivot uses AS engine – in memory column based store w/VertiPaq. All calculations are local after import.
• Excel has it’s own local in process SSAS engine. New#powerPivot functions – Data Access eXpressions (DAX)
• #powerPivot compression 10:1 (approx) and depends…
• More info about #PowerPivot:VertiPaq does compression about 10:1, then SSAS does on top compression 1.5:1 or 2:1.
• MOLAP stores multiple records in the grain of fact table, so some records could be rolled up
• Vertipaq has a row for each source data, so different from MOLAP.
• There is on disk structure for #PowerPivot that looks similar to SSAS. c:\local user\… Additional info: Denny Lee just blogged about this with more details.
• Sub-folder customdata has file with backup of SSAS database for #PowerPivot.
• Showing demo of silverlight gallery of #PowerPivot reports in SharePoint
• #PowerPivot - when open workbook in Excel services, no interaction with SSAS first
• when click on slicer, excel services /#PowerPivot service talks to AS service engine. So on demand loading of SSAS DB.
• first initialization could take time – depends on traffic, etc. Same published #PowerPivot can serve many people.
• Need to optimize SharePoint for #PowerPivot setup.
• Capacity planning-need memory for any in use database and buffer 10-20% for auto detection(?).
• #PowerPivot keeps cache of detached databases. in the SSAS backup folder
• SSAS servers will be 64-128GB and more for #PowerPivot. Will work with 8GB, but most likely you will want more.
• #PowerPivot capacity planning depends - #users total/concurrent, # of files, max file size, data refresh options
• in SharePoint content db workbooks are stored as blobs . Could use Remote Blob store in FileStream. Overhead 2-3%
• but better handle higher concurrency scenarios (cont..)
• different topology options – one sharepoint server, multiple servers with different services on them, etc…
• Currently SharePoint requires Kerberos security all the way
• in SharePoint 2010 – new “claims tokens”, so no need for Kerberos for #PowerPivot
• Kerberos still might be required, but there is reduction in cases when you need it for #PowerPivot and SharePoint 2010.
• #PowerPivot - before installing YOU MUST READ MANUAL! That is important.
• upgrade SharePOInt 2007 to 2010 is very complex for#PowerPivot. Cannot upgrade CTP2->CTP3. Not clear CTP3->RTM.
• SharePoint is optimized for download, so there are concerns for upload #PowerPivot (large files)
• power pivot max size 2GB (SharePoint limit). But you can create >2GB #PowerPivot files, 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
• in sharepoint LargeChunkFileSize parameter is useless – don’t touch it for #PowerPivot.
• to upload #PowerPivot to SharePoint you need very good network connection! Maybe first copy to SharePoint server – faster.
• troubleshooting – error messages very en-cryptic for#PowerPivot.
• use ULS logs (Bing or google for more info) from SharePoint to troubleshoot#PowerPivot.
• from ULS logs use correlationID to track down log data for one event. Log files very large, filter by time too. #powerpivot
there is no separate #PowerPivot log. Error could be in#PowerPivot, Excel services, etc., so ULS one option.
• You can use SQL Profiler to troubleshoot#PowerPivot SSAS instance! Good news!
• so to troubleshoot #PowerPivot you need to know SharePoint and SSAS and use tools! Consultant will be still busy…
• session almost done, time for lunch. See you latter tonight for #PowerPivot DAX session!
• create a view with fewer records and create#PowerPivot, deploy to Sharepoint, then update view to include all records. Faster.
• #PowerPivot tip: Rename *.xlsx file to *.zip and inside you will see data file that you can rename to *.abf and restore to SSAS server that runs in PowerPivot integrated mode
• this was the best session so far at - Best Practice on#PowerPivot by @dennylee and Dave Wickert
- from the session “DAX in #PowerPivot for Excel 2010″ by Howie Dickerman.
• Data Analysis Expressions = DAX.
• DAX lets user do multidimensional analysis without user knowing that this is multidimensional analysis.
• sample DAX: =[Qty]*[Price] – syntax just like Excel.
• DAX uses Excel functions, but no notion of addressing indv cells or ranges – instead columns in the data
• DAX is not replacement for MDX
• DAX provides functions that implement relations database concepts: Filter tables, aggregates, follow relationships
• DAX one to many function sample: =SUMX(RELATEDTABLE([Sales], Sales[Amount])
• DAX has functions to assist with dynamic aggregations of measures:
• DAX dynamic sample: =VALUES(Time[year]) & “.” & VALUES(Product[ProductID])
• DAX sample: =IF(VALUES(Time[Year])=2008,”Baseline”,”normal”)
• DAX calc expression in 2 places – calc columns (full materialize) and measures (eval for each cell dynamically)
• More than 80 Excel functions in DAX
• CTP3 has new FORMAT function to allow to convert any number to string #DAX
• DAX sample: [salesAmt]/[SalesAmt](All(Product)) – use measure as function.
• DAX = Sales[SalesAmt]/CALCULATE(Sales[SalesAmt], ALL(Product)) more complex syntax
• CTP3 DAX has 35 Time Intelligence Functions – require date column in the data. Column type Date
• DAX this version does not have custom time periods and weeks. Works just with Yr, Qtr,Mth,Day
• DAX function samples: FirstDate, LastDate, FirstNonBlank, StartOfMonth, StartOfQtr, EndOfYear
• DAX: DateAdd, DatesBetween, DatesInperiod – 26 function that return a table of dates
• DAX: PreviousDay, Nextmonth, DatesMTD, DatesYTD, TotalMTD, OpeningBalanceQuarter, ClosingBalanceYear, etc <-more functions
• Year over year growth: =Sales[SalesAmtt]-Sales[SalesAmth)(DateAdd(Time[Date],-1,Year].All(Time))
• AllTime – need to add now in CTP3, not in RTM(will be under cover). So that first year has data.
• DAX sample: QTD Sales =TotalQTD(Sales[SalesAmt],Time[Date], All(Time))
• DAX yearAgo:=Sales[SalesAmt)(ParallelPeriod(Time[Date],-12, Month).All(Time))
• I missed single quotes in same functions prev. If table name contains spaces, need to put single quote around names.
• DAX autocomplete in CTP3 add single quotes, but they are optional.
More Details:
http://www.ssas-info.com/VidasMatelisBlog/195_my-tweets-from-sql-pass-summit-2009-ssas-powerpivot-dax-and-more
http://ms-olap.blogspot.com/2010/05/self-service-bi-powerpivot-and-future.html#comment-form
http://cwebbbi.wordpress.com/2010/11/11/pass-summit-day-2/
~Rav’s