SSAS Interview Questions

What is SSAS?

Ans: SSAS stands for SQL Server Analysis Services, which delivers online Analytical processing and functionalities related to data mining. SSAS uses cubes to represent the data structures and performs analysis using data mining capabilities. SSAS links up with the SQL server. SSAS helps in creating, designing, and managing the data structures using the data from the various available resources.

What is aggregation in SSAS?

Ans: Aggregations refers to the pre-calculated totals to be retrieved from the cube directly instead of re-calculating the data from a data source for each query.Aggregations help in improving and enhancing performance. Aggregations need to be designed based on the business requirements.In order to design and perform aggregations,the Aggregation Design Wizard is used.

    Wish to make a career in the world of SSAS? Start with SSAS Training !

What is impersonation? Explain the different options of impersonation available in SSAS?

Ans: In SSAS, impersonation helps to assume the identity/security context of the particular client application to perform server-side data operations. The data side operations include data access, processing, etc. There are multiple options available in SSAS mentioned below:

• Usage of Username and password
• Usage of Service account
• Usage of credentials of the current user
• Inherit

Define the term “Data Source” and explain the different types of data sources supported by SSAS?

Ans: Data Source in SSAS, includes the connectivity information to connect to the underlying database to load the data for processing. It consists of the following information.

• Provider
• Database name
• Server name
• Impersonation information
• SSAS provides its intense support to OLE DB and .Net providers. Apart from these, the significant sources that are supported by SSAS are Teradata, Ms Access, Oracle, IBM DB2, and many other relational databases.

Can you give a brief explanation about the two-tier architecture of SSAS?

Ans:

• To perform OLAP and Data mining functionalities, SSAS makes use of client and server components.
• The server part is considered as a Microsoft Windows service. Every instance is calculated to be a  separate instance of the Windows service.
• Clients interact with SSAS using some protocols which allow them to access any standard multi-dimensional data. One of the protocols used is XML for Analysis.

What is UDM? Explain its significance in SSAS?

Ans: UDM stands for the Unified Dimensional Model in SSAS. It is a bridge between the data sources and the users. It helps in the collection of data from all the different available sources to one single model.UDM consists of different components like Data Source, Data Source View, and Dimensional model.

Significance of UDM in SSAS: 

• Provides a user model.
• performance queries are comparatively highly flexible and faster.
• Captures the business rules in the model.

Tell us about SSAS components in brief?

Ans: SSAS uses a standard, automated engine called OLAP engine that will allow users to explore the data interactively. OLAP engine helps in enabling fast ad-hoc queries by the end-users. Exploring data can be done using Drilling, slicing, and pivoting.

Drilling: Drilling is a process of finding out the details of the data.

Slicing: Slicing is the process that represents the data in the form of rows and columns.

Pivoting: Pivoting refers to the process of switching the data, specifically the categories of data between the rows and columns.

What is FASMI in SSAS?

Ans: FASMI refers to Fast Analysis Shared Multi-dimensional Information. FASMI is an alternative term for OLAP. A database is an OLAP database if the database follows and satisfies the FASMI rules. 

Fast Analysis: The analysis can be performed within 5 seconds or less in the OLAP scenario.

Shared: The data can be accessed by many users using the factors of sensitivity and writebacks

Multi-dimensional: The structure of the data should be multi-dimensional.

Information: OLAP database is capable of handling massive volumes of data.

What is SSAS Dimension? What are the types of SSAS dimensions?

Ans: Dimensions in SSAS includes the group of attributes, represented in the form of columns in a table or as views. Dimensions can be used multiple times in a cube, or many cubes and interlinked. The significant type of dimensions is database dimension and cube dimension.

Database dimension: A dimension that is not dependent on a cube is called a database dimension.

Cube dimension: An instance of a database dimension is called a cube dimension.

 If you have any doubts on SSAS, then get them clarified from SSAS Industry experts on our  SSAS Tutorial !

. Explain the major differences between calculated measure and derived measure?

Ans: A difference is identified between calculated measure and derived measured based on the time frame of the calculation being performed.

Calculated measure: A measure is called a calculated measure as the calculation is performed when aggregations are created. The values obtained are not stored in a cube. Derived measure: A measure is called a derived measure as the calculation is performed before aggregations are created. These values that are obtained are stored in a cube.

SSAS Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project-Based Learning

Explore Curriculum

 

. What is a partition and why is it used?

Ans:  A partition in SSAS is referred to as a physical location of the stored cube data. There will be one partition for every cube by default. Every time a measure group is created, another partition will be created.

