Overview
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.
Explanation
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
- Pre-requisites
- Identifying Complementary Technologies (Database Engine / SSIS / SSRS)
- Determine SSAS Server Mode
- Licensing Versions
- Configuring SSAS
- Best Practices
- Configuring Server Properties
- Configuring Security
- Server Security
- User Security
- Data Security
- Configuring Logging
- Query logging
- Exception logging
- Deployment
- Deploying Databases
- Synchronizing Databases
- Backup and Restore
- Backup
- Restore
- 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.
Siddharth has more than 14 years of experience in the IT Industry, with more than a decade of experience in Business Intelligence and Analytics, for clients banking, logistics, government, Media Entertainment, products, life sciences and other domains. He has been a lead architect for a portfolio of 40+ apps, containing apps in web, mobile, BI, Analytics, data warehousing, reporting, collaboration, CMS, NoSQL and other technologies. He has several certifications and is a published author for online and print-media publications, as well as the MSDN Library.
In his present role, he remains responsible for architecture design, technology stack selection, infrastructure design, 3rd party products evaluation and procurement, and performance engineering. These applications use technologies like Elasticsearch / Lucene, MongoDB, SharePoint 2013 and 2010, jQuery-based framework like Highcharts and GoJS, SQL Server and the Microsoft Business Intelligence stack (SSIS, SSAS, SSRS, MDX, PowerPivot, PowerView), jQueryMobile, Bootstrap, iOS xCode framework, and many others.
- MSSQLTips Awards: Champion (100+ tips) – 2018 | Author of the Year – 2017 | Author Contender – 2016, 2018-2019