SSAS Interview Questions on Deployment, Processing, Querying, and Accessing

By:   |   Comments (5)   |   Related: More > Professional Development Interview Questions BI


Problem

Building SSAS solutions is one part of the battle when delivering BI solutions to the users. Another part of the battle is to deploy and process those solutions making them highly available and accessible via different reporting tools. Having a good understanding on these aspects of delivering SSAS solutions to the users is very important and one of the major focus areas for SSAS interviews.  Read on to check out the next installment of the SSAS interview questions.

Solution

In the previous tips of this series, I covered questions on basic concepts related to Data Sources, Dimensions, Measures, Actions, Storage, Aggregations, Translations, Perspectives, and Security. In this sixth and last tip of the Analysis Services interview questions and answers series, I will be covering questions on deployment, processing, querying, and accessing SSAS solutions.

What are the deployment options available in SSAS?

Below are the different options to deploy a SSAS solution to an OLAP Server:

  • Business Intelligence Development Studio (BIDS): This is one of the primary tools used for developing SQL Server BI solutions and is the most frequently used IDE by the developer community in SQL Server 2008. BIDS can be used to deploy the SSAS Objects/Solutions.  BIDS was replaced by the SQL Server Data Tools in SQL Server 2012, but has much of the same functionality.
  • Analysis Services Deployment Wizard: Analysis Services Deployment Wizard is a tool included as part of the SSAS installation and can be used to deploy the SSAS objects/solutions.
  • XML for Analysis Services (XMLA): It is used for deploying the SSAS objects to the OLAP Server. Apart from deployment purposes, XMLA scripts can be used for various other purposes like managing SSAS objects, creating/modifying SSAS objects and processing SSAS objects, etc. It is also known as the Analysis Services Scripting Language (ASSL).

What is processing in SSAS?

Processing is the process to load data into SSAS objects like Cubes, Partitions, and Dimensions, etc. on the OLAP Server with the data from the underlying relational data warehouse. As part of the processing step(s), aggregations are performed, data is loaded into one or more pre-defined partitions and various actions, which involve data modifications are performed in this step. The processed data is stored into respective locations like the OLAP Server, relational data warehouse, etc. depending on the Storage Mode defined for the different objects.

What is database synchronization in SSAS? What is its advantage?

Database synchronization is the process of updating the data and metadata in a database based on the target SSAS Server based on the data and metadata from the source SSAS Server. Analysis Services offers a tool called the "Synchronize Database Wizard" to synchronize databases on two different SSAS databases.

The primary advantage of a database synchronization via the Database Synchronization Wizard is that, while the databases are being synchronized by the wizard, the destination database continues to be available and the users can access the database as usual. Once the synchronization is completed, Analysis Services drops the out dated copy (old database) and switches automatically to the new database which has been updated.

What are the different processing modes available in SSAS? Explain each one of them.

