You design a Business Intelligence (BI) solution by using SQL Server 2008. You create a SQL Server 2008 Analysis Services (SSAS) solution by using SQL Server 2008. The solution contains a dimension named DimCustomer that represents customers. The solution provides a list of top 10 customers based on the sales amount. End users of the solution analyze data by using filters in Microsoft Excel worksheet. You need to ensure that the list is updated when the filters are applied.
Which named set expression should you use?
A.
CREATE SET CURRENTCUBE.[Top 10 Customer] AS
TOPCOUNT([DimCustomer].[Customer].MEMBERS,10,[Measures].[SalesAmount])
B.
CREATE DYNAMIC SET CURRENTCUBE.[Top 10 Customer] AS
TOPCOUNT([DimCustomer].[Customer].MEMBERS,10,[Measures].[SalesAmount])
C.
CREATE HIDDEN SET CURRENTCUBE.[Top 10 Customer] AS
TOPCOUNT([DimCustomer].[Customer].MEMBERS,10,[Measures].[SalesAmount])
D.
CREATE SESSION SET CURRENTCUBE.[Top 10 Customer] AS
TOPCOUNT([DimCustomer].[Customer].MEMBERS,10,[Measures].[SalesAmount])
Explanation:
Tip: "when the filters are applied" = "CREATE DYNAMIC"http://msdn.microsoft.com/en-us/library/ms145963.aspx
CREATE SET Statement (MDX)
CREATE [SESSION] [ STATIC | DYNAMIC ] [HIDDEN] SET
CURRENTCUBE | Cube_Name.Set_Name AS ‘Set_Expression’
[,Property_Name = Property_Value, …n]
Set Evaluation
Set evaluation can be defined to occur differently; it can be defined to occur only once at set creation or can be defined to occur every time the set is used.
– STATIC – Indicates that the set is evaluated only once at the time the CREATE SET statement is evaluated.
– DYNAMIC – Indicates that the set is to be evaluated every time it is used in a query.
Set Visibility – The set can be either visible or not to other users who query the cube.
– HIDDEN – Specifies that the set is not visible to users who query the cube.
Scope – A user-defined set can occur within one of the scopes listed in the following table.
– Query scope – The visibility and lifetime of the set is limited to the query. The set is defined in an individual query. Query scope overrides session scope. For more information, see Creating Query-Scoped Named Sets (MDX).
– Session scope – The visibility and lifetime of the set is limited to the session in which it is created. (The lifetime is less than the session duration if a DROP SET statement is issued on the set.) The CREATE SET statement creates a set with session scope. Use the WITH clause to create a set with query scope.