Top 45+ Practice SSAS Interview Questions [ANSWERED] in 2020
SSAS Interview Questions and Answers

Top 45+ Practice SSAS Interview Questions [ANSWERED]

Last updated on 04th Jul 2020, Blog, Interview Questions

About author

Imran (Sr SSAS Developer )

He is Possessing 7+ Years Of Experience in SSAS. His Passion lies in Developing Entrepreneurs & Activities. Also, Rendered his intelligence to the Enthusiastic JOB Seekers.

(5.0) | 15212 Ratings 6342

These SSAS Interview Questions have been designed specially to get you acquainted with the nature of questions you may encounter during your interview for the subject of SSAS.As per my experience good interviewers hardly plan to ask any particular question during your interview, normally questions start with some basic concept of the subject and later they continue based on further discussion and what you answer.we are going to cover top 100 SSAS Interview questions along with their detailed answers. We will be covering SSAS scenario based interview questions, SSAS interview questions for freshers as well as SSAS interview questions and answers for experienced. 

1. What is SQL Server Analysis Services (SSAS)?

Ans:

SQL Server Analysis Services (SSAS) is the On-Line Analytical Processing (OLAP) Component of SQL Server. SSAS allows you to build multidimensional structures called Cubes to per-calculate and store complex aggregations, and also to build mining models to perform data analysis to identify valuable information like trends, patterns, relationships etc. within the data using Data Mining capabilities of SSAS, which otherwise could be really difficult to determine without Data Mining capabilities. SSAS comes bundled with SQL Server and you get to choose whether or not to install this component as part of the SQL Server Installation.

2. What is the difference between SSAS 2005 and SSIS 2008?

Ans:

 In 2005 it was not possible to create an empty cube but in 2008 we can create an empty cube. 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, we can create ONLY 2000 partitions per Measure Group in ssas 2005 and the same limit of partitions is removed in ssas 2008. You can answer more but if you end this with these then the interviewer feels that you are REAL EXPERIENCED.

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

Ans:

 They are 3 types of dimensions:

  • confirm dimension
  • junk dimension
  • degenerate attribute

4. What are Measures and Measure Groups? What is the difference between them?

Ans:

 A Measure is any numeric quantity/value that represents a metric aligned to an organization’s goals. This is the value which the business users are interested in, and are interested in viewing these values from different angles and different granularity levels. A measure is also commonly called a fact. The term “measures” and “facts” are used interchangeably. A Measure Group is a collection/group of measures which belong to the same underlying fact table. In SSAS, typically each Measure Group is tied to each one of the underlying fact tables. A Measure is a single numeric value whereas a Measure Group is a collection of measures.

5.  What is a data warehouse in short DWH?

Ans:

  • The data warehouse is an informational environment that provides an integrated and total view of the enterprise Makes the enterprise’s current and historical information easily available for decision making Makes decision-support transactions possible without hindering operational systems Renders the organization’s information consistent Presents a flexible and interactive source of strategic information OR a warehouse is a Subject oriented Integrated Time variant Non volatile for doing decision support.

OR

  • Collection of data in support of management’s decision making process”. He defined the terms in the sentence as follows.

OR

  • Subject oriented: It defines the specific business domain ex: banking, retail, insurance, etc…..
  • Integrated:It should be in a position to integrated data from various source systems. Ex: sql,oracle,db2 etc……
  • Time variant: It should be in a position to maintain the data the various time periods.
  • Non volatile: Once data is inserted it can’t be changed

6. What are Calculated Members? How do they differ from Measures?

Ans:

Calculated Members are members of a measure group and are defined based on a combination of one or more base measures, arithmetic/conditional operators, numeric values, and functions, etc. For example, profit is a calculated member/calculate measure, which is defined based on various base measures like selling price, cost, price, tax amount, freight amount, etc. The value of a measure (base measure) is stored in a cube as part of the cube processing process. Whereas the value of a calculated member/measure is calculated on the fly in response to a user request and only the definition is stored in the cube.

7. What are Named Sets? What are the two types of Named Sets?

Ans:

