Download New Updated (Spring 2015) Microsoft 70-448 Actual Tests 121-130

Ensurepass

 

QUESTION 121

You are a database developer and you have about two years experience in creating business Intelligence (BI) by using SQL Server 2008. Now you are employed in a company named NaproStar which uses SQL Server 2008. You work as the technical support. According to the requirement of the company CIO, you design a SQL Server 2008 Integration Services (SSIS) data flow. The data flow inserts data from a source query to a destination table. But now you must make sure that only those rows that do not already exist in the destination table are inserted. So what should you do to accomplish this task? (choose more than one)

 

A.

In order to accomplish this task, you can use the Fuzzy Lookup transformation

B.

In order to accomplish this task, you can use the Fuzzy Grouping transformation

C.

In order to accomplish this task, you can use the Merge transformation

D.

In order to accomplish this task, you can use the Lookup transformation

E.

In order to accomplish this task, you can use the Union All transformation

F.

In order to accomplish this task, you can use the Merge Join transformation

 

Correct Answer: DF

Explanation:

Lookup Transformation

The Lookup transformation performs lookups by joining data in input columns with columns in a reference dataset. You use the lookup to access additional information in a related table that is based on values in common columns.

 

The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL query. The Lookup transformation uses either an OLE DB connection manager or a Cache connection manager to connect to the reference dataset. For more information, see OLE DB Connection Manager and Cache

 

Connection Manager

Integration Services and SQL Server differ in the way they compare strings. If the Lookup transformation is configured to load the reference dataset into cache before the Lookup transformation runs, Integration Services does the lookup comparison in the cache. Otherwise, the lookup operation uses a parameterized SQL statement and SQL Server does the lookup comparison. This means that the Lookup transformation might return a different number of matches from the same lookup table depending on the cache type. Merge Join Transformation The Merge Join transformation provides an output that is generated by joining two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured. The result is a table that lists all products and their country/region of origin. For more information, see Using Joins. You can configure the Merge Join transformation in the following ways:

 

* Specify the join is a FULL, LEFT, or INNER join.

* Specify the columns the join uses.

* Specify whether the transformation handles null values as equal to other nulls.

 

Note: If null values are not treated as equal values, the transformation handles null values like the SQL Server Database Engine does.

This transformation has two inputs and one output. It does not support an error output.

Input Requirements

The Merge Join Transformation requires sorted data for its inputs. For more information about this important requirement, see How to: Sort Data for the Merge and Merge Join Transformations.

 

Join Requirements

The Merge Join transformation requires that the joined columns have matching metadata. For example, you cannot join a column that has a numeric data type with a column that has a character data type. If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged.

 

 

QUESTION 122

You are a database developer and you have about two years experience in creating business Intelligence (BI) by using SQL Server 2008. Now you are employed in a company named NaproStar which uses SQL Server 2008. You work as the technical support. You make modification on a SQL Server 2008 Integration Services (SSIS) package. Look at the following SQL Server 2008 table. The package loads the data extracted from a text file to this table. Now you receive an order from your company CIO, the package has to be updated to extract data from multiple text files of the same directory. In order to achieve this goal, what should you do? (Choose more than one)

 

A.

An expression should be added to the Flat File connection manager

B.

A variable should be added to the package

C.

A For Loop container should be added to the package

D.

A variable should be added to the data flow task

E.

Ad Foreach Loop container should be added to the package

F.

An expression should be added to the Flat File Source component

 

Correct Answer: ABE

 

 

QUESTION 123

You are a database developer and you have about two years experience in creating business Intelligence (BI) by using SQL Server 2008. Now you are employed in a company named NaproStar which uses SQL Server 2008. You work as the technical support. Now you receive an e-mail from your company CIO, in the e-mail, the CIO assigns a task to you. You use SQL Server 2008 Reporting Services (SSRS) to create a solution. There is a dimension named Number and a time dimension named Day. A column named PresentBalance is contained in a fact table. PresentBalance contains the current account balance. A measure named NumberBalance has to be created from the PresentBalance column. The NumberBalance is used to display the closing account balance for a time period. So what action should you perform?

 

A.

The AggregationFunction property of NumberBalance should be set to FirstNonEmpty

B.

The AggregationFunction property of NumberBalance should be set to Sum

C.

The AggregationFunction property of NumberBalance should be set to ByAccount

D.

The AggregationFunction property of NumberBalance should be set to LastNonEmpty

 

Correct Answer: D

Explanation:

EXAM TIP

Development and Enterprise editions of SSAS provide several functions–such as LastChild, LastNonEmpty, and AverageOfChildren–that help you handle semiadditive measures. for example, LastNonEmpty will let you get the closing inventory balance for a given time period that is not empty. Although you can use custom MDX expressions to aggregate semiadditive measures, you will get better performance if you use these standard functions. for the complete list of standard aggregation functions, see the topic “Configuring Measure Properties” in SQL Server 2008 Books online (see References).

 

 

QUESTION 124

