Тест №70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012
Продолжительность: Языки теста: English Online тест: Кол-во вопросов: Мин.проходной балл:
Темы:
Skills Being MeasuredThis
exam measures your ability to accomplish the technical tasks listed
below.The percentages
indicate the relative weight of each major topic area on the exam.The
higher the percentage, the more questions you are likely to see on that
content area on the exam.
The information after “This objective
may include but is not limited to” is intended to further define or
scope the objective by describing the types of skills and topics that
may be tested for the objective. However, it is not an exhaustive list
of skills and topics that could be included on the exam for a given
skill area. You may be tested on other skills and topics related to the
objective that are not explicitly listed here.
Plan Business Intelligence (BI) Infrastructure (15%)
Plan for performance.
This objective
may include but is not limited to: optimize batch
procedures: extract, transform, load (ETL) in SQL Server
Integration Services (SSIS)/SQL and processing phase in
Analysis Services; configure Proactive Caching within SQL
Server Analysis Services (SSAS) for different scenarios;
understand performance consequences of Unified Dimension
Model (UDM) and Data warehouse (DWH) design; analyze and
optimize performances of Multidimensional Expression (MDX)
and Data Analysis Expression (DAX) queries; optimize queries
for huge data sets; understand the difference between
partitioning for load performance versus query performance
in SSAS; appropriately index a fact table; optimize Analysis
Services cubes in UDM; create aggregations using Usaged
Based Optimizations
Plan for
scalability.
This objective
may include but is not limited to: Multidimensional OLAP
(MOLAP); Relational OLAP (ROLAP); Hybrid OLAP (HOLAP)
Plan and manage upgrades.
This objective
may include but is not limited to: plan change management
for a BI solution
Maintain server health.
This objective
may include but is not limited to: design an automation
strategy
Design BI Infrastructure (16%)
Design a security strategy.
This objective
may include but is not limited to: configure security and
impersonation between database, analysis services and
frontend; implement Dynamic Dimension Security within a
cube; configure security for an Extranet environment;
configure Kerberos Security; skills on authentication
mechanisms, ability to build secure solutions end to end;
design security roles for calculated measures; understand
the tradeoffs between regular SSAS security and dynamic
security; plan and implement security requirements of a BI
solution
Design a SQL partitioning strategy.
This objective
may include but is not limited to: choose the proper
partitioning strategy for the data warehouse and cube;
implement a parallel load to fact tables by using partition
switching; use data compression in Fact tables
Design a backup strategy.
This objective
may include but is not limited to: design a High
Availability (HA) and disaster recovery (DR) strategy;
proactively preventing issues
Design a logging and auditing strategy.
This objective
may include but is not limited to: design a new SSIS logging
infrastructure (i.e. info available thru the catalog views);
validate data is balancing and reconciling correctly
Design a Reporting Solution (24%)
Design a Reporting Services dataset.
This objective
may include but is not limited to: data query parameters;
creating appropriate SQL queries for an application (MDX
queries); managing data rights and security; extracting data
from Analysis Services; balancing query-based processing
versus filter-based processing; managing data sets through
the use of stored procedures
Manage Excel Services/Reporting for SharePoint.
This objective
may include but is not limited to: configure data refresh
schedules for PowerPivot published to SharePoint; publish BI
info to SharePoint; use SharePoint to accomplish BI
administrative tasks
Design a data acquisition strategy.
This objective
may include but is not limited to: identify the data sources
that needs to be used to pull in the data; determine the
changes (incremental data) in the data source (time window);
identify the relationship and dependencies between the data
sources; determine who can access which data; what data can
be retained for how long (regulatory compliance, data
archiving, aging); design a data movement strategy; profile
source data
Plan and manage reporting services configuration.
This objective
may include but is not limited to: native mode
Design BI reporting solution architecture.
This objective
may include but is not limited to: linked reports,
drill-down reports, drill-through reports, migration
strategies, access report services API, sub reports,
Code-Behind strategies; identify when to use Reporting
Services, Report Builder, or Crescent; design/implement
context transfer when interlinking all types of reports (RS,
RB, Crescent, Excel, PowerPivot); implement BI tools for
reporting in SharePoint (Excel Services versus Performance
Point versus Reporting Services); select a subscription
strategy
Design BI Data Models (34%)
Design the data warehouse.
This objective
may include but is not limited to: design a data model that
is optimized for reporting; design & build a cube on top;
design enterprise data warehouse (EDW) and OLAP cubes;
choose between natural keys and surrogate keys when
designing the data warehouse; use the facilities available
in SQL Server to design, implement and maintain a data
warehouse (partitioning, slowly changing dimensions (SCD),
change data capture (CDC), Clustered Index Views etc.);
identify design best practices; implement a many-to-many
relationship in an OLAP cube; design a data mart/warehouse
in reverse from an Analysis Services cube (or empty Analysis
Services cube that was created referring requirements); use
rowstamp in the data warehouse; choose between performing
aggregation operations in the SSIS pipeline or the
relational engine; select surround architecture
Design a schema.
This objective
may include but is not limited to: multidimensional modeling
starting from a star schema; relational modeling for a Data
Mart; choose or create a topology
Design cube architecture.
This objective
may include but is not limited to: produce efficient
aggregated cubes; partition cubes and build aggregation
strategies for the separate partitions; design a data model;
choose the proper partitioning strategy for the data
warehouse and cube; design the data file layout for a data
warehouse keeping maximum performance in mind; given a
requirement, identify the Aggregation method that should be
selected for a measure in a MOLAP cube; design cube
aggregations to maintain a balance between storage and
performance; performance tune a MOLAP cube using
aggregations; design a data source view; cube drill-through
and write back actions
Design fact tables.
This objective
may include but is not limited to: design a data warehouse
that supports many to many dimensions with factless fact
tables
Design BI Semantic Models.
This objective
may include but is not limited to: plan for a
multidimensional cube; write a UDM model with many to many
(this is related to MDX/BISM code, but it is a good example
for exercises; choose between UDM and BISM depending on the
type of data and workload
Design and create MDX calculations.
This objective
may include but is not limited to: MDX authoring; identify
the structures of MDX and the common functions (tuples,
sets, topcount, SCOPE etc.); identify which MDX statement
would return the required result (single result and multiple
MDX options provided to test taker); implement a custom MDX
or logical solution for a pre-prepared case task
Design an ETL Solution (11%)
Design SSIS package execution.
This objective
may include but is not limited to: using new project
deployment model; passing values at execution time; share
parameters between packages
Plan to deploy SSIS solutions.
This objective
may include but is not limited to: deploy the package to
another server with different security requirements; secure
Integration Services packages that are deployed at the file
system; demonstrate awareness of SSIS packages/projects and
how they interact with environments; decide between
performing aggregation operations in the SSIS pipeline or
the relational engine
Design package configurations for SSIS packages.
This objective
may include but is not limited to: avoid repeating
Configuration Information entered in SSIS packages and use
configuration files