Designing Business Intelligence Solutions with Microsoft SQL Server 2012 70-467 Test Preparation

1
IT Certification leaders in simulated test
engines guides
Fravo
Get Certified Secure your Future
Designing Business Intelligence Solutions with
Microsoft SQL Server 2012 Exam 70-467 Demo
Edition
2
Section 1 Sec One (1 to 13) Details Topic 1,
Tailspin Toys Case A Background You are the
business intelligence (BI) solutions architect
for Tailspin Toys. You produce solutions by using
SQL Server 2012 Business Intelligence edition
and Microsoft SharePoint Server 2010 Service Pack
1 (SP1) Enterprise edition. Technical
Background Data Warehouse The data warehouse is
deployed on a SQL Server 2012 relational
database. A subset of the data warehouse schema
is shown in the exhibit. (Click the Exhibit
button.)
The schema shown does not include the table
design for the product dimension. The schema
includes the following tables ? FactSalesPlan
table stores data at month-level granularity.
There are two scenarios Forecast and Budget. ?
 The  DimDate  table  stores  a  record  for
 each  date  from  the  beginning  of
 the company's operations through to the end of
the next year. ?  The  DimRegion  table  stores
 a  record  for  each  sales  region,  classified
 by  country. Sales regions do not relocate to
different countries. ? The DimCustomer table
stores a record for each customer. ?  The
 DimSalesperson  table  stores  a  record  for
 each  salesperson.  If  a  salesperson relocates
 to  a  different  region,  a  new  salesperson
 record  is  created  to  support
3
historically  accurate  reporting.  A  new
 salesperson  record  is  not  created  if
 a salesperson's name changes. ? The DimScenario
table stores one record for each of the two
planning scenarios. All relationships  between
 tables  are  enforced  by  foreign  keys.  The
 schema  design  is  as denormalized as possible
for simplicity and accessibility. One exception
to this is the DimRegion table, which is
referenced by two dimension tables. Each product
is classified by a category and subcategory and
is uniquely identified in the source database by
using its stock-keeping unit (SKU). A new SKU is
assigned to a product if its size changes.
Products are never assigned to a different
subcategory, and subcategories are never assigned
to a different category. Extract, transform, load
(ETL) processes populate the data warehouse every
24 hours. ETL Processes One  SQL  Server
 Integration  Services  (SSIS)  package  is
 designed  and  developed  to populate  each
 data  warehouse  table.  The  primary  source
 of  data  is  extracted  from  a SQL  Azure
 database.  Secondary  data  sources  include  a
 Microsoft  Dynamics  CRM 2011 on- premises
database. ETL developers develop packages by
using the SSIS project deployment model. The ETL
developers are responsible for testing the
packages and producing a deployment file. The
deployment file is given to the ETL
administrators. The ETL administrators belong to
a Windows security group named SSISOwners that
maps to a SQL Server login named SSISOwners. Data
Models The  IT  department  has  developed  and
 manages  two  SQL  Server  Analysis
 Services (SSAS)  BI  Semantic  Model  (BISM)
 projects  Sales  Reporting  and  Sales
 Analysis. The  Sales  Reporting  database  has
 been  developed  as  a  tabular  project.  The
 Sales Analysis  database  has  been  developed
 as  a  multidimensional  project.
 Business analysts  use  PowerPivot  for
 Microsoft  Excel  to  produce  self-managed
 data  models based  directly  on  the  data
 warehouse  or  the  corporate  data  models,
 and  publish  the PowerPivot workbooks to a
SharePoint site. The sole purpose of the Sales
Reporting database is to support business user
reporting and   ad-hoc   analysis   by   using
  Power   View.   The   database   is
  configured   for DirectQuery mode and all model
queries result in SSAS querying the data
warehouse. The database is based on the entire
data warehouse. The Sales Analysis database
consists of a single SSAS cube named Sales. The
Sales cube  has  been  developed  to  support
 sales  monitoring,  analysts,  and  planning.
 The Sales cube metadata is shown in the
