You need to implement the Customer Sales and Manufacturing data models. What should you do? (Each correct answer presents a partial solution. Choose all that apply.)

Question

Exhibit

Options

• AUse the Database Synchronization Wizard to upgrade the database to tabular mode.
• BUse SQL Server Integration Services (SSIS) to copy the database design to the SSAS instance, and
• CUse SQL Server Data Tools (SSDT) to redevelop and deploy the projects.
• DUse the current SSAS instance.
• EInstall a new instance of SSAS in tabular mode.

• A
  10% (3)
• B
  3% (1)
• C
  83% (24)
• D
  3% (1)

Explanation

Case Study: 3 - Data Architect (QUESTION 53 - QUESTION 60) General Background You are a Data Architect for a company that uses SQL Server 2012 Enterprise edition. You have been tasked with designing a data warehouse that uses the company's financial database as the data source. From the data warehouse, you will develop a cube to simplify the creation of accurate financial reports and related data analysis. Technical Background You will utilize the following three servers: - ServerA runs SQL Server Database Engine. ServerA is a production server and also hosts the financial database. - ServerB runs SQL Server Database Engine, SQL Server Analysis Services (SSAS) in multidimensional mode, SQL Server Integration Services (SSIS), and SQL Server Reporting Services (SSRS). - ServerC runs SSAS in multidimensional mode. - The financial database is used by a third-party application and the table structures cannot be modified. The relevant tables in the financial database are shown in the exhibit. (Click the Exhibit button.) The SalesTransactions table is 500 GB and is anticipated to grow to 2 TB. The table is partitioned by month. It contains only the last five years of financial data. The CouponUsed, OnSale, and Closeout columns contain only the values Yes or No. Each of the other tables is less than 10 MB and has only one partition. The SecurityFilter table specifies the sites to which each user has Business Requirements The extract, transform, load (ETL) process that updates the data warehouse must run daily between 8:00 P.M. and 5:00 A.M. so that it doesn't impact the performance of ServerA during business hours. The cube data must be available by 8:00 A.M. The cube must meet the following business requirements: - Ensure that reports display the most current information available. - Allow fast access to support ad-hoc reports and data analysis. Business Analysts will access the data warehouse tables directly, and will access the cube by using SSRS, Microsoft Excel, and Microsoft SharePoint Server 2010 PerformancePoint Services. These tools will access only the cube and not the data warehouse. Technical Requirements SSIS solutions must be deployed by using the project deployment model. You must develop the data warehouse and store the cube on ServerB. When the number of concurrent SSAS users on ServerB reaches a specific number, you must scale out SSAS to ServerC and meet following - Maintain copies of the cube on ServerB and ServerC. - Ensure that the cube is always available on both servers. - Minimize query response time. The cube must meet the following technical requirements: - The cube must be processed by using an SSIS package. - The cube must contain the prior day's data up to 8:00 P.M. but does not need to contain same-day data. - The cube must include aggregation designs when it is initially - A product dimension must be added to the cube. It will contain a hierarchy comprised of product name and product color. Because of the large size of the SalesTransactions table, the cube must store only aggregations-- the data warehouse must store the detailed data. Both the data warehouse and the cube must minimize disk space usage. As the cube size increases, you must plan to scale out to additional servers to minimize processing time. The data warehouse must use a star schema design. The table design must be as denormalized as possible. The history of changes to the Customer table must be tracked in the data warehouse. The cube must use the data warehouse as its only data source. Security settings on the data warehouse and the cube must ensure that queries against the SalesTransactions table return only records from the sites to which the current user has access. The ETL process must consist of multiple 5SIS packages developed in a single project by using the least amount of effort. The SSIS packages must use a database connection string that is set at execution time to connect to the financial database. All data in the data warehouse must be loaded by the SSIS packages. You must create a Package Activity report that meets the following requirements: - Track SSIS package execution data (including package name, status, start time, end time, duration, and rows processed). - Use the least amount of development effort.

No community discussion yet for this question.