70-467 - Designing Business Intelligence Solutions with Microsoft SQL Server 2012

Go back to Microsoft

Example Questions

An existing cube dimension that has 30 attribute hierarchies is performing very poorly. You have the following requirements: ?Implement drill-down browsing. ?Reduce the number of attribute hierarchies but ensure that the information contained within them is available to users on demand. ?Optimize performance. You need to redesign the cube dimension to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) You are designing a partitioning strategy for a large fact table in a Manufacturing data warehouse. Tens of millions of new inventory fact records are loaded into the data warehouse weekly, outside of business hours. Most queries against the database are generated by reports and by cube processing. Data is frequently queried at the day level and occasionally at the month level. You are designing a SQL Server Integration Services (SSIS) solution. The solution will contain an SSIS project that includes several SSIS packages. Each SSIS package will define the same connection managers and variables. You have the following requirements: ?Ensure that the deployment model supports changing the content of connection strings by using parameters at execution time. ?Ensure that the deployment model automatically starts from calls to the catalog.start_execution stored procedure in the SSISDB database. ?Maximize performance at execution time. ?Minimize development effort. You need to design a solution that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) You need to fix the PowerPivot data refresh problem by using the least amount of administrative effort. What should you do? You are designing a multidimensional OLAP (MOLAP) cube. The MOLAP cube must meet the following requirements: Ensure that workloads for aggregation tuning can be automatically collected. Require the least amount of effort to perform manual aggregation tuning. Minimize impact on the performance of previously tuned queries. You need to design a MOLAP cube that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) You need to implement the aggregation designs for the cube. What should you do? You are designing a strategy for an enterprise reporting solution that uses SQL Server Reporting Services (SSRS). Many of the SSRS reports will use common utilities and functions, including the following: ?Report utility functions and business logic in code ?Standardized report formatting properties such as fonts and colors for report branding Formatting may change and new functions may be added as the reporting solution evolves. You need to create a strategy for deploying the formatting and code across the entire enterprise reporting solution. You must also ensure that reports can be easily updated to reflect formatting and function changes. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) You need to choose the appropriate key to use when designing a dimension table based on the Customer table. What should you do? You are designing a subscription strategy for a SQL Server Reporting Services (SSRS) report. You have an application that populates a table with user-specific subscription schedules and report formats. You need to ensure that users can receive reports by email according to their preferences. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) You need to perform the pre-upgrade database backup operation by using SQL Server Management Studio (SSMS). How should you configure the backup options? You are modifying a SQL Server Reporting Services (SSRS) report for a SQL Server Analysis Services (SSAS) cube. The report defines a report parameter of data type Date/Time with which users can filter the report by a single date. The parameter value cannot be directly used to filter the Multidimensional Expressions (MDX) query for the dataset. You need to ensure that the report displays data filtered by the user-entered value. You must achieve this goal by using the least amount of development effort. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) You are designing an extract, transform, load (ETL) process for loading data from a SQL Server database into a large fact table in a data warehouse each day with the prior day's sales data. The ETL process for the fact table must meet the following requirements: ?Load new data in the shortest possible time. ?Remove data that is more than 36 months old. ?Ensure that data loads correctly. ?Minimize record locking. ?Minimize impact on the transaction log. You need to design an ETL process that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) You need to roll back the compatibility level of the Research database. What should you do? You are designing a fact table in a SQL Server database. The fact table must meet the following requirements: ?Include a columnstore index. ?Allow users to choose up to 10 dimension tables and up to five facts at one time. ?Maximize performance of queries that aggregate measures by using any of the 10 dimensions. ?Support billions of rows. ?Use the most efficient design strategy. You need to design the fact table to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) You need to create the Package Activity report. What should you do? You need to scale out SSAS. What should you do? You need to design a cube partitioning strategy to be implemented as the cube size increases. What should you do? You are designing an extract, transform, load (ETL) process for loading data from a SQL Azure database into a large fact table in a data warehouse each day with the prior day's sales data. The ETL process for the fact table must meet the following requirements: ?Load new data in the shortest possible time. ?Remove data that is more than 36 months old. ?Minimize record locking. ?Minimize impact on the transaction log. You need to design an ETL process that meets the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) You need to develop a BISM that meets the business requirements for ad-hoc and daily operational analysis. You must minimize development effort. Which development approach and mode should you use? You are designing a SQL Server Integration Services (SSIS) solution that will load multiple Online Transactional Processing (OLTP) data sources into a SQL Server data mart. You have the following requirements: ?Ensure that the process supports the creation of an exception report that details possible duplicate key values, null ratios within columns, and column-length distributions of values. ?Ensure that users can generate the exception report in an XML format. ?Use the minimum development effort. You need to design the SSIS solution to meet the requirements. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.) A SQL Server Analysis Services (SSAS) cube contains a large measure group. The fact table supporting the measure group is loaded with new data throughout the day. You have the following requirements: ?Ensure that the cube displays current data as quickly as possible. ?Maximize availability of the cube. ?Maximize query performance for all aggregation levels. You need to choose a partitioning strategy that meets the requirements. Which partitioning strategy should you choose? (More than one answer choice may achieve the goal. Select the BEST answer.) You are modifying a star schema data mart that feeds order data from a SQL Azure database into a SQL Server Analysis Services (SSAS) cube. The data mart contains two large tables that include flags and indicators for some orders. There are 100 different flag columns, each with 10 different indicator values. Some flags reuse indicators. The tables both have a granularity that matches the fact table. You have the following requirements: ?Allow users to slice data by all flags and indicators. ?Modify the date dimension table to include a surrogate key of a numeric data type and add the surrogate key to the fact table. ?Use the most efficient design strategy for cube processing and queries. You need to modify the schema. What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

Study Guides