Below are the processing options available in SSAS:

  • Process Add: This processing mode is available for Dimensions, Measure Groups and Partitions. When this processing mode is applied to Dimensions, it adds new members into the dimensions from the underlying database and updates the descriptions of the attributes. When this processing mode is applied to Measure Groups and Partitions, it adds the incremental fact data into the cube and processes only the affected partitions.
  • Process Clear: This processing mode is available for Dimensions, Measure Groups, Partitions, Cubes, Mining Models and Structures as well as OLAP (SSAS) databases. This processing mode is used to remove the data from an Analysis Services object. Data is removed from an object and all the subsequent child level objects.
  • Process Clear Structure: This processing mode is available only for Mining Structures as part of the Data Mining component of SSAS. This processing mode is used to clear the training data previously loaded into a mining structure.
  • Process Data: This processing mode is available for Dimensions, Measure Groups, Partitions, and Cubes. This processing mode is used to process the data without actually building or creating the aggregations and indexes.
  • Process Default: This processing mode is available for Dimensions, Measure Groups, Partitions, Mining Models and Structures, Cubes, and OLAP (SSAS) databases. When this process is applied to any of these objects, it first determines the current state of the object from a processing standpoint and then does the necessary processing to bring the object to current/fully processed state.
  • Process Full: This processing mode is available for Dimensions, Measure Groups, Partitions, Mining Models and Structures, Cubes, and OLAP (SSAS) Databases. When this processing mode is applied to an object, the object itself and all the subsequent lower level objects are fully processed. The data is cleared from an object if it already has some previously processed data.
  • Process Index: This processing mode is available for Dimensions, Measure Groups, Partitions, and Cubes. This option works only on the processed objects and for unprocessed objects you will get an error. When applied on any processed objects, it creates aggregations and indexes if they don't already exist. It recreates them if they already exist.
  • Process Structure: This processing mode is available for Mining Structures and Cubes. When this processing mode is applied to a cube, it creates the cube structure and it also processes the cube dimensions if they are not already processed. This processing mode loads the data into the Mining Structure, when applied on it.
  • Process Update: This processing mode is available only for Dimensions. When this processing mode is used, it freshly reads all the dimension data from the database and updates the data in the dimension.

What are the different ways to process objects in SSAS?

SSAS objects can be processed using various techniques. The following are the most popular tools and techniques used to process objects in SSAS:

  • SQL Server Integration Services (SSIS): Analysis Service Processing Task in SSIS can be used to process SSAS objects.
  • Business Intelligence Development Studio (BIDS): SSAS Objects can be processed via Business Intelligence Development Studio from within the SSAS Project.  BIDS was replaced by the SQL Server Data Tools in SQL Server 2012, but has much of the same functionality.
  • SQL Server Management Studio (SSMS): SQL Server Management Studio can be used in multiple ways to process SSAS objects.
    • Object Explorer: Using SSMS, one can directly connect to Analysis Services Server and process the objects through the Object Explorer (GUI).
    • XMLA: An XML for Analysis Services (XMLA) script can be executed directly in SSMS to process the SSAS objects.
    • SQL Server Agent: An XMLA script can be executed using a SQL Server Agent Job which also helps in scheduling the script to perform the processing during off hours.
  • Command Line: SSAS offers the following two command line utilities which can be used to process SSAS objects:
    • Power Shell: Power Shell is a well known and very powerful command line utility which is part of SQL Server. It can be used to process SSAS objects. Apart from this, Power Shell can be used to perform various other operations within SQL Server.
    • ascmd: The ascmd utility can be used to execute XMLA scripts as well as MDX and DMX queries against an instance of SSAS. Using this utility, one can execute the relevant scripts to process the SSAS objects.
  • Programmatically: Analysis Management Objects (AMO) is a collection of SSAS management classes, which can be used for processing SSAS objects. Apart from this, AMO can be used for various management activities in SSAS.

What are the programmability options available in SSAS?

In Analysis Services there are two major programmability options: AMO and ADOMD.NET.

AMO (Analysis Management Objects) is a set of classes used for managing an Analysis Services instance and can be used to perform various management activities related to SSAS. Using AMO, the following operations can be performed on the SSAS objects:

  • Creation
  • Modification
  • Deletion
  • Processing

With AMO, one cannot query the SSAS objects. To query the data from SSAS programmatically, one can use ADOMD.NET which is a .NET based data provider. It is primarily used to communicate with SSAS and uses Multidimensional Expressions (MDX), Data Mining Extensions (DMX), and Analysis Services Scripting Language (ASSL), etc. commands to query the data.

What are the different querying and programming languages supported by SSAS?

The following languages are supported by SSAS:

  • T-SQL: Transact-SQL queries can be used to query certain aspects of SSAS.
  • MDX: It stands for Multidimensional Expressions. MDX queries are the primary means of querying data in SSAS.
  • DMX: It stands for Data Mining Extensions. DMX is used for queries related to Data Mining in SSAS.
  • AMO: It stands for Analysis Management Objects. It is .NET based and can be used to manage SSAS objects, creating/modifying/deleting SSAS objects and implementing security in certain scenarios.