following graphic.
4
Details of specific Sales cube dimensions are
described in the following table.
The Sales cube dimension usage is shown in the
following graphic.
5
The  Sales  measure  group  is  based  on  the
 FactSales  table.  The  Sales  Plan
 measure group  is  based  on  the  FactSalesPlan
 table.  The  Sales  Plan  measure  group  has
 been configured  with  a  multidimensional  OLAP
 (MOLAP)  writeback  partition.  Both measure
 groups  use  MOLAP  partitions,  and
 aggregation  designs  are  assigned  to
 all partitions.  Because  the  volumes  of  data
 in  the  data  warehouse  are  large,
 an incremental processing strategy has been
implemented. The Sales Variance calculated member
 is  computed  by  subtracting  the  Sales  Plan
 forecast  amount  from  Sales.  The Sales
 Variance  /o  calculated  member  is  computed
 by  dividing  Sales  Variance  by Sales. The
cube's Multidimensional Expressions (MDX) script
does not set any color properties. Analysis and
Reporting SQL Server Reporting Services (SSRS)
has been configured in SharePoint
integrated mode. A business analyst has created a
PowerPivot workbook named Manufacturing Performanc
e  that  integrates  data  from  the  data
 warehouse  and  manufacturing  data from  an
 operational  database  hosted  in  SQL  Azure.
 The  workbook  has  been published in a
PowerPivot Gallery library in SharePoint Server
and does not contain any  reports.  The  analyst
 has  scheduled  daily  data  refresh  from  the
 SQL  Azure database.  Several  SSRS  reports
 are  based  on  the  PowerPivot  workbook,  and
 all reports are configured with a report
execution mode to run on demand. Recently  users
 have  noticed  that  data  in  the  PowerPivot
 workbooks  published  to SharePoint Server is
not being refreshed. The SharePoint administrator
has identified that  the  Secure  Store  Service
 target  application  used  by  the  PowerPivot
 unattended data refresh account has been
deleted. Business Requirements ETL Processes All
ETL administrators must have full privileges to
administer and monitor the SSIS catalog, and to
import and manage projects. Data Models The
budget and forecast values must never be
accumulated when querying the Sales cube. Queries
should return the forecast sales values by
default.
6
Business  users  have  requested  that  a  single
 field  named  SalespersonName  be
 made available to report the full name of the
salesperson in the Sales Reporting data
model. Writeback is used to initialize the budget
sales values for a future year and is based on a
weighted allocation of the sales achieved in the
previous year. Analysis and Reporting Reports
based on the Manufacturing Performance PowerPivot
workbook must deliver data that is no more than
one hour old. Management has requested a new
report named Regional Sales. This report must
be based on the Sales cube and must allow users
to filter by a specific year and present a grid
 with  every  region  on  the  columns  and  the
 Products  hierarchy  on  the  rows.
 The hierarchy  must  initially  be  collapsed
 and  allow  the  user  to  drill  down  through
 the hierarchy to analyze sales. Additionally,
sales values that are less than S5000 must
be highlighted in red. Technical
Requirements Data Warehouse Business logic in the
form of calculations should be defined in the
data warehouse to ensure consistency and
availability to all data modeling
experiences. The schema design should remain as
denormalized as possible and should not
include unnecessary columns. The schema design
must be extended to include the product dimension
data. ETL Processes Package executions must log
only data flow component phases and errors. Data
Models Processing time for all data models must
be minimized. A key performance indicator (KPI)
must be added to the Sales cube to monitor
sales performance.  The  KPI  trend  must  use
 the  Standard  Arrow  indicator  to
 display improving, static, or deteriorating
Sales Variance values compared to the
previous time period. Analysis and Reporting IT
 developers  must  create  a  library  of  SSRS
 reports  based  on  the  Sales
 Reporting database.  A  shared  SSRS  data
 source  named  Sales  Reporting  must  be
 created  in  a SharePoint data connections
library.
QUESTION 1 You  need  to  fix  the  PowerPivot
 data  refresh  problem  by  using  the  least
 amount  of administrative effort. What should
you do?
A. Use the PowerPivot Configuration Tool and
select the Upgrade Features, Services, Application
s and Solutions option. B.  Use  the  Power
 Pivot  Configuration  Tool  and  select  the
 Configure  or  Repair PowerPivot for SharePoint
option. C. Reinstall SSAS in PowerPivot for
SharePoint mode by using the SQL Server
2012 installation media. D. In SharePoint Central
Administration, create a target application and
configure the
7
PowerPivot service application settings to use
the target application.
Answer B
QUESTION 2 You need to grant appropriate
permissions to the SSISOwners SQL Server
login. What should you do?
A. Map the login to the SSISDB database. Assign
the user to the ssis_admin role. B. Map the login
to the msdb database. Assign the user to the
db_owner role. C. Map the login to the msdb
database. Assign the user to the db_ssisadmin
role. D. Map the login to the SSISDB database.
Assign the user to the db_ssisadmin role. E. Map
the login to the SSISDB database. Assign the user
to the db_owner role. F. Map the login to the
msdb database. Assign the user to the ssis_admin
role.
Answer D
QUESTION 3 You   need   to   configure   the
  Scenario   attribute   to   ensure   that
  business   users appropriately  query  the
 Sales  Plan  measure.  What  should  you  do?
 (Each  correct answer presents part of the
solution. Choose all that apply.)
A. Set the AttributeHierarchyVisible property to
False. B. Set the IsAggregatable property to
False. C. Set the Usage property to Parent. D.
set the DefaultMember property to the Forecast
member. E. Set the AttributeHierarchyEnabled
property to False. F. Set the RootMemberIf
property to ParentIsMissing.
Answer C, D
Explanation The  Sales  measure  group  is
 based  on  the  FactSales  table.  The  Sales
 Plan  measure group  is  based  on  the
 FactSalesPlan  table.  The  Sales  Plan  measure
 group  has  been configured  with  a
 multidimensional  OLAP  (MOLAP)  writeback
 partition.  Both measure  groups  use  MOLAP
 partitions,  and  aggregation  designs  are
 assigned  to  all partitions.
8
QUESTION 4 DRAG DROP You need to configure the
attribute relationship types for the Salesperson
dimension. Which configuration should you use? To
answer, drag the appropriate pair of attributes
and attribute relationships from the list  to
 the  correct  location  or  locations  in  the
 answer  area.  (Answer  choices  may  be used
once, more than once, or not all.)
Answer
QUESTION 5 You need to define the trend
calculation for the sales performance KPI. Which
KPI trend MDX expression should you use?
A. CASE WHEN  Sales  Variance    lt  (Sales
 Variance  ,  Date.Calendar.PrevMember) THEN
-1 WHEN  Sales  Variance      (Sales
 Variance  ,  Date.Calendar.PrevMember) THEN
0 ELSE 1 END B. IIF(Sales Variance 3 lt (Sales
Variance , Date.Calendar.PrevMember), 1, 0)
C. IIF(Sales Variance lt (Sales Variance ,
Date.Calendar.PrevMember), 0, 1) D. CASE
9
WHEN  Sales  Variance    lt  (Sales  Variance
 ,  Date.Calendar.PrevMember) THEN     1
    WHEN     Sales     Variance          
    (Sales     Variance     , Date.Calendar
.PrevMember) THEN 0 ELSE -1 END
Answer A
QUESTION 6 DRAG DROP You need to extend the
schema design to store the product dimension
data. Which design should you use? To answer,
drag the appropriate table or tables to
the correct  location  or  locations  in  the
 answer  area.  (Fill  from  left  to  right.
 Answer choices may be used once, more than once,
or not all.)
Answer
10
QUESTION 7 You need to configure package
execution logging to meet the requirements. What
should you do?
A. Configure logging in each ETL package to log
the OnError, OnInformation, and Diagnostic
events. B.   Set   the   SSIS   catalog's
  Server-wide   Default   Logging   Level
  property   to Performance. C. Set the SSIS
catalog's Server-wide Default Logging Level
property to Basic. D. Set the SSIS catalog's
Server-wide Default Logging Level property to
Verbose. E. Configure  logging  in  each  ETL
 package  to  log  the  OnError,  OnPreExecute,
 and OnPostExecute events.
Answer B
QUESTION 8 You need to create the Sales
Reporting shared SSRS data source. Which SSRS
data connection type should you use?
A. OData B. Microsoft SQL Server C. ODBC
11
D. OLE DB
Answer B
QUESTION 9 You need to select an appropriate
tool for creating the Regional Sales report.
Which tool  or  tools  should  you  use?  (Each
 correct  answer  presents  a  complete
 solution. Choose all that apply.)
A. Excel 2010, using the CUBE functions B. Power
View, using a Matrix C. Excel 2010, using a
PivotTable D. Report Builder, using a Matrix
Answer B, C, D
Explanation B Working with a matrix in Power
View A  matrix  is  a  type  of  visualization
 that  is  similar  to  a  table  in  that  it
 is  made  up  of rows and columns. However, a
matrix can be collapsed and expanded by rows
and/or columns. If it contains a hierarchy, you
can drill down/drill up. C  Using  an  Excel
 Pivot  Table,  connecting  to  the  Cube,  will
 give  you  the  option  to drill  down  the
 cube.  Using  conditional  formatting  you  can
 highlight  specific  value ranges. D Matrices
provide functionality similar to crosstabs and
pivot tables. At run time, as the  report  data
 and  data  regions  are  combined,  a  matrix
 grows  horizontally  and vertically on the page.
Values in matrix cells display aggregate values
scoped to the intersection of the row and column
groups to which the cell belongs. You can
format the  rows  and  columns  to  highlight
 the  data  you  want  to  emphasize.  You  can
 also include  drilldown  toggles  that
 initially  hide  detail  data  the  user  can
 then  click  the toggles to display more or less
detail as needed. From scenario Management has
requested a new report named Regional Sales. This
report must be based on the Sales cube and must
allow users to filter by a specific year and
present a grid  with  every  region  on  the
 columns  and  the  Products  hierarchy  on  the
 rows.  The hierarchy  must  initially  be
 collapsed  and  allow  the  user  to  drill
 down  through  the hierarchy to analyze sales.
Additionally, sales values that are less than
5000 must be highlighted in red.
QUESTION 10 DRAG DROP
12
You need to complete the following UPDATE
statement to initialize the budget sales values
for 2012. Which MDX weight value expression
should you use? To answer, drag the appropriate
weight value expression to the answer area.
Answer
QUESTION 11 You need to select an appropriate
tool for creating the Regional Sales
report. Which  tools  or  tools  should  you
 use?  (Each  correct  answer  presents  a
 complete solution. Choose all that apply.)
13
A. Power View, using a table configured for
vertical multiples B. Excel 2010, using a
PivotTable C. Report Builder, using a Matrix D.
Power View, using a table configured for
horizontal multiples
Answer B, C
QUESTION 12 You need to create the calculation
for Salesperson Name. What should you do?
(Each correct answer presents a complete
solution. Choose all that apply.)
A. Create a computed column in the data
warehouse's DimSalesperson table. Include the
column in the Sales Reporting model's Salesperson
table. B. Modify the data warehouse's
DimSalesperson table and add a new column. Use
an UPDATE  statement  to  populate  the  new
 column  with  values.  Update  the  SSIS package
 developed  to  populate  the  data  warehouse's
 DimSalesperson  table  to  use  a Derived Column
transformation to produce the calculation. C.
Configure the Sales Reporting model's Salesperson
table properties to be based on a query. Define a
derived column in the query. D.  Add  a
 calculated  column  to  the  Sales  Reporting
 model's  Salesperson  table  by using the Data