Query performance will be high if a partition is used because SSAS will read the data from the partitions that contain answers to the queries. Partitions help in the management of cubes and also stores aggregations.

. What is attribute hierarchy, and why is Attribute Hierarchy Display Folder property used?

Ans:  An attribute hierarchy is a hierarchy or strategy followed and created for every attribute in a dimension by SSAS. An attribute consists of two levels: All level and detail level.Hierarchies are used to organize the attributes which will be converted into user-defined hierarchies to provide a path for the cube.AttributeHierarchyDisplayFolder property helps in identifying and displaying the associate attribute hierarchy to the end-users.

. In short, illustrate the steps or Process of creating a cube?

Ans: Below are the steps to be followed to create a cube.

• Creation of data source
• Creation of data source view
• Creation of dimensions
• Creation of a cube
• Deployment and processing of the cube.

. List out the differences between SSAS 2005 and SSAS 2008?

Ans: SSAS 2008 has come up with some advancements based on the limitations in SSAS 2005. The significant differences between SSAS 2005 and SSAS 2008 are:

• Empty cube creation is not possible in SSAS 2005, while it is possible in SSAS 2008.
• Attribute relationships tab is a new feature in SSAS 2008 that is added and implemented in Dimension Designer. Implementation of Attribute relationship was complex in SSAS 2005.
• The creation of partitions was limited to 2000 in SSAS 2005, while in SSAS 2008, there is no partition limit and removed

. What is meant by a tuple? What is the process to extract the first tuple from the set?

Ans: A tuple is a slice of data from a cube. It is a combination of one or more members from different dimensions. We can extract the first tuple from the set using a function Set.Item(0).The below query 

SELECT {[Date].[Calendar].[Calendar Year].Members

}

ON 0

FROM [Adventure Works]

Results: CY 2001 CY 2002 CY 2003 CY 2004

$8,065,435.31$24,144,429.65 $32,202,669.43 $16,038,062.60

. What do you understand by named queries, and why are named queries used?

Ans: Named queries are the SQL expressions or queries in the data source view that will act as a table. The main functionality of the named query is to combine the data from one or more tables. Named queries do not require any schematic changes to the source data. The named query is used to confirm the data source.

. Explain the terms data warehouse and data mart. Elucidate the differences between them.

Ans: Data warehouse is an environment that represents the organization’s data. Data warehouse gives a complete view of the enterprise - current and historical information for decision making.
A data mart is defined as the subset of the organization data. It includes explicitly analytical data of a particular subject or department in an organization. A data mart is of three different types:

• Dependent
• Independent
• Logical data mart

Difference between data warehouse and data mart: The complete data of an organization or enterprise is called a data warehouse, while data mart is the subject of the complete data.

. Can we hide an attribute?

Ans: A property called “ AttributeHierarchyVisble” has to be selected with the value as False in the properties of the attribute.

It is represented as AttributeHierarchyVisible = False.

. Is it possible to not process an attribute? If yes, how?

Ans: Yes, there is a possibility to not process an attribute by using a hierarchy property called: AttributeHierarchyEnabled.

Select the property option: “AttributeHierarchyEnabled”=false.

. How do you provide security to a cube? Explain the procedure involved in it?

Security is one of the most essential factors to be considered when dealing with confidential data. Security is provided to the cubes by defining roles. The following is the procedure used to enable security to a cube.

• Defining the role
• Setting up the permissions
• Adding the correct users fit to the role.

Subscribe to our YouTube channel to get new updates..!

Subscribe

 

. For a measure group, can you estimate the number of minimum and maximum partitions required?

Ans: The minimum number of partitions allowed per group measure is one.
The maximum number of partitions allowed in a measure group is 2000( As per SSAS 2005). With SSAS 2008 and the next versions, there is no limit for the number of maximum partitions.

. How do you add a new column to the cube after the cube creation ?

Ans: A new column can be added to the cube after cube creation using Named calculation. A named calculation helps add a new column in the data source view by using hard coded values or using existing values or a combination of both.

. What do you understand about a rigid and flexible relationship? Which type of relationship is better for the performance?

Ans: Rigid and Flexible relationships are two different types of attribute relationship that represents the relation between various attributes.

Rigid Attribute Relationship: The relationship between the attributes remains the same, constant without any changes. Example: Month and date.

Flexible Attribute relationship: The relationship between the attributes will be dynamic, changing from time to time.Example: Relationship between an employee and a Manager.

. What do you understand by the terms: Fact Table and Dimension Table?

