Implementing Data Warehouse with SQL Server 2012 Interview Questions - Part 1 of 2 (On Comprehensions from TK 70-463)

70-463 Comprehension Questions

Question 1

How do you connect multiple Star schemas in a DW? Answer.

You connect multiple Star schemas through shared dimensions.

Hide the Answer.

Question 2

You are designing an accounting system. Your measures are debit, credit, and balance. What is the additivity of each measure? Answer.

Debit and credit are additive measures, and balance is a semi-additive measure.

Hide the Answer.

Question 3

How can SQL Server help you with values for your surrogate keys? Answer.

SQL Server can autonumber your surrogate keys. You can use the IDENTITY property or sequence objects.

Hide the Answer.

Question 4

How many columnstore indexes can you have per table? Answer.

You can have one columnstore index per table.

Hide the Answer.

Question 5

Should you use page compression for OLTP environments? Answer.

No, you should use age compression only for data warehousing environments.

Hide the Answer.

Question 6

How many partitions can you have per table? Answer.

In SQL Server 2012, you can have up to 15,000 partitions per table.

Hide the Answer.

Question 7

What is the SQL Server Import and Export Wizard? Answer.

The Import and Export Wizard is a utility that provides a simplified interface for developing data movement operations where data is extracted from a source and loaded into a destination, without the need for any transformations.

Hide the Answer.

Question 8

What is the principal difference between simple and complex data movements? Answer.

In simple data movements, data is copied from one data store into another one unmodified, whereas in complex data movements, data is modified (transformed) before being loaded into the destination data store.

Hide the Answer.

Question 9

What is SSDT? Answer.

SQL Server Data Tools (SSDT) is a special edition of Visual Studio used for developing SQL Server 2012 solutions, such as SSIS packages, SSAS multi-dimensional models, and SSRS reports.

Hide the Answer.

Question 10

What is a control flow? Answer.

In SSIS packages, the control flow defines the tasks used in performing data management operations; it determines the order in which these tasks are executed and the conditions of their execution.

Hide the Answer.

Question 11

What is a data flow? Answer.

In SSIS packages, the data flow is a special control flow task used specifically in data movement operations and data transformations.

Hide the Answer.

Question 12

What is the purpose of connection managers in SSIS at design time? Answer.

At design time, connection managers are used by the SSIS developer to configure a connection to a data source.

Hide the Answer.

Question 13

What is the purpose of connection managers in SSIS at run time? Answer.

At run time, connection managers are used by the SSIS engine to establish connections to data sources.

Hide the Answer.

Question 14

How does connection manager scope affect their use? Answer.

A project-scoped connection manager is available to all packages of a particular SSIS project, whereas a package-scoped connection manager is only available to the package in which it was created.

Hide the Answer.

Question 15

What tasks is the Foreach Loop container suited for? Answer.

It is suited for executing a set of operations repeatedly based on an enumerable collection of items (such as files in a folder, a set of rows in a table, or an array of items).

Hide the Answer.

Question 16

How can the current item or its properties be made available to the tasks inside a Foreach Loop container? Answer.

You can assign the values returned by the Foreach Loop container to a variable.

Hide the Answer.

Question 17

Is it possible to change the settings of an SSIS object at run time? Answer.

Yes, it is. Every setting that supports expressions can be modified at run time.

Hide the Answer.

Question 18

Can SSIS execution be redirected from one task to another? Answer.

Yes, by using different conditions in precedence constraints, the order of execution can be directed to the following tasks in one branch or to another branch.

Hide the Answer.

Question 19

Can multiple precedence constraints lead from the same preceding task (i.e., multiple constraints from a single task to the following tasks)? Answer.

Yes, multiple precedence constraints can lead from a single task to the following tasks, but only one precedence constraint can exist between two distinct tasks.

Hide the Answer.

Question 20

What is the principal difference between a success constraint and a completion constraint? Answer.

A success constraint will only allow the process to continue to the following task if the preceding task completed successfully, whereas a completion constraint will allow the process to continue as soon as the preceding task has completed, regardless of the outcome.

Hide the Answer.

Question 21

You need to migrate a user-created Microsoft Access database to SQL Server, but the data flow SSIS Toolbox does not contain an Access source adapter. How do you import this data into SQL Server? Answer.

