Online Access Free 070-768 Practice Test

You are designing a SQL Server Analysis Services (SSAS) cube.You need to create a measure to count unique customers.What should you do?

• A.Add a measure that uses the LastNonEmpty aggregate function. Use a regular relationship between the time dimension and the measure group.
• B.Create a dimension. Then add a cube dimension and link it several times to the measure group.
• C.Create several dimensions. Add each dimension to the cube.
• D.Add a hidden measure that uses the Sum aggregate function. Add a calculated measure aggregating the measure along the time dimension.

How do you support the profit percentage in your solution design?

• A.Create a calculated measure to divide profit by revenue.
• B.Add a fact table column and set the FormatString for the new measure to Percent.
• C.Add a new measure group to the cube to isolate the measure.
• D.Define the MeasureExpression property on a new measure to divide profit by revenue.

You are optimizing a Microsoft SQL Server Analysis Services (SSAS) multidimensional model over a SQL Server database.
You have a table named City which has several dimensions that do not contain a space in their names.
One dimension is named SalesTerritory rather than Sales Territory.
You need to ensure that Report developers can drag the attribute name to the report rather than having to re-label the attributes by implementing spaces.
You must minimize administrative effort and not break any upstream processes.
What should you do?

• A.In the SQL Server database, run the system procedure sp_rename to rename the columns in the base tables with the target name.
• B.In SQL Server Management Studio, navigate to the City table, expand the columns, press F2, and rename the columns in the base tables.
• C.In the SQL Server database, implement a view over the City table that aliases the columns in the tables.
• D.In the SQL Server database, implement a SYNONYM.

You are developing a BI Semantic Model (BISM) that retrieves data from several sources including a SQL Azure database and an OData data feed.
The model will be deployed to a server with significantly more memory than the total size of the source data.
You have the data feed URL, which you will use when developing the model in SQL Server Data Tools (SSDT).
The model must meet the following requirements:
- Maximize performance
- Data latency of up to one month is acceptable
You need to choose a project type and a data access mode to meet the requirements.
What should you do?

• A.Select the multidimensional project type and use the ROLAP storage mode.
• B.Select the tabular project type and use the In-Memory query mode.
• C.Select the tabular project type and use the DirectQuery query mode.
• D.Select the multidimensional project type and use the MOLAP storage mode.

Hotspot Question
A database named DB2 uses the InMemory query mode. Users frequently run the following query:

You need to configure SQL Server Profiler to determine why the query is performing poorly.
Which three event should you monitor on the SQL Server Profiler trace events configuration page? To answer, select the appropriate options in the answer area.

Correct Answer:

Explanation:
By using SQL Profiler, you can intercept two classes of trace events from Analysis Services, DAX Query Plan and DirectQuery events, both generated by the DirectQuery engine. Here, in this scenario we have a DAX Query.
DAX Query Plan events are generated by the DAX formula. By using the In-Memory mode, you store a copy of data in the xVelocity (VertiPaq) storage engine.
Figure: This is how a query is executed by using In-Memory mode.