Microsoft-PRO Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008 Set 1

You design a SQL Server 2008 Analysis Services (SSAS) solution. Your solution has a measure named Sales Amount and a dimension named Date. The Date dimension has a hierarchy named Fiscal that has levels named Fiscal Year, Fiscal Quarter, and Fiscal Month. You need to create a calculated member to analyze the Sales Amount share percentage at different levels compared to the total sales for a given Fiscal Year. Which code segment should you use?

You design a Business Intelligence (BI) solution by using SQL Server 2008. You deploy a SQL Server 2008 Analysis Services (SSAS) cube. The cube contains a measure group that uses table binding. The measure group contains 200 million rows. A job that processes the measure group fails. The log shows an out-of- memory error. The job uses the Process Update option. You need to resolve the error. You need to perform this action without increasing the available physical memory for the SSAS instance. What should you do?

You design a Business Intelligence (BI) solution by using SQL Server 2008. You create a SQL Server 2008 Analysis Services (SSAS) solution. The solution contains a cube that has a measure named SalesAmount. The measure contains customer sales data for the last six months. The cube has a single partition that has the storage property set to real-time hybrid online analytical processing (HOLAP). Queries against the cube must return current sales data that is entered one hour before cube processing. The partition takes two hours to process and the response time for the queries is slow. You need to improve the cube processing and query response time. What should you do?

You design a Business Intelligence (BI) solution by using SQL Server 2008. Your company processes all transaction data in a Point of Sale (POS) application. Based on the transaction data, you design a solution to predict the type of products a customer tends to purchase on a single visit. You need to identify the appropriate algorithm to design the solution. Which algorithm should you use?

You design a Business Intelligence (BI) solution by using SQL Server 2008. The solution includes the following components: A data warehouse that is hosted on SQL Server 2008 SQL Server Integration Services (SSIS) packages that connect to a third-party relational database A SQL Server 2008 Analysis Services (SSAS) project that reads data from the data warehouse The third-party relational database does not support Windows authentication. A new security policy of the company requires that only members of the sysadmin role can access the credentioals used by SSIS to connecto to thte database. You need to ensure that the solution meets the requirements of the security policy. What should you do?

You design a Business Intelligence (BI) solution by using SQL Server 2008. You plan to design a logging strategy for a SQL Server 2008 Integration Services (SSIS) solution. The SSIS solution contains 15 packages. You want to log detailed information about each package. You need to ensure that custom events specific to each control flow task in each package are logged. What should you do?

You design a Business Intelligence (BI) solution by using SQL Server 2008. The solution will support a Microsoft ASP.NET application that is deployed to a Web farm. Reports will be deployed to a SQL Server 2008 Reporting Services (SSRS) instance. The databases for the SSRS instance will be deployed to a two-node failover cluster that hosts a single instance of SQL Server 2008. You need to ensure that the SSRS instance remains available even when one of the servers fails. What should you do?

You design a Business Intelligence (BI) solution by using SQL Server 2008. Employees use a Windows Forms application based on Microsoft .NET Framework 3.5. SQL Server is not installed on the employees' computers. You write a report by using Report Definition Language (RDL). You need to ensure that if the employees are disconnected from the corporate network, the application renders the report. What should you do?

You design a Business Intelligence (BI) solution by using SQL Server 2008. You have developed SQL Server 2008 Reporting Services (SSRS) reports that are deployed on an SSRS instance. You plan to develop a new application to view the reports. The application will be developed by using Microsoft ASP.NET 3.5. You need to ensure that the application can perform the following tasks: -Display available reports in a tree view control. -Create and manage subscriptions on reports. What should you do?

You are the lead developer for a SQL Server 2008 data warehousing project. The source database for the project is an online transaction processing (OLTP) system. The OLTP system executes 4,000 transactions every minute during business hours. The OLTP system records only the date and time of insertion of a new row and not for the updates of existing rows. You plan to design an extract, transform, and load (ETL) process for the project that populates a data warehouse from the source database. The ETL process must be configured in the following manner: -To run after business hours -To capture new rows and existing rows that have been modified You need to ensure that only new rows or modified rows from the database tables are processed by the ETL process. What should you do?