Question 1
How do you connect multiple Star schemas in a DW? Answer.
You connect multiple Star schemas through shared dimensions.
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.
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.
Question 4
How many columnstore indexes can you have per table? Answer.
You can have one columnstore index per table.
Question 5
Should you use page compression for OLTP environments? Answer.
No, you should use age compression only for data warehousing environments.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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).
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Question 26
How can you modify the value of a system variable? Answer.
System variables are read-only; their values are determined by SSIS.
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.
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.
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.
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.
Question 31
Which SCD types does the Slowly Changing Dimension Wizard support? Answer.
Types 1 and 2
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.
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.
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 systems 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.
Question 35
Can you have multiple checkpoint files for one package? Answer.
No, you can only have one checkpoint file per package.
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.
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.
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).
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.
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.
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).
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.
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.
Question 44
Can log configurations be copied from one SSIS object to another? Answer.
Yes. Log configurations can be exported to files and reapplied.
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.
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.
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.
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.
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).
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.
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.
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.
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.
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).
Question 55
On which levels in the SSISDB catalog are the main predefined reports available? Answer.
On the SSISDB, project, and package levels
Question 56
Which performance counters display information that the data flow has started swapping to disk storage? Answer.
The Buffers Spooled counter
Question 57
Is trust a hard data quality dimension? Answer.
No, trust is a typical soft data quality dimension.
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.
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.
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.
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.
Question 62
Do fact tables in a data warehouse contain master data? 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.
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.
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.
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.
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.
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?
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.
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.
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.
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.
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.
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.
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.
Question 76
When should custom components be used instead of script components? 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.
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.
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.
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.
Question 81
Which SSIS transformation is useful for de-duplication? Answer.
You can use the SSIS Fuzzy Grouping transformation for de-duplication.
