10 Things All BI Administrators Should Know

1. 10 Things All BI Administrators Should Know Robert L Davis

2. Who am I? @SQLSoldier 11+ www.sqlsoldier.com

3. 10 Things All BI Admins Should Know • 10 – SQL components don’t play well together • Database Engine, Analysis Services, and SSIS Packages are resource hogs • Each one will try to use as many resources as it can • They don’t care what the other needs • Recommendation: separate components to different servers

4. 10 Things All BI Admins Should Know • 9 – File placement still matters • Separate files to different drives for best performance • BackupDir • DataDir • LogDir • TempDir

5. 10 Things All BI Admins Should Know • 8 – Backup the encryption keys • SSRS stores data encrypted in ReportServer database • Data source accounts and passwords • Report subscriptions • Key can be recreated but encrypted data will be lost • Import to other SSRS instances to create a farm

6. 10 Things All BI Admins Should Know • 7 – Warm the cache in SSAS • Run a pre-defined set of queries • Use the WITH CACHE statement to pre-load a commonly used slice of a cube WITHCACHEAS '([Sales Territory].[Sales Territory Region].Members)' SELECT {[Sales Territory].[Sales Territory Region].Members} ONCOLUMNS, {[Measures].[Order Count]} ONROWS FROM [Adventure Works];

7. 10 Things All BI Admins Should Know • 6 – More RAM > large paging file > out of memory errors • SSAS uses RAM if it can • If available RAM is low, SSAS will use the paging file • If out of RAM and paging file space, out of memory errors may occur

8. 10 Things All BI Admins Should Know • 5 – Tune parallelism for high CPU servers • ThreadPool\Query\Maxthreads <= 2 X CPU count • Controls maximum concurrency • ThreadPool\Process\MaxThreads <= 10 X CPU count • Controls maximum parallel threads per process • Logical CPUs

9. 10 Things All BI Admins Should Know • 4 – Use partitioned views • Older, static data in partitioned table • Newer, changing data in stand-alone table • Combine with a partitioned view

10. 10 Things All BI Admins Should Know • 3 – Load balancing SSAS > Clustering • Hardware or Software load balancing • Allows you to process cubes one at a time programmatically • Scalable to many servers

11. 10 Things All BI Admins Should Know • 2 – Don’t give SSRS users database access • Used stored credentials of a non-user domain service account

12. 10 Things All BI Admins Should Know • 1 – Back up your databases • SSAS databases should be backed up too • Use SSMS to create backup script in XMLA • Schedule via a SQL job <Backupxmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>Adventure Works DW 2008R2</DatabaseID> </Object> <File>E:\MSSQL\OLAP\BAK\Adventure Works DW 2008R2.abf</File> <AllowOverwrite>true</AllowOverwrite> </Backup>

13. 10 Things All BI Administrators Should Know Q&A

14. 10 Things All BI Administrators Should Know Thank You! • The PowerPoint slide-deck and recording of the session will be available on my website later today. • http://www.sqlsoldier.com