Implementing a Data Warehouse with Microsoft SQL Server 2012
Question No: 141 HOTSPOT – (Topic 3)
You are using the Master Data Services (MDS) Add-in for Excel to configure the entities in a model. The model consists of two entities: one named Customer and one named State. You open the Customer entity.
Currently, data stewards can enter any text value in the Customer entity#39;s State attribute. You must restrict the entry of values in the State attribute to members defined in the State entity.
You need to configure the State attribute of the Customer entity.
Which option should you use? (To answer, select the appropriate area in the answer area.)
Question No: 142 HOTSPOT – (Topic 3)
You are the Master Data Services (MDS) administrator at your company. An existing user needs to integrate data from other systems into MDS.
You need to give the user permissions to integrate data.
Which user management menu item should you select? (To answer, configure the appropriate option or options in the dialog box in the answer area.)
Question No: 143 – (Topic 3)
To support the implementation of new reports, Active Directory data will be downloaded to a SQL Server database by using a SQL Server Integration Services (SSIS) 2012 package.
The following requirements must be met:
->All the user information for a given Active Directory group must be downloaded to a SQL Server table.
->The download process must traverse the Active Directory hierarchy recursively.
You need to configure the package to meet the requirements by using the least development effort.
Which item should you use?
Script component configured as a transformation
Script component configured as a source
Script component configured as a destination
Question No: 144 – (Topic 3)
You are designing a SQL Server Integration Services (SSIS) data flow to load sales transactions from a source system into a data warehouse hosted on Windows Azure SQL Database. One of the columns in the data source is named ProductCode.
Some of the data to be loaded will reference products that need special processing logic in the data flow.
You need to enable separate processing streams for a subset of rows based on the source product code.
Which data flow transformation should you use?
Explanation: Script Component Transformation:
The transformation that uses script to extract, transform, or load data.
* SQL Server Integration Services provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases.
Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.
Not A, Not B: No Data Transforms called Source Assistant or Destination Assistant Not C: Not related to auditing
Question No: 145 – (Topic 3)
You are designing a data warehouse that contains a product dimension.
The product dimension contains the original product attributes as well as the most recent set of updated attributes. When a product is updated, the original product attributes must be preserved.
The product dimension design must:
->Support a Type 3 Slowly Changing Dimension (SCD) process
->Minimize the total storage consumed
You need to design a solution that meets these requirements. What should you do?
Design two tables. One table will hold the original product surrogate key and attributes. The other table will hold the most recent product surrogate key and attributes.
Design a table as a Type 2 SCD dimension that holds the surrogate key for each product and its attributes. Limit the number of records per product within the table to two.
Design a table to hold the surrogate key for each product and its attributes as well as additional columns to store the original attributes.
Design a table to hold the surrogate key for each product and its attributes. Add a column to store an indicator that identifies the record as original data or recent data.
Question No: 146 – (Topic 3)
You maintain a SQL Server Integration Services (SSIS) package. The package was developed by using SQL Server 2008 Business Intelligence Development Studio (BIDS).
The package includes custom scripts that must be upgraded. You need to upgrade the package to SQL Server 2012.
Which tool should you use?
SQL Server Integration Services Deployment Wizard
SQL Server Configuration Manager
SSIS Upgrade Wizard in SQL Server Management Studio
SSIS Upgrade Wizard in SQL Server 2008 BIDS
Explanation: You can upgrade packages that were created in earlier versions of Integration Services to the Integration Services format that SQL Server 2012 uses. SQL Server provides the SSIS Package Upgrade Wizard to help in this process. Because you can configure the wizard to backup up your original packages, you can continue to use the original packages if you experience upgrade difficulties.
You can run the SSIS Package Upgrade Wizard from SQL Server Data Tools (SSDT), from SQL Server Management Studio, or at the command prompt.
* When you upgrade an instance of SQL Server 2005 or SQL Server 2008 to the current release of SQL Server, your existing SQL Server 2008 Integration Services (SSIS) packages are not automatically upgraded to the package format that the current release SQL Server Integration Services uses. You will have to select an upgrade method and manually upgrade your packages.
Question No: 147 – (Topic 3)
You are implementing a SQL Server Integration Services (SSIS) package that imports Microsoft Excel workbook data into a Windows Azure SQL Database database. The package has been deployed to a production server that runs Windows Server 2008 R2 and SQL Server 2012.
The package fails when executed on the production server.
You need to ensure that the package can load the Excel workbook data without errors. You need to use the least amount of administrative effort to achieve this goal.
What should you do?
Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
Enable Address Windowing Extensions (AWE) for the local SQL Server instance.
Replace the SSIS Excel source with a SSIS Flat File source.
Install a 64-bit ACE driver and replace the Excel source with an OLE DB source.
Explanation: XLSX files, the new file type in Excel 2007/2010 cannot be opened with Excel Source/Destination in SSIS. To use XLSX in SSIS we need to install ACE driver and use OLE DB Source/Destination to read read/write data in XLSX files.
ACE is a set of components that facilitate the transfer of data between existing Microsoft Office files such as Microsoft Office Access (*.mdb and *.accdb) files and Microsoft Office Excel 7 (*.xls, *.xlsx, and *.xlsb) files to Microsoft SQL Server. If the SQL Server or your development box does not have ACE driver then install that 1st.
The most efficient way to link to SQL Server is using an OLEDB connection manager.
Incorrect: Not B:
Address Windowing Extensions (AWE) is a Microsoft Windows application programming interface that allows a 32-bit software application to access more physical memory than it has virtual address space.
Address Windowing Extensions (AWE) is a set of extensions that allows an application to quickly manipulate physical memory greater than 4GB. Certain data-intensive applications, such as database management systems and scientific and engineering software, need access to very large caches of data. In the case of very large data sets, restricting the cache to fit within an application#39;s 2GB of user address space is a severe restriction.
Question No: 148 – (Topic 3)
You are installing the Data Quality Server component of Data Quality Services.
You need to provision the hardware and install the software for the server that runs the Data Quality Server.
You must ensure that the minimum Data Quality Server prerequisites are met.
What should you do?
Install Microsoft Internet Explorer 6.0 SP1 or later.
Install SQL Server 2012 Database Engine.
Make sure the server has at least 4 GB of RAM.
Install Microsoft SharePoint Server 2010 Standard Edition.
Explanation: Data Quality Server Minimum System Requirements
SQL Server 2012 Database Engine.
Memory (RAM): Minimum: 2 GB
Recommended: 4 GB or more
Note: SQL Server Data Quality Services (DQS) is a new feature in SQL Server 2012 that contains the following two components: Data Quality Server and Data Quality Client.
Question No: 149 DRAG DROP – (Topic 3)
You deploy a server that has SQL Server installed.
You deploy a SQL Server Integration Services (SSIS) package to the server.
You need to automate the execution of the package. The solution must ensure that you receive a notification if the package to execute.
In which order should you perform all the actions?
Question No: 150 – (Topic 3)
You are developing a SQL Server Integration Services (SSIS) project that contains a project Connection Manager and multiple packages.
All packages in the project must connect to the same database. The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed.
You need to develop this project with the least amount of development effort.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
Set the Sensitive property of the parameter to True.
Edit each package Connection Manager. Set the ServerName property to
Edit the project Connection Manager in Solution Explorer. Set the ServerName property to @[$Project::ServerParam].
Create a project parameter named ServerName.
Create a package parameter named ServerName in each package.
Set the Required property of the parameter to True.
Explanation: C: From question: quot; The server name for the database must be set by using a parameter named ServerParam when any package in the project is executed.quot;
D: SSIS 2012 has introduced the concept of Project level connection managers. An SSIS
project is generally more than one package. To simplify lives, the SSIS team now allows for the sharing of common resources across projects, connection managers being one of those resources.
F: When a parameter is marked as required, a server value or execution value must be specified for that parameter. Otherwise, the corresponding package does not execute. Although the parameter has a default value at design time, it will never be used once the project is deployed.
* Integration Services (SSIS) parameters allow you to assign values to properties within packages at the time of package execution. You can create project parameters at the project level and package parameters at the package level. Project parameters are used to supply any external input the project receives to one or more packages in the project.
Package parameters allow you to modify package execution without having to edit and redeploy the package.
Reference: Integration Services (SSIS) Parameters
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|