You design a SQL Server 2008 Analysis Services (SSAS) solution. Your solution has a date dimension named Date and measures named Sales Amount and Total Product Cost.
You want to create a calculated measure named Profit. You also want to calculate the differences between the first half and second half of the year for all the measures. You run the following Multidimensional Expressions (MDX) query:
WITH
MEMBER [Measures].[Profit] AS
([Measures].[Sales Amount] – [Measures].[Total Product Cost])/[Measures].[Sales Amount], Format_String = “Percent”
MEMBER [Date].[Fiscal Semester of Year].[Half Year Difference] AS [Date].[Fiscal Semester of Year].[FY H2] – [Date].[Fiscal Semester of Year].[FY H1]
SELECT
{ [Measures].[Sales Amount], [Measures].[Total Product Cost], [Measures].[Profit] } ON COLUMNS, { [Date].[Fiscal Semester of Year].[FY H1], [Date].[Fiscal Semester of Year].[FY H2], [Date].[Fiscal Semester of Year].[Half Year Difference] } ON ROWS
FROM [Adventure Works]
The Profit calculated measure calculates an incorrect value as shown in the exhibit. (Click the Exhibit button.)

You need to ensure that the MDX query calculates the correct value. Which code segment should you use to replace the WITH clause in the MDX query?

A.
WITH
MEMBER [Measures].[Profit] AS
([Measures].[Sales Amount] – [Measures].[Total Product Cost])/[Measures].[Sales Amount], Format_String = “Percent”, SOLVE_ORDER = 1
MEMBER [Date].[Fiscal Semester of Year].[Half Year Difference] AS [Date].[Fiscal Semester of Year].[FY H2] – [Date].[Fiscal Semester of Year].[FY H1], SOLVE_ORDER = 2

B.
WITH
MEMBER [Measures].[Profit] AS
([Measures].[Sales Amount] – [Measures].[Total Product Cost])/[Measures].[Sales Amount], Format_String = “Percent”, SOLVE_ORDER = 2
MEMBER [Date].[Fiscal Semester of Year].[Half Year Difference] AS [Date].[Fiscal Semester of Year].[FY H2] – [Date].[Fiscal Semester of Year].[FY H1], SOLVE_ORDER = 1

C.
WITH
MEMBER [Measures].[Profit] AS
([Measures].[Sales Amount] – [Measures].[Total Product Cost])/[Measures].[Sales Amount], Format_String = “Percent”, SOLVE_ORDER = 1
MEMBER [Date].[Fiscal Semester of Year].[Half Year Difference] AS [Date].[Fiscal Semester of Year].[FY H2] – [Date].[Fiscal Semester of Year].[FY H1], SOLVE_ORDER = 2, SCOPE_ISOLATION = CUBE

D.
WITH
MEMBER [Measures].[Profit] AS
([Measures].[Sales Amount] – [Measures].[Total Product Cost])/[Measures].[Sales Amount], Format_String = “Percent”, SOLVE_ORDER = 1
MEMBER [Date].[Fiscal Semester of Year].[Half Year Difference] AS [Date].[Fiscal Semester of Year].[FY H2] – [Date].[Fiscal Semester of Year].[FY H1], SCOPE_ISOLATION = CUBE

Explanation:
Tip: “Multidimensional Expressions … replace the WITH clause” = “SOLVE_ORDER = 1 … SOLVE_ORDER = 2” (sem SCOPE_ISOLATION)

http://msdn.microsoft.com/en-us/library/ms145539.aspx
Understanding Pass Order and Solve Order (MDX)
When a cube is calculated as the result of an MDX script, it can go through many stages of computation depending on the use of various calculation-related features. Each of these stages is referred to as a calculation pass.
A calculation pass can be referred to by an ordinal position, called the calculation pass number. The count of calculation passes that are required to fully compute all the cells of a cube is referred to as the calculation pass depth of the cube.
Fact table and writeback data only impact pass 0. Scripts populate data after pass 0; each assignment and calculate statement in a script creates a new pass. Outside the MDX script, references to absolute pass 0 refer to the last pass created by the script for the cube.
Calculated members are created at all passes, but the expression is applied at the current pass. Prior passes contain the calculated measure, but with a null value.
Solve Order
Solve order determines the priority of calculation in the event of competing expressions. Within a single pass, solve order determines two things:
* The order in which Microsoft SQL Server Analysis Services evaluates dimensions, members, calculated members, custom rollups, and calculated cells.
* The order in which Analysis Services calculates custom members, calculated members, custom rollup, and calculated cells.
The member with the highest solve order takes precedence.
Note: The exception to this precedence is the Aggregate function. Calculated members with the Aggregate function have a lower solve order than any intersecting calculated measure.
Solve Order Values and Precedence

Understanding SOLVe_OrDer
When you create calculated members on both the row and column axes, and one depends on the other, you need to tell MDX in what order to perform the calculations. In our experience,
its quite easy to get this wrong, so we caution you to verify the results of your SOLVE_ORDER keyword.