Implementing a Data Warehouse using SQL
Question No: 61 DRAG DROP – (Topic 1)
You are developing a SQL Server Integration Services (SSIS) package that imports data into a data warehouse. You are developing the part of the SSIS package that populates the ProjectDates dimension table.
The business key of the ProjectDates table is the ProjectName column. The business user has given you the dimensional attribute behavior for each of the four columns in the ProjectDates table:
鈥xpectedStartDate – New values should be tracked over time.
鈥ctualStartDate – New values should not be accepted.
鈥xpectedEndDate – New values should replace existing values.
鈥ctualEndDate – New values should be tracked over time.
You use the SSIS Slowly Changing Dimension Transformation.
You must configure the Change Type value for each source column.
Which settings should you select? (To answer, select the appropriate setting or settings in the answer area. Each Change Type may be used once, more than once, or not at all.)
Question No: 62 – (Topic 1)
You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. A Data Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort. What should you do?
Create an Integration Services package and use a Data Profiling transform.
Create a custom .NET application based on the Knowledgebase class.
Create a data quality project.
Create a CLR stored procedure based on the Knowledgebase class.
Create a Master Data Services (MDS) business rule.
Question No: 63 – (Topic 1)
You are creating a SQL Server Master Data Services (MDS) model. This model is used to store a master list of products.
An attribute must be added to the Product entity to define the sales manager responsible
for each product.
You need to create an attribute in the Product entity that prevents users from entering invalid sales manager values.
Which type of attribute should you create?
Explanation: References: http://msdn.microsoft.com/en-us/library/bb190163.aspx http://msdn.microsoft.com/en-us/library/ee633737.aspx http://msdn.microsoft.com/en-us/library/ee633759.aspx http://msdn.microsoft.com/en-us/library/ee633745.aspx http://msdn.microsoft.com/en-us/library/ee633724.aspx http://msdn.microsoft.com/en-us/library/ee633733.aspx http://msdn.microsoft.com/en-us/library/ff487058.aspx
Question No: 64 – (Topic 1)
You are developing a SQL Server Integration Services (SSIS) package to load data into a SQL Server table on ServerA. The package includes a data flow and is executed on ServerB. The destination table has its own identity column.
The destination data load has the following requirements:
->The identity values from the source table must be used.
->Default constraints on the destination table must be ignored.
->Batch size must be 100,000 rows.
You need to add a destination and configure it to meet the requirements.
Which destination should you use?
OLE DB Destination with Fast Load
SQL Server Destination
ADO NET Destination without Bulk Insert
ADO NET Destination with Bulk Insert
OLE DB Destination without Fast Load
Question No: 65 DRAG DROP – (Topic 1)
You are developing a SQL Server Integration Services (SSIS) package to insert new data into a data mart. The package uses a Lookup transformation to find matches between the source and destination.
The data flow has the following requirements:
鈥ew rows must be inserted.
鈥ookup failures must be written to a flat file.
In the Lookup transformation, the setting for rows with no matching entries is set to Redirect rows to no match output. You need to configure the package to direct data into the correct destinations.
How should you design the data flow outputs? (To answer, drag the appropriate transformation from the list of answer options to the correct location in the answer area.)
Records that have no match in the destination are new records and so should be inserted.
Any errors should be recorded in the flat file.
Question No: 66 – (Topic 1)
You are designing a SQL Server Integration Services (SSIS) package that uses the Fuzzy Lookup transformation.
The reference data to be used in the transformation does not change.
You need to reuse the Fuzzy Lookup match index to increase performance and reduce maintenance.
What should you do?
Select the GenerateAndPersistNewIndex option in the Fuzzy Lookup Transformation Editor.
Select the GenerateNewIndex option in the Fuzzy Lookup Transformation Editor.
Select the DropExistingMatchlndex option in the Fuzzy Lookup Transformation Editor.
Execute the sp_FuzzyLookupTableMaintenanceUninstall stored procedure.
Execute the sp_FuzzyLookupTableMaintenanceInvoke stored procedure.
Question No: 67 – (Topic 1)
You are designing a data warehouse for a software distribution business that stores sales by software title. It stores sales targets by software category. Software titles are classified into subcategories and categories. Each software title is included in only a single software subcategory, and each subcategory is included in only a single category. The data warehouse will be a data source for an Analysis Services cube.
The data warehouse contains two fact tables:
->factSales, used to record daily sales by software title
->factTarget, used to record the monthly sales targets by software category
Reports must be developed against the warehouse that reports sales by software title, category and subcategory, and sales targets.
You need to design the software title dimension. The solution should use as few tables as possible while supporting all the requirements.
What should you do?
Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory and a fourth bridge table that joins software titles to their appropriate category and subcategory table records with foreign key constraints. Direct the cube developer to use key granularity attributes.
Create three software tables, dimSoftware, dimSoftwareCategory, and dimSoftwareSubcategory. Connect factSales to all three tables and connect factTarget to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
Create one table, dimSoftware, which contains Software Detail, Category, and Subcategory columns. Connect factSales to dimSoftware with a foreign key constraint. Direct the cube developer to use a non-key granularity attribute for factTarget.
Create two tables, dimSoftware and dimSoftwareCategory. Connect factSales to dimSoftware and factTarget to dimSoftwareCategory with foreign key constraints. Direct the cube developer to use key granularity attributes.
Question No: 68 – (Topic 1)
You develop a SQL Server Integration Services (SSIS) package that imports SQL Azure data into a data warehouse every night.
The SQL Azure data contains many misspellings and variations of abbreviations. To import the data, a developer used the Fuzzy Lookup transformation to choose the closest- matching string from a reference table of allowed values. The number of rows in the reference table is very large.
If no acceptable match is found, the Fuzzy Lookup transformation passes a null value.
The current setting for the Fuzzy Lookup similarity threshold is 0.50. Many values are incorrectly matched.
You need to ensure that more accurate matches are made by the Fuzzy Lookup transformation without degrading performance.
What should you do?
Change the Exhaustive property to True.
Change the similarity threshold to 0.55.
Change the similarity threshold to 0.40.
Increase the maximum number of matches per lookup.
Answer: B Explanation:
Question No: 69 – (Topic 1)
You are completing the installation of the Data Quality Server component of SQL Server Data Quality Services (DQS).
You need to complete the post-installation configuration. What should you do?
Install the Analysis Services OLE DB Provider.
Make the data available for DQS operations.
Run the Data Quality Server Installer.
Answer: C Explanation:
Question No: 70 – (Topic 1)
Occasionally a job that executes an existing SQL Server Integration Services (SSIS) package does not complete and nothing is processed.
You need to ensure that package logging occurs. Your solution must minimize deployment and development efforts.
What should you do?
Create a reusable custom logging component.
Use the gacutil command.
Use the Project Deployment Wizard.
Run the package by using the dtexec /rep /conn command.
Add a data tap on the output of a component in the package data flow.
Create an OnError event handler.
Use the dtutil /copy command.
Deploy the package by using an msi file.
Run the package by using the dtexec /dumperror /conn command.
Run the package by using the dtexecui.exe utility and the SQL Log provider.
Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.
Answer: J Explanation: References:
Topic 2, Volume B
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|