SSAS interview questions for experienced/SSAS Interview Questions and Answers for Freshers & Experienced

What is the Cube dimension?

A cube dimension is an instance of a database dimension within a cube is called a cube dimension. A database dimension can be used in multiple cubes, and multiple cube dimensions can be based on a single database dimension.

What are MOLAP and its advantage?

MOLAP (Multidimensional Online Analytical Processing): MOLAP is the most used storage type. It's designed to offer maximum query performance to the users. the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is both good and bad. When a cube with MOLAP storage is processed, the data is pulled from the relational database, the aggregations are performed, and the data is stored in the AS database. The data inside the cube will refresh only when the cube is processed, so latency is high.

What are the types of storage modes?

There are three standard storage modes in OLAP applications

1. MOLAP
2. ROLAP
3. HOLAP

What is the use of AttributeHierarchyVisible?

AttributeHierarchyVisible: Determines whether the attribute hierarchy is visible to client applications. The default value is True. However, if an attribute hierarchy will not be used for querying, you can save processing time by changing the value of this property to False.

What is the use of AttributeHierarchyOptimizedState?

AttributeHierarchyOptimizedState: Determines the level of optimization applied to the attribute hierarchy. By default, an attribute hierarchy is FullyOptimized, which means that Analysis Services builds indexes for the attribute hierarchy to improve query performance. The other option, NotOptimized, means that no indexes are built for the attribute hierarchy. Using NotOptimized is useful if the attribute hierarchy is used for purposes other than querying because no additional indexes are built for the attribute. Other uses for an attribute hierarchy can be helping to order another attribute.

What is the use of AttributeHierarchyDisplayFolder property?

AttributeHierarchyDisplayFolder: Identifies the folder in which to display the associated attribute hierarchy to end-users. For example, if I set the property value as “Test” to all the Attributes of a dimension then a folder with the name “Test” will be created and all the Attributes will be placed into the same.

What are calculated members and what is its use?

Calculations are item in the cube that are evaluated at runtime:

Calculated members: You can create customized measures or dimension members, called calculated members, by combining cube data, arithmetic operators, numbers, and/or functions.
Example: You can create a calculated member called Marks that converts dollars to marks by multiplying an existing dollar measure by a conversion rate. Marks can then be displayed to end users in a separate row or column. Calculated member definitions are stored, but their values exist only in memory. In the preceding example, values in marks are displayed to end users but are not stored as cube data.

What is the use of IsAggregatable property?

In Analysis Service we generally see all dimension has All member. This is because of the IsAggregatable property of the attribute. You can set its value to false so that it will not show All members. It's the default member for that attribute.

If you hide this member then you will have to set other attribute values to the default member else it will pick some value as default and this will create confusion in browsing data if someone is not known to change in default member.

What are a star, snowflake, and star flake schema?

Star schema: The instar schema fact table will be directly linked with all dimension tables. The star schema’s dimensions are denormalized with each dimension being represented by a single table. In a star schema, a central fact table connects a number of individual dimension tables.
Snowflake: The snowflake schema is an extension of the STAR SCHEMA, where each point of the star explodes into more points. In a star schema, each dimension is represented by a single dimensional table, whereas in a snowflake schema, that dimensional table is normalized into multiple lookup tables, each representing a level in the dimensional hierarchy. In the snowflake schema, the fact table will be linked directly as well as there will be some intermediate dimension tables between fact and dimension tables.
Star flake: A hybrid structure that contains a mixture of the star(denormalized) and snowflake(normalized) schemas.

What are confirmed dimensions, junk dimension, and degenerated dimensions?

Confirm dimension: It is the dimension that is sharable across the multiple facts or data model. This is also called Role Playing Dimensions.

Junk dimension: A number of very small dimensions might be lumped (a small irregularly shaped) together to form a single dimension, a junk dimension – the attributes are not closely related. Grouping Random flags and text Attributes in a dimension and moving them to a separate sub dimension is known as the junk dimension.

Degenerated dimension: In this degenerate dimension contains their values in the fact table and the dimension is not available in the dimension table. Degenerated Dimension is a dimension key without a corresponding dimension.

Example: In the PointOfSale Transaction Fact table, we have:

Date Key (FK), Product Key (FK), Store Key (FK), Promotion Key (FP), and POS Transaction Number