What are the different tools used for accessing cubes?

Here is a list of different tools which are used for accessing Cubes in Analysis Services:

  • Business Intelligence Development Studio (BIDS): BIDS is used to access the cube and preview the data at the design time. This option is useful only for the developer community and is not applicable to business analysts and decision makers.  BIDS was replaced by the SQL Server Data Tools in SQL Server 2012, but has much of the same functionality.
  • SQL Server Management Studio (SSMS): SSMS is used to access the cube by people in different roles. It is used to access the cube by the development/testing teams to validate the data. Those business analysts and decision makers (very few) who are familiar with SSMS; also use it for accessing the cubes.
  • Microsoft Office Excel: Excel being one of the most widely used data analysis tool, has the capability to connect to cubes and perform different kinds of reporting such as pivot tables, charts, etc. It is used by many decision makers, business analysis, and even developers for reporting purposes.
  • SQL Server Reporting Services (SSRS): SSRS allows Analysis Services databases or cubes as a data source to build reports. This option is available both in BIDS\SQL Server Data Tools while building canned reports and in Report Builder while doing ad-hoc reporting.
  • PerformancePoint Services (PPS): PPS which is now a part of SharePoint 2010 allows building KPIs, scorecards, reports, and dashboards with Analysis Services cubes as data source.

These are the major tools in the Microsoft BI stack which support Analysis Services data sources. Apart from these, there are various third party tools which support Analysis Services as a data source.

How do you capture which users are connecting to SSAS, which objects of SSAS are being used, which queries are being executed, etc.?

Analysis Services offers Dynamic Management Views (DMV), which are along the same lines as the DMVs for the relational database engine. Using the DMVs is the most common approach to capturing these statistics and to monitor the current status of the SSAS server. With the SSAS DMVs, one can capture various statistics including:

  • Utilization of resources such as CPU, memory, etc.
  • Usage of aggregations, hierarchies, and other components of SSAS.
  • Who is connecting to the OLAP database and what are the different objects being used.
  • Queries being executed by the users, time taken for executing different user queries, and so on.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Dattatrey Sindol Dattatrey Sindol has 8+ years of experience working with SQL Server BI, Power BI, Microsoft Azure, Azure HDInsight and more.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, November 20, 2013 - 3:45:46 PM - Pradeep Back To Top (27559)

Why we create User defined hierarchies,i know we can create for drill down purpose,except that any thing

is there?

if we create user defined hierchy,any file is created or what is the role of aggregation releated to userdefined hierchy?

please answer this......?

Saturday, December 1, 2012 - 7:26:40 AM - etudio Back To Top (20686)

 

Hello,

 

I am building a 4.5 bn row fact measure group for a cube. OLTP and OLAP partitioning are done and an incremental process is handling every hour a process nicely on the current partition.

My questions are:

- is there a way to deploy a new dimension  and / or a new attribute to a dimension without having to repress the entire cube?

- Can we deploy partitions without having to reprocess old partitions? 

 

Thanks

 

 

Thursday, November 1, 2012 - 7:48:11 PM - VKK Back To Top (20180)
Q)How to get the list of DMVs?
A) Connect to Analysis Service using SSMS and run the below query:
 
SELECT * FROM $System.DBSchema_Tables 
WHERE TABLE_TYPE = 'SCHEMA' 
ORDER BY TABLE_NAME ASC
 
Q) How to get connected session details:
A) Connect to Analysis Service using SSMS and run the below query:
SELECT *
FROM $System.discover_sessions
 

Friday, October 12, 2012 - 7:01:43 AM - Mostafa shafeian Back To Top (19888)

hi

thanks for these tips.

Saturday, September 1, 2012 - 5:21:07 AM - Sreenivas Reddy Back To Top (19363)

 

Thanks providing the great tips these tips are great help full for me














get free sql tips
agree to terms