Designing Business Intelligence Solutions with Microsoft SQL Server 2012 v11.0

Page:    1 / 12   
Total 176 questions Expand All

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 discussion

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 discussion

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 discussion

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 discussion

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 discussion

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 discussion

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 discussion

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 discussion

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 discussion

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.

Reporting Business users frequently generate reports in Microsoft Excel by using PowerPivot. The PowerPivot Management Dashboard does not currently display any usage data. Several SSRS reports exist that use the data warehouse as a source. The data warehouse queries are aggregate queries that use the factOrders table and one or more dimension tables. All SSRS data sources use Integrated Windows authentication. SSRS displays a security access error message when managers run SSRS reports based on the Operations database. Reporting performance has become unacceptably slow. Business Requirements Improve the query spe

Question discussion

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 discussion

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 discussion

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 discussion

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 : B,D

Question discussion

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

Question discussion

Page:    1 / 12   
Total 176 questions Expand All

Talk to us!


Have any questions or issues ? Please dont hesitate to contact us