A Named Set is a set of dimension members (usually a subset of dimension members) and is defined using MDX (a Multidimensional Expression). Often Named Sets are defined for improved usability by the end users and client applications. Apart from that, they can also be used for various calculations at the cube level. Similar to calculated members/measures, named sets are defined using a combination of cube/dimension data, arithmetic operators, numeric values, functions, etc. Some of the examples of Named Sets are top 50 customers, top 10 products, top 5 students, etc.

8. What is data mart?

Ans:

  1.  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.
  1. 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

9. What is the difference between a data mart and a data warehouse? What are the different Storage Modes supported by Cube Partitions?

Ans:

Data Warehouse is complete data whereas Data mart is Subset of the same.

  1. There are primarily two types of data in SSAS: summary and detail data. Based on the approach used to store each of these two types of data, there are three standard storage modes supported by partitions:
  • ROLAP: ROLAP stands for Real Time Online Analytical Processing. In this storage mode, summary data is stored in the relational data warehouse and detail data is stored in the relational database. This storage mode offers low latency, but it requires large storage space as well as slower processing and query response times.
  • MOLAP: MOLAP stands for Multidimensional Online Analytical Processing. In this storage mode, both summary and detail data is stored on the OLAP server (multidimensional storage). This storage mode offers faster query response and processing times, but offers a high latency and requires an average amount of storage space. This storage mode leads to duplication of data as the detail data is present in both the relational as well as the multidimensional storage.
  • HOLAP: HOLAP stands for Hybrid Online Analytical Processing. This storage mode is a combination of ROLAP and MOLAP storage modes. In this storage mode, summary data is stored in OLAP server (Multidimensional storage) and detail data is stored in the relational data warehouse. This storage mode offers optimal storage space, query response time, latency and fast processing times.

10. Have you ever worked on performance tuning, if yes what are the steps involved in it?

Ans:

 We need to identify the bottlenecks to tune the performance, to overcome the bottleneck we need to follow the following:

  • Avoid named queries
  • Unnecessary relationships between tables
  • Proper attribute relationships to be given
  • Proper aggregation design
  • Proper partitioning of data
  • Proper dimension usage design
  • Avoid unnecessary many to many relationships
  • Avoid unnecessary measures

Set Attribute Hierarchy Enabled = FALSE to Attributes that is not required won’t take even a single measure which is not necessary.

11. What is processing in SSAS?

Ans:

 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 predefined 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.

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

Ans:

  •  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 outdated copy (old database) and switches automatically to the new database which has been updated.

13. What are the difficulties faced in cube development?

Ans:

 You can tell any area where you feel difficult to work. But always the best answers will be the following:

  • Giving attribute relationships
  • Calculations
  • Giving dimension usage (many to many relationship)
  • Analyzing the requirements

14. Explain the flow of creating a cube?

Ans:

  •  Steps to create a cube in ssas
  • Create  a data source.
  • Create a datasource view.
  • Create Dimensions
  • Create a cube.
  • Deploy and Process the cube.

15. What are aggregates? What is the purpose of defining an aggregation design in Analysis Services?

Ans:

Aggregates are summarized values, each of which corresponds to a combination of an attribute from each dimension and a measure group. An aggregate in SSAS is the differentiating factor between OLAP and OLTP, and is the fundamental principle of SSAS/OLAP, which offers blazing fast performance.

16. What is a datasource or DS?

Ans:

 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.

17. What is a data source view or DSV?

Ans:

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.

18.  What is the name of the calculation?

Ans:

A named calculation is a SQL expression represented as a calculated column. This expression appears and behaves as a column in the table. A named calculation lets you extend the relational schema of existing tables or views in a data source view without modifying the tables or views in the underlying data source. Named calculation is used to create a new column in the DSV using hard coded values or by using existing columns or even with both.

19. What are translations? What is its significance in SSAS?

Ans:

Translations in SSAS allow us to bind labels/properties of those objects in SSAS which can be represented in multiple languages. In simple terms, Translations allow us to display the Labels and Captions of various SSAS objects in different languages. In SSAS both metadata and data can be translated. The objects which support Translations in SSAS include databases, cubes, dimensions, attributes, hierarchies, measure groups, calculated members, KPIs, and various other objects.

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

