Q1. You are designing an enterprise star schema that will consolidate data from three independent data marts. One of the data marts is hosted on SQL Azure.

Most of the dimensions have the same structure and content. However, the geography dimension is slightly different in each data mart.

You need to design a consolidated dimensional structure that will be easy to maintain while ensuring that all dimensional data from the three original solutions is represented.

What should you do?

A. Create a conformed dimension for the geography dimension.

B. Implement change tracking.

C. Create a degenerate dimension for the geography dimension.

D. Create a Type 2 slowly changing dimension for the geography dimension.

View Answer

Q2. DRAG DROP

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:

•New rows must be inserted.

•Lookup 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.)

View Answer

Q3. You administer a Microsoft SQL Server 2021 server that has SQL Server Integration Services (SSIS) installed.

You plan to deploy new SSIS packages to the server. The SSIS packages use the Project Deployment Model together with parameters and Integration Services environment variables.

You need to configure the SQL Server environment to support these packages. What should you do?

A. Create SSIS configuration files for the packages.

B. Create an Integration Services catalog.

C. Install Data Quality Services.

D. Install Master Data services.

View Answer

Q4. 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.

View Answer

Q5. 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.

View Answer

Q6. You are adding a new capability to several dozen SQL Server Integration Services (SSIS) packages.

The new capability is not available as an SSIS task. Each package must be extended with the same new capability.

You need to add the new capability to all the packages without copying the code between packages.

What should you do?

A. Use the Expression task.

B. Use the Script task.

C. Develop a custom task.

D. Use the Script component,

E. Develop a custom component.

View Answer

Q7. DRAG DROP

A SQL Server Integration Services (SSIS) package named DataFeed interacts with an external vendor data feed. The package is executed several times a day, either as part of other packages' control flow or by itself. The external data feed is unreliable because network failures and slow response times are frequent. The package is currently deployed on the file system.

To analyze the reliability of the external data feed, you must collect execution data. Every time the DataFeed package is executed, the following information must be logged:

✑ Start Time

✑ End Time

✑ Execution Result

✑ Execution Duration

You need to design a logging solution that meets the requirements by using the least amount of administrative and development effort.

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.)

View Answer

Q8. A SQL Server Integration Services (SSIS) package imports daily transactions from several files into a SQL Server table named Transaction. Each file corresponds to a different store and is imported in parallel with the other files. The data flow tasks use OLE DB destinations in fast load data access mode.

The number of daily transactions per store can be very large and is growing. The Transaction table does not have any indexes.

You need to minimize the package execution time. What should you do?

A. Partition the table by day and store.

B. Create a clustered index on the Transaction table.

C. Run the package in Performance mode.

D. Increase the value of the Row per Batch property.

View Answer

Q9. You are deploying a new SQL Server Integration Services (SSIS) project to the test environment.

A package in the project uses a custom task component.

You need to ensure that the custom object is deployed on the test environment correctly. What should you do?

A. Run the package by using the dtexec /rep /conn command.

B. Create a reusable custom logging component.

C. Create an OnError event handler.

D. Use the gacutil command.

E. Use the dtutil /copy command.

F. Deploy the package to the Integration Services catalog by using dtutil and use SQL Server to store the configuration.

G. Run the package by using the dtexec /dumperror /conn command.

H. Use the Project Deployment Wizard.

I. Deploy the package by using an msi file.

J. Add a data tap on the output of a component in the package data flow.

K. Run the package by using the dtexecui.exe utility and the SQL Log provider.

View Answer

Q10. DRAG DROP

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:

✑  Development

✑ QA

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.)

View Answer