It is impossible to pass Microsoft 70 463 dumps exam without any help in the short term. Come to Ucertify soon and find the most advanced, correct and guaranteed Microsoft 70 463 dumps practice questions. You will get a surprising result by our Renewal Implementing a Data Warehouse with Microsoft SQL Server 2012 practice guides.
Q1. You develop and deploy a SQL Server Integration Services (SSIS) package.
The package is stored in the file system.
You need to execute the package without importing it to the SSIS server.
What should you use to execute the package? (Each correct answer presents a complete solution. Choose all that apply.)
A. catalog.start_package
B. dtexec
C. SQL Server Management Studio
D. SQL Server Agent
Answer: B,D
Q2. You are reviewing the design of an existing fact table named factSales, which is loaded incrementally from a SQL Azure database by a SQL Server Integration Services (SSIS) package each hour. The fact table has approximately 4 billion rows and is dimensioned by product, sales date, and sales time of day.
The database administrator is concerned about the rapid growth of the database and users experience poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.
You need to reduce the report processing time and minimize the growth of the database.
What should you do?
A. Create an indexed view over the fact table to sum orderTotal by month.
B. Create a view over the fact table to sum orderTotal by month.
C. Change the granularity of the fact table to month.
D. Partition the fact table by productKey.
Answer: B
Q3. 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.)
A. Use a project deployment model. Modify connection manager properties to use project parameters. Ensure that the SSISDB database is created.
B. Use a project deployment model. Configure connections in an XML configuration file referenced by an environment variable that corresponds to the SQL Server environment of each SSIS package.
C. Use a package deployment model. Use a SQL Server package configuration with a common filter. Change the contents of the SSIS Configurations table at runtime.
D. Use a package deployment model. Save each SSIS package to a file share that can be accessed from all environments.
Answer: A
Q4. You are designing a SQL Server Integration Services (SSIS) 2012 package that imports data from a Windows Azure SQL Database database into a SQL Server database.
The SSIS package has the following requirements:
Every night, a very large amount of data is imported into the staging database.
Package processing time must be minimized.
The package must run on its own dedicated server when it is deployed to
production.
Transaction log activity when data is imported must be minimized.
You need to design the package to meet the requirements.
Which destination component should you use?
A. Raw File
B. ODBC
C. Bulk Insert
D. OLE DB
Answer: D
Q5. 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?
A. SQL Server Integration Services Deployment Wizard
B. SQL Server Configuration Manager
C. SSIS Upgrade Wizard in SQL Server Management Studio
D. SSIS Upgrade Wizard in SQL Server 2008 BIDS
Answer: C
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.
Note:
* 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.
Q6. You are creating a SQL Server Integration Services (SSIS) package to populate a fact table from a source table. The fact table and source table are located in a SQL Azure database. The source table has a price field and a tax field. The OLE DB source uses the data access mode of Table.
You have the following requirements:
. The fact table must populate a column named TotalCost that computes the sum of the price and tax columns. . Before the sum is calculated, any records that have a price of zero must be discarded.
You need to create the SSIS package in SQL Server Data Tools.
In what sequence should you order four of the listed components for the data flow task? (To answer, move the appropriate components from the list of components to the answer area and arrange them in the correct order.)
Answer:
Q7. You are developing a SQL Server Integration Services (SSIS) package.
The package uses custom functionality that accesses a SQL Server database. The custom
functionality must be implemented by using Language Integrated Query (LINQ).
You need to ensure that the LINQ code can be debugged at design time.
What should you select from the SSIS Toolbox? (To answer, drag the appropriate item to the correct location in the answer area.)
Answer:
Q8. You administer a Microsoft SQL Server database. You want to import data from a text file to the database.
You need to ensure that the following requirements are met:
. Data import is performed by using a stored procedure.
. Data is loaded as a unit and is minimally logged.
Which data import command and recovery model should you choose? (To answer, drag the appropriate data import command or recovery model to the appropriate location or locations in the answer area. Each data import command or recovery model may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.)
Answer:
Q9. You are developing a SQL Server Integration Services (SSIS) package. The package contains a user-defined variable named .Queue which has an initial value of 10.
The package control flow contains many tasks that must repeat execution until the .Queue variable equals 0.
You need to enable the tasks to be grouped together for repeat execution.
Which item should you add to the package? (To answer, select the appropriate item in the answer area.)
Answer:
Q10. 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?
A. Create a custom SSIS source component that encapsulates the 32-bit driver and compile it in 64-bit mode.
B. Install a 64-bit ACE driver and execute the package by using the 64-bit run-time option.
C. Execute the package by using the 32-bit run-time option.
D. Replace the SSIS Excel source with a SSIS Flat File source.
Answer: C
Explanation: * See step 3 below.
To publish an Excel worksheet to Azure SQL Database, your package will contain a Data
Flow Task, Excel Source task, and ADO NET Destination.
1) Create an SSIS project.
2) Drop a.Data Flow Task.onto the.Control Flow.design surface, and double click the
Data Flow Task.
3) Drop an.Excel Source.onto the Data Flow design surface.
Note.When using the.Excel Source.task on a 64-bit machine, set Run64BitRuntime to
False.
* Incorrect:
Not D: The Flat File source reads data from a text file. The text file can be in delimited,
fixed width, or mixed format.
Q11. You are creating a SQL Server Integration Services (SSIS) package that implements a Type 3 Slowly Changing Dimension (SCD).
You need to add a task or component to the package that allows you to implement the SCD logic.
What should you use?
A. a Data Conversion component
B. an Execute SQL task that executes a MERGE statement on the database
C. a Merge component
D. an Expression task
Answer: C
Explanation: Note: Type of Slowly Changing Dimensions Slowly Changing Dimensions are categorized into three types named: Type 1, Type 2, and Type3. The Type 1 SCD does not maintain the history of changing attributes, it overwrites values of the attributes. Type 2 maintains historical values for changing attributes. Type 3 that we do not use much maintains separate columns for changed attributes. SSIS SCD wizard supports both Type 1 and Type 2. Using MERGE instead of SCD wizard Replacement of SCD wizard with MERGE is not a straightforward technique. If the SCD has both Type 1 and Type 2 types attributes, they need to be handled separately.
Q12. You develop a SQL Server Integration Services (SSIS) package that imports Windows Azure SQL Database data into a data warehouse every night.
The Windows Azure SQL Database 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?
A. Change the similarity threshold to 0.40.
B. Decrease the maximum number of matches per lookup.
C. Change the similarity threshold to 0.85.
D. Increase the maximum number of matches per lookup.
Answer: C
Explanation: * Similarity threshold Set the similarity threshold at the component level by using the slider. The closer the value is to 1, the closer the resemblance of the lookup value to the source value must be to qualify as a match. Increasing the threshold can improve the speed of matching since fewer candidate records need to be considered.
Incorrect:
* Maximum number of matches to output per lookup
Specify the maximum number of matches the transformation can return for each input row.
The default is 1.
Q13. You are reviewing the design of a customer dimension table in an existing data warehouse hosted on SQL Azure.
The current dimension design does not allow the retention of historical changes to customer attributes such as Postcode.
You need to redesign the dimension to enable the full historical reporting of changes to multiple customer attributes including Postcode.
What should you do?
A. Add StartDate and EndDate columns to the customer dimension.
B. Add an IsCurrent column to the customer dimension.
C. Enable Snapshot Isolation on the data warehouse.
D. Add CurrentValue and PreviousValue columns to the customer dimension.
Answer: A
Q14. 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?
A. Run the Data Quality Server Installer.
B. Install the data providers that are used for data refresh.
C. Run the dbimpexp.exe command.
D. Install the Analysis Services OLE DB Provider.
Answer: B
Q15. You are reviewing the design of an existing fact table named factSales, which is loaded from a SQL Azure database by a SQL Server Integration Services (SSIS) package each day. The fact table has approximately 1 billion rows and is dimensioned by product, sales date, and sales time of day.
The database administrator is concerned about the growth of the database. Users report poor reporting performance against this database. Reporting requirements have recently changed and the only remaining report that uses this fact table reports sales by product name, sale month, and sale year. No other reports will be created against this table.
You need to reduce the report processing time and minimize the growth of the database.
What should you do?
A. Partition the table by product type.
B. Create a view over the fact table to aggregate sales by month.
C. Change the granularity of the fact table to month.
D. Create an indexed view over the fact table to aggregate sales by month.
Answer: C
Q16. You are installing SQL Server Data Quality Services (DQS).
You need to give specific users access to the Data Quality Server.
Which SQL Server application should you use?
A. SQL Server Configuration Manager
B. SQL Server Data Tools
C. SQL Server Management Studio
D. Data Quality Client
Answer: C