Designing Business Intelligence Solutions with Microsoft SQL Server 2012 v11.0
Question 1
You need to identify changes in the financial database.
What should you do?
- A. Add SQL Server replication to each table.
- B. Extract data from the current partition of each table.
- C. Add a timestamp column to each table.
- D. Perform a full extract of each table.
- E. Enable change data capture on each table.
Answer : E
Question 2
You need to identify changes in the financial database.
What should you do?
- A. Add SQL Server log shipping to each table.
- B. Add SQL Server mirroring to each table.
- C. Perform a full extract of each table.
- D. Enable change data capture on each table.
- E. Create an AlwaysOn Availability Group that includes all the tables.
Answer : D
Question 3
You need to restrict access to data in the tables in the data warehouse.
What should you do?
- A. Configure column-level permissions.
- B. Configure database roles.
- C. Create views and grant permissions to the views.
- D. Configure application roles.
Answer : C
Question 4
You need to implement the aggregation designs for the cube.
What should you do?
- A. Use the Usage-Based Optimization Wizard.
- B. Use the Aggregation Design Wizard.
- C. Partition the cube by month.
- D. Implement cache warming in SSAS via an SSIS package.
Answer : B
Question 5
You need to design a cube partitioning strategy to be implemented as the cube size increases.
What should you do?
- A. Use relational OLAP (ROLAP) on all local partitions.
- B. Implement monthly remote partitions.
- C. Use multidimensional OLAP (MOLAP) on all local partitions.
- D. Implement monthly local partitions.
Answer : B
Question 6
You need to create the Package Activity report.
What should you do?
- A. Create a log table and use SSIS event handlers to write to the log table. Then create an SSRS report that uses the log table.
- B. Use the SSIS log provider for SQL Server. Then create an SSRS report that uses the sysssislog table.
- C. Create a log table and build a custom log provider to write to the log table. Then create an SSRS report that uses the log table.
- D. Create an SSRS report that uses thecatalog.executions and catalog.execution_data_statistics views.
Answer : D
Question 7
You need to implement security in the cube to limit the sites visible to each user.
What should you do?
- A. Create an SSAS database role in the cube for each user and assign the sites each user can access to his or her database role.
- B. Create an SSAS server role for each user and assign the sites each user can access to his or her server role.
- C. Create an SSAS database role and define a Multidimensional Expressions (MDX) calculation to implement dynamic dimension security.
- D. Create a view on the SalesTransactions table that uses the SecurityFilter and User table data to limit the sites for each user.
Answer : C
Question 8
You need to implement the product dimension.
What should you do?
- A. In the data warehouse, create a product dimension from a view that joins the Product and ProductColor tables in the financial database and contains product name and product color attributes.
- B. In the data warehouse, create a dimension table that contains product name and a dimension table that contains product color.
- C. In the data warehouse, create a product dimension table that contains product name and product color.
- D. In the cube, create a named query that joins the Product and ProductColor tables in the financial database.
Answer : C
Question 9
You need to scale out SSAS.
What should you do?
- A. Back up the cube on ServerB and restore it on ServerC each day.
- B. Create an empty cube on ServerC and link to the objects in the cube on ServerB.
- C. Process the cube on both ServerB and ServerC each day.
- D. Synchronize the cube from ServerB to ServerC each day.
Answer : D
Question 10
You need to configure a parameter for the database connection string.
What should you do?
A. Use a required package parameter.
B. Use a required project parameter.
C. Use a package configuration.
D. Use a global variable.
Answer : B
Topic 4, WingTip Toys
General Background
You are a data architect for WingTip Toys. The company uses SQL Server 2012 Enterprise
edition. SQL Server Analysis Services (SSAS) and SQL Server Reporting Services (SSRS)
are installed on separate servers.
Data Warehouse
The company's data warehouse initially contained less than 100 MB and 100 million rows
of data from only one data source. It now contains more than 10 TB and 10 billion rows of
data, in 25 tables, from 12 data sources.
The largest table in the data warehouse, the factOrders table, contains 5 TB of data. The
OrderDateKey InvoiceDateKey, and
ShipDateKey. The data warehouse server has 1 TB of RAM. Memory usage is currently at
20 percent.
One billion rows of data are added to the data warehouse each month. New data is copied
each night from the data sources into SQL Server staging tables, and existing records are
not updated. The largest data set is order information, which is loaded in parallel into
multiple staging tables, one for each data source. All the staging tables have the same
structure and belong to the same filegroup as the factOrders table.
The dimCustomers table stores customer information that may change over time.
Data Models
You are developing three SSAS databases, as described in the following table.
Question 11
You need to implement the date dimension in the Operations database.
What should you do?
- A. Create three database dimensions. Add each database dimension as a cube dimension by setting the Referenced relationship type.
- B. Create one database dimension. Add three cube dimensions based on the database dimension. Set the Regular relationship type for each cube dimension.
- C. Create three database dimensions. Add each database dimension as a cube dimension by setting the Regular relationship type.
- D. Create one database dimension. Add three cube dimensions based on the database dimension. Set the Referenced relationship type for each cube dimension.
Answer : B
Question 12
You need to ensure that managers can successfully run reports.
What should you do?
- A. Implement Kerberos delegation.
- B. Configure the SSRS data sources to store Windows credentials.
- C. Implement forms-based authentication.
- D. Configure the CustomData property in the connection strings.
Answer : A
Question 13
You need to improve the performance of data warehouse queries.
What should you do?
- A. Create columnstore indexes.
- B. Create clustered indexes.
- C. Create indexed views.
- D. Create bitmap indexes.
Answer : A
Question 14
You need to configure permissions for the Customers dimension.
What should you do? (Each correct answer presents a complete solution. Choose all that apply.)
- A. In SQL Server Management Studio, configure the BusinessUsers role to disallow the reading of all definitions.
- B. In SQL Server Data Tools, configure the BusinessUsers role to disallow the reading of the Customers dimension definition.
- C. In SQL Server Management Studio, deny the member set for the Customers dimension data by using the Multidimensional Expressions (MDX) expression Filter([BusinessUsers]).
- D. In SQL Server Management Studio, configure the BusinessUsers role to disallow the reading of the Customers dimension definition.
Answer : BD
Question 15
You need to select the appropriate mode for the Sales database.
Which mode should you select?
- A. ROLAP
- B. Direct Query
- C. MOLAP
- D. In-Memory
Answer : B