You are a database developer and you have about two years experience in reating business Intelligence (BI) by using SQL Server 2008. Now you are employed in a company named NaproStar which uses SQL Server 2008. You work as the technical support. Now you receive an e-mail from your company CIO, in the e-mail, the CIO assigns a task to you. You use SQL Server 2008 Reporting Services (SSRS) to create a solution. Your solution includes a time dimension named DimDay. And the DimDay dimension contains a hierarchy named Yr-Qtr-Mth. There is a measure group named Measures01 which contains a measure named Number. Now a calculated member has to be created, then it will be used to display the Number measure in the previous time period for the current level of the Yr-Qtr-Mth hierarchy. In the following options, which expression should be used?

 

A.

([Measures01].[Number],OPENINGPERIOD( [DimDay].[Yr-Qtr-Mth].FirstSibling.Level))

B.

([Measures01].[Number],LASTPERIODS( 0,[DimDay].[Yr-Qtr-Mth].PrevMember))

C.

([Measures01].[Number],[DimDay].[Yr-Qtr-Mth].PrevMember)

D.

([Measures01].[Number],[DimDay].[Yr-Qtr-Mth].FirstSibling)

 

Correct Answer: C

Explanation:

Functions for navigating Hierarchies

MDX provides functions such as PrevMember, Children, and Parent for navigating hierarchies.

PrevMember takes a member and returns a previous member in the hierarchy. For example, [Order Date]. [Calendar].PrevMember returns the previous member with respect to the current member of the Calendar hierarchy. So if the Calendar hierarchy is expanded to the Year level and the current cell is referenced by year 2004, PrevMember will return year 2003.

 

EXAM TIP

The PrevMember function is frequently used to defi ne the KPI Trend or Goal properties. for example, the Reseller Sales KPI uses the following expression to set the Goal property to 40 percent more than the Reseller Sales Amount for the previous date period: 1.40 * ([Date].[Calendar].PrevMember, [Measures].[Reseller Sales Amount])

Consequently, if the user browses the cube data by years, the PrevMember function will return the previous year for each year. If the user browses data by quarters, the PrevMember will return the previous quarter for each quarter, and so on.

 

 

QUESTION 125

You are a database developer and you have about two years experience in creating business Intelligence (BI) by using SQL Server 2008. Now you are employed in a company named NaproStar which uses SQL Server 2008. You work as the technical support. Now you receive an e-mail from your company CIO, in the e-mail, the CIO assigns a task to you. You use SQL Server 2008 Reporting Services (SSRS) to create a solution. You enable proactive caching for a partition. A data source view (DSV) is added to the solution. The DSV contains a table which is named Worker. The table has two columns: WorkerKey and ExecutiveKey. The ExecutiveKey column references the executive of the workers. In the DSV, you have to define the relationship between the employees and their manager. What action should you perform?

 

A.

You should use WorkerKey as the source column and ExecutiveKey as the destination column to create a relationship.

B.

A Named Calculation should be created. The Name Calculaton uses WorkerKey and ExecutiveKey.

C.

You should use ExecutiveKey as the source column and WorkerKey as the destination column to create a relationship.

D.

A Named Query should be created. The Named Query uses a relationship with WorkerKey as the source column and ExecutiveKey as the destination column.

Correct Answer: C

Explanation:

To create a new attribute relationship, follow these steps:

1. In the Attributes pane, right-click the source attribute that is on the “one” side of the relationship, and then select New Attribute Relationship.

2. Configure the relationship by using the Create Attribute Relationship dialog box Make sure that the Name drop-down list below Source Attribute shows the attribute that is on the “many” side of the relationship and that the Name drop-down list below Related Attribute shows the attribute on the “one” side of the relationship. Use the Relationship Type drop-down list to specify a Flexible or Rigid relationship type. You can also create a new relationship in the Dimension Designer by dragging the source attribute onto the related attribute in the Diagram pane.

To delete an attribute relationship, select the relationship in the Attribute Relationships pane (or click the arrow connector in the Diagram pane) and then press Delete,

 

 

QUESTION 126

You are a database developer and you have about two years experience in creating business Intelligence (BI)by using SQL Server 2008. Now you are employed in a company named NaproStar which uses SQL Server 2008. Now you are in charge of a SQL Server 2008 Analysis Services (SSAS) instance. There is a database in the instance. After the DataDir property of the instance is changed to a new folder, you must make sure that the instance uses the new folder with as little downtime as possible. So what action should you perform?

 

A.

You have to synchronize the database.

B.

You have to process the database.

C.

After you delete the files in the original folder, you have to restart the Analysis Services service.

D.

You have to shut down the Analysis Services service, and then restart it.

 

Correct Answer: B

 

 

QUESTION 127

You are a database developer and you have about two years experience in creating business Intelligence (BI) by using SQL Server 2008. Now you are employed in a company named NaproStar which uses SQL Server 2008. You work as the technical support. Now you receive an e-mail from your company CIO, in the e-mail, the CIO assigns a task to you. You use SQL Server 2008 Reporting Services (SSRS) to create a solution. There is a time dimension named DimDay in the solution. There is a table named FinishedSales. This table contains two columns both of which refer to the DimDay dimension. The two columns are respectively named BillDate and MailDate. Now you get an e-mail from your company CIO, in the e-mail the CIO posts a requirement. The DimDay dimension must be able to be used to browse through measures in the FinishedSales table for both BillDate and MailDate. You have to ensure this. So what action should you perform?

 