Although it is not listed in the toolbox, Access is one of the many database sources and destinations that SSIS works with. To extract data from Access, you have three choices: You can use the new ODBC source adapter, create a package connection to the Microsoft Jet OLE DB Provider, or use the OLE DB source adapter.

Hide the Answer.

Question 22

What is the difference between the Union All and the Merge transformation? Answer.

The Merge transformation is similar to Union All, but with Merge, the sources have to be sorted and the sort position is preserved.

Hide the Answer.

Question 23

What is the difference between the Lookup and Merge Join transformations? Answer.

The Lookup transformation does not need a sorted input; it is a non-blocking transformation, and in cases when more matching rows exist from the referenced dataset, only the first one will be retrieved. This means that the transformation will never retrieve more rows than exist in the input rows. With the Merge Join transformation more rows can be retrieved, because all matching data is retrieved.

Hide the Answer.

Question 24

Which SSIS objects can access container-scoped variables? Answer.

Container-scoped variables are only accessible to the container and the SSIS objects it contains.

Hide the Answer.

Question 25

How many namespaces are available for SSIS variables? Answer.

SSIS variables can exist in two namespaces: user variables in the User namespace, and system variables in the System namespace.

Hide the Answer.

Question 26

How can you modify the value of a system variable? Answer.

System variables are read-only; their values are determined by SSIS.

Hide the Answer.

Question 27

Which .NET Framework programming language is used for SSIS expressions? Answer.

SSIS expressions use a special, proprietary expression language that is only available for SSIS development and is therefore not part of the .NET Framework.

Hide the Answer.

Question 28

What are SSIS expressions typically used for? Answer.

SSIS expressions allow you to determine values needed in SSIS execution dynamcally at run time, rather than having to assign constants to them at design time.

Hide the Answer.

Question 29

What is the principal purpose of a master package? Answer.

A master package provides centralized control flow and configuration to SSIS solutions using multiple SSIS packages.

Hide the Answer.

Question 30

Can child package properties be set from the master package? Answer.

Yes, master package parameters and variables can be used to set the child package parameters.

Hide the Answer.

Question 31

Which SCD types does the Slowly Changing Dimension Wizard support? Answer.

Types 1 and 2

Hide the Answer.

Question 32

Which are the new data flow components in SQL Server 2012 for implementing CDC functionality? Answer.

The CDC source, for reading the changed rows, and the CDC splitter, for splitting the input rows into different outputs.

Hide the Answer.

Question 33

Can you add an error flow path to the OLE DB destination adapter to store all rows that could not be loaded into a destination table? Answer.

Yes, this is a very useful feature when you are designing a robust ETL process.

Hide the Answer.

Question 34

Suppose you have a package to which you add a sequence container that contains several tasks, one that calls a command on a legacy system and another that is a data flow task that imports data into SQL Server. Both tasks have the Transaction­ Option property set to Required. Even with the MSDTC service started and trans­ actions turned on, your sequence container fails before tasks even run. What is the problem? Answer.

The transactions featured in SSIS use the MSDTC service. However, not all sys­tems support the MSDTC, and a transaction cannot be forced on a noncompliant system, so the container will fail. You should remove the legacy task from the sequence container or set the TransactionOption property to Not Supported.

Hide the Answer.

Question 35

Can you have multiple checkpoint files for one package? Answer.

No, you can only have one checkpoint file per package.

Hide the Answer.

Question 36

Can you use a data flow task for a specific event handler? Answer.

Yes. You can use all the tasks from the SSIS Toolbox.

Hide the Answer.

Question 37

When are property expressions evaluated as a package is running? Answer.

Unlike parameters that are read at the start of package execution, property expressions are updated when the property is accessed by the package during package execution. A property expression can change the value of a property in the middle of package execution, so that the new value is read when the property is needed by the package.

Hide the Answer.

Question 38

How are SSIS events propagated to the environment? Answer.

SSIS events can be consumed by log providers, which send them to destinations in the environment (such as tables in a database, files in the file system, or operating system event handlers).

Hide the Answer.

Question 39

Which SSIS object can be configured for logging? Answer.

Any SSIS package executable (control flow container or task) can be configured for logging, including the package itself.

Hide the Answer.

Question 40

Can SSIS variables create log entries? Answer.

Variables can be configured to trigger an event when their values change, and this event can then be propagated to a log provider.

Hide the Answer.

Question 41

Does the OnError event generated by a data flow task contain enough information to identify the erroneous rows? Answer.

