Microsoft Designing Business Intelligence Solutions with Microsoft SQL Server 2012

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 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 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 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 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 are creating a Multidimensional Expressions (MDX) calculation for Projected Revenue in a cube. For Customer A, Projected Revenue is defined as 150 percent of the Total Sales for the customer. For all other customers, Projected Revenue is defined as 110 percent of the Total Sales for the customer. You need to calculate the Projected Revenue as efficiently as possible. Which calculation should you use? (More than one answer choice may achieve the goal. Select the BEST answer.)

You are developing the database schema for a SQL Server Analysis Services (SSAS) BI Semantic Model (BISM). The BISM will be based on the schema displayed in the following graphic. You have the following requirements:
?Ensure that queries of the data model correctly display average student age by class and average class level by student.
?Minimize development effort.
You need to design the data model.
What should you do? (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.)

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.