Q1. - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) tabular project. A model defines a measure named Revenue and includes a table named Date. The table includes year, semester, quarter, month, and date columns. The Date column is of data type Date. The table contains a set of contiguous dates.
You need to create a measure to report on year-over-year growth of revenue.
What should you do? (Each answer presents a complete solution. Choose all that apply.)
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C,D
Q2. - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) cube for the sales department at your company.
The sales department requires the following set of metrics:
. Unique count of customers
. Unique count of products sold . Sum of sales
You need to ensure that the cube meets the requirements while optimizing query response time.
What should you do? (Each answer presents a complete solution. Choose all that apply.)
A. Place the measures in a single measure group.
B. Place the distinct count measures in separate measure groups.
C. Use the additive measure group functions.
D. Use the semiadditive measure group functions.
E. Use the Count and Sum measure aggregation functions.
F. Use the Distinct Count and Sum measure aggregation functions.
Answer: B,F
Q3. - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) tabular project.
In the data warehouse, a table named Sales Persons and Territories defines a relationship between a salesperson's name, logon ID, and assigned sales territory.
You need to ensure that each salesperson has access to data from only the sales territory assigned to that salesperson. You need to use the least amount of development effort to achieve this goal.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)
A. Create a new role named Sales Persons with Read permission. Add each salesperson's logon as a member to the role.
B. Add the Sales Persons and Territories table to the model, define the relationships, and then implement dynamic security by using row filters. Grant each salesperson access to the model.
C. Create a new Active Directory Domain Services (AD DS) security group and add each salesperson as a member. Then create a new role named Sales Persons with Read permission. Add the group as a member to the new role.
D. Create a separate tabular model for each sales territory and assign each tabular model a corresponding sales territory name. Grant each salesperson access to the corresponding tabular model of the assigned sales territory.
Answer: B
Q4. - (Topic 4)
You are developing a tabular Business Intelligence Semantic Model (BISMJ database based on a SQL Server database.
In the data source, the FactlnternetSales table is partitioned by month. Data from the current month has been updated and new data has been inserted in the FactlnternetSales table, in the DimProduct table, and in the DimCustomer table.
In the model, the FactlnternetSales table is also partitioned by month.
You need to ensure that the model has the most recent data while minimizing the processing time.
What should you do?
A. Process the latest FactlnternetSales model table partition, the DimProduct table, and the DimCustomer table with the Process Clear processing option. Then process the database with the Process Data processing option.
B. Process the latest FactlnternetSales model table partition, the DimProduct table, and the DimCustomer table with the Process Clear processing option. Then process the database with the Process Full processing option.
C. Process the latest FactlnternetSales model table partition, the DimProduct table, and the DimCustomer table with the Process Defrag processing option. Then process the database with the Process Recalc processing option.
D. Process the latest FactlnternetSales model table partition, the DimProduct table, and the DimCustomer table with the Process Data processing option. Then process the database with the Process Defrag processing option.
E. Process the latest FactlnternetSales model table partition, the DimProduct table, and the DimCustomer table with the Process Data processing option. Then process the database with the Process Recalc processing option.
Answer: D
Q5. - (Topic 4)
You are developing a BI Semantic Model (BISM) that will be used to analyze complex budgeting and forecast data sourced from a financial database. The model will be deployed to a server with 28 GB of RAM.
The source data, located in a SQL Server data warehouse, is currently using 15 terabytes of disk space and is doubling in size every month. The model will be queried by staff in the accounting department by using Microsoft Excel 2010.
You need to ensure the highest query performance and scalability for the accounting department queries.
Which project type should you choose?
A. Tabular project that uses the In-Memory query mode
B. PowerPivot workbook deployed to SharePoint
C. Tabular project that uses the DirectQuery query mode
D. Multidimensional project
Answer: D
Q6. - (Topic 4)
You are developing a SQL Server Reporting Services (SSRS) report. The report includes a dataset with fields named.
Year MonthNumber, and RegCount. The report includes a table that displays the number of recorded registration occurrences per year,as shown in the following table.
You need to modify the table to include a graphical item displaying the monthly registration trend to the right of the Reg Count column.
What should you do?
A. Add an Indicator item to a new column on the right of the Reg Count column. Select the Directional Indicator Type and then assign the MonthNumber field to the Start property.
B. Add an Indicator item to a new column on the right of the Reg Count column. Select the Directional Indicator Type and then select the MonthNumber field for Value.
C. Add a Sparkline item to a new column on the right of the Reg Count column. Then select the RegCount field for Values and the MonthNumber field for Series Groups.
D. Add a text box to a new column on the right of the Reg Count column. Then use a Go to report action to link to a separate report showing the monthly trend.
E. Add a Sparkline item to a new column on the right of the Reg Count column. Then select the RegCount field for Values and the MonthNumber field for Category Groups.
Answer: E
Q7. HOTSPOT - (Topic 4)
You are developing a SQL Server Analysis Services (SSAS) cube for the accounts department.
You create a measure group named Exchange Rate that consists of measures pertaining to currency exchange rates. One of the measures in this group is named Average Rate and it will be used to report the average currency exchange rate over time.
Currently the AggregationFunction property for the Average Rate measure is set to Sum.
You need to ensure that Average Rate measure reports the average of the currency exchange rate over time.
Which value should you select for the AggregationFunction property for the Average Rate measure? To answer, select the appropriate setting in the answer area.
Answer:
Q8. - (Topic 4)
You install SQL Server Reporting Services (SSRS). You need to restore a copy of the symmetric key. Which command should you run?
A. rskeymgmt -d
B. rskeymgmt -e -f %temp%\rs.key -p Password1
C. rskeymgmt -i
D. rskeymgmt -a -f %temp%\rs.key -p Password1
Answer: D
Q9. DRAG DROP - (Topic 4)
You are developing a SQL Server PowerPivot model to monitor the rate of production of defective products.
The PowerPivot model contains many tables. The FactProduction table is configured as shown in the following diagram.
The model requires a key performance indicator (KPI) named Defective Units to measure the rate of production of defective units. The rate of production of defective units is calculated by dividing the sum of the Defects column by the sum of the Units column.
The KPI has the following requirements.
. If the rate of production of defective products equals or exceeds 5% then the KPI must report a status of off target (red). . If the rate of production of defective products is less than 5% and equals or exceeds 2% then the KPI must report a status of slightly off target (yellow). . If the rate of production of defective products less than 2% then the KPI must report a status of on target (green).
You need to develop the KPI to meet the requirements.
Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Answer:
Q10. - (Topic 4)
You have a SQL Server Analysis Services (SSAS) database named DB1 on a server named Server1.
You need to deploy DB1 from Server1 to four other servers. If the destination server already contains a copy of DB1, the database must be updated only. If the destination server does not contain a copy of DB1, the database must be copied to the destination server.
What is the best deployment option to use? More than one answer choice may achieve the goal. Select the BEST answer.
A. Analysis Services Deployment Wizard
B. Analysis Management Objects (AMO) automation
C. Backup and Restore
D. Synchronize Database Wizard
Answer: C