The quantity and quality of information available in an event depend on its origin (for example, SSIS run time, data providers, or database management systems).

Hide the Answer.

Question 42

Is it possible to correlate log entries generated by SSIS events with data generated by SSIS auditing? Answer.

Yes, both logging as well as auditing can be configured to generate information that can be used to correlate log entries generated by an SSIS executable with the corresponding audit entries.

Hide the Answer.

Question 43

What is an SSIS package template? Answer.

An SSIS package template is a regular SSIS package that has been stored in a specific location and can be used by SSDT when a developer adds new items to an SSIS project.

Hide the Answer.

Question 44

Can log configurations be copied from one SSIS object to another? Answer.

Yes. Log configurations can be exported to files and reapplied.

Hide the Answer.

Question 45

What are SQL Server Integration Services (SSIS)? Answer.

SSIS is a feature of SQL Server that hosts SSIS deployment, maintenance, execu- tion, and monitoring.

Hide the Answer.

Question 46

How can SSIS be installed? Answer.

SSIS can be installed together with other SQL Server features, added to an existing SQL Server installation, or used to upgrade an earlier version of SSIS.

Hide the Answer.

Question 47

What is SSISDB? Answer.

SSISDB is a special database provided by SQL Server 2012 to be used as the principal SSIS solution repository.

Hide the Answer.

Question 48

What is SSIS Server? Answer.

SSIS server is a name used to refer to an instance of SQL Server hosting the SSISDB catalog. Any instance of SQL Server 2012 can be used as the SSIS server, except SQL Server Express.

Hide the Answer.

Question 49

How can SSIS projects be deployed? Answer.

Under the project deployment model, SSIS project deployment is integrated into SQL Server Data Tools (SSDT), as well as SQL Server Management Studio (SSMS).

Hide the Answer.

Question 50

Is it possible to execute a deployed SSIS package manually? Answer.

Yes. After deployment, SSIS packages can be executed on demand by using SSMS or by using the DTExec command-line utility.

Hide the Answer.

Question 51

How can SSIS packages be executed programmatically? Answer.

Programmatic access to deployed SSIS packages is possible through Transact-SQL, Windows PowerShell, and the SSIS managed API.

Hide the Answer.

Question 52

Is it possible to monitor SSIS executions? Answer.

Yes. The execution of SSIS processes can be monitored by using the Active Operations Viewer in SSMS, as well as by using a variety of standard built-in reports. Additionally, custom reports can be developed, and integrated with SSMS.

Hide the Answer.

Question 53

Who can access SSISDB objects after they have been deployed? Answer.

By default, access to SSISDB objects is limited to the users who created them and the members of the ssis_admin database role. Of course, for any user other than the SSISDB database owner to be able to create SSISDB objects, the administrator must first allow that user access to the SSISDB catalog.

Hide the Answer.

Question 54

How can permissions on various SSISDB objects be controlled? Answer.

Permissions can be controlled explicitly on folders, projects, and environments, but not on packages, environment references, or variables. Permissions on the latter are inherited from the containing object (the project or the environment to which the object belongs).

Hide the Answer.

Question 55

On which levels in the SSISDB catalog are the main predefined reports available? Answer.

On the SSISDB, project, and package levels

Hide the Answer.

Question 56

Which performance counters display information that the data flow has started swapping to disk storage? Answer.

The Buffers Spooled counter

Hide the Answer.

Question 57

Is trust a hard data quality dimension? Answer.

No, trust is a typical soft data quality dimension.

Hide the Answer.

Question 58

What are the prerequisites for installing Data Quality Server? Answer.

SQL Server 2012 Database Engine Services are needed for Data Quality Server. In addition, it is highly recommended that you install Management Tools as well.

Hide the Answer.

Question 59

What are the prerequisites for installing Data Quality Client? Answer.

For Data Quality Client, you need to have the .NET Framework 4 and at least Internet Explorer 6 SP1 installed on your computer. The .NET Framework 4 is installed automatically during Data Quality Client setup if it was not already installed on the machine.

Hide the Answer.

Question 60

How can you delete old DQS log files? Answer.

You have to use Windows Explorer to delete old log files. There is no user interface for this task in Data Quality Client or SQL Server Management Studio.

Hide the Answer.

Question 61

Are constraints in a relational database enough to enforce data accuracy? Answer.

No, constraints enforce data integrity; they cannot enforce data accuracy as well.