Date Dimension corresponds to Date Key, Production Dimension corresponds to Production Key. In a traditional parent-child database, POS Transactional Number would be the key to the transaction header record that contains all the info valid for the transaction as a whole, such as the transaction date and store identifier. But in this dimensional model, we have already extracted this info into other dimensions. Therefore, POS Transaction Number looks like a dimension key in the fact table but does not have the corresponding dimension table.

How many types of dimensions are there and what are they?

They are 3 types of dimensions:

Confirm dimension
Junk dimension
Degenerate attribute

How many types of attribute relationships are there?

They are 2 types of attribute relationships they are

1. Rigid
2. Flexible

Rigid: In Rigid relationships where the relationship between the attributes is fixed, attributes will not change levels or their respective attribute relationships.

Example: The time dimension. We know that month “January 2009″ will ONLY belong to Year “2009″ and it won't be moved to any other year.

Flexible: In Flexible relationship between the attributes is changed.

Example: An employee and department. An employee can be in the accounts department today but it is possible that the employee will be in the Marketing department tomorrow.

What Is Rolap And Its Advantage?

ROLAP (Relational Online Analytical Processing) : ROLAP does not have the high latency disadvantage of MOLAP. With ROLAP, the data and aggregations are stored in relational format. This means that there will be zero latency between the relational source database and the cube.

Disadvantage of this mode is the performance, this type gives the poorest query performance because no objects benefit from multi dimensional storage.

What Is Molap And Its Advantage?

MOLAP (Multi dimensional Online Analytical Processing) : MOLAP is the most used storage type. Its designed to offer maximum query performance to the users. the data and aggregations are stored in a multidimensional format, compressed and optimized for performance. This is both good and bad. When a cube with MOLAP storage is processed, the data is pulled from the relational database, the aggregations are performed, and the data is stored in the AS database. The data inside the cube will refresh only when the cube is processed, so latency is high.

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

1. The primary step is to log in to the SSAS instance.

2. Select the database that you would like to backup by right-clicking on it.

3. Check for backup option and click on the same.

4. On selecting a path, the backup will be store in that location. If the path is not selected, it will use C drive as a default folder.
5. SSAS Database backup can also be scheduled using the below steps:

6. From SSMS, right-click on the job folder of SQL
Click on New job
7. Write a job name which is used as a job identity
8. Navigate to steps page
9. Select the option: New
After updating the Step name, Select the SSAS command option as the job type.
Update the server name.
Write the XMLA script as below:

AdventureWorksDW.abf

true

password

10. Click on OK, check for schedule option, and schedule accordingly based on your requirement.

What Is Scd (slowly Changing Dimension)?

Slowly changing dimensions (SCD) determine how the historical changes in the dimension tables are handled. Implementing the SCD mechanism enables users to know to which category an item belonged to in any given date.

What Is Role Playing Dimension With Two Examples?

Role play dimensions: We already discussed about this. This is nothing but CONFIRMED Dimensions. A dimension can play different role in a fact table you can recognize a roleplay dimension when there are multiple columns in a fact table that each have foreign keys to the same dimension table.

Ex1: There are three dimension keys in the factinternalsales,factresellersales tables which all refer to the dimtime table,the same time dimension is used to track sales by that contain either of these fact table,the corresponding role-playing dimension are automatically added to the cube.

Ex2 : In retail banking, for checking account cube we could have transaction date dimension and effective date dimension. Both dimensions have date, month, quarter and year attributes. The formats of attributes are the same on both dimensions, for example the date attribute is in ‘dd-mm-yyyy’ format. Both dimensions have members from 1993 to 2010.

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

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 Many Types Of Relations Are There Between Dimension And Measure

They are six relation between the dimension and measure group, they are

* No Relationship
* Regular
* Refernce
* Many to Many
* Data Mining
* Fact

Explain the types of dimensions in short words?

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.

What Is Regular Type, No Relation Type, Fact Type, Referenced Type, Many-to-many Type With Example?


1. No relationship: The dimension and measure group are not related.
2. Regular: The dimension table is joined directly to the fact table.
3. Referenced: The dimension table is joined to an intermediate table, which in turn,is joined to the fact table.
4. Many to many:The dimension table is to an intermediate fact table,the intermediate fact table is joined , in turn, to an intermediate dimension table to which the fact table is joined.
5. Data mining:The target dimension is based on a mining model built from the source dimension. The source dimension must also be included in the cube.
6 Fact table: The dimension table is the fact table.

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

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 is a Factless fact table?

