SQL Server Analysis Services Administration Tutorial


SQL Server Analysis Services (SSAS) is the technology from the Microsoft Business Intelligence stack to develop Online Analytical Processing (OLAP) solutions. SSAS comes in two different modes - Online Analytical Processing (OLAP) and Tabular.

Administration is an inevitable part of any SSAS practitioner's day to day job. It's a myth that only a DBA is required to perform administrative tasks. In real world scenarios, Developers have the need to perform administrative tasks on database objects to setup their development and testing environments as per project requirements. SSAS administration skills can help a developer self-administer their development instances and efficiently make necessary changes at the instance and database level without any dependencies which can save critical development time.

Administration is a DBA's core skill and by having fundamental and detailed understanding of features that are unique to SSAS administration, DBAs can broaden their knowledge in the SQL Server administration ecosystem. It also helps the DBA to better understand the needs of the OLAP Development teams.

Administration of a SSAS instance / database is similar to SQL Server Database Engine management in some ways. There are some administration aspects which are also unique to SSAS like processing dimensions, facts and cubes. Any IT environment employing SSAS as its OLAP technology typically involves the following administrative tasks. These tasks are an integral part of any SSAS solution that Developers as well as DBAs need to perform in production and/or non-production environments depending on their role on the team.

  1. Installation of SSAS
    • Pre-requisites
    • Identifying Complementary Technologies (Database Engine / SSIS / SSRS)
    • Determine SSAS Server Mode
    • Licensing Versions
  2. Configuring SSAS
    • Best Practices
    • Configuring Server Properties
  3. Configuring Security
    • Server Security
    • User Security
    • Data Security
  4. Configuring Logging
    • Query logging
    • Exception logging
  5. Deployment
    • Deploying Databases
    • Synchronizing Databases
  6. Backup and Restore
    • Backup
    • Restore
  7. Performance Monitoring
    • Profiler and Performance Counters
    • Flight Recorder

In this tutorial we will step through each of the above mentioned topics with a hands-on exercise on how to accomplish each of these tasks. We will also point out areas which may be exclusive to DBAs and areas that are applicable to both Developers and DBAs.

Comments For This Article

get free sql tips
agree to terms