SSAS Interview Questions and Answers

Q1 : Define the SQL Server Analysis Services (SSAS)?
A : SQL Server Analysis Services (SSAS) is the On-Line Analytical Processing (OLAP) Component of SQL Server. SSAS enables you to manufacture multidimensional structures called Cubes to pre-compute and store complex conglomerations, and furthermore to construct mining models to perform information examination to recognize profitable data like patterns, designs, connections and so forth inside the information utilizing Data Mining capacities of SSAS, which generally could be extremely hard to decide without Data Mining abilities.

Q2 : What is data mart?
A : A data mart is a subset of an organizational data store, usually oriented to a specific purpose or major data subject that may be distributed to support business needs. Data marts are analytical data stores designed to focus on specific business functions for a specific community within an organization.
Data marts are often derived from subsets of data in a data warehouse, though in the bottom-up data warehouse design methodology the data warehouse is created from the union of organizational data marts.
They are 3 types of data mart they are
Dependent
Independent
Logical data mart

Q3 : What is the difference between SSAS 2005 and SSAS2008?
A : 1. In 2005 it’s 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.

Q4 : What is a Named Calculation in SSAS?
A : A Named Calculation is another segment added to a Table in DSV and depends on an articulation. This capacity enables you to include an additional section into your DSV which depends on at least one segments from hidden information source Table(s)/View(s) joined together utilizing an articulation without requiring the option of a physical segment in the fundamental database Table(s)/View(s

Q5 : What is data source view or DSV?
A : A data source view is a persistent set of tables from a data source that supplies 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.
A 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.

Q6 : What are the types of database schema?
A : They are 3 types of database schema they are
1. Star
2. Snowflake
3. Starflake

Q7 : What are the different data sources supported by SSAS?
A : SSAS Supports both.Net and OLE DB Providers. Following are a portion of the significant sources bolstered by SSAS: SQL Server, MS Access, Oracle, Teradata, IBM DB2, and other social databases with the fitting OLE DB supplier.

Q8 : What Is Partition, How Will You Implement It?
A : You can use the Partition Wizard to define partitions for a measure group in a cube. By default, a single partition is defined for each measure group in a cube. Access and processing performance, however, can degrade for large partitions. By creating multiple partitions, each containing a portion of the data for a measure group, you can improve the access and processing performance for that measure group.

Q9 : What is the surrogate key?
A : A surrogate key is the SQL created a key which acts like another essential key for the table in the database, Data distribution centers normally utilize a surrogate key to particularly recognize an element. A surrogate isn’t produced by the client yet by the framework. An essential contrast between an essential key and surrogate key in a couple of databases is that essential key particularly distinguishes a record while a Surrogate key exceptionally recognizes an element.

Q10 : How will you hide an attribute?
A : We can hide the attribute by selecting “AttributeHierarchyVisible = False” in properties of the attribute.

Q11 : What is measure group, measure?
A : These measure groups can contain distinctive measurements and be at various granularity yet inasmuch as you show your 3D square effectively, your clients will have the capacity to utilize measures from every one of these measure bunches in their questions effortlessly and without agonizing over the fundamental multifaceted nature.

Q12 : What is Attribute hierarchy?
A : An attribute hierarchy is created for every attribute in a dimension, and each hierarchy is available for dimensioning fact data. This hierarchy consists of an “All” level and a detail level containing all members of the hierarchy.
you can organize attributes into user-defined hierarchies to provide navigation paths in a cube. Under certain circumstances, you may want to disable or hide some attributes and their hierarchies.

Q13 : What is use of AttributeHierarchyEnabled?
A : AttributeHierarchyEnabled: Determines whether an attribute hierarchy is generated by Analysis Services for the attribute. If the attribute hierarchy is not enabled, the attribute cannot be used in a user-defined hierarchy and the attribute hierarchy cannot be referenced in Multidimensional Expressions (MDX) statements.

Q14 : What are types of storage modes?
A : There are three standard storage modes in OLAP applications
MOLAP
ROLAP
HOLAP

Q15 : What is MOLAP and its advantage?
A : 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.
Advantages:
Since the data is stored on the OLAP server in optimized format, queries (even complex calculations) are faster than ROLAP.
The data is compressed so it takes up less space.
And because the data is stored on the OLAP server, you don’t need to keep the connection to the relational database.
Cube browsing is fastest using MOLAP

Q16 : What are Translations and its use?
A : Translation: The translation feature in analysis service allows you to display caption and attributes names that correspond to a specific language. It helps in providing GLOBALIZATION to the Cube.

Q17 : What is HOLAP and its advantage?
A : Hybrid Online Analytical Processing (HOLAP): HOLAP is a combination of MOLAP and ROLAP. HOLAP stores the detail data in the relational database but stores the aggregations in multidimensional format. Because of this, the aggregations will need to be processed when changes are occur. With HOLAP you kind of have medium query performance: not as slow as ROLAP, but not as fast as MOLAP. If, however, you were only querying aggregated data or using a cached query, query performance would be similar to MOLAP. But when you need to get that detail data, performance is closer to ROLAP.
Advantages:
HOLAP is best used when large amounts of aggregations are queried often with little detail data, offering high performance and lower storage requirements.
Cubes are smaller than MOLAP since the detail data is kept in the relational database.
Processing time is less than MOLAP since only aggregations are stored in multidimensional format.
Low latency since processing takes place when changes occur and detail data is kept in the relational database.

Q18 : What is Database dimension?
A : All the dimensions that are created using NEW DIMENSION Wizard are database dimensions. In other words, the dimensions which are at Database level are called Database Dimensions.

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

Q20 : Difference between Database dimension and Cube dimension?
A : The Database dimension has only Name and ID properties, whereas a Cube dimension has several more properties.
Database dimension is created one where as Cube dimension is referenced from database dimension.
Database dimension exists only once.where as Cube dimensions can be created more than one using ROLE PLAYING Dimensions concept.

Q21 : What is SCD (slowly changing dimension)?
A : 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

Q22 : What are types of SCD?
A : It is a concept of STORING Historical Changes and when ever 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

Q23 : What is perspective, have you ever created perspective?
A : 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 ex we are having retail and hospital data and end user is subscribed to see only hospital data, then we can create perspective according to it.

Q24 : What is a cube?
A : The basic unit of storage and analysis in Analysis Services is the cube. A cube is a collection of data that’s been aggregated to allow queries to return data quickly.
For example, a cube of order data might be aggregated by time period and by title, making the cube fast when you ask questions concerning orders by week or orders by title.

Q25 : What is the minimum and maximum number of partitions required for a measure group?
A : 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.