This is a very important interview question. The “Factless Fact Table” is a table that is similar to Fact Table except for having any measure; I mean that this table just has the links to the dimensions. These tables enable you to track events; indeed they are for recording events.

Factless fact tables are used for tracking a process or collecting stats. They are called so because the fact table does not have aggregatable numeric values or information. They are mere key values with reference to the dimensions from which the stats can be collected

What are types of SCD?

It is a concept of STORING Historical Changes and whenever an IT guy finds a new way to store then a new Type will come into picture. Basically there are 3 types of SCD they are given below:

>> SCD type1
>> SCD type2
>> SCD type3

What is the maximum size of a dimension?

The maximum size of the dimension is 4 gb.

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

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.

Can we hide an attribute?

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.

What Are Calculated Members And What Is Its Use?

Calculations are item in the cube that are eveluated at runtime

Calculated members: You can create customized measures or dimension members, called calculated members, by combining cube data, arithmetic operators, numbers, and/or functions.

Example: You can create a calculated member called Marks that converts dollars to marks by multiplying an existing dollar measure by a conversion rate. Marks can then be displayed to end users in a separate row or column. Calculated member definitions are stored, but their values exist only in memory. In the preceding example, values in marks are displayed to end users but are not stored as cube data.

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

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:

1. Dependent
2. Independent
3. 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.

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

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.

What is perspective, have you ever created perspective?

Perspectives are a way to reduce the complexity of cubes by hidden elements like measure groups, measures, dimensions, hierarchies etc. It’s nothing but slicing of a cube, for example we are having retail and hospital data and the end user is subscribed to see only hospital data, then we can create perspective according to it.

What is the minimum and maximum number of partitions required for a measure group?

In 2005 a MAX of 2000 partitions can be created per measure group and that limit is lifted in later versions. In any version the MINIMUM is ONE Partition per measure group.

How in MDX query can I get the top 3 sales years based on order quantity?

By default Analysis Services returns members in an order specified during attribute design. Attribute properties that define ordering are “OrderBy” and “OrderByAttribute”. Let's say we want to see order counts for each year. In Adventure Works MDX query would be:

SELECT {[Measures].[Reseller Order Quantity]} ON 0
, [Date].[Calendar].[Calendar Year].Members ON 1
FROM [Adventure Works];
Same query using TopCount:
SELECT
{[Measures].[Reseller Order Quantity]} ON 0,
TopCount([Date].[Calendar].[Calendar Year].Members,3, [Measures].[Reseller Order Quantity]) ON 1
FROM [Adventure Works];

How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?

Simply using the bottom count will return customers with null sales. You will have to combine it with NONEMPTY or FILTER.

SELECT { [Measures].[Internet Sales Amount] } ON COLUMNS ,
BOTTOMCOUNT(
NONEMPTY(DESCENDANTS( [Customer].[Customer Geography].[All Customers]
, [Customer].[Customer Geography].[Customer] )
, ( [Measures].[Internet Sales Amount] ) )
, 10
, ( [Measures].[Internet Sales Amount] )
) ON ROWS
FROM [Adventure Works]
WHERE ( [Date].[Calendar].[Calendar Year].&[2003] ) ;

After creating the cube, if we added a new column to the OLTP table then how do you add this new attribute to the cube?

Just open the data source view and on right click we find the option REFRESH. Click the REFRESH then it will add new attributes to the table which can be added to Cube.

What is AMO?

The full form of AMO is Analysis Management Objects. This is used to create or alter cubes from .NET code.

What is the Factless fact table?

This is a very important interview question. The “Fact less Fact Table” is a table which is similar to a Fact Table except for having any measure; I mean that this table just has the links to the dimensions. These tables enable you to track events; indeed they are for recording events. Fact less fact tables are used for tracking a process or collecting stats. They are called so because the fact table does not have aggregator numeric values or information. They are mere key values with reference to the dimensions from which the stats can be collected

What is the fact table?

A fact table contains the basic information that you wish to summarize. The table that stores the detailed value for measure is called fact table. In simple and best we can define it as “The table which contains METRICS” that are used to analyse the business.

It consists of 2 sections:

>> Foreigner key to the dimension
>> measures/facts(a numerical value that used to monitor business activity)

How will you add a new column to an existing table in data source view?

By using named calculations we can add a new column to an existing table in the data source view. Named Calculation is explained above.

What is Time Intelligence? How is it implemented in SSAS?