A.

Between the DimDay dimension and the FinishedSales measure group, a many-to-many relationship should be created.

B.

Between the DimDay dimension and the FinishedSales measure group, a reference relationship should be created.

C.

Between the DimDay dimension and the FinishedSales measure group, two regular relationships should be created. After you create the two relationships, you should use different measure group columns.

D.

Between the DimDay dimension and the FinishedSales measure group, two regular relationships should be created. After you create the two relationships, you should use identical measure group columns.

 

Correct Answer: C

 

 

QUESTION 128

You are a database developer and you have about two years experience in creating business Intelligence (BI) by using SQL Server 2008. Now you are employed in a company named NaproStar which uses SQL Server 2008. You work as the technical support. Now you receive an e-mail from your company CIO, in the e-mail, the CIO assigns a task to you. You use SQL Server 2008 Reporting Services (SSRS) to create a solution. The solution contains a dimension named DimDay and a measure group named FinishedSales. The DimDay dimension contains an attribute which is named Week. The FinishedSales references the DimDay dimension. According to the requirement of the company CIO, you must make sure that the aggregations designed on FinishedSales always include the Week attribute. So what action should be performed?

 

A.

You should set the Usage property appropriately

B.

You should set the IsAggregatable property appropriately

C.

You should set the AggregationUsage property appropriately

D.

You should set the AttributeHierarchyEnabled property appropriately

 

Correct Answer: C

Explanation:

Review Aggregation Usage (Aggregation Design Wizard)

SQL Server 2008 R2 Other Versions

Use the Review Aggregation Usage page to configure aggregation usage settings.

Options

Default: Select to set the aggregation usage setting for the attribute to Default. By using this setting, the designer applies a default rule based on the type of attribute and dimension.

Full: Select to set the aggregation usage setting for the attribute to Full. By using this setting, every aggregation for the cube must include this attribute or a related attribute that is lower in the attribute chain. The Full aggregation usage setting should be avoided when an attribute contains many members. If specified for multiple attributes or attributes that have many members, this setting might prevent aggregations from being designed because of excessive size.

None: Select to set the aggregation usage setting for the attribute to None. By using this setting, no aggregation for the cube can include this attribute.

Unrestricted: Select to set the aggregation usage setting for the attribute to Unrestricted. By using this setting, no restrictions are put on the aggregation designer; however, the attribute must still be evaluated to determine whether it is a valuable aggregation candidate.

Set All to Default: Select to set the aggregation usage settings for all attributes to Default. The presence of an (All) level in an attribute hierarchy depends on the IsAggregatable property setting for the attribute and the presence of an (All) level in a user-defined hierarchy depends on the IsAggregatable property of the attribute at the top-most level of user-defined hierarchy.

If the IsAggregatable property is set to True, an (All) level will exist. A hierarchy has no (All) level if the IsAggregatable property is set to False.

 

 

 

 

 

 

 

 

 

 

 

 

QUESTION 129

You are a database developer and you have about two years experience in creating business Intelligence (BI) by using SQL Server 2008. Now you are employed in a company named NaproStar which uses SQL Server 2008. You work as the technical support. Now you receive an e-mail from your company CIO, in the e-mail, the CIO assigns a task to you. You use SQL Server 2008 Reporting Services (SSRS) to create a solution. There is a table in the solution. The table is named FinishedBills. The table contains two columns respectively named BillKey and ClientKey. You have to create a measure, the number of customers who have placed bills is returned by the measure. So what action should you perform?

 

A.

The Usage property should be set to Count of rows and the Source Column property should be set to OrderKey.

B.

The Usage property should be set to Count of rows and the Source Column property should be set to CustomerKey.

C.

The Usage property should be set to Distinct count and the Source Column property should be set to OrderKey.

D.

The Usage property should be set to Distinct count and the Source Column property should be set to CustomerKey

 

Correct Answer: D

 

 

QUESTION 130

You are a database developer and you have about two years experience in creating business Intelligence (BI) by using SQL Server2008. Now you are employed in a company named NaproStar which uses SQL Server2008. You work as the technical support. Now you receive an e-mail from your company CIO, in the e-mail, the CIO assigns a task to you. You use SQL Server 2008 Reporting Services (SSRS) to create a project. Data from a SQL Server 2005 Analysis Services database will be displayed by the report. According to the requirement of the company CIO, you must make sure that member properties and extended properties are displayed by the report. So what should you do?

 

A.

The SQL Server Analysis Services data source should be used.

B.

The SQL Server data source should be used

C.

The OLE DB for SQL Server data source should be used.

D.

The OLE DB for the Analysis Services 8.0 data source should be used

 

Correct Answer: A

 

Free VCE & PDF File for Microsoft 70-448 Real Exam

Instant Access to Free VCE Files: MCSE|MCSA|MCITP…
Instant Access to Free PDF Files: MCSE|MCSA|MCITP…