SSAS Interview Questions
SSAS is 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 multidimensional database called a cube. Here I have listed the top 25 SSAS Interview questions. Top 25 SSAS interview questions include basic and important questions for experienced and fresher.
1. What is SQL Server Analysis Services (SSAS)?
- SQL (SSAS) is the online analytical processing component of SQL Server.
- SSAS allows to build Multidimensional structures called cubes
- SSAS comes with bundled SQL server and get you choose install this component part of the SQL server installation.
2. What language are used in SSAS?
- Structured Query Language (SQL)
- Multidimensional Expressions (MDX):
- It is an industry standard query language orientated towards 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. In this service 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 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 it is called as 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 is the only component in SQL server
- Faster Analysis and troubleshooting.
6. What is a Data Source?
- Data source contains the connection information used by SSAS
- Data source is primarily contains the following information (Apart from various other properties like Query, timeout, isolation, etc.)
- In this name given to the connection setup to database from a server.
- Server name
- Database name
- Impersonation information
7. What are the different data sources supported by SSAS?
- SQL Server
- MS Access
- IBM DB2
8. What is 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 possible without hindering operational system.
9. Explain the flow of creating a cube?
- Create a data source
- Create a data source view
- Create Dimensions
- Create a cube
- Deploy and process cube
10. What is Analysis Services?
- Analysis server is the middle tier for analytical processing, OLAP, Data mining.
- Analysis services assist in creating, managing, designing multidimensional structures containing data from varied sources.
- 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, and 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 is 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 analyse an organization’s raw data.
- In this discipline mode up of several related activities, data mining, querying and reporting, online analytical process.
16. What are the 8 major fact tables in IB?
8 Major Facts
- Client account
- Prop Desk
- Deal pipeline
17. What are the pros and cons of using Tables and Named Queries in DSV?
|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 on 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 fact table?
- Fact table contains the basic information that wish to summarize.
- The table stores detailed value for measure is called a Fact table.
- It is defined as Table which contains METRICS” that are Used to analyse the business.
19. What is named query?
- Named query is similar to view in the Database.
- It is used to create a virtual table in the DSV. This does not impact underlying database.
- Most 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?
22. What is a partition?
- Partition analysis services is 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 types of storage modes?
Three types of storage modes:
25. Describe the Significance of SSAS?
The benefits of SSAS
- Convenient user with friendly interactive features
- Enables to create and manage of data warehouse.
- High offers and security principles for enhanced efficiency
- Provides speed analysis and troubleshooting option