Time Intelligence is a technique, which allows us to align the Time Dimension in SSAS with our actual Calendar, thereby making time calculations like Period to Date, Comparison across Parallel Time Periods, Cumulative aggregates, etc. very simple without the need for us to write explicit MDX queries/expressions.

What is a data source view or DSV?

A data source view is a persistent set of tables from a data source that supply the data for a particular cube. BIDS also includes a wizard for creating data source views, which you can invoke by right-clicking on the Data Source Views folder in Solution Explorer. Data source view is the logical view of the data in the data source. Data source view is the only thing a cube can see.

What is a datasource or DS?

The data source is the Physical Connection information that the analysis service uses to connect to the database that hosts the data. The data source contains the connection string which specifies the server and the database hosting the data as well as any necessary authentication credentials.

What MDX functions do you most commonly use?

This is a great question because you only know this answer by experience. If you ask me this question, the answer practically rushes out of me. “CrossJoin, Descendants, and NonEmpty, in addition to Sum, Count, and Aggregate. My personal favorite is CrossJoin because it allows me to identify non-contiguous slices of the cube and aggregate even though those cube cells don’t roll up to a natural ancestor.” Indeed, CrossJoin has easily been my bread and butter.

Name a few Business Analysis Enhancements for SSAS?

The following table lists the business intelligence enhancements that are available in Microsoft SQL Server Analysis Services (SSAS). The table also shows the cube or dimension to which each business intelligence enhancement applies, and indicates whether an enhancement can be applied to an object that was created without using a data source and for which no schema has been generated.

What is processing?

Processing is a critical and resource-intensive operation in the data warehouse lifecycle and needs to be carefully optimized and executed. Analysis Services offers high performance and scalable processing architecture with a comprehensive set of controls for database administrators.

We can process an OLAP database, individual cube, Dimension, or a specific Partition in a cube.

What is WriteBack? What are the pre-conditions?

The Enable/Disable Writeback dialog box enables or disables writeback for a measure group in a cube. Enabling writeback on a measure group defines a writeback partition and creates a writeback table for that measure group. Disabling writeback on a measure group removes the writeback partition but does not delete the writeback table, to avoid unanticipated data loss.

What are the different ways of creating Aggregations?

We can create aggregations for faster MDX statements using Aggregation Wizard or thru UBO – Usage-Based Optimizations. Always, prefer the UBO method in real-time performance troubleshooting.

What are the roles of an Analysis Services Information Worker?

The role of an Analysis Services information worker is the traditional “domain expert” role in business intelligence (BI) someone who understands the data employed by a solution and is able to translate the data into business information. The role of an Analysis Services information worker often has one of the following job titles: Business Analyst (Report Consumer), Manager (Report Consumer), Technical Trainer, Help Desk/Operation, or Network Administrator.

What is a RAGGED hierarchy?

Under normal circumstances, each level in a hierarchy in Microsoft SQL Server Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drill-down paths. Expanding through every level for every drill down the path is then unnecessarily complicated.

What is a RAGGED hierarchy?

Under normal circumstances, each level in a hierarchy in Microsoft SQL Server Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drill-down paths. Expanding through every level for every drill down the path is then unnecessarily complicated.

Search
R4R Team
R4R provides SSAS Freshers questions and answers (SSAS Interview Questions and Answers) .The questions on R4R.in website is done by expert team! Mock Tests and Practice Papers for prepare yourself.. Mock Tests, Practice Papers,SSAS interview questions for experienced,SSAS Freshers & Experienced Interview Questions and Answers,SSAS Objetive choice questions and answers,SSAS Multiple choice questions and answers,SSAS objective, SSAS questions , SSAS answers,SSAS MCQs questions and answers Java, C ,C++, ASP, ASP.net C# ,Struts ,Questions & Answer, Struts2, Ajax, Hibernate, Swing ,JSP , Servlet, J2EE ,Core Java ,Stping, VC++, HTML, DHTML, JAVASCRIPT, VB ,CSS, interview ,questions, and answers, for,experienced, and fresher R4r provides Python,General knowledge(GK),Computer,PHP,SQL,Java,JSP,Android,CSS,Hibernate,Servlets,Spring etc Interview tips for Freshers and Experienced for SSAS fresher interview questions ,SSAS Experienced interview questions,SSAS fresher interview questions and answers ,SSAS Experienced interview questions and answers,tricky SSAS queries for interview pdf,complex SSAS for practice with answers,SSAS for practice with answers You can search job and get offer latters by studing r4r.in .learn in easy ways .