SSAS Interview Questions

by Azhar Uddin Technical Architect

If you're looking for SSAS Interview Questions for Experienced or Freshers, you are at right place. There are lot of opportunities from many reputed companies in the world. According to research SSAS has a market share of about 26.35%. So, You still have opportunity to move ahead in your career in SSAS certification guide. Mindmajix offers Advanced SSAS Interview Questions 2018 that helps you in cracking your interview & acquire dream career as SSAS Developer.

Q. What is SQL Server Analysis Services (SSAS)? List out the features?

Microsoft SQL Server 2014 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting us design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets we design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

Analysis Services is a middle tier server for analytical processing, OLAP, and Data mining. It manages multidimensional cubes of data and provides access to heaps of information including aggregation of data. One can create data mining models from data sources and use it for Business Intelligence also including reporting features.

Analysis service provides a combined view of the data used in OLAP or Data mining. Services here refer to OLAP, Data mining. Analysis services assists in creating, designing and managing multidimensional structures containing data from varied sources. It provides a wide array of data mining algorithms for specific trends and needs.

Some of the key features are:

1.Ease of use with a lot of wizards and designers.

2.Flexible data model creation and management

3.Scalable architecture to handle OLAP

4.Provides integration of administration tools, data sources, security, caching, and reporting etc.

5.Provides extensive support for custom applications

Q. What is the difference between SSAS 2005 and SSAS2008?

1.In 2005 its not possible to create an empty cube but in 2008 we can create an empty cube.

2.A new feature in Analysis Services 2008 is the Attribute Relationships tab in the Dimension Designer.to implement attribute relationship is complex in ssas 2005

3.we can create ONLY 2000 partitions per Measure Group in ssas 2005 and the same limit of partitions is removed in ssas 2008.

Wish to Learn SSAS? Click Here

Q. What is OLAP? How is it different from OLTP?

1.OLAP stands for On-Line Analytical Processing. It is a capability or a set of tools which enables the end users to easily and effectively access the data warehouse data using a wide range of tools like MICROSOFT EXCEL, REPORTING SERVICES, and many other 3rd party BUSINESS INTELLIGENCE TOOLS.

2.OLAP is used for analysis purposes to support day-to-day business decisions and is characterized by less frequent data updates and contains historical data. Whereas, OLTP (On-Line Transactional Processing) is used to support day-to-day business operations and is characterized by frequent data updates and contains the most recent data along with limited historical data based on the retention policy driven by business needs.

Q. What is a Data Source? What are the different data sources supported by SSAS?

A DATA SOURCE contains the connection information used by SSAS to connect to the underlying database to load the data into SSAS during processing. A Data Source primarily contains the following information (apart from various other properties like Query timeout, Isolation etc.):

1.Provider

2.Server Name

3.Database Name

4.Impersonation Information

SSAS Supports both .Net and OLE DB Providers. Following are some of the major sources supported by SSAS: SQL Server, MS Access, Oracle, Teradata, IBM DB2, and other relational databases with the appropriate OLE DB provider.

Q. What is Impersonation? What are the different impersonation options available in SSAS?

Impersonation allows SSAS to assume the identity/security context of the client application which is used by SSAS to perform the server side data operations like data access, PROCESSING etc. As part of impersonation, the following options are available in SSAS:

1.Use a specific Windows user name and password: This option lets you to specify Windows account credentials which will be used by SSAS to perform operations like source data access, processing etc.

2.Use the service account: When this option is selected, SSAS uses the credentials of the service account under which the Analysis Services service is configured/running for source data access, processing etc.

3.Use the credentials of the current user: When this option is set, SSAS uses the credentials of the current user for performing operations like DMX Open Queries, Local cubes etc. This option cannot be used for performing server side operations like source data access, processing etc.

4.Inherit: This option let’s the SSAS server decide which impersonation mode is suitable for each type of operation. When this option is set, by default SSAS will use the service account for operations like processing and the credentials of the current user for operations like Local cubes, querying the data mining models, etc…

Q. What is a Data Source View?

A DATA SOURCE VIEW (DSV) is a logical view of the underlying database schema and offers a layer of abstraction for the underlying database schema. This layer acts as a source for SSAS and captures the schema related information from the underlying database. The schematic information present in DSV includes the following:

1.Underlying database Table(s)/View(s) metadata

2.PRIMARY KEY & FOREIGN KEY RELATIONSHIPS between the underlying database Table(s)

3.Additional columns in the form of Named Calculations

4.Complex logic on the underlying Table(s)/View(s) in the form of Named Queries

SSAS can only see the schematic information present in the DSV and it cannot see the schematic information from the underlying database.

Q. What is a Named Calculation? In what scenarios do you use it?

A Named Calculation is a new column added to a Table in DSV and is based on an expression. This capability allows you to add an extra column into your DSV which is based on one or more columns from underlying data source Table(s)/View(s) combined using an expression without requiring the addition of a physical column in the underlying database Table(s)/View(s).

The expression used in the Named Calculation should conform to the underlying data source dialect. For example, if the underlying data source is SQL Server, then it should conform to T-SQL, If is it Oracle, then it should conform to PL/SQL, etc…

Named Calculations can be used in many scenarios, following are some of the common scenarios:

1.For creating Derived Columns. Say you have First Name and Last Name in the underlying data source Table/View and you want to get the Full Name as “First Name + space + Last Name”. Such things can be added as a Named Calculation.

2.For performing Lookup Operations. Say you have an Employee table which has AddressID and an Address table in which AddressID is the Primary Key. Now, to get the address information (say Country) into the Employee table in DSV, a Named Calculation can be added to the Employee table with the following expression:

(

SELECT Country

FROM Address

WHERE AddressID = Employee.AddressID

)

Visit here to get more!