01. What property do you set for each measure to improve the legibility of values in a client application?
02. You are responsible for installing new database server instances. You must install Microsoft SQL Server Analysis Services (SSAS) to support deployment of the following projects.
You develop both projects by using SQL Server Data Tools.
- Project1 uses the tabular data model.
- Project2 uses SQL Server data mining to predict customer-purchasing intentions by using the Decision Trees algorithm.
You need to install the appropriate services to support both projects. Which two actions should you perform?
a) Install one tabular instance of SSAS and enable the Data Mining Extensions.
b) Install one multidimensional instance of SSAS.
c) Install one tabular instance of SSAS.
d) Install a multidimensional instance and a Power Pivot instance of SSAS on the same server.
e) Install two separate tabular instances of SSAS.
03. How can you enhance the model to support analysis by date?
a) Create calculated columns in the Internet Sales and Direct Sales tables to derive year, quarter, and month from the InvoiceDate columns.
b) Create a calculated table using the CALENDARAUTO function and add calculated columns to the calculated table to derive year, quarter, and month from the InvoiceDate columns.
c) Create a date table in a new SQL Server database, and import it into the model by using the Table Import Wizard.
d) Create a calculated column in the Internet Sales and Direct Sales tables using the CALENDAR function on the InvoiceDate column.
04. What property do you set to hide the phone number attribute in the customer dimension from slicing and dicing operations, but allows it to be included in the dimension as a member property?
05. You manage your department's SQL Server Analysis Services (SSAS) projects. The sales department uses a tabular model to analyze sales data. Users report excessive wait times when updating the model.
You need to improve the performance of the model. What should you do?
a) Define relationships.
b) Create a perspective.
c) Define a hierarchy.
d) Partition the table.
06. Which of the following object types do you add to the cube?
a) Calculated measure
b) Calculated member set
c) Static named set
d) Dynamic named set
07. How do you support the profit percentage in your solution design?
a) Add a fact table column and set the FormatString for the new measure to Percent.
b) Create a calculated measure to divide profit by revenue.
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.
08. What type of partitioning strategy should you implement to support the analysis of hourly Internet sales transactions?
a) Convert the model to DirectQuery. Add columnstore indexes to the source tables. In the model, define a sample partition for all tables to view data during development.
b) Set the model to DirectQuery With In-Memory Mode. Define a sample partition for the Internet Sales table to view data during development.
c) Create one partition per day dynamically by running an SSIS package daily to build the new partition. Load the data for the current day by performing a Process Full operation on the partition.
d) Create one partition per hour per day dynamically by running an SSIS package each hour to build the new partition. Load the data for the current hour by performing a Process Add, and then execute a Process Recalc on the partition
09. You manage an instance of SQL Server Analysis Services (SSAS). Over the last several months, the usage on the server increased by 50% as users begin to create and run more analyses. Queries are taking longer to run, and processor usage is consistently above 80%.
You predict that query usage will continue to increase at 5% per month. You need to scale SSAS to accommodate the present and future processing load.
Which three actions could you take?
Each correct answer presents a complete solution.
a) Create a second instance of SSAS on a different server and dedicate this instance to processing queries.
b) Create a new local SSAS partition.
c) Create a new Network Load Balanced (NLB) cluster.
d) Create a second instance of SSAS on the same server and dedicate this instance to processing queries.
e) Create a new remote SSAS partition.
10. Which is the easiest option for configuring and maintaining security for each salesperson?
a) Create a role for each salesperson with a row filter restricting the user to a specific sales territory in the Direct Sales table.
b) Create one role for the salesperson group with a row filter on the Direct Sales table that uses the CUSTOMDATA function.
c) Create a one role for the salesperson group with a row filter on the Direct Sales table that uses the CONTAINS and USERNAME functions to find the user’s sales territory in the Salesperson table.
d) Create a permissions table, add it to the tabular model, and define a relationship between it and the Direct Sales table.