Implementing Data Models and Reports with Microsoft SQL Server 2012 v1.0
Question 1
General Background -
You are the data architect for a company that uses SQL Server 2012 Enterprise Edition. You design data modeling and reporting solutions that are based on a sales data warehouse.
Background -
The solutions will be deployed on the following servers:
ServerA runs SQL Server Database Engine, ServerA is the data warehouse server.
ServerB runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, and SQL Server Integration Services (SSIS).
ServerC runs SSAS in tabular mode, SQL Server Reporting Services (SSRS) running in SharePoint mode, and Microsoft SharePoint 2010 Enterprise Edition with SP1.
The data warehouse schema currently contains the tables shown in the exhibit. (Click the Exhibit button.)
Business Requirements -
The reporting solution must address the requirements of the sales team, as follows:
Team members must be able to view standard reports from SharePoint.
Team members must be able to perform ad-hoc analysis by using Microsoft Power View and Excel.
Team members can have standard reports delivered to them on a schedule of their choosing.
The standard reports -
Will use a sales territory hierarchy for organizing data by region.
Will be accessible from SharePoint.
The Excel ad-hoc reports -
Will use the same data store as the standard reports.
Will provide direct access to the data store for the sales team and a simplified view for the executive team.
Technical Requirements -
The standard reports must be based on an SSAS cube. The schema of the data warehouse on ServerA must be able to support the ability to slice the fact data by the following dates:
Order date (OrderDateKey)
Due date (DueDateKey)
Ship date (ShipDateKey)
Additions and modifications to the data warehouse schema must adhere to star schema design principles to minimize maintenance and complexity
.
The multidimensional and tabular models will be based on the data warehouse. The tabular and multidimensional models will be created by using SQL Server
Data Tools (SSDT). The tabular project is named AdhocReports and the multidimensional project is named Standard Reports.
The cube design in the Standard Reports project must define two measures for the unique count of sales territories (SalesTerritoryKey) and products
(ProductKey).
A deployment script that can be executed from a command-line utility must be created to deploy the StandardReports project to ServerB.
The tabular model in the AdhocReports project must meet the following requirements:
A hierarchy must be created that consists of the SalesTerritoryCountry and SalesTerritoryRegion columns from the DimSalesTerritory table and the
EmployeeName column from the DimEmployee table.
A key performance indicator (KPI) must be created that compares the total quantity sold (OrderQuantity) to a threshold value of 1,000.
A measure must be created to calculate day-over-day (DOD) sales by region based on order date.
SSRS on ServerC must be configured to meet the following requirements:
It must use a single data source for the standard reports.
It must allow users to create their own standard report subscriptions.
The sales team members must be limited to only viewing and subscribing to reports in the Sales Reports library.
A week after the reporting solution was deployed to production, Marc, a salesperson, indicated that he has never received reports for which he created an SSRS subscription. In addition, Marc reports that he receives timeout errors when running some reports on demand.
You need to develop the multidimensional project to meet the requirements of the Excel users.
What should you do?
- A. Create a separate cube for the executive team so that it contains only the data they want to see.
- B. Create a perspective for the executive team.
- C. Create security roles to restrict access to the executive team.
- D. Create a view for the executive team.
Answer : B
Question 2
General Background -
You are the data architect for a company that uses SQL Server 2012 Enterprise Edition. You design data modeling and reporting solutions that are based on a sales data warehouse.
Background -
The solutions will be deployed on the following servers:
ServerA runs SQL Server Database Engine, ServerA is the data warehouse server.
ServerB runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, and SQL Server Integration Services (SSIS).
ServerC runs SSAS in tabular mode, SQL Server Reporting Services (SSRS) running in SharePoint mode, and Microsoft SharePoint 2010 Enterprise Edition with SP1.
The data warehouse schema currently contains the tables shown in the exhibit. (Click the Exhibit button.)
Business Requirements -
The reporting solution must address the requirements of the sales team, as follows:
Team members must be able to view standard reports from SharePoint.
Team members must be able to perform ad-hoc analysis by using Microsoft Power View and Excel.
Team members can have standard reports delivered to them on a schedule of their choosing.
The standard reports -
Will use a sales territory hierarchy for organizing data by region.
Will be accessible from SharePoint.
The Excel ad-hoc reports -
Will use the same data store as the standard reports.
Will provide direct access to the data store for the sales team and a simplified view for the executive team.
Technical Requirements -
The standard reports must be based on an SSAS cube. The schema of the data warehouse on ServerA must be able to support the ability to slice the fact data by the following dates:
Order date (OrderDateKey)
Due date (DueDateKey)
Ship date (ShipDateKey)
Additions and modifications to the data warehouse schema must adhere to star schema design principles to minimize maintenance and complexity
.
The multidimensional and tabular models will be based on the data warehouse. The tabular and multidimensional models will be created by using SQL Server
Data Tools (SSDT). The tabular project is named AdhocReports and the multidimensional project is named Standard Reports.
The cube design in the Standard Reports project must define two measures for the unique count of sales territories (SalesTerritoryKey) and products
(ProductKey).
A deployment script that can be executed from a command-line utility must be created to deploy the StandardReports project to ServerB.
The tabular model in the AdhocReports project must meet the following requirements:
A hierarchy must be created that consists of the SalesTerritoryCountry and SalesTerritoryRegion columns from the DimSalesTerritory table and the
EmployeeName column from the DimEmployee table.
A key performance indicator (KPI) must be created that compares the total quantity sold (OrderQuantity) to a threshold value of 1,000.
A measure must be created to calculate day-over-day (DOD) sales by region based on order date.
SSRS on ServerC must be configured to meet the following requirements:
It must use a single data source for the standard reports.
It must allow users to create their own standard report subscriptions.
The sales team members must be limited to only viewing and subscribing to reports in the Sales Reports library.
A week after the reporting solution was deployed to production, Marc, a salesperson, indicated that he has never received reports for which he created an SSRS subscription. In addition, Marc reports that he receives timeout errors when running some reports on demand.
You need to configure the permissions for the sales team members in the Sales Reports library.
Which permissions should you use? (Each answer presents part of the solution. Choose all that apply.)
- A. Delete Items
- B. Add Items
- C. View Items
- D. Create Alerts
- E. Manage Alerts
- F. Edit Items
Answer : CF
Question 3
General Background -
You are the data architect for a company that uses SQL Server 2012 Enterprise Edition. You design data modeling and reporting solutions that are based on a sales data warehouse.
Background -
The solutions will be deployed on the following servers:
ServerA runs SQL Server Database Engine, ServerA is the data warehouse server.
ServerB runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, and SQL Server Integration Services (SSIS).
ServerC runs SSAS in tabular mode, SQL Server Reporting Services (SSRS) running in SharePoint mode, and Microsoft SharePoint 2010 Enterprise Edition with SP1.
The data warehouse schema currently contains the tables shown in the exhibit. (Click the Exhibit button.)
Business Requirements -
The reporting solution must address the requirements of the sales team, as follows:
Team members must be able to view standard reports from SharePoint.
Team members must be able to perform ad-hoc analysis by using Microsoft Power View and Excel.
Team members can have standard reports delivered to them on a schedule of their choosing.
The standard reports -
Will use a sales territory hierarchy for organizing data by region.
Will be accessible from SharePoint.
The Excel ad-hoc reports -
Will use the same data store as the standard reports.
Will provide direct access to the data store for the sales team and a simplified view for the executive team.
Technical Requirements -
The standard reports must be based on an SSAS cube. The schema of the data warehouse on ServerA must be able to support the ability to slice the fact data by the following dates:
Order date (OrderDateKey)
Due date (DueDateKey)
Ship date (ShipDateKey)
Additions and modifications to the data warehouse schema must adhere to star schema design principles to minimize maintenance and complexity
.
The multidimensional and tabular models will be based on the data warehouse. The tabular and multidimensional models will be created by using SQL Server
Data Tools (SSDT). The tabular project is named AdhocReports and the multidimensional project is named Standard Reports.
The cube design in the Standard Reports project must define two measures for the unique count of sales territories (SalesTerritoryKey) and products
(ProductKey).
A deployment script that can be executed from a command-line utility must be created to deploy the StandardReports project to ServerB.
The tabular model in the AdhocReports project must meet the following requirements:
A hierarchy must be created that consists of the SalesTerritoryCountry and SalesTerritoryRegion columns from the DimSalesTerritory table and the
EmployeeName column from the DimEmployee table.
A key performance indicator (KPI) must be created that compares the total quantity sold (OrderQuantity) to a threshold value of 1,000.
A measure must be created to calculate day-over-day (DOD) sales by region based on order date.
SSRS on ServerC must be configured to meet the following requirements:
It must use a single data source for the standard reports.
It must allow users to create their own standard report subscriptions.
The sales team members must be limited to only viewing and subscribing to reports in the Sales Reports library.
A week after the reporting solution was deployed to production, Marc, a salesperson, indicated that he has never received reports for which he created an SSRS subscription. In addition, Marc reports that he receives timeout errors when running some reports on demand.
You need to configure the SSRS data source.
What should you do?
- A. Use Windows credentials.
- B. Prompt the user for credentials.
- C. In the data source configuration window, select the Credentials are not required option.
- D. Store the credentials.
Answer : A
Question 4
General Background -
You are the data architect for a company that uses SQL Server 2012 Enterprise Edition. You design data modeling and reporting solutions that are based on a sales data warehouse.
Background -
The solutions will be deployed on the following servers:
ServerA runs SQL Server Database Engine, ServerA is the data warehouse server.
ServerB runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, and SQL Server Integration Services (SSIS).
ServerC runs SSAS in tabular mode, SQL Server Reporting Services (SSRS) running in SharePoint mode, and Microsoft SharePoint 2010 Enterprise Edition with SP1.
The data warehouse schema currently contains the tables shown in the exhibit. (Click the Exhibit button.)
Business Requirements -
The reporting solution must address the requirements of the sales team, as follows:
Team members must be able to view standard reports from SharePoint.
Team members must be able to perform ad-hoc analysis by using Microsoft Power View and Excel.
Team members can have standard reports delivered to them on a schedule of their choosing.
The standard reports -
Will use a sales territory hierarchy for organizing data by region.
Will be accessible from SharePoint.
The Excel ad-hoc reports -
Will use the same data store as the standard reports.
Will provide direct access to the data store for the sales team and a simplified view for the executive team.
Technical Requirements -
The standard reports must be based on an SSAS cube. The schema of the data warehouse on ServerA must be able to support the ability to slice the fact data by the following dates:
Order date (OrderDateKey)
Due date (DueDateKey)
Ship date (ShipDateKey)
Additions and modifications to the data warehouse schema must adhere to star schema design principles to minimize maintenance and complexity
.
The multidimensional and tabular models will be based on the data warehouse. The tabular and multidimensional models will be created by using SQL Server
Data Tools (SSDT). The tabular project is named AdhocReports and the multidimensional project is named Standard Reports.
The cube design in the Standard Reports project must define two measures for the unique count of sales territories (SalesTerritoryKey) and products
(ProductKey).
A deployment script that can be executed from a command-line utility must be created to deploy the StandardReports project to ServerB.
The tabular model in the AdhocReports project must meet the following requirements:
A hierarchy must be created that consists of the SalesTerritoryCountry and SalesTerritoryRegion columns from the DimSalesTerritory table and the
EmployeeName column from the DimEmployee table.
A key performance indicator (KPI) must be created that compares the total quantity sold (OrderQuantity) to a threshold value of 1,000.
A measure must be created to calculate day-over-day (DOD) sales by region based on order date.
SSRS on ServerC must be configured to meet the following requirements:
It must use a single data source for the standard reports.
It must allow users to create their own standard report subscriptions.
The sales team members must be limited to only viewing and subscribing to reports in the Sales Reports library.
A week after the reporting solution was deployed to production, Marc, a salesperson, indicated that he has never received reports for which he created an SSRS subscription. In addition, Marc reports that he receives timeout errors when running some reports on demand.
You need to deploy the StandardReports project.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
- A. Use the Analysis Services Deployment utility to create an XMLA deployment script.
- B. Deploy the project from SQL Server Data Tools (SSDT).
- C. Use the Analysis Services Deployment wizard to create an XMLA deployment script.
- D. Use the Analysis Services Deployment wizard to create an MDX deployment script.
Answer : BC
Mix Questions -
Question 5
You are creating a table named Orders. You need to ensure that every time a new row is added to the Orders table, a user-defined function is called to validate the row before the row is added to the table.
What should you use? More than one answer choice may achieve the goal. Select the BEST answer.
- A. A FOREIGN KEY constraint
- B. A data manipulation language (DML) trigger
- C. A DEFAULT constraint
- D. A CHECK constraint
- E. A Data Definition Language (DDL) trigger
Answer : B
Explanation:
DML triggers is a special type of stored procedure that automatically takes effect when a data manipulation language (DML) event takes place that affects the table or view defined in the trigger. DML events include INSERT, UPDATE, or DELETE statements. DML triggers can be used to enforce business rules and data integrity, query other tables, and include complex Transact-SQL statements.
Incorrect Answers:
D: CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns. You can create a CHECK constraint with any logical (Boolean) expression that returns TRUE or FALSE based on the logical operators.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/triggers/dml-triggers
Question 6
You are developing a SQL Server Analysis Services (SSAS) tabular project. The model includes a table named Sales. The Sales table includes a single date column.
The Sales table must meet the following requirements:
-> Queries must be able to return all rows.
-> Must be able to support four different processing schedules for different date ranges.
Date ranges must not include any overlapping data.
You need to implement a solution that meets the requirements.
What should you do?
- A. Create four partitions for the Sales table. Create four roles. Use the same row filter queries for each role and partition.
- B. Convert the Sales table into four smaller tables by using row filter queries. Use one perspective for all four tables.
- C. Create four partitions for the Sales table. Use row filter queries for each partition.
- D. Convert the Sales table into four smaller tables by using row filter queries. Use one perspective for each of the four tables.
Answer : C
Question 7
You are administrating a SQL Server Analysis Services (SSAS) tabular database.
You need to create a new role that allows its members to query data and to refresh data in the model.
Which permission should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)
- A. Browse and Manage
- B. Administrator
- C. Read and Process
- D. Explore and Manage
Answer : C
Explanation:
* Giving a database role permission to process an Analysis Services database means that the role has permission to perform all processing options on the database. This includes the processing of all cubes, dimensions, mining structures, and mining models in the database. However, the role does not have permission to read database metadata or access any data in the database itself.
Question 8
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:= LOOKUP(Geography[City],Geography[GeographyKey],[GeographyKey])
- B. City:= LOOKUPVALUE(Geography[City],Geography[GeographyKey],[GeographyKey])
- C. =RELATED(Geography[City])
- D. =RELATED(Geography.City)
- E. =VALUES(Geography[City])
- F. City:=VALUES(Geography[City])
Answer : C
Explanation:
* RELATED Function
Returns a related value from another table.
Question 9
You are managing a SQL Server Analysis Services (SSAS) tabular database.
The database must meet the following requirements:
-> The processing must load data into partitions or tables.
-> The processing must not rebuild hierarchies or relationships.
-> The processing must not recalculate calculated columns.
You need to implement a processing strategy for the database to meet the requirements.
Which processing mode should you use?
- A. Process Clear
- B. Process Data
- C. Process Add
- D. Process Full
- E. Process Default
Answer : C
Explanation:
Process Add: This processing mode is available for Dimensions, Measure Groups and Partitions. When this processing mode is applied to Dimensions, it adds new members into the dimensions from the underlying database and updates the descriptions of the attributes. When this processing mode is applied to Measure
Groups and Partitions, it adds the incremental fact data into the cube and processes only the affected partitions.
Incorrect Answers:
B: Process Data: This processing mode is available for Dimensions, Measure Groups, Partitions, and Cubes. This processing mode is used to process the data without actually building or creating the aggregations and indexes.
References:
https://www.mssqltips.com/sqlservertip/2756/ssas-interview-questions-on-deployment-processing-querying-and-accessing/
Question 10
You are developing a SQL Server Analysis Services (SSAS) tabular project that will be used by the finance, sales, and marketing teams.
The sales team reports that the model is too complex and difficult to use. The sales team does not need any information other than sales-related resources in the tabular model. The finance and marketing teams need to see all the resources in the tabular model.
You need to implement a solution that meets the needs of the sales team while minimizing development and administrative effort.
What should you do?
- A. Create a separate partition for each team.
- B. Create a separate data source for each team.
- C. Create a perspective for the sales team.
- D. Enable client side security to filter non-sales data.
Answer : C
Question 11
You are developing a SQL Server Analysis Services (SSAS) tabular project.
You need to grant the minimum permissions necessary to enable users to query data in a data model.
Which role permission should you use?
- A. Explorer
- B. Process
- C. Browser
- D. Administrator
- E. Select
- F. Read
Answer : F
Question 12
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
Question 13
You are troubleshooting query performance for a SQL Server Analysis Services (SSAS) cube.
A user reports that a Multidimensional Expressions (MDX) query is very slow.
You need to identify the MDX query statement in a trace by using SQL Server Profiler.
Which event class should you use?
- A. Get Data From Aggregation
- B. Query Subcube
- C. Query Begin
- D. Progress Report Begin
- E. Calculate Non Empty Begin
- F. Execute MDX Script Begin
Answer : C
Question 14
DRAG DROP -
You install a SQL Server Analysis Services (SSAS) instance in tabular mode on a server.
While processing a very large tabular model, you receive an out-of-memory error. You identify that the amount of physical memory in the server is insufficient.
Additional physical memory cannot be installed in the server.
You need to configure the server to allow paging to disk by using the operating system page file (pagefile.sys).
Which four 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.)
Select and Place:
Answer : 
Explanation:
Note:
* View or set configuration properties in Management Studio
1. In SQL Server Management Studio, connect to an Analysis Services instance.
2. In Object Explorer, right-click the Analysis Services instance, and then clickProperties. The General page appears, displaying the more commonly used properties.
3. To view additional properties, click theShow Advanced (All) Propertiescheckbox at the bottom of the page.
Modifying server properties is supported only for tabular mode and multidimensional mode servers. If you installed PowerPivot for SharePoint, always use the default values unless you are directed otherwise by a Microsoft product support engineer.
* VertiPaqPagingPolicy
Specifies the paging behavior in the event the server runs low on memory. Valid values are as follows:
Zero (0) is the default. No paging is allowed. If memory is insufficient, processing fails with an out-of-memory error.
1 enables paging to disk using the operating system page file (pagefile.sys).
When VertiPaqPagingPolicy is set to 1, processing is less likely to fail due to memory constraints because the server will try to page to disk using the method that you specified. Setting the VertiPaqPagingPolicy property does not guarantee that memory errors will never happen. Out of memory errors can still occur under the following conditions:
There is not enough memory for all dictionaries. During processing, Analysis Services locks the dictionaries for each column in memory, and all of these together cannot be more than the value specified for VertiPaqMemoryLimit.
There is insufficient virtual address space to accommodate the process.
To resolve persistent out of memory errors, you can either try to redesign the model to reduce the amount of data that needs processing, or you can add more physical memory to the computer.
Applies to tabular server mode only
* Incorrect Answers: VertiPaqMemoryLimit
If paging to disk is allowed, this property specifies the level of memory consumption (as a percentage of total memory) at which paging starts. The default is 60. If memory consumption is less than 60 percent, the server will not page to disk.
This property depends on the VertiPaqPagingPolicyProperty, which must be set to 1 in order for paging to occur.
Applies to tabular server mode only.
Reference: Memory Properties; Configure Server Properties in Analysis Services
Question 15
You maintain SQL Server Analysis Services (SSAS) instances.
You need to configure an installation of PowerPivot for Microsoft SharePoint in a SharePoint farm.
Which tool should you use? (Each correct answer presents a complete solution. Choose all that apply.)
- A. SQL Server Configuration Manager
- B. PowerPivot Configuration Tool
- C. SharePoint Products Configuration Wizard
- D. SharePoint Central Administration
- E. PowerShell
Answer : BDE
Explanation:
Power Pivot for SharePoint must be configured before it can be used. After you install Power Pivot for SharePoint using SQL Server Setup, you can configure it using any of the following approaches:
-> Power Pivot Configuration Tool or Power Pivot for SharePoint 2013 Configuration tool
-> SharePoint Central Administration
-> PowerShell cmdlets
References: https://docs.microsoft.com/en-us/sql/analysis-services/power-pivot-sharepoint/power-pivot-server-administration-and-configuration-in-central- administration