★ Pass on Your First TRY ★ 100% Money Back Guarantee ★ Realistic Practice Exam Questions
Free Instant Download NEW 70-463 Exam Dumps (PDF & VCE):
Available on:
https://www.certleader.com/70-463-dumps.html
It is impossible to pass Microsoft 70-463 exam without any help in the short term. Come to Pass4sure soon and find the most advanced, correct and guaranteed Microsoft 70-463 practice questions. You will get a surprising result by our Up to the immediate present Implementing a Data Warehouse with Microsoft SQL Server 2012 practice guides.
2021 Oct 70-463 vce download:
Q101. 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?
A. Create an Integration Services package and use a Fuzzy Lookup transform.
B. Create a Master Data Services (MDS) model.
C. Create a Data Quality Project.
D. Create an Integration Services package and use a DQS Cleansing transform.
E. Create an Integration Services package and use a Fuzzy Grouping transform.
Answer: D
Explanation: The DQS Cleansing transformation uses Data Quality Services (DQS) to correct data from a connected data source, by applying approved rules that were created for the connected data source or a similar data source. To determine whether the data has to be corrected, the DQS Cleansing transformation processes data from an input column when the following conditions are true:
* The column is selected for data correction.
* The column data type is supported for data correction.
* The column is mapped a domain that has a compatible data type.
* The transformation also includes an error output that you configure to handle row-level errors
Q102. You are designing a data warehouse hosted on SQL Azure. The data warehouse currently includes the dimUser and dimDistrict dimension tables and the factSales fact table. The dimUser table contains records for each user permitted to run reports against the warehouse; and the dimDistrict table contains information about sales districts.
The system is accessed by users from certain districts, as well as by area supervisors and users from the corporate headquarters.
You need to design a table structure to ensure that certain users can see sales data for only certain districts. Some users must be permitted to see sales data from multiple districts.
What should you do?
A. Add a district column to the dimUser table.
B. Partition the factSales table on the district column.
C. Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables.
D. For each district, create a view of the factSales table that includes a WHERE clause for the district.
Answer: C
Q103. Topic 2)
You are the administrator for a Data Quality Server. You are adding a user who must have permission to:
Edit and execute a project
View the activity monitoring data
This user must not be able to:
Perform any kind of knowledge management
Create or change a knowledge base
Terminate an activity or perform administrative duties
You need to develop a Transact-SQL (T-SQL) script to meet these requirements.
What should you do? (To answer, drag the appropriate code segment or segments to the correct location or locations in the answer area.)
Answer:
Q104. 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
Q105. 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?
A. Script task
B. Script component configured as a transformation
C. Script component configured as a source
D. Script component configured as a destination
Answer: C
Renovate 70-463 training kit download:
Q106. 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?
A. 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.
B. 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.
C. 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.
D. 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.
Answer: C
Q107. You are creating a Data Quality Services (DQS) solution. You must provide statistics on the accuracy of the data.
You need to use DQS profiling to obtain the required statistics.
Which DQS activity should you use?
A. Cleansing
B. Matching
C. Knowledge Discovery
D. Matching Policy
Answer: A
Q108. You are developing a SQL Server Integration Services (SSIS) package.
The package uses a data flow task to source data from a SQL Server database for loading into a dimension table in a data warehouse.
You need to create a separate data flow path for data that has been modified since it was last processed.
Which data flow components should you use to identify modified data? (Each correct answer presents a complete solution. Choose all that apply.)
A. Multicast
B. Data Conversion
C. Lookup
D. Slowly Changing Dimension
E. Aggregate
Answer: A,C
Explanation: A: The transformation that distributes data sets to multiple outputs.
The transformation that distributes data sets to multiple outputs.
C: Lookup Transformation
The transformation that looks up values in a reference table using an exact match.
Note:
* 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.
Incorrect: Not B: Data Conversion Transformation The transformation that converts the data type of a column to a different data type. Not D: Slowly Changing Dimension Transformation The transformation that configures the updating of a slowly changing dimension. Not E: The Aggregate transformation applies aggregate functions, such as Average, to column values and copies the results to the transformation output. Besides aggregate functions, the transformation provides the GROUP BY clause, which you can use to specify groups to aggregate across.
Q109. Your company uses a proprietary encryption algorithm to secure sensitive data. A custom cryptographic assembly was developed in Microsoft .NET and is used in several applications.
A SQL Server Integration Services (SSIS) package is importing data into a Windows Azure SQL Database database. Some of the data must be encrypted with the proprietary encryption algorithm.
You need to design the implementation strategy to meet the requirements while minimizing development and deployment effort and maximizing data flow performance.
What should you do?
A. Create a SQL Common Language Runtime (SQLCLR) function that uses the custom assembly to encrypt the data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.
B. Use an SSIS Script transformation that uses the custom assembly to encrypt the data when inserting it.
C. Create a SQL Common Language Runtime (SQLCLR) stored procedure that uses the custom assembly to encrypt the data, deploy it in the Windows Azure SQL Database database, and use it when inserting data.
D. Use an SSIS Script task that uses the custom assembly to encrypt the data when inserting it.
Answer: B
Q110. You are creating a sales data warehouse. When a product exists in the product dimension, you update the product name. When a product does not exist, you insert a new record.
In the current implementation, the DimProduct table must be scanned twice, once for the insert and again for the update. As a result, inserts and updates to the DimProduct table take longer than expected.
You need to create a solution that uses a single command to perform an update and an insert.
How should you use a MERGE T-SQL statement to accomplish this goal? (To answer, drag the appropriate answer choice from the list of options to the correct location or locations in the answer area.
You may need to drag the split bar between panes or scroll to view content.)
Answer: