PrepAway - Latest Free Exam Questions & Answers

Category: 70-452

Exam 70-452: PRO: Designing a Business Intelligence Infrastructure Using Microsoft SQL Server 2008

Which code segment should you use to replace the WITH clause in the MDX query?

You design a SQL Server 2008 Analysis Services (SSAS) solution. Your solution has a date dimension named Date and measures named Sales Amount and Total Product Cost.
You want to create a calculated measure named Profit. You also want to calculate the differences between the first half and second half of the year for all the measures. You run the following Multidimensional Expressions (MDX) query:
WITH
MEMBER [Measures].[Profit] AS
([Measures].[Sales Amount] – [Measures].[Total Product Cost])/[Measures].[Sales Amount], Format_String = "Percent"
MEMBER [Date].[Fiscal Semester of Year].[Half Year Difference] AS [Date].[Fiscal Semester of Year].[FY H2] – [Date].[Fiscal Semester of Year].[FY H1]
SELECT
{ [Measures].[Sales Amount], [Measures].[Total Product Cost], [Measures].[Profit] } ON COLUMNS, { [Date].[Fiscal Semester of Year].[FY H1], [Date].[Fiscal Semester of Year].[FY H2], [Date].[Fiscal Semester of Year].[Half Year Difference] } ON ROWS
FROM [Adventure Works]
The Profit calculated measure calculates an incorrect value as shown in the exhibit. (Click the Exhibit button.)

You need to ensure that the MDX query calculates the correct value. Which code segment should you use to replace the WITH clause in the MDX query?

What should you do to prepare the cube to define currency conversion?

You design a SQL Server 2008 Analysis Services (SSAS) solution. Your solution has a cube. The structure of the cube is as shown in the exhibit. (Click the Exhibit button.)
Each branch does transactions by using the local currency of the country in which it is located. Daily exchange rates for all local currencies are recorded against the U.S. dollar in the FactCurrencyRate measure group.
All transactions must be reported in U.S. dollars.
You need to prepare the cube to define currency conversion.
What should you do?

Exhibit:

Which set of relationships should you use?

You design a Business Intelligence (BI) solution by using SQL Server 2008. You create a SQL Server 2008 Analysis Services (SSAS) solution by using SQL Server 2008. The solution contains a dimension named DimProduct. The DimProduct dimension contains attributes named Product, Color, Sub-Category, and Category. The Product attribute is the key attribute for DimProduct.
A sample data set of the solution is as shown in the following table.

Product
Color
Sub-Category
Category
A001
Blue
Jeans
Clothing
A002
Red
Jeans
Clothing
A003
Yellow
Couch
Furniture
A004
Red
T-shirt
Clothing
A005
Black
Chair
Furniture

You discover that the DimProduct dimension has performance issues. You need to design attribute relationships on the DimProduct dimension for optimal performance.
Which set of relationships should you use?

What should you do to ensure the following business requirements?

You design a Business Intelligence solution by using SQL Server 2008. The solution includes a SQL Server 2008 Analysis Services (SSAS) database. You design a data mining structure. The structure is used by a data mining model that predicts the expected growth for a particular market location. The model also populates a data mining dimension named Market Location.
The database includes a cube that contains a calculated member named Predicted Revenue. The calculated member uses predictions from the data mining model.
You have the following business requirements:
The view shown in the following exhibit must be displayed to consultants. (Click the Exhibit button.)

The view shown in the following exhibit must be displayed to managers. (Click the Exhibit button.)

You need to design a solution that meets the business requirements. What should you do?

What should you do to ensure that users are able to create the necessary reports?

You design a Business Intelligence (BI) solution by using SQL Server 2008. Users have no specialized knowledge of the Transact-SQL (T-SQL) language.
You plan to create a reporting solution by using SQL Server 2008 Reporting Services (SSRS). You have data stored in a SQL Server 2008 relational data warehouse. You need to ensure that users are able to create the necessary reports by using data from the data warehouse.
What should you do? (Exhibit)

What should you do to accomplish the following tasks?

You design a Business Intelligence (BI) solution by using SQL Server 2008. You need to load data into your online transaction processing (OLTP) database once a week by using data from a flat file. The file contains all the details about new employees who joined your company last week. The data must be loaded into the tables shown in the exhibit. (Click the Exhibit button.) Employee.EmployeeID is an identity.

A SQL Server 2008 Integration Services (SSIS) package contains one data flow for each of the destination tables. In the Employee Data Flow, an OLE DB Command transformation executes a stored procedure that loads the Employee record and returns the EmployeeID value.
You need to accomplish the following tasks:
-Ensure that the EmployeeID is used as a foreign key (FK) in all child tables for the correct Employee record.
-Minimize the number of round trips to the database.
-Ensure that the package performs in the most efficient manner possible.
What should you do?

What should you do to ensure that the solution meets the requirements?

You design a Business Intelligence (BI) solution by using SQL Server 2008. The solution includes a SQL Server 2008 Analysis Services (SSAS) database. The database contains a cube named Financials. The cube contains objects as shown in the exhibit.

A calculated member named Gross Margin references both Sales Details and Product Costs. You need to ensure that the solution meets the following requirements:
-Managers must be able to view only their cost center’s percentage of the company’s gross margin.
-The impact on query performance is minimal.
What should you do?

What should you do to reduce the disk space required for the DimCustomer dimension table?

You design a Business Intelligence (BI) solution by using SQL Server 2008. You create a SQL Server 2008 Analysis Services (SSAS) solution that has a dimension table named DimCustomer.
The DimCustomer table has the following attributes:
-Gender
-Address
-Marital Status
-Phone Number

You discover that DimCustomer takes a long time to process. You need to reduce the processing time of DimCustomer. You also need to reduce the disk space required for the DimCustomer dimension table.
What should you do?

What should you do to improve the cube processing and query response time?

You design a Business Intelligence (BI) solution by using SQL Server 2008. You create a SQL Server 2008 Analysis Services (SSAS) solution. The solution contains a cube that has a measure named SalesAmount. The measure contains customer sales data for the last six months. The cube has a single partition that has the storage property set to real-time hybrid online analytical processing (HOLAP).
Queries against the cube must return current sales data that is entered one hour before cube processing. The partition takes two hours to process and the response time for the queries is slow. You need to improve the cube processing and query response time.
What should you do?


Page 1 of 912345...Last »