Hide the Answer.

Question 62

Do fact tables in a data warehouse contain master data? Answer.

No, fact tables in a data warehouse typically contain transactional data, which is usually not considered to be master data.

Hide the Answer.

Question 63

How can you export data from your MDS database? Answer.

You can query the subscription views you create, or you can integrate your application directly to Master Data Services through the Master Data Manager web service.

Hide the Answer.

Question 64

You want to improve the quality of your master data. You want to prevent the insertion of inaccurate values into an attribute, and furthermore, you want to notify a data steward responsible for this attribute when an inaccurate value is inserted. How can you achieve these two tasks? Answer.

You can create one or more change value and/or validation business rules to prevent the insertion of inaccurate values or to correct inaccurate values automatically. You can create a business rule for an external action such as sending an email message in order to notify the data steward responsible for the attribute.

Hide the Answer.

Question 65

Can you deploy an existing MDS model to another MDS instance with data included? Answer.

Yes. You have to extract the package with the MDSModelDeploy command.

Hide the Answer.

Question 66

A user is a member of two groups. You give Read-Only permission for an object to the first group, Update permission for the same object to the second group, and Read-Only permission for the same object to the user. What effective permission does the user have for that object? Answer.

The user has the Update effective permission for that object.

Hide the Answer.

Question 67

How can you update batches of MDS data without using the staging process? Answer.

Use Excel 2012 with MDS Add-in for Excel to update batches of data in an MDS model.

Hide the Answer.

Question 68

You want to use a knowledge base that exists in one DQS instance in another DQS instance. Should you re-create the knowledge base on the second DQS instance manually? Answer.

You want to use a knowledge base that exists in one DQS instance in another DQS instance. Should you re-create the knowledge base on the second DQS instance manually?

Hide the Answer.

Question 69

Which are the two cleansing phases of a DQS project? Answer.

The two cleansing phases of a DQS project are computer-assisted cleansing and interactive cleansing.

Hide the Answer.

Question 70

Can you use the SSIS Data Profiling task to cleanse your data? Answer.

No, with the SSIS Data Profiling task you can only profile your data.

Hide the Answer.

Question 71

How do you perform a prediction by using a data mining model? Answer.

You need to create a DMX prediction query that joins patterns stored in an SSAS mining model with your data.

Hide the Answer.

Question 72

Which languages are supported by the two text mining transformations? Answer.

Both the Term Extraction and Term Lookup transformations are limited to the English language only.

Hide the Answer.

Question 73

How can you select a predefined number of rows randomly? Answer.

You can use the SSIS Row Sampling transformation to select a predefined number of rows randomly.

Hide the Answer.

Question 74

Can you log the execution of your script in the script task? Answer.

Yes, you can use the Log property of the Dts object to log the execution of your script.

Hide the Answer.

Question 75

What does it mean if the value of the SynchronousInputID property of a script component is the component’s input ID? Answer.

This means that the component returns synchronous outputs.

Hide the Answer.

Question 76

When should custom components be used instead of script components? Answer.

Generally, the number-one reason for using custom components is reusability. If the same custom operation needs to be implemented in multiple SSIS packages, custom components with package-independent development, deployment, and maintenance capabilities provide a more appropriate alternative.

Hide the Answer.

Question 77

When is it necessary to use asynchronous outputs? Answer.

Whenever a data transformation operation produces zero, one, or more rows per each input row, asynchronous outputs need to be used, because additional rows cannot be added to a synchronous output, nor can they be removed from them.

Hide the Answer.

Question 78

Why do custom components need design-time programmatic logic? Answer.

Before being used in an executing SSIS package to perform the principal data management operation, each custom component is used by the SSIS package developer—it needs to be placed in the data flow, configured, and validated.

Hide the Answer.

Question 79

Which two SSIS transformations are useful for identity mapping and de-duplication? Answer.

The SSIS Fuzzy Lookup transformation is useful for identity mapping, and the SSIS Fuzzy Grouping transformation is useful for de-duplication.

Hide the Answer.

Question 80

How can you influence which column in a table should be more important for matching than other columns? Answer.

When creating a matching KB, you can define weight for each domain. Give higher weight to the domain mapped to the column you want to be more important for matching.

Hide the Answer.

Question 81

Which SSIS transformation is useful for de-duplication? Answer.

You can use the SSIS Fuzzy Grouping transformation for de-duplication.

Hide the Answer.