SSAS Interview Questions

1. What you should know about MDX? [Beginner]

Q. How is the syntax for MDX different and alike to T-SQL?
A. http://www.mosha.com/msolap/articles/MDXForEveryone.htm

Q. Define a Dimension, Hierarchy, Level, Member, a Cube, a Cell, a Tuple, and a Set?
A. A Dimension is an object in an OLAP Database that contains elements that can be used to define how we look at OLAP data. For example, in a typical OLAP Database you would have a Date Dimension and we might want to view the data my Years, Months or Periods, Weeks, or Days. We have a Hierarchy when there are elements within a Dimension that relate to one another in a typical, one to many, relationship. An example is sown by a year having 12 months, a month having 28, 29, 30, or 31 days depending on the month. The heirarchical relationship is Year -> Month -> Day in this example. Each layer of the hierarchy is a Level. In the above example, we have a root Level (Year) and a leaf Level (Day), and an intermediate Level (Month). A member is a single element within the Dimension. June would be a member of the Month Level within Year-Month-Day Hierarchy of a Date Dimension. To understand a Tuple, you must know what a Cube and a Cell is. A Cube is a many sided (based on the number of dimensions associated to it) object that houses the aggregate and non-aggregate data in individual Cells that are the intersection of the many sides of the Cube. A Cell is simply the intersection of the many sides of a Cube for any given Member of each Dimension. The Tuple is the representation of each Dimension Member that defines a Cell within a Cube. Tuples are syntactically represented by Parentheses. A Set is one or more Tuples that define the cells for a given query result. Typically, a query will result in two dimensional axes (Columns and Rows). Each axis (Column and/or Row) must have a Set defined for the axis. Sets are syntactically represented by Curly Braces.

Q. What the difference between flexible and rigid attribute relationship in SSAS Dimensions. How does this relationship impact aggregation design and performance?
A. A Rigid relationship indicates that the dimension data does not change. A Flexible relationship (default setting) allows for changes in the dimension data. Flexible relationships force Analysis Services to drop and re-compute any existing aggregations during incremental dimension processing.Rigid relationships do not require the re-computing of existing aggregations during incremental processing and thereby reduce total processing time. Hierarchies with rigid relationships can also be queried faster than those with flexible relationships. The flip side of the coin is that changing any dependent attribute that has a rigid relationship within any hierarchy requires full process of the entire dimension.

Q. In MDX, what is the difference between the IF and IIF statements?
A. The IF statment checks for a condition THEN if true performs some operation then END IF. The IIF statement checks for a condition if true (,) performs an operation, else if false (,) performs another operation. Syntax for each follows:
IF [Measures].CurrentMember IS [Measures].[Sales Amt] THEN [Measures].[Sales Amt] * .10 END IF
IIF([Measures].[Sales Amt] > 1000, "Sales are Good.","Sales are not Good")

Q. When setting up partitions, what must you do to make sure you don't load duplicate data into a partition?
A. You must define a Where Clause in the Partition Configuration.

Q. What is the first statement in a Cube's MDX Script?
A. The CALCULATE statement.

Q. How do you create SubCube space in a Cube?
A. By using the SCOPE statement within the MDX Script.

Q. What is the best practice for keeping your cubes sparse?
A. Don't populate null values with 0.

Q. How does SSAS handle null values in terms of math operations?
A. Null * 2 = Null, Null + 1 = 1

Q. How do you define the Cube Space in SSAS 2005?
A. The cube space is defined by the Attriute Hierarchies in the many dimensions of the cube. More specifically, it is the product of all attribute hierarchies in a cube. User Hierarchies do not impact the cube space and attributes without attribute hierarchies exist outside the cube space.

Q. How can dimension attributes that do not have an attribute hierarchy be used in a cube?
A. They can be used as a Member Property within the dimension.