Q1. - (Topic 1) 

You need to create the sales territory and product measures. 

Which aggregate function should you use for both measures? 

A. COUNT (DISTINCT column_name) 

B. Distinct Count 

C. Distinct 

D. Count 

View Answer

Q2. - (Topic 10) 

You are developing a SQL Server Analysis Services (SSAS) tabular project. 

A column named City must be added to the table named Customer. The column will be used in the definition of a hierarchy. The City column exists in the Geography table that is related to the Customer table. 

You need to add the City column to the Customer table. 

How should you write the calculation? 

A. City:=RELATED(Geography[City]) 

B. =RELATEDTABLE(Geography) 

C. =RELATED(Geography[City]) 

D. City:=Geography[City] 

E. City:=RELATEDTABLE(Geography) 

F. =Geography[City] 

View Answer

Q3. - (Topic 10) 

You are creating a new report in SQL Server Report Builder. You add a SQL Azure data 

source. Then you add a dataset that has four fields named Year, Country, Category, and Sales. 

You must design a matrix as shown in the following diagram. 

The category rows (the first three rows as shown in the diagram) must present total sales amount by category. The country rows (the next six rows as shown in the diagram) must present total sales amount by country. The total row must present the total sales for each year. 

You add a matrix to the report. You add a grouping of the Category field on the rows and a grouping of the Year field on the columns. 

You need to add the countries on the rows of the matrix. 

Which Row Group option should you select when you add the group? 

A. Adjacent Below 

B. Parent Group 

C. Adjacent Above 

D. Child Group 

View Answer

Q4. DRAG DROP - (Topic 7) 

You are creating the Australian states query. 

Which Multidimensional Expressions (MDX) calculation should you use to complete the 

query? 

To answer, drag the appropriate calculation to the answer area. 

View Answer

Q5. - (Topic 9) 

You are creating a product dimension table and a SQL Server Integration Services (SSIS) package that will load the table. 

You need to keep a history in the table of changes to the names of products. 

Which three actions should you perform? Each correct answer presents part of the solution. 

A. Add a Slowly Changing Dimension transformation to the package. 

B. Enable Change Data Capture for the table. 

C. Create an inferred member column in the table. 

D. From the Slowly Changing Dimension Wizard, set the name column to Type 3. 

E. Create an end date column in the table. 

F. Create a start date column in the table. 

View Answer

Q6. - (Topic 10) 

You are developing a multidimensional project that includes a dimension named Organization. The dimension is based on the DimOrganization table in the data warehouse. The following diagram illustrates the table design. 

The Organization dimension includes a parent-child hierarchy named Organizations. 

The dimension includes the following dimension attributes: 

Organization, which is a key attribute 

Organizations, which defines the parent-child hierarchy 

Currency Code, which is a regular attribute 

When users browse the dimension, three hierarchies are visible to them. 

You need to ensure that the Organization hierarchy is not visible to users. 

... 

What should you do? 

A. Set the AttributeHierarchyDisplayFolder property to Null for the Organization attribute. 

B. Delete the Organization attribute. 

C. Set the AttributeHierarchyEnabled property to False for the Organization attribute. 

D. Set the AttributeHierarchyVisible property to False for the Organization attribute. 

View Answer

Q7. - (Topic 6) 

You need to implement a strategy for efficiently storing sales order data in the data warehouse. 

What should you do? 

A. Separate the factOrders table into multiple tables, one for each month that has orders, and use a local partitioned view. 

B. Separate the factOrders table into multiple tables, one for each day that has orders, and use a local partitioned view. 

C. Create daily partitions in the factOrders table. 

D. Create monthly partitions in the factOrders table. 

View Answer

Q8. - (Topic 1) 

You need to configure the SSRS data source. 

What should you do? 

A. Store the credentials. 

B. In the data source configuration window, select the Prompt for credentials option. 

C. Create a .NET form to enable users to enter their credentials. 

D. Configure Kerberos authentication. 

View Answer

Q9. - (Topic 10) 

You are creating a SQL Server Analysis Services (SSAS) multidimensional database. 

Users need a time dimension for: 

Dates 

Delivery dates 

Ship dates 

... 

You need to implement the minimum number of required SSAS objects. 

What should you do? 

A. Use role playing dimensions. 

B. Use the Business Intelligence Wizard to define dimension intelligence. 

C. Add a measure that uses the Count aggregate function to an existing measure group. 

D. Add a measure that uses the DistinctCount aggregate function to an existing measure group. 

E. Add a measure that uses the LastNonEmpty aggregate function. Use a regular relationship between the time dimension and the measure group. 

F. Add a measure group that has one measure that uses the DistinctCount aggregate function. 

G. Add a calculated measure based on an expression that counts members filtered by the Exists and NonEmpty functions. 

H. Add a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension. 

I. Create several dimensions. Add each dimension to the cube. 

J. Create a dimension. Then add a cube dimension and link it several times to the measure group. 

K. Create a dimension. Create regular relationships between the cube dimension and the measure group. Configure the relationships to use different dimension attributes. 

L. Create a dimension with one attribute hierarchy. Set the XsAggregatable property to False and then set the DefaultMember property. Use a regular relationship between the dimension and measure group. 

M. Create a dimension with one attribute hierarchy. Set the IsAggregatable property to False and then set the DefaultMember property. Use a many-to-many relationship to link the dimension to the measure group. 

N. Create a dimension with one attribute hierarchy. Set the ValueColumn property, set the IsAggregatable property to False, and then set the DefaultMember property. Configure the cube dimension so that it does not have a relationship with the measure group. Add a calculated measure that uses the MemberValue attribute property. 

O. Create a new named calculation in the data source view to calculate a rolling sum. Add a measure that uses the Max aggregate function based on the named calculation. 

View Answer

Q10. - (Topic 10) 

You maintain a multidimensional Business Intelligence Semantic Model (BISM) that was developed with default settings. 

The model has one cube and the cube has one measure group. The measure group is based on a very large fact table and is partitioned by month. The fact table is incrementally loaded each day with approximately 800,000 new rows. 

You need to ensure that all rows are available in the cube while minimizing the processing time. 

Which processing option should you use? 

A. Process Add 

B. Process Clear 

C. Process Default 

D. Process Index 

E. Process Data 

F. Process Full 

View Answer