Infosys SSAS (SQL Server Analysis Services) technical interview questions with answers that will help you prepare. These cover beginner, intermediate, and advanced levels, as Infosys usually tests both conceptual clarity and hands-on knowledge.
Basic Level
1. What is SSAS and why is it used?
SSAS (SQL Server Analysis Services) is a Microsoft tool used for Online Analytical Processing (OLAP) and data mining. It allows users to analyze large volumes of data efficiently by creating cubes, dimensions, and measures for fast reporting and business intelligence.
2. What are cubes in SSAS?
A cube is a multidimensional data structure that stores aggregated measures along different dimensions (e.g., sales by time, product, region). It enables faster querying compared to relational databases.
3. What are dimensions and facts in SSAS?
-
Facts: Numeric data (like sales, revenue, quantity).
-
Dimensions: Contextual data (like time, geography, product) used to analyze facts.
4. What is the difference between MOLAP, ROLAP, and HOLAP?
-
MOLAP: Data stored in a multidimensional format for fast performance.
-
ROLAP: Data stored in relational tables; slower but real-time.
-
HOLAP: Hybrid approach; detailed data in relational tables, aggregates in multidimensional format.
Intermediate Level
5. What is a measure group in SSAS?
A measure group is a collection of related measures (facts) that share the same dimensionality, usually based on a fact table in the data warehouse.
6. What is the role of aggregations in SSAS?
Aggregations pre-calculate summary data (like totals, averages) at different levels, improving query performance by avoiding on-the-fly computation.
7. What are KPIs in SSAS?
Key Performance Indicators (KPIs) measure performance against targets. For example, “Sales Growth vs Target Growth.” KPIs consist of value, goal, status, and trend.
8. What are partitions in SSAS cubes?
Partitions divide cube data into smaller, manageable chunks (e.g., by year or region). This improves query performance and processing efficiency.
9. What is a perspective in SSAS?
A perspective is a customized view of a cube designed for specific business users, showing only relevant dimensions and measures.
10. What is a calculated member in SSAS?
A calculated member is a new measure created using MDX expressions. Example: [Measures].[Profit] = [Measures].[Sales] - [Measures].[Cost].
Advanced Level
11. Explain MDX and its usage in SSAS.
MDX (Multidimensional Expressions) is a query language used in SSAS to query cubes. Example:
SELECT {[Measures].[Sales]} ON COLUMNS,
{[Date].[Year].[2025]} ON ROWS
FROM [SalesCube]
This retrieves sales for the year 2025.
12. What is the difference between Tabular and Multidimensional models in SSAS?
-
Multidimensional Model: Uses OLAP cubes, dimensions, and MDX. Suited for complex, enterprise-level analytics.
-
Tabular Model: Uses relational tables, in-memory storage, and DAX queries. Faster and simpler for self-service BI.
13. How do you handle many-to-many relationships in SSAS?
By creating an intermediate measure group (bridge table) that connects the fact table with the related dimension.
14. What are Actions in SSAS?
Actions allow users to perform additional operations in a cube, like drilling through data or linking to reports/web pages.
15. How do you optimize cube performance in SSAS?
-
Designing proper aggregations.
-
Using partitions.
-
Choosing correct storage modes (MOLAP, ROLAP, HOLAP).
-
Optimizing dimension hierarchies.
-
Using caching effectively.