1 / 14

Essential tips for BI Admins

Learn key strategies for optimizing BI components. Discover insights on resource allocation, file management, key backups, cache warming, and more to enhance BI performance. Access practical recommendations from an expert in the field.

phuc
Download Presentation

Essential tips for BI Admins

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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

More Related