Ans: Fact Table: The Fact table is defined as the table, which includes the summary or the basic information. It is used to perform business analysis by using Metrics. In technical terms, the Fact table is the table that stores the detailed value for the measure.

Fact table mainly consists of two different sections:

• Measures/facts ( a numeric representation to analyze the business data)
  Foreign key to the dimension.
• Dimension table: The Dimension table includes the hierarchical data of an organization or enterprise which is specific to the business. It consists of the specific name of each member of a dimension. The dimension table uses a key attribute called the primary key column that will contain unique information/ value for each dimension.

. Explain the types of dimensions in short words?

Ans: There are three types of dimensions called Confirm dimensions, junk dimensions, degenerated dimensions.

Confirm dimension: Confirm dimension, also referred to as Role-playing dimension, is sharable across the data model.

Junk dimension: Lumping of small dimensions to form a single dimension together is called as Junk dimension. Grouping and moving them to a different single dimension is referred to as a junk dimension.

Degenerated dimensions: Degenerated Dimension does not contain the dimension id in the dimension table. It only includes the values in the fact table.

. Define OLAP and OLTP. Can you explain how OLAP is different from OLTP?

Ans: OLAP refers to Online Analytical Processing, a technology built to perform and organize the data of large businesses. OLAP allows using different sources of data; it will enable the end-users to access and manage the data warehouse data effectively and efficiently. OLAP uses a wide range of tools like Microsoft Excel, reporting services,and other business intelligence tools.

OLTP refers to Online Transactional Processing, used to perform transactional oriented tasks like inserting, updating, or deleting small amounts of data—examples of OLTP:Online booking.

OLAP consists of historical data and less frequent updates,which provides its support to make day-to-day decisions. At the same time,OLTP includes more regular updates and also contains current data and historical data.

If you have any doubts on ssas, then get them clarified from ssas Industry experts on our SSAS Community !

. Give a brief idea on the different languages used in SSAS?

Ans: The different languages used in SSAS are:

SQL language: SQL language is a standard language used in relational database systems to perform tasks like storing, updating, and retrieving the data using SQL statements.

MDX Language: MDX Language (Multidimensional Expressions) language is similar to SQL Language, which is used as a query language for OLAP cubes to perform Online Analytical Processing.

DMX Language: DMX Language (Data Mining Extensions) is another standard language used in SSAS to perform functions related to data mining models using DDL(Data Definition Language) statements, DML(Data Manipulation Language) statements.

Analysis Services Scripting Language: To create and manage Analysis Services structures directly on the server, ASSL adds a common language and object definition language. Analysis Services Scripting Language is an extension of the XMLA language.

. How is data stored in SSAS? Explain the different modes of storage in SSAS?

Ans: Data is stored in the form of cubes in SSAS. SSAS includes two different types of data called detailed data and summary. There are three types of storage modes used in cube partitions based on the approach to storing the data.

ROLAP: ROLAP (Real-Time Online Analytical Processing) is a storage mode where the detailed data is stored and represented in the relational database. At the same time, the summary is stored and served in the form of a data warehouse. This storage model requires an ample storage space with low latency and slow processing.

MOLAP: MOLAP (Multidimensional Online Analytical Processing) is the storage mode where the summary and detail data is represented in the multi-dimensional storage called OLAP server. The query response and processing is highs while the latency is high too. Using this storage mode, duplication of data occurs.

HOLAP: HOLAP (Hybrid Online Analytical Processing) is the storage mode where detailed data is stored in a relational database while the summary is stored and represented in the OLAP server. Hence, it is a mixture of both ROLAP and MOLAP. There are no limitations in HOLAP like the MOLAP and ROLAP. It utilizes optimized storage space with high processing time.

.What are the types of processing used in SSAS? Explain each one of them.

Ans: Processing in SSAS refers to the procedure of loading the data in SSAS. There are different types of processing used in SSAS. 

Process Ad: The process ad mode is used for performing the below operations.

• Adding new members to the dimensions from the database
• Updating the descriptions of the attributes
• Adding incremental fact data into the cube
• Applied on dimensions, groups, and partitions.

Process Clear: The below operations are performed in Process clear.

• Removing the data from the SSAS object and all child-level objects.
• Applicable for dimensions, partitions, cubes, mining models, and structures.

Process Clear Structure: 

• Applicable only for data mining components.
• Removing the training data that is previously loaded.

Process Data:The following are the operations by Process data.

• Processing of data is done without aggregation creation or building.
• Applicable only for partitions, dimensions, cubes.

.Give a brief view of the steps involved in backup SSAS.