SSAS is a Microsoft business intelligence tool for online analytical processing and data mining functionality. This tool is used to create projects which deliver an end product as a multidimensional database called a cube. Here I have listed the top 25 SSAS Interview questions. The top 25 SSAS interview questions include basic and important questions for experienced and fresher.
SSAS Interview Questions
1. What is SQL Server Analysis Services (SSAS)?
- SQL (SSAS) is the online analytical processing component of SQL Server.
- SSAS allows the building of Multidimensional structures called cubes
- SSAS comes with bundled SQL server and gets you to choose to install this component part of the SQL server installation.
2. What language is used in SSAS?
- Structured Query Language (SQL)
- Multidimensional Expressions (MDX):
- It is an industry-standard query language orientated toward analysis
- Data Mining Extensions (DMX):
- It is an industry-standard query language oriented toward data mining.
- Analysis Services Scripting Language (ASSL):
- It is used to manage Analysis Services database objects.
3. Explain the Two-Tier Architecture of SSAS?
SSAS is used both server client components to supply OLAP and data mining functionality BI applications.
Server client components:
- It is implemented as a Microsoft service. This service is implemented as a separate instance of the window service.
- Client components with analysis services using the standard XML protocol
4. What are the components of SSAS?
- Drilling refers to the process of exploring details and data
- Slicing refers to the process of placing data in rows and columns
- Pivoting refers to switching categories of data between rows and columns
- OLAP engine is used for enabling fast Adhoc queries by end users.
- OLAP is called a dimensional database.
5. What is the need for SSAS Component?
- SSAS is very easy to use and interactive
- Apply efficient security principles
- Ability to create and manage Data warehouse
- Analysis services are the only component of the SQL server
- Faster Analysis and troubleshooting.
6. What is a Data Source?
- The data source contains the connection information used by SSAS
- Data source primarily contains the following information (Apart from various other properties like Query, timeout, isolation, etc.)
- This name was given to the connection setup to the database from a server.
- Provider
- Server name
- Database name
- Impersonation information
7. What are the different data sources supported by SSAS?
- SQL Server
- MS Access
- Oracle
- Teradata
- IBM DB2
8. What is a data warehouse in short DWH?
- Provides an integrated and total view of the enterprise.
- Renders the organization’s information consistent
- Enterprise’s current history information available for decision making
- Decision support transactions are possible without hindering the operational system.
9. Explain the flow of creating a cube?
Cube creating
- Create a data source
- Create a data source view
- Create Dimensions
- Create a cube
- Deploy and process the cube
10. What are Analysis Services?
- The analysis server is the middle tier for analytical processing, OLAP, and Data mining.
- Analysis services assist in creating, managing, and designing multidimensional structures containing data from varied sources.
- The analysis provides a wide array of data mining algorithms for specific trends and needs
11. List out some analyzing features?
- Flexible data model and creation management
- Scalable architecture to handle OLAP
- Provides integration of administration tools, data sources, security, caching, reporting, etc.
- Provides extensive support for custom applications
- Easy to use with a lot of wizards and designers
12. What is FASMI?
Fast Analysis –It is defined in the OLAP scenario in five seconds or less
Shared – It must support access to the data by many users
Multidimensional – Data inside the OLAP Database must be multidimensional in structure
Information – OLAP database must support large volumes of data
13. How Cubes are implemented in SSAS?
- SSAS Cubes are created using the cube Wizard
- Cubes are multidimensional models that store data from one or more sources
- Cubes can also store aggregations
- Build in dimensions created by cubes
14. What are the different processing modes available in SSAS?
The processing modes:
- Process add
- Process clear
- Process clear structure
- Process Data
- Process Default
- Process full
- Process index
- Process structure
- Process update
15. What is Business Intelligence?
- It is an umbrella Term. That refers to a variety of software applications used to analyze an organization’s raw data.
- This discipline is made up of several related activities, including data mining, querying and reporting, and the online analytical process.
16. What are the 8 major fact tables in IB?
8 Major Facts
- Position
- Transaction
- Risk
- Performance
- Collateral
- Client Account
- Prop Desk
- Deal Pipeline
17. What are the pros and cons of using Tables and Named Queries in DSV?
| Tables | Named Queries |
| Named calculations can be added to tables in DSV | Named Calculations cannot be added to Named Queries in DSV. |
| Keys and Relationships are automatically set (by the wizard) based on the Keys and Relationships in the underlying database schema. | Keys and Relationships have to be set explicitly in the DSV. |
| Only one Table View from the underlying data source can be referenced in DSV. | More than one Table View from the underlying data source can be referenced using a SQL Expression in the DSV. |
| Any filter limiting conditions cannot be applied to a table in DSV. | Filter limiting conditions can be applied as part of the SQL expression in the Named Query in the DSV. |
18. What is a fact table?
- The fact table contains the basic information that wishes to summarize.
- The table stores detailed values for a measure called a Fact table.
- It is defined as a Table that contains METRICS” that are Used to analyze the business.
19. What is a named query?
- The named query is similar to the view in the Database.
- It is used to create a virtual table in the DSV. This does not impact the underlying database.
- Most are used to merge the two or more tables in the data source view or filter columns of a table
20. How many types of dimensions are there and what are they?
- Three types of Dimensions:
- Confirm Dimension
- Junk Dimension
- Degenerate attribute
21. What are the types of database schema?
- Star
- Snowflake
- Starflake
22. What is a partition?
- Partition analysis services are the physical location of stored cube data.
- Every cube has at least one partition by default
- Partition is a powerful and flexible means of managing cubes, especially large cubes.
23. What are the different levels implemented in SSAS?
- Server Level
- Database Level
- Cube Level
- Dimension Level
- Measure Level
- Cell Level
24. What are the types of storage modes?
Three types of storage modes:
- MOLAP
- ROLAP
- HOLAP
25. Describe the Significance of SSAS?
The benefits of SSAS
- Convenient user with friendly interactive features
- Enables to create and manage a data warehouse.
- High offers and security principles for enhanced efficiency
- Provides speed analysis and troubleshooting option