Ans:

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.

    Subscribe For Free Demo

    21. What is a query?

    Ans:

    The query in DSV is similar to View in Database. This is used to create a Virtual table in DSV which will not impact the underlying database. Named query is mainly used to merge the two or more tables in the datasource view or to filter columns of a table.

    22. Why do we need named queries?

    Ans:

    A named query is used to join multiple tables, to remove unnecessary columns from a table of a database. You can achieve the same in the database using Views but this Named Queries will be the best bet when you don’t have access to create Views in the database.

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

    Ans:

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

    24. What is a dimension table?

    Ans:

     A dimension table contains hierarchical data by which you’d like to summarize. A dimension table contains specific business information, a dimension table that contains the specific name of each member of the dimension. The name of the dimension member is called an “attribute”. The key attribute in the dimension must contain a unique value for each member of the dimension. This key attribute is called “primary key column”. The primary key column of each dimension table corresponds to the one of the key columns  in any related fact table.

    25. What is the fact table?

    Ans:

    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)

    26. What is the Factless fact table?

    Ans:

    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

    27. What are the types of processing and explain each?

    Ans:

    They are 6 types of processing in ssas ,they are:

    • Process Full
    • Process Data
    • Process Index
    • Process Incremental
    • Process Structure
    • UnProcess
    1.  Process Full: Processes an Analysis Services object and all the objects that it contains. When Process Full is executed against an object that has already been processed, Analysis Services drops all data in the object, and then processes the object. This kind of processing is required when a structural change has been made to an object, for example, when an attribute hierarchy is added, deleted, or renamed. This processing option is supported for cubes, databases, dimensions, measure groups, mining models, mining structures, and partitions.
    1. Process Data: Processes data only without building aggregations or indexes. If there is data is in the partitions, it will be dropped before re-populating the partition with source data. This processing option is supported for dimensions, cubes, measure groups, and partitions.
    1. Process Index: Creates or rebuilds indexes and aggregations for all processed partitions. This option causes an error on unprocessed objects. This processing option is supported for cubes, dimensions, measure groups, and partitions.
    1. Process Increment: Adds newly available fact data and process only to the relevant partitions. This processing option is supported for measure groups, and partitions.
    1. Process Structure: If the cube is unprocessed, Analysis Services will process, if it is necessary, all the cube’s dimensions. After that, Analysis Services will create only cube definitions. If this option is applied to a mining structure, it populates the mining structure with source data. The difference between this option and the Process Full option is that this option does not iterate the processing down to the mining models themselves. This processing option is supported for cubes and mining structures.
    1. Un process : Drops the data in the object specified and any lower-level constituent objects. After the data is dropped, it is not reloaded. This processing option is supported for cubes, databases, dimensions, measure groups, mining models, mining structures, and partitions.
    1. Process Default: Detects the process state of an object, and performs processing necessary to deliver unprocessed or partially processed objects to a fully processed state. This processing option is supported for cubes, databases, dimensions, measure groups, mining models, mining structures, and partitions.

    28 What is a cube?

    Ans:

    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.

    29. What is AMO?

    Ans:

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

    30. 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?

    Ans:

    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.

    Course Curriculum

    Enroll in SSAS Certification Course Led By Industry Experts

    Weekday / Weekend BatchesSee Batch Details

    31. What is attribute relationships, why do we need it?

    Ans:

    Attribute relationships are the way of telling the analysis service engine that how the attributes are related with each other. It will help to relate two or more  attributes to each other.Processing time will be decreased if proper relationships are given. This increases the Cube Processing performance and MDX query performance too. In Microsoft SQL Server Analysis Services, attributes within a dimension are always related either directly or indirectly to the key attribute. When you define a dimension based on a star schema, which is where all dimension attributes are derived from the same relational table, an attribute relationship is automatically defined between the key attribute and each non-key attribute of the dimension. When you define a dimension based on a snowflake schema, which is where dimension attributes are derived from multiple related tables, an attribute relationship is automatically defined as follows:

    • Between the key attribute and each non-key attribute bound to columns in the main dimension table.
    • Between the key attribute and the attribute bound to the foreign key in the secondary table that links the underlying dimension tables.
    • Between the attribute bound to foreign key in the secondary table and each non-key attribute bound to columns from the secondary table.

    32.What is the difference between SSAS 2005 and SSAS 2008?

    Ans:

    • In 2005 it was not possible to create an empty cube but in 2008 we can create an empty cube.
    • 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
    • we can create ONLY 2000 partitions per Measure Group in ssas 2005 and the same limit of partitions is removed in ssas 2008.

    33. What are KPIs and what is its use?

    Ans:

     In Analysis Services, a KPI is a collection of calculations that are associated with a measure group in a cube that are used to evaluate business success. We use KPI to see the business at the particular point, this is represents with some graphical items such as traffic signals etc

    34. What are actions, how many types of actions are there, explain with example?

    Ans:

    Actions are a powerful way of extending the value of SSAS cubes for the end user. They can click on a cube or portion of a cube to start an application with the selected item as a parameter, or to retrieve information about the selected item. One of the objects supported by a SQL Server Analysis Services cube is the action. An action is an event that a user can initiate when accessing cube data. The event can take a number of forms. For example, a user might be able to view a Reporting Services report, open a Web page, or drill through to detailed information related to the cube data

    Analysis Services supports three types of actions:

    • Report action: Report action Returns a Reporting Services report that is associated with the cube data on which the action is based.
    • Drill through: Drill through Returns a result set that provides detailed information related to the cube data on which the action is based.
    • Standard: Standard has five action sub types that are based on the specified cube data.
    • Data set: Returns a multidimensional data set.
    • Proprietary: Returns a string that can be interpreted by a client application.
    • Row set: Returns a tabular row set.
    • Statement: Returns a command string that can be run by a client application.
    • URL:  Returns a URL that can be opened by a client application, usually a browser.

    35. What is partition, how will you implement it?

    Ans:

    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.

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

    Ans:

    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.

    37. What are Aggregations and its use?

    Ans:

    Aggregations provide performance improvements by allowing Microsoft SQL Server Analysis Services (SSAS) to retrieve pre-calculated totals directly from cube storage instead of having to recalculate data from an underlying data source for each query. To design these aggregations, you can use the Aggregation Design Wizard. This wizard guides you through the following steps:

    • Selecting standard or custom settings for the storage and caching options of a partition, measure group, or cube.
    • Providing estimated or actual counts for objects referenced by the partition, measure group, or cube.
    • Specifying aggregation options and limits to optimize the storage and query performance delivered by designed aggregations.
    • Saving and optionally processing the partition, measure group, or cube to generate the defined aggregations.
    • After you use the Aggregation Design Wizard, you can use the Usage-Based Optimization Wizard to design aggregations based on the usage patterns of the business users and client applications that query the cube.

    38. What is perspective, have you ever created perspective?

    Ans:

     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.

    39. What is deploy, process and build?

    Ans:

    • Build: Verifies the project files and creates several local files.
    • Deploy: Deploy the structure of the cube(Skeleton) to the server.
    • Process: Read the data from the source and build the dimensions and cube structures

    Elaborating the same is given below:

    • Build: It is used to process the data of the cube database. Build is a version of a program. As a rule, a build is a pre-release version and as such is identified by a build number, rather than by a release number. Reiterative (repeated) builds are an important part of the development process. Throughout development, application components are collected and repeatedly compiled for testing purposes, to ensure a reliable final product. Build tools, such as make or Ant, enable developers to automate some programming tasks. As a verb, to build can mean either to write code or to put individual coded components of a program together.
    • Deployment: During development of an Analysis Services project in Business Intelligence Development Studio, you frequently deploy the project to a development server in order to create the Analysis Services database defined by the project. This is required to test the project. for example, to browse cells in the cube, browse dimension members, or verify key performance indicators (KPIs) formulas.

    40. What is the maximum size of a dimension?

    Ans:

    The maximum size of the dimension is 4 gb.

    41 What are types of SCD?

    Ans:

     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

    42 What is measure group, measure?

    Ans:

    • Measure groups : These measure groups can contain different dimensions and be at different granularity  but so long as you model your cube correctly, your users will be able to use measures from each of these measure groups in their queries easily and without worrying about the underlying complexity.
    • Creating multiple measure groups : To create a new measure group in the Cube Editor, go to the Cube Structure tab and right-click on the cube name in the Measures pane and select ‘New Measure Group’. You’ll then need to select the fact table to create the measure group from and then the new measure group will be created; any columns that aren’t used as foreign key columns in the DSV will automatically be created as measures, and you’ll also get an extra measure of aggregation type Count. It’s a good idea to delete any measures you are not going to use at this stage.
    •  Measures :  Measures are the numeric values that our users want to aggregate, slice, dice and otherwise analyze, and as a result, it’s important to make sure they behave the way we want them to. One of the fundamental reasons for using Analysis Services is that, unlike a relational database it allows us to build into our cube design business rules about measures: how they should be formatted, how they should aggregate up, how they interact with specific dimensions and so on.

    43 What is the attribute?

    Ans:

     An attribute is a specification that defines a property of an object, element, or file. It may also refer to or set the specific value for a given instance of such.

     44 What is a surrogate key?

    Ans:

    •  A surrogate key is the SQL generated key which acts like an alternate primary key for the table in the database, Data warehouses commonly use a surrogate key to uniquely identify an entity. A surrogate is not generated by the user but by the system. A primary difference between a primary key and surrogate key in few databases is that primary key uniquely identifies a record while a Surrogatekey uniquely identifies an entity.
    • Ex: An employee may be recruited before the year 2000 while another employee with the same name may be recruited after the year 2000. Here, the primary key will uniquely identify the record while the surrogate key will be generated by the system (say a serial number) since the SK is NOT derived from the data.

    45 How many types of relations are there between dimension and measure group?

    Ans:

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

    • No Relationship
    • Regular
    • Reference
    • Many to Many
    • Data Mining
    • Fact

    46 What is regular type, no relation type, fact type, referenced type, many-to-many type with example?

    Ans:

    •  No relationship: The dimension and measure group are not related.
    • Regular: The dimension table is joined directly to the fact table.
    • Referenced: The dimension table is joined to an intermediate table, which in turn,is joined to the fact table.
    • 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.
    • 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.
    • Fact table: The dimension table is the fact table.

    47 What are calculated members and what is its use?

    Ans:

    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.

    48. What are attribute relationships, why do we need them?

    Ans:

     Attribute relationships are the way of telling the analysis service engine that how the attributes are related with each other. It will help to relate two or more  attributes to each other.Processing time will be decreased if proper relationships are given. This increases the Cube Processing performance and MDX query performance too. In Microsoft SQL Server Analysis Services, attributes within a dimension are always related either directly or indirectly to the key attribute. When you define a dimension based on a star schema, which is where all dimension attributes are derived from the same relational table, an attribute relationship is automatically defined between the key attribute and each non-key attribute of the dimension. When you define a dimension based on a snowflake schema, which is where dimension attributes are derived from multiple related tables, an attribute relationship is automatically defined as follows:

    • Between the key attribute and each non-key attribute bound to columns in the main dimension table.
    • Between the key attribute and the attribute bound to the foreign key in the secondary table that links the underlying dimension tables.
    • Between the attribute bound to foreign key in the secondary table and each non-key attribute bound to columns from the secondary table.

    49. How many types of attribute relationships are there?

    Ans:

    They are 2 types of attribute relationships they are:

    • Rigid
    • 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 the month “January 2009″ will ONLY belong to Year “2009″ and it won’t be moved to any other year.
    • Flexible: The 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.

    50.What is a role playing dimension with two examples?

    Ans:

    •  Role play dimensions: We already discussed this. This is nothing but CONFIRMED Dimensions. A dimension can play a different role in a fact table. You can recognize a role play 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 internationalizes, fact re seller sales tables which all refer to the dim time table,the same time dimension is used  to track sales that contain either of these fact tables,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.
    Course Curriculum

    Get On-Demand SSAS Training from Top-Rated Instructors

    • Instructor-led Sessions
    • Real-life Case Studies
    • Assignments
    Explore Curriculum

    51. What are conformed dimensions, junk dimension and degenerate dimensions?

    Ans:

    • Confirm dimension: It is the dimension which is shareable 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 of Random flags and text Attributes in a dimension and moving them to a separate sub dimension is known as junk dimension.
    • Degenerated dimension: In this degenerate dimension contains their values in fact table and the dimension is not available in dimension table. Degenerated Dimension is a dimension key without 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.

    52. What are the types of database schema?

    Ans:

    They are 3 types of database schema they are:

    • Star
    • Snowflake
    • Starflake

    53. What is star, snowflake and star flake schema?

    Ans:

    • Star schema: In star schema fact table will be directly linked with all dimension tables. The star schema’s dimensions are demoralized 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 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 star(demoralized) and snowflake(normalized) schema’s.

    54.How will you hide an attribute?

    Ans:

    We can hide the attribute by selecting “AttributeHierarchyVisible = False” in properties of the attribute.

    55. How will you make an attribute not process?

    Ans:

     By selecting  “ AttributeHierarchyEnabled = False”, we can make an  attribute not in process.

    56. What is the use of IsAggregatable property?

    Ans:

     In Analysis Service we generally see all dimensions have All members. 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. Its default member for that attribute. If you hide this member then you will have to set another attribute value to 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.

    57. What are key, name and value columns of an attribute?

    Ans:

    •  Key column of any attribute: Contains the column or columns that represent the key for the attribute, which is the column in the underlying relational table in the data source view to which the attribute is bound. The value of this column for each member is displayed to users unless a value is specified for the Name Column property.
    • Name  column of an attribute: Identifies the column that provides the name of the attribute that is displayed to users, instead of the value in the key column for the attribute. This column is used when the key column value for an attribute member is cryptic or not otherwise useful to the user, or when the key column is based on a composite key. The Name Column property is not used in parent-child hierarchies; instead, the Name Column property for child members is used as the member names in a parent-child hierarchy.
    • Value columns of an attribute: Identifies the column that provides the value of the attribute. If the Name Column element of the attribute is specified, the same Data Item values are used as default values for the Value Column element. If the Name Column element of the attribute is not specified and the Key Columns collection of the attribute contains a single Key Column element representing a key column with a string data type, the same Data Item values are used as default values for the Value Column element.

    58. What is hierarchy, what are its types and differences between them?

    Ans:

    •  A hierarchy is a very important part of any OLAP engine and allows users to drill down from  summary levels hierarchies represent the way user expect to explore data at more detailed level hierarchies  is made up of multiple levels creating the structure based on end user requirements.
    • years->quarter->month->week ,are all the levels of calendar hierarchy

    They are 2 types of hierarchies they are:

    • Natural hierarchy
    • Unnatural hierarchy
    • Natural hierarchy: This means that the attributes are intuitively related to one another. There is a clear relationship from the top of the hierarchy to the bottom. Example: An example of this would be date: year, quarter and month follow from each other, and in part, define each other.
    • Unnatural hierarchy: This means that the attributes are not clearly related. Example: An example of this might be geography; we may have country -> state -> city, but it is not clear where Province might sit.

    59. What is Attribute hierarchy?

    Ans:

    An attribute hierarchy is created for every attribute in a dimension, and each hierarchy is available for dimension 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.

    60. What is the use of AttributeHierarchyDisplayFolder property ?

    Ans:

    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.

    61. What is the use of AttributeHierarchyEnabled?

    Ans:

    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.

    62.  What is the use of AttributeHierarchyOptimizedState?

    Ans:

     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.

    63. What is the use of AttributeHierarchyOrdered ?

    Ans:

    AttributeHierarchyOrdered: Determines whether the associated attribute hierarchy is ordered. 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.

    64. What is the use of AttributeHierarchyVisible ?

    Ans:

    Attribute Hierarchy Visible: 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.

    65. What are types of storage modes?

    Ans:

    There are three standard storage modes in OLAP applications:

    • MOLAP
    • ROLAP
    • HOLAP

    66. What is MOLAP and its advantage?

    Ans:

    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.

    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.

    67. What is ROLAP and its advantage?

    Ans:

    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.

    Advantages:

    • Since the data is kept in the relational database instead of on the OLAP server, you can view the data in almost real time.
    • Also, since the data is kept in the relational database, it allows for much larger amounts of data, which can mean better scalability.
    • Low latency.

    68. What is HOLAP and its advantage?

    Ans:

    Hybrid Online Analytical Processing (HOLAP): HOLAP is a combination of MOLAP and ROLAP. HOLAP stores the detailed data in the relational database but stores the aggregations in multidimensional format. Because of this, the aggregations will need to be processed when changes 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 detailed 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 detailed 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.

    69. What are Translations and its use?

    Ans:

    Translation: The translation feature in analysis service allows you to display captions and attributes names that correspond to a specific language. It helps in providing GLOBALIZATION to the Cube.

    70. What is the Database dimension?

    Ans:

    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.

    SSIS Sample Resumes! Download & Edit, Get Noticed by Top Employers! Download

    71. What is Cube dimension?

    Ans:

    A cube dimension is an instance of a database dimension within a cube 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

    72. Difference between Database dimension and Cube dimension?

    Ans:

    • 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.

    73. How will you add a dimension to the cube?

    Ans:

    To add a dimension to a cube follow these steps:  

    • In Solution Explorer, right-click the cube, and then click View Designer. 
    • In the Design tab for the cube, click the Dimension Usage tab.
    •   Either click the Add Cube Dimension button, or right-click anywhere on the work surface  and then click Add Cube Dimension.
    •   In the Add Cube Dimension dialog box, use one of the following steps:
    • To add an existing dimension, select the dimension, and then click OK.
    • To create a new dimension to add to the cube, click New dimension, and then follow the steps in the Dimension Wizard.

    74. What is SCD (slowly changing dimension)?

    Ans:

     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.

    76. What  is Type1, Type2, Type3 of SCD?

    Ans:

    Type 1: In Type 1 Slowly Changing Dimension, the new information simply overwrites the original information. In other words, no history is kept.

    In our example, recall we originally have the following table:

    Customer KeyNameState
    1001ChristinaIllinois

    After Christina moved from Illinois to California, the new information replaces the new record, and we have the following table:

    Customer KeyNameState
    1001ChristinaCalifornia
    • Advantages: This is the easiest way to handle the Slowly Changing Dimension problem, since there is no need to keep track of the old information.
    • Disadvantages: All history is lost. By applying this methodology, it is not possible to trace back in history.
    • Usage:  About 50% of the time.
    • When to use Type 1: Type 1 slowly changing dimension should be used when it is not necessary for the data warehouse to keep track of historical changes.
    •  Type 2: In Type 2 Slowly Changing Dimension, a new record is added to the table to represent the new information. Therefore, both the original and the new record will be present. The new record gets its own primary key.

    In our example, recall we originally have the following table:

    Customer KeyNameState
    1001ChristinaIllinois

    After Christina moved from Illinois to California, we add the new information as a new row into the table:

    Customer KeyNameState
    1001ChristinaIllinois
    1005ChristinaCalifornia
    • Advantages: This allows us to accurately keep all historical information.
    • Disadvantages:
    1. This will cause the size of the table to grow fast. In cases where the number of rows for the table is very high to start with, storage and performance can become a concern.
    1. This necessarily complicates the ETL process.
    • Usage: About 50% of the time.
    • Type3 : In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active.

    In our example, recall we originally have the following table:

    Customer KeyNameState
    1001ChristinaIllinois

    To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns:

    • Customer Key,Name,OriginalState,CurrentState,Effective Date

    After Christina moved from Illinois to California, the original information gets updated, and we have the following table (assuming the effective date of change is January 15, 2003):

    Customer KeyNameOriginalStateCurrentStateEffective Date
    1001ChristinaIllinoisCalifornia15-JAN-2003
    • Advantages:
    1. This does not increase the size of the table, since new information is updated.
    2. This allows us to keep some part of history.
    • Disadvantages: Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if Christina later moves to Texas on December 15, 2003, the California information will be lost.
    • Usage: Type 3 is rarely used in actual practice.

    Are you looking training with Right Jobs?

    Contact Us
    Get Training Quote for Free