SQL Server Analysis Services Administration Tutorial
By: Siddharth Mehta
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.
- Installation of SSAS
- Identifying Complementary Technologies (Database Engine / SSIS / SSRS)
- Determine SSAS Server Mode
- Licensing Versions
- Best Practices
- Configuring Server Properties
- Server Security
- User Security
- Data Security
- Query logging
- Exception logging
- Deploying Databases
- Synchronizing Databases
- 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.