SSAS interview questions

Question

For interview purpose what questions can we expect from SSAS prespective.

a) Entry/Beginners level (1-6 months)

b) Intermediate

c) Advance

Thanks

Answer 1

Here are some general approaches I use for interviewing different groups of SSAS programmers:

Test Knowledge of BIDS for Developing Cubes

1. Ask the candidate to explain all the steps they need to complete in BIDS to create and publish a cube from scratch. For simplicity sake, I usually ask them to assume they have a Kimball method data warehouse on one SQL Server that has 2 fact tables and 5 dimension tables.

Most candidates who claim to have SSAS experience can explain the life cycle of building a cube, but rarely can they actually explain the steps to build a cube correctly. Experienced users should talk about setting up the databse connection, creating a DSV, generating a cube, generating dimension tables or modifying dimension tables created by the cube, defining attribute relationships for dimensions, defining relationships in the cube between fact and dimension tables, deploying the cube, etc. Candidates should know the terminology inside and out.

1. If the candidate describes the top-line process for building cubes in BIDS, then drill into details about the DSV. What are named queries? What are advantages and disadvantages of named queries? Should you link directly to tables, views, or named queries? Do views have any advantages over direct links to tables?

2. Ask the candidate to describe in detail how they would add a new attribute to a dimension. Assume for simplicity sake that someone has already added the column to the underlying database table and you now need to adjust the cube definition and deploy the changes.

3. Ask the candidate how cubes are maintained from day to day. Ask about the differences between fully processing cubes and dimensions versus partially processing cubes. Ask about what happens if a customer cancels an order and how that should propagate through the data warehouse. See if the candidate talks about ledger-style transactions versus status changes and how this impacts processing the fact table. Ask about how partitions are used, how they are defined, when you should use them, and when you shouldn't use them.

4. Ask detailed questions about advantages and disadvantages of date dimensions, time dimensions, how the dimensions should be maintained to handle new dates, etc. The candidate should explain an automated method for maintaining dates except for holidays.

5. Ask how changes to the cube are tested before publishing the changes to end users. I once interviewed a candidate who answered most of the technical questions about how to build a cube in BIDS correctly, but then couldn't explain to me how to test the cube. The candidate simply said he would publish the changes and then his manager would take care of everything. When I asked how he would test drill through actions, slicing behavior, etc., it became clear that the "architect" had no idea how any of this actually worked.

6. Ask how the candidate troubleshoots performance issues. Good answers should talk about SQL Profiler, testing MDX queries directly in Management Studio, monitoring key perfmon statistics, redefining attribute relationships and cube relationships, loading data into cleansed tables instead of using raw source tables, isolating analysis services performance from other application or sql server services, etc.

Test Knowledge of MDX

1. Ask the candidate some basic MDX questions. Ask questions like "I have a cube called new_cube and it has a products dimension and a orders fact table. Tell me roughly how you would filter this down to 3 orders." If the candidate can only explain how to do it in a GUI such as int Excel or SSRS, then ask some deeper questions about returning nulls, returning all records regardless of nulls, or returning non-null values.

2. Ask the candidate about when they actually code MDX versus just use a GUI. Ask about which tools the candidate used to interact with the data. If it is Excel, then ask if they have used the olap extensions or data mining extensions. Ask what they can see in SQL Server Mangement Studio. If it is Excel, then ask how they handled refreshing data between months without having to change parameters. If it is SSRS, then ask how they handled multivalue parameters or changing dates for subscriptions. If they did most of their work in Management Studio, then ask questions about syntax and different methods for limiting data to a subset of users, orders, or dates.

Test Knowledge of Data Warehousing Design Principals

1. Ask questions about Kimball method data warehouses, star schemas, snowflake schemas, degenerate dimensions, data dimensions, time dimensions, surrogate keys, etc.

2. Ask questions about SQL Server database design pricipals such as the differences between indexes, non-clustered indexes, clustered indexes, composite indexes, CTEs, table-value functions, looping over data, fizban test, creating and managing SQL Server Agent Jobs and schedules, how to troubleshoot slow-performing queries, etc. An excellent SSAS architect should be an expert SQL DBA from a data warehousing perspective. Don't ask questions about replication, log shipping, mirroring, clustering, etc., since this is usually outside of the pervue of data warehousing SQL DBAs.

3. Ask questions about SSIS. An excellent SSAS architect must understand how to build complex SSIS packages including importing a filtered list of changing files from a directory, pulling data in via data flows, explain how to use fast load options for bulk inserts, talk about script components as sources or transformations, etc.

At the end of all of this, you should be able to determine if the user is a SSAS architect, a wannabe SSAS architect who has lots of SQL DBA data warehousing architecture experience, a SSAS report writer in Excel, SSRS, or other BI platform, a report writer who doesn't really understand what's happening under the covers, a newbie, or a faker. Keep in mind that a lot of really good data warehouse architects don't have much SSAS experience. If you are looking for an experienced SSAS architect, then they basically have to be able to do the entire Microsoft BI stack. Anyone else fits into some other category.