Тест №70-466: Implementing Data Models and Reports 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.
Build an Analysis Services Database (38%)
Design dimensions and measures.
This objective may include but is not limited
to: given a requirement, identify the
dimension/measure group relationship that should
be selected; design patterns for representing
business facts and dimensions (many-to-many
relationships); design dimensions to support
multiple related measure groups (many related
fact tables); handle degenerate dimensions in a
cube; identify the attributes for dimensions;
identify the measures; aggregation behavior for
the measures; hierarchies
Implement and configure dimensions in a cube.
This objective may include but is not limited
to: translations; attribute relations;
hierarchies; implement SQL Server Analysis
Services (SSAS) dimensions and cubes; identify
the Attribute Relationships that should be made
for a given set of Attributes in a dimension;
develop new custom attributes on dimensions;
detect possible design flaws in attribute
relationships; create attribute relationships
correctly in an analysis services dimension;
implement time Dimensions in cubes; manage SSAS
parent-child dimensions; dimension type
Design a schema to support cube architecture.
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; identify the
appropriate data types with correct precision
and size
Create measures.
This objective may include but is not limited
to: logically group measures; select appropriate
aggregation functions; format measures
Implement a cube.
This objective may include but is not limited
to: use Business Intelligence Development Studio
(BIDS) to build the cube; use BIDS to do
non-additive or semi-additive measures in a
cube; measures, perspectives; translations;
dimension usage; cube specific dimension
properties; measure groups; implement reference
dimensions; implement many to many
relationships; implement fact relationships;
implement role-playing relationships; define
granularity; create and manage linked measure
groups and linked dimensions; actions
This objective may include but is not limited
to: MDX authoring; identify the structures of
MDX and the common functions (including tuples,
sets, topcount, and SCOPE); identify which MDX
statement would return the required result;
implement a custom MDX or logical solution for a
pre-prepared case task; graphical query designer
or the generic query designer
Implement custom logic in a data model.
This objective may include but is not limited
to: Data Analysis Expressions (DAX) calculated
columns and measures; key performance indicators
(KPI); calculated members; use MDX functions to
calculate members; relative measures (growth,
YoY, same period last year), percentage of total
using MDX; named sets; add intelligence to
dimensions; Analysis Services stored procedures
Implement storage design in a multidimensional
model.
This objective may include but is not limited
to: aggregations; partitions; storage modes;
proactive caching; manage write-back partitions
Select an appropriate model for data analysis.
This objective may include but is not limited
to: UDM; Scalability, Cleansed; traditional
hierarchical; high volume of data; advanced
features (support for financial applications;
many to many); organizational BI; Tabular Data
Model: raw data; relational tables and
relationships; simpler data structures; team and
personal BI; choose between multidimensional and
tabular models
Manage, Maintain, and Troubleshoot an SSAS Database
(18%)
Analyze data model performance.
This objective may include but is not limited
to: performance consequences of data warehouse
design; optimize performance by changing the
design of the cube or dimension; analyze and
optimize performances of an MDX/DAX query;
optimize queries for huge data sets; optimize
MDX in the calculations; performance monitor
counters; DMVs; performance counters; growth of
the cache, logging options
Process data models.
This objective may include but is not limited
to: processing tables or partitions for tabular
models; processing databases, cubes, and
dimensions for multidimensional models; full
processing vs. incremental processing, remote
processing; lazy aggregations; automate with
Analysis Management Objects (AMO) or XML for
Analysis (XMLA)
Troubleshoot data analysis issues.
This objective may include but is not limited
to: use SQL Profiler; troubleshoot duplicate key
dimension processing errors; error logs and
event viewer logs of SSAS; mismatch of data:
incorrect relationships or aggregations; dynamic
security issues; validate logic and calculations
Deploy SSAS databases.
This objective may include but is not limited
to: Deployment Wizard; BIDS; SSMS; automation;
test solution post deployment; decide whether or
not to process
Install and maintain a SSAS instance.
This objective may include but is not limited
to: software installation of SSAS; development
tools, development and production box
installation considerations; upgrade; data file
and program file location; plan for
Administrator accounts; updates (service packs);
install and maintain each instance type of
Analysis Services, including PowerPivot; restore
and import PowerPivot
Build a Tabular Data Model (17%)
Configure permissions and roles in Business
Intelligence Semantic Model (BISM).
This objective may include but is not limited
to: server roles; SSAS database roles; implement
dynamic security (custom security approaches);
role-based access; test security permissions;
cell level permissions
Implement a tabular data model.
This objective may include but is not limited
to: define tables; import data; calculated
columns; relationships; hierarchies and
perspectives; manage visibility of columns and
tables; optimize BISM for Crescent; mark a date
table; sort a column by another column
Implement business logic in a tabular data
model.
This objective may include but is not limited
to: measures and KPIs; DAX; relationship
navigation; time intelligence; context
modification
Implement data access for a tabular data model.
This objective may include but is not limited
to: manage partitions; processing; Vertipaq vs.
Direct Query
Build a Report with SQL Server Reporting Services
(SSRS) (28%)
Design a report.
This objective may include but is not limited
to: select report components (crosstab report,
Tablix, design chart, data visualization
components); report templates (Report Definition
Language); identify the data source and
parameters; design a grouping structure;
drill-down reports, drill-through reports;
determine whether any expressions are required
to display data that is not coming directly from
the data source
Implement a report layout.
This objective may include but is not limited
to: formatting; apply conditional formatting;
page configuration; headers and footers; matrix;
table; chart; image; list; indicators, maps,
grouping; use Report Builder to implement a
report layout; create a range of reports using
different data regions; custom fields (implement
different parts of the report); collections
(global collections); use expressions; data
visualization components; identify report parts;
group variables and report variables
Configure authentication and authorization for a
reporting solution.
This objective may include but is not limited
to: configure server-level and item-level
role-based security; configure Windows
authentication and custom authentication
(forms-based authentication); configure
Reporting service security (setup or addition of
role); authenticate against data source; store
credential information; describe Report Server
security architecture and site level security;
create system level roles; item level security;
create a new role assignment; assign Windows
users to roles; secure reports using roles;
configure Microsoft SharePoint groups and
permissions
Implement interactivity in a report.
This objective may include but is not limited
to: drilldown; drillthrough; interactive
sorting; parameters (databound, multi-value);
create dynamic reports in SSRS using parameters;
show/hide property; actions (including jump to
report); filters; parameter list; fixed headers;
document map; embedded HTML
Troubleshoot reporting services issues.
This objective may include but is not limited
to: query the executionlog views in ReportServer
database; view reportingservices log files;
Windows Reliability and Performance monitor; use
the ReportServer: Service and Web Service
objects; long-running reports; rendering;
connectivity issues; use SQL Profiler; data
reconciliation: incorrect relationships or
aggregations; dynamic security issues; validate
logic and calculations
Manage a report environment.
This objective may include but is not limited
to: manage subscriptions and subscription
settings; manage data sources; integrate
SharePoint Server 2010; email delivery settings;
manage the number of snapshots; manage schedules
and running jobs; manage report server logs;
manage report server databases; manage the
encryption keys; set up the execution log
reporting; review the reports; site level
settings; design report life cycle; automate
management of reporting services; create a
report organization structure; install and
configure reporting services
Configure report data sources and datasets.
This objective may include but is not limited
to: query types (stored procedure vs. table vs.
text only); parameterized connection strings
(dynamic connection strings); filter location
(dataset vs. query); configure data source
options, for example, extract and connect to
different LOB platforms; shared and embedded
data sources and datasets; connect to SQL Azure
database; SQL Data Market; MDX queries; work
with non-relational data sources such as xml or
SharePoint