|
SSAS (SQL Server Analysis Services) is one of the latest technologies which helps in attaining and analyzing business requirements using business strategies. SSAS in Business Intelligence has come up with many opportunities around the globe. Professionals and experts in Business Intelligence are looking for individuals who know SSAS. Here are some of the SSAS Interview questions that will help you to crack the interview at the first shot. This guide includes SSAS frequently asked questions and answers, which will improve the knowledge on SSAS and will help you attain the best job in Business Intelligence career.
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.
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 !
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:
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.
Ans:
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:
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.
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.
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 !
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.
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.
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.
Ans: Below are the steps to be followed to create a cube.
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:
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
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.
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:
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.
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.
Ans: Yes, there is a possibility to not process an attribute by using a hierarchy property called: AttributeHierarchyEnabled.
Select the property option: “AttributeHierarchyEnabled”=false.
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.
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.
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.
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.
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:
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.
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 !
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.
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.
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.
Process Clear: The below operations are performed in Process clear.
Process Clear Structure:
Process Data:The following are the operations by Process data.
Ans:
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:
10. Click on OK, check for schedule option, and schedule accordingly based on your requirement.
Batch starts on 4th Apr 2023, Weekday batch
Batch starts on 8th Apr 2023, Weekend batch
Batch starts on 12th Apr 2023, Weekday batch
©2023 HKR Trainings. All rights Reserved.