Case Study # 1:
BACKGROUND
Fabrikam, Inc. is a manufacturing company that provides components for the engineering
industry. Fabrikam has a sister company named Contoso, Ltd.
Work orders are processed by the following departments:
• The Production department handles request for components that are not in stock.
• The Warehouse department handles the storage and stock management of premanufactured
components.
• The Packaging department handles the packaging of stock in the warehouse or the
production department.
• The Distribution department handles the delivery of orders to clients.
The company uses a flexible resourcing structure where individual employees can be reassigned
between departments. Productivity is higher when specific groups of staff members are assigned
to a particular department.
EXISTING ENVIRONMENT
Fabrikam uses a Microsoft SQL Server 2005 database named FabrikamSQLDB to control and
route client requests as work orders to the respective department.
New Client and ProductOrder records can be added to the FabrikamSQLDB database throughout
the day. These records must be reflected correctly in any reports with zero latency.
The FabrikamSQLDB schema is shown in the following exhibit.
The performance of a department is measured against the following factors:
• Time to resolve a work order
• Number of work orders completed in a given period
Existing SSAS Solution
Fabrikam uses a live Microsoft SQL Server 2005 Analysis Services (SSAS) database named
FabrikamASDB. At present, the company processes the Fabrikam SSAS solution every evening.
This processing takes three hours.
The Analysis Server Query Log is enabled.
The SSAS solution contains the following objects:
• A data source view. The data source view within the FabrikamASDB database sources data
from the FabrikamSQLDB database.
• A cube named FabrikamCube. FabrikamCube exists within the FabrikamASDB database.
FabrikamCube contains the following measure groups:
• The ProductOrder measure group. This measure group is based on the ProductOrder table.
o The ProductOrder measure group has Regular relationships to the Client, Currency, and
Product dimensions.
o The ProductOrder measure group uses real-time ROLAP that has no aggregations.
o Reports against the ProductOrder measure group currently tend to perform poorly when
aggregates are produced across a large range of data.
o Users have drillthrough capability to individual rows within the ProductOrder measure
group.
• The ProductOrderStatus measure group. This measure group is based on the
ProductOrderStatus table.
o The ProductOrderStatus measure group has approximately 20 million rows.
o The ProductOrderStatus measure group is split into the following two partitions:
o The CurrentDay partition. This partition is designed to contain all order status data for
the current working day and any future-dated orders.
o The Historical partition. This partition is designed to contain all historical data prior to
the current working day.
o Data within each partition is filtered by a query. Each query contains dates when the
measure group was partitioned.
o Data older than the current working day is used within highly aggregated reports.
o Data for the current working day is used for individual order status reviews.
• The EmployeeAssignment measure group. This measure group is based on the
EmployeeAssignment table.
o Records are added to the EmployeeAssignment data table continuously throughout the
day.
FabrikamCube contains the following dimensions:
• A Client dimension. Clients are divided into three regions: North, South, and West. Each
region is stored within the Region attribute of the Client dimension.
Case Study # 1 (Questions):
Question: 1
You need to ensure the Client dimension is able to support new orders and new client records.
Which setting should you configure?
A. ROLAP storage
B. Lazy aggregations
C. Write Enabled property
D. Unknown Member functionality
Answer: A
Question: 2
You need to meet the requirements of the accounts team.
What should you do?
A. Set the Dimension type to Currency.
B. Set the Currency member name as a unique attribute.
C. Hide non-leaf data within the Currency dimension key attribute.
D. Disable the All member of the Currency dimension key attribute.
Answer: D
Question: 3
You need to make structural changes to the cube. You also need to ensure that appropriate
aggregations are created after the changes are made.
What should you do?
A. Record a weeks user activity by using SQL Profiler. Apply structural changes. Replay the
activity and then run the Usage Based Optimization Wizard.
B. Apply structural changes. Run the Database Engine Tuning Advisor.
C. Apply structural changes. Run the Usage Based Optimization Wizard.
D. Record a weeks user activity by using SQL Profiler. Apply structural changes. Run the
Database Engine Tuning Advisor.
Answer: A
Question: 4
You need to select appropriate processing methods for the EmployeeAssignment measure group
and the Employee dimension.
Which method should you choose?
A. Run a Process Update against the Employee dimension. Run a Process Full against the
EmployeeAssignment measure group.
B. Run a Process Full against the EmployeeAssignment measure group. Run a Process Update
against the Employee dimension.
C. Run a Process Full against the Employee dimension. Run a Process Incremental against the
EmployeeAssignment measure group.
D. Run a Process Incremental against the EmployeeAssignment measure group. Run a Process
Full against the Employee dimension.
Answer: A
Question: 5
You need to ensure that the new sales manager is able to view clients within only the North and
South regions.
What should you do?
A. Add a Denied member set for the West clients to NorthRole.
B. Add an Allowed member set for the South clients to NorthRole.
C. Create a new role for the sales manager and add a Denied member set for the South clients.
D. Create a new role for the sales manager and add an Allowed member set for the South clients.
Remove the sales manager from SouthAndWestRole.
Answer: D
Question: 6
You need to meet the business requirements of the packaging department.
What should you do?
A. Add the ProductSize data table and the ProductSizeDescriptor column to the Product
dimension. Set the order of the ProductSKU attribute to the ProductSizeDescriptor column.
B. Add the ProductSize data table as a dimension and then add this dimension to the cube as a
Referenced dimension. Set the order of the key attribute of the ProductSize dimension to the
ProductSizeDescriptor column.
C. Add the ProductSize data table and the ProductSizeDescriptor column to the Product
dimension. Create a user-defined hierarchy that has ProductSKU as the top-level attribute and
ProductSizeDescriptor as the bottom-level attribute.
D. Add the ProductSize data table as a dimension and then add this dimension to the cube as a
Referenced dimension. Create a user-defined hierarchy within the ProductSize dimension. Set
the ProductSizeDescriptor attribute as the top-level attribute.
Answer: A
Question: 7
You need to ensure the Order Control team is able to track the number of orders that reach the
production department.
What should you do?
A. Create a new Time dimension based on the Holiday table. Use derived columns for the month
and the year.
B. Create a Server Time dimension. Within this dimension, enable the time periods year, month,
and date.
Select the Additional Manufacturing calendar option.
C. Create a new Time dimension based on the date values within the ProductOrderStatus table.
Flag those days on which no orders were received as Holiday.
D. Create a new Time dimension based on a data source view named query. Derive the data for
this data source view from unique dates within the ProductOrderStatus table. Use a subquery
to identify the holidays and derived columns for the month and the year.
Answer: D
Question: 8
You need to design a storage mode for the ProductOrderStatus measure group.
What should you do?
A. Use ROLAP for the Historical partition and MOLAP for the CurrentDay partition.
B. Use MOLAP for the Historical partition and ROLAP for the CurrentDay partition.
C. Use ROLAP for the Historical partition and Automatic MOLAP for the CurrentDay partition.
D. Use HOLAP for the Historical partition and Low-latency MOLAP for the CurrentDay partition.
Answer: B
Question: 9
You need to maintain the partitions on the ProductOrderStatus measure group.
What should you do?
A. Create a Microsoft SQL Server 2005 Integration Services (SSIS) package that alters the
partition queries and processes aggregated partitions every evening.
B. Enable proactive caching on the partitions. Set the cache to update periodically with a latency
of 24 hours.
C. Set the rebuild interval of the partitions to 36 hours. Create a Microsoft SQL Server 2005
Integration Services (SSIS) package that processes aggregated partitions every evening.
D. Enable proactive caching on the ProductOrderStatus measure group. Set scheduled polling
that re-processes the measure group whenever the system dates change.
Answer: A
Question: 10
You need to ship a copy of the FabrikamASDB metadata to Contoso, Ltd.
What should you do?
A. Use the Synchronize Database Wizard.
B. Describe the FabrikamASDB database in an XML for Analysis (XMLA) script. Ship the script to
Contoso, Ltd.
C. Copy the folder structure of the live SSAS solution to a compressed file. Ship the compressed
file to Contoso, Ltd.
D. Backup the FabrikamASDB database. Run a Process Full on the restored database. Ship the
database to Contoso, Ltd.
Answer: B
Question: 11
You need to help the HR team improve the resource allocation process.
What should you do?
A. Perform data mining regression analysis to identify the correlation between productivity and
specific employee groups.
B. Set the GroupingBehaviour property within the Employee dimension.
C. Partition the EmployeeAssignment measure group into groups by department.
D. Use the Fuzzy Grouping task in Microsoft SQL Server 2005 Integration Services (SSIS).
Answer: A
Case Study # 2:
BACKGROUND
Woodgrove Bank is a banking institution that offers deposit accounts and mortgage accounts.
At present, the bank holds 1,000,000 accounts. On an average 50,000 new accounts are added
every year.
Ten percent of the accounts are mortgage accounts. The balances of mortgage accounts are
recalculated every week.
Some of the existing Microsoft SQL Server Reporting Services (SSRS) reports are doublecounting
transactions where there is more than one account holder for the same account.
EXISTING ENVIRONMENT
Woodgrove Bank has partially implemented a data warehouse named WoodgroveDW.
WoodgroveDW Data Store
The WoodgroveDW database schema is shown in the exhibit.
No comments:
Post a Comment