Analysis Expressions (DAX) language CONCATENATE
function. E.  Create  a  view  in  the  data
 warehouse  that  defines  a  derived  column
 based  on  the DimSalesperson  table.  Base  the
 Sales  Reporting  model's  Salesperson  table
 on  the view. Include the column in the Sales
Reporting model's Salesperson table. F. Add a
calculated column to the Sales Reporting model's
Salesperson table by using the Data Analysis
Expressions (DAX) language ADDCOLUMNS function.
Answer B, E
QUESTION 13 You  need  to  configure  data
 refresh  for  the  Manufacturing  Performance
 PowerPivot workbook.  What  should  you  do?
 (Each  correct  answer  presents  part  of  the
 solution. Choose ail that apply.)
A. Configure the PowerPivot Data Refresh Timer
Job to run every 60 minutes. B. Restore the
PowerPivot workbook to an SSAS instance in
tabular mode. C.  Script  a  process  command
 and  configure  a  SQL  Server  Agent  job  to
 execute  the command every 60 minutes.
14
D.  Restore  the  PowerPivot  workbook  to  an
 SSAS  instance  in  PowerPivot  for SharePoint
mode.
Answer A
Section 2 Sec Two (14 to 20) Details Topic 2,
Contoso, Ltd Case A General Background You are
the SQL Server Administrator for Contoso, Ltd.
You have been tasked with upgrading all existing
SQL Server instances to SQL Server
2012. Technical Background The  corporate
 environment  includes  an  Active Directory
Domain Services  (AD  DS) domain named
contoso.com. The forest and domain levels are set
to Windows Server 2008. All default containers
are used for computer and user accounts. All
servers run Windows Server 2008 R2 Service Pack 1
(SP1). All client computers run Windows
7 Professional  SP1.  All  servers  and  client
 computers  are  members  of  the
 contoso.com domain. The current SQL Server
environment consists of a single instance
failover cluster of SQL  Server  2008  R2
 Analysis  Services  (SSAS).  The  virtual
 server  name  of  the cluster is SSASCluster.
The cluster includes two nodes Node1 and Node2.
Node1 is currently the active node. In
anticipation of the upgrade, the prerequisites
and shared components  have  been  upgraded  on
 both  nodes  of  the  cluster,  and  each  node
 was rebooted during a weekly maintenance
window. A  single-server  deployment  of  SQL
 Server  2008  R2  Reporting  Services  (SSRS)
 in native mode is installed on a server named
SSRS01. The Reporting Server service
is configured  to  use  a  domain  service
 account.  SSRS01  hosts  reports  that  access
 the SSAS  databases  for  sales  data  as  well
 as  modeling  data  for  the  Research
 team. SSRS01  contains  94  reports  used  by
 the  organization.  These  reports  are
 generated continually  during  business  hours.
 Users  report  that  report  subscriptions  on
 SSRS01 are  not  being  delivered.  You  run
 the  reports  on  demand  from  Report  Manager
 and find that the reports render as expected. A
 new  server  named  SSRS02  has  been  joined
 to  the  domain,  SSRS02  will  host  a single-
server deployment of SSRS so that snapshots of
critical reports are accessible during the
upgrade. The server configuration is shown in the
exhibit. (Click the Exhibit button.)
15
The  production  system  includes  three  SSAS
 databases  that  are  described  in
 the following table.
All SSAS databases are backed up once a day, and
backups are stored offsite. Business
Requirements After the upgrade users must be able
to perform the following tasks
16
? Ad-hoc analysis of data in the SSAS databases
by using the Microsoft Excel PivotTable client. ?
Daily operational analysis by executing a custom
application that uses ADOMD.NET and existing
Multidimensional Expressions (MDX) queries. The
detailed data must be stored in the
model. Technical Requirements You need to
minimize downtime during the SSASCluster upgrade.
The upgrade must minimize user intervention and
administrative effort. The  upgrade  to  SQL
 Server  2012  must  maximize  the  use  of  all
 existing  servers, require the least amount of
administrative effort, and ensure that the SSAS
databases are operational as soon as
possible. You  must  implement  the  highest
 level  of  domain  security  for  client
 computers connecting to SSRS01. The SSRS
instance on SSRS01 must use Kerberos
delegation to  connect  to  the  SSAS  databases.
 Email  notification  for  SSRS01  has  not
 been previously configured. Email notification
must be configured to use the SMTP server SMTP01
 with  a  From  address  of  reports_at_contoso.com.
 Report  distribution  must  be secured by using
SSL and must be limited to the contoso.com
domain. You have the following requirements for
SSRS02 ? Replicate the SSRS01 configuration. ?
Ensure that all current reports are available on
SSRS02. ? Minimize the performance impact on
SSR501. In preparation for the upgrade, the
SSRS-related components have been installed
on the  new  SSRS02  server  by  using  the
 Reporting  Services  file-only  installation
 mode. The  Reporting  Services  databases  have
 been  restored  from  SSRS01  and
 configured appropriately. You must design a
strategy to recover the SSRS instance on SSRS01
in the event of a system failure. The strategy
must ensure that SSRS can be recovered in the
minimal amount  of  time  and  that  reports  are
 available  as  soon  as  possible.  Only
 functional components must be recovered. SSRS02
is the recovery server and is running the same
version of SSRS as SSRS01. A  full  backup  of
 the  SSRS  databases  on  SSRS01  is  performed
 nightly.  The  report server  configuration
 files,  custom  assemblies,  and  extensions  on
 SSRS02  are manually synchronized with
SSRS01. Prior to implementing the upgrade to SQL
Server 2012, you must back up all existing SSAS
databases. Databases  on  SSRS01  is  performed
 nightly.  The  report  server  configuration
 files, custom  assemblies,  and  extensions  on
 SSRS02  are  manually  synchronized
 with SSRS01. Prior to implementing the upgrade
to SQL Server 2012, you must back up all
existing SSAS  databases.  The  backup  must
 include  only  the  partitioning,  metadata,
 and aggregations to minimize the processing time
required when restoring the databases. You  must
 minimize  processing  time  and  the  amount  of
 disk  space  used  by  the backups. Before
upgrading SSAS on the SSASCluster, all existing
databases must be moved to a  temporary  staging
 server  named  SSAS01  that  hosts  a  default
 instance  of  SQL Server 2012
17
Analysis Services. This server will be used for
testing client applications connecting to  SSAS
 2012,  and  as  a  disaster  recovery  platform
 during  the  upgrade.  You  must move  the
 databases  by  using  the  least  amount  of
 administrative  effort  and  minimize downtime. A
ll SSAS databases other than the Research
database must be converted to tabular BI Semantic
Models (BISMs) as part of the upgrade to SSAS
2012. The Research team must have access to the
Research database for modeling throughout the
upgrade. To facilitate this, you detach the
Research database and attach it to SSAS01. While
testing the Research database on SSAS01, you
increase the compatibility level to 1100. You
then discover a compatibility issue with the
application. You must roll back the compatibility
level of the database to 1050 and retest. After
completing the upgrade, you must do the
following 1. Design a role and assign an MDX
expression to the Allowed member set property of
the Customer dimension to allow sales
representatives to browse only members of the
 Customer  dimension  that  are  located  in
 their  sales  regions.  Use  the
 sales representatives' logins and minimize
impact on performance. 2. Deploy a data model to
allow the ad-hoc analysis of data. The data model
must be cached and source data from an OData feed.
QUESTION 14 You need to configure security for
the SSRS instance on SSRS01 to connect to
SSAS and minimize downtime. What should you do?
(Each correct answer presents part of the
solution. Choose all that apply.)
A. Register a service principal name for the
Report Server service. B. Register a service
principal name for the Analysis Services
service. C. Restart the IIS service. D. Configure
SSRS01 to use the Negotiate authentication
type. E. Configure SSRS01 to use the Custom
authentication type.
Answer A, D
Explanation A (not B) If you are deploying
Reporting Services in a network that uses
the Kerberos  protocol  for  mutual
 authentication,  you  must  create  a  Service
 Principal Name (SPN) for the Report Server
service if you configure it to run as a domain
user account. D (not E) See step 6 below. To
register an SPN for a Report Server service
running as a domain user ?  Install  Reporting
 Services  and  configure  the  Report  Server
 service  to  run  as  a domain user account.
Note that users will not be able to connect to
the report server until you complete the
following steps.
18
? Log on to the domain controller as domain
administrator. ? Open a Command Prompt window. ?
Copy the following command, replacing placeholder
values with actual values that are valid for your
network ? Setspn -a http/ltcomputer-namegt.ltdomain-
namegtltportgtltdomain-user-accountgt ? Run the
command. ? Open the RsReportServer.config file
and locate the ltAuthenticationTypesgt section. Add
ltRSWindowsNegotiate/gt as the first entry in this
section to enable NTLM.  RSWindowsNegotiate.
 If  you  initially  set  the  Windows  service
 account  for  the report server to
NetworkService or LocalSystem in Reporting
Services Configuration Manager,
 RSWindowsNegotiate  is  added  to  the
 RSReportServer.config  file  as  the default
 setting.  With  this  setting,  the  report
 server  can  accept  requests  from
 client applications  requesting  Kerberos  or
 NTLM  authentication.  If  Kerberos  is
 requested and  the  authentication  fails,  the
 report  server  switches  to  NTLM
 authentication  and prompts  the  user  for
 credentials  unless  the  network  is
 configured  to  manage authentication
transparently. Using  RSWindowsNegotiate  is
 your  best  option  because  it  provides  the
 greatest flexibility for multiple clients in an
intranet environment. Not C IIS is not mention
in this scenario. Note From scenario / A
single-server deployment of SQL Server 2008 R2
Reporting Services (SSRS) in native mode is
installed on a server named SSRS01. The Reporting
Server service is configured to use a domain
service account.
Reference Register a Service Principal Name
(SPN) for a Report Server
QUESTION 15 You need to perform the pre-upgrade
database backup operation by using SQL
Server Management Studio (SSMS). How should you
configure the backup options?
A. Select the Apply compression check box. Select
the Encrypt backup file check box and supply a
password. B. Clear the Apply compression check
box. Select the Encrypt backup file check box and
supply a password. C. Clear the Apply compression
check box. Clear the Encrypt backup file check
box. D. Select the Apply compression check box.
Clear the Encrypt backup file check box.
Answer D
QUESTION 16
19
You need to implement the Customer Sales and
Manufacturing data models. What should you do?
(Each correct answer presents a partial solution.
Choose all that apply.)
A.  Use  the  Database  Synchronization  Wizard
 to  upgrade  the  database  to  tabular mode.
B.  Use  SQL  Server  Integration  Services
 (SSIS)  to  copy  the  database  design  to
 the SSAS instance, and specify tabular mode as
the destination. C. Use SQL Server Data Tools
(SSDT) to redevelop and deploy the projects. D.
Use the current SSAS instance. E. Install a new
instance of SSAS in tabular mode.
Answer C, E
Explanation C Tabular models are authored in
SQL Server Data Tools (SSDT) using new
tabular model project templates. You can import
data from multiple sources, and then enrich the
  model   by   adding   relationships,
  calculated   columns,   measures,   KPIs,
  and hierarchies. Models can then be deployed to
an instance of Analysis Services where client
reporting applications can connect to them.
Deployed models can be managed in SQL Server
Management Studio just like multidimensional
models. They can also be  partitioned  for
 optimized  processing  and  secured  to  the
 row-level  by  using  role based security. E
 If  you  are  installing  Analysis  Services  to
 use  the  new  tabular  modeling  features, you
must install Analysis Services in a server mode
that supports that type of model. The server mode
is Tabular, and it is configured during
installation. After you install the server in
this mode, you can use it host solutions that you
build in  tabular  model  designer.  A  tabular
 mode  server  is  required  if  you  want
 tabular model data access over the network.
From scenario / Deploy a data model to allow the
ad-hoc analysis of data. The data model must
be cached and source data from an OData feed. /
All SSAS databases other than the Research
database must be converted to tabular BI
 Semantic  Models  (BISMs)  as  part  of  the
 upgrade  to  SSAS  2012.  The  Research team
must have access to the Research database for
modeling throughout the upgrade. To facilitate
this, you detach the Research database and attach
it to SSAS01. The Business Intelligence
Semantic Model (BISM) is a single unified BI
platform which  has  both  multi-dimensional  as
 well  as  tabular  data  modeling  capabilities
 to offer best of both worlds and choice for the
developer.
Reference Install Analysis Services in Tabular
Mode Tabular Modeling (SSAS Tabular)
20
QUESTION 17 You need to re-establish
subscriptions on SSRS01. What should you do?
A. Manually failover the active node. B. Install
prerequisites and upgrade shared components on
Node1 and Node2. C. Generate a SQL Server 2012
configuration file by running the SQL Server
Setup executable. D. Upgrade Node1 by using the
SQL Server 2012 Upgrade wizard.
Answer A
Explanation SSRS  reports  are  scheduled  by
 SQL  server  Agent  jobs.  Start  the  SQL
 Server  Agent on SSRS01.
QUESTION 18 You need to roll back the
compatibility level of the Research database.
What should you do?
A. Restore a backup of the previous version of
the database. B. Use an ALTER DATABASE statement
to set the compatibility option. C.  Change  the
 Compatibility  Level  property  in  the  XMLA
 script,  and  then  execute the script. D. In
SQL Server Management Studio (SSMS), change the
compatibility level in the database properties.
Answer A
QUESTION 19 You  need  to  develop  a  BISM
 that  meets  the  business  requirements  for
 ad-hoc  and daily   operational   analysis.
  You   must   minimize   development   effort.
  Which development approach and mode should you
use?
A.  Develop  a  tabular  project  and  configure
 the  model  with  the  Direct  Query
 mode setting on and the project query mode set
to DirectQuery. B.  Develop  a  tabular  project
 and  configure  the  model  with  the  Direct
 Query  mode setting on and the project query
mode set to In-Memory with DirectQuery.
21
C. Develop a multidimensional project and
configure the model with the DirectQuery mode
setting off. D. Develop a multidimensional
project and configure the cube to use hybrid
OLAP (HOLAP) storage mode.
Answer C
Explanation / After the upgrade users must be
able to perform the following tasks /  Ad-hoc
 analysis  of  data  in  the  SSAS  databases  by
 using  the  Microsoft  Excel PivotTable client
(which uses MDX). /   Daily   operational
  analysis   by   executing   a   custom
  application   that   uses ADOMD.NET and
existing Multidimensional Expressions (MDX)
queries. / Deploy a data model to allow the
ad-hoc analysis of data. The data model must
be cached and source data from an OData feed. We
 cannot  use  DirectQuery  mode  so  C  is  the
 only  answer  that  will  provide  the required
 caching.  When  a  model  is  in  DirectQuery
 mode,  it  can  only  be  queried  by using DAX.
You cannot use MDX to create queries. This means
that you cannot use the Excel Pivot Client,
because Excel uses MDX.
QUESTION 20 You  need  to  use  SQL  Server
 Management  Studio  (SSMS)  to  make  the
 SSAS databases available for application
testing. What should you do?
A. Restore the SSAS databases from the latest
backup to SSAS01. B. Script the databases as a
Create script to a new window and then execute
the script on SSAS01. C.  Detach  the  SSAS
 databases  from  the  SSASCluster,  and  then
 attach  them  to SSAS01. D. Use the
Import/Export Wizard to copy the databases from
the production server to the development server.
Answer A
22
(No Transcript)