Implementing a Data Warehouse using SQL
Question No: 51 – (Topic 1)
You are developing a project that contains multiple SQL Server Integration Services (SSIS) packages. The packages will be deployed to the SSIS catalog. One of the steps in each package accesses an FTP site to download sales transaction data.
You create project parameters to store the username and password that are used to access the FTP site.
You need to ensure that the username and password values are encrypted when they are deployed.
What should you do?
Set the Sensitive property of the parameters to True.
Set the ProtectionLevel property of the package to EncryptSensitiveWithUserKey.
Change the parameters to package parameters.
Change the project to the Legacy Deployment model.
Question No: 52 – (Topic 1)
You are using the Knowledge Discovery feature of the Data Quality Services (DQS) client application to modify an existing knowledge base.
In the mapping configuration, two of the three columns are mapped to existing domains in the knowledge base. The third column, named Team Type, does not yet have a domain.
You need to complete the mapping of the Team Type column. What should you do?
Add a column mapping for the Team Type column.
Map a composite domain to the source column.
Create a composite domain that includes the Team Type column.
Add a domain for the Team Type column.
Question No: 53 DRAG DROP – (Topic 1)
A SQL Server Integration Services (SSIS) project has been deployed to the SSIS catalog. The project includes a project Connection Manager to connect to the data warehouse.
The SSIS catalog includes two Environments:
Each Environment defines a single Environment Variable named ConnectionString of type string. The value of each variable consists of the connection string to the development or QA data warehouses.
You need to be able to execute deployed packages by using either of the defined Environments.
Which three actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Question No: 54 – (Topic 1)
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?
Add StartDate and EndDate columns to the customer dimension.
Add an IsCurrent column to the customer dimension.
Enable Snapshot Isolation on the data warehouse.
Add CurrentValue and PreviousValue columns to the customer dimension.
Question No: 55 – (Topic 1)
You are designing a SQL Server Integration Services (SSIS) package that uploads a file to a table named Orders in a SQL Azure database.
The company#39;s auditing policies have the following requirements:
鈥n entry must be written to a dedicated SQL Server log table named OrderLog.
鈥he entry must be written as soon as the file upload task completes. You need to meet the company#39;s policy requirements.
Which event handler should you use?
Question No: 56 – (Topic 1)
You are implementing the indexing strategy for a fact table in a data warehouse. The fact table is named Quotes. The table has no indexes and consists of seven columns:
Each of the following queries must be able to use a columnstore index:
鈥ELECT AVG ([Close]) AS [AverageClose] FROM Quotes WHERE [QuoteDate] BETWEEN #39;20100101#39; AND #39;20101231#39;.
鈥ELECT AVG([High] – [Low]) AS [AverageRange] FROM Quotes WHERE [QuoteDate] BETWEEN #39;20100101#39; AND #39;20101231#39;.
鈥ELECT SUM([Volume]) AS [SumVolume] FROM Quotes WHERE [QuoteDate] BETWEEN #39;20100101#39; AND #39;20101231#39;.
You need to ensure that the indexing strategy meets the requirements. The strategy must also minimize the number and size of the indexes.
What should you do?
Create one columnstore index that contains [ID], [Close], [High], [Low], [Volume], and [QuoteDate].
Create three coiumnstore indexes: One containing [QuoteDate] and [Close]
One containing [QuoteDate], [High], and [Low] One containing [QuoteDate] and [Volume]
Create one columnstore index that contains [QuoteDate], [Close], [High], [Low], and [Volume].
Create two columnstore indexes:
One containing [ID], [QuoteDate], [Volume], and [Close] One containing [ID], [QuoteDate], [High], and [Low]
Question No: 57 – (Topic 1)
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?
Partition the table by product type.
Create a view over the fact table to aggregate sales by month.
Change the granularity of the fact table to month.
Create an indexed view over the fact table to aggregate sales by month.
Question No: 58 – (Topic 1)
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?
Add a district column to the dimUser table.
Partition the factSales table on the district column.
Create a userDistrict table that contains primary key columns from the dimUser and dimDistrict tables.
For each district, create a view of the factSales table that includes a WHERE clause for the district.
Question No: 59 DRAG DROP – (Topic 1)
You are designing an extract, transform, load (ETL) process with SQL Server Integration Services (SSIS). Two packages, Package A and Package B, will be designed. Package A will execute Package B.
Both packages must reference a file path corresponding to an input folder where files will be located for further processing.
You need to design a solution so that the file path can be easily configured with the least administrative and development effort.
Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Question No: 60 – (Topic 1)
You are creating a SQL Server Master Data Services (MDS) model for a company.
The source data for the company is stored in a single table that contains the manager-to- subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company. Which hierarchy type should you use?
Reference: http://technet.microsoft.com/en-us/library/ff487006.aspx Reference: http://msdn.microsoft.com/en-us/library/ee633747.aspx Reference: http://technet.microsoft.com/en-us/library/ee633759.aspx
|Lowest Price Guarantee||Yes||No||No|
|Free VCE Simulator||Yes||No||No|