Boost your exam-day confidence with an Exam Replay or an Exam Replay with Practice Test.

Exam
70-470

SQL Server logo

  • Published:
    10 August 2014
  • Languages:
    English, Japanese
  • Audiences:
    IT professionals
  • Technology:
    Microsoft SQL Server 2014
  • Credit towards certification:
    MCP, MCSE

Recertification for MCSE: Business Intelligence

* Pricing does not reflect any promotional offers or reduced pricing for Microsoft Imagine Academy program members, Microsoft Certified Trainers, and Microsoft Partner Network program members. Pricing is subject to change without notice. Pricing does not include applicable taxes. Please confirm exact pricing with the exam provider before registering to take an exam.

Effective May 1, 2017, the existing cancellation policy will be replaced in its entirety with the following policy: Cancelling or rescheduling your exam within 5 business days of your registered exam time is subject to a fee. Failing to show up for your exam appointment or not rescheduling or cancelling your appointment at least 24 hours prior to your scheduled appointment forfeits your entire exam fee.

Skills measured

This exam measures your ability to accomplish the technical tasks listed below. View video tutorials about the variety of question types on Microsoft exams.

Please note that the questions may test on, but will not be limited to, the topics described in the bulleted text.

Do you have feedback about the relevance of the skills measured on this exam? Please send Microsoft your comments. All feedback will be reviewed and incorporated as appropriate while still maintaining the validity and reliability of the certification process. Note that Microsoft will not respond directly to your feedback. We appreciate your input in ensuring the quality of the Microsoft Certification Program.

If you have concerns about specific questions on this exam, please submit an exam challenge.

If you have other questions or feedback about Microsoft Certification exams or about the certification program, registration, or promotions, please contact your Regional Service Center.

Build an analysis services multidimensional database
  • Implement a cube
    • Use SQL Server Data Tools - Business Intelligence (SSDT-BI) to build the cube; use SSDT-BI to do non-additive or semi-additive measures in a cube, define measures, specify perspectives, define translations, define dimension usage, define cube-specific dimension properties, define measure groups, implement reference dimensions, implement many-to-many relationships, implement fact relationships, implement role-playing relationships, create and manage linked measure groups and linked dimensions, create actions
  • Implement custom logic in a data model
    • Define key performance indicators (KPIs); define calculated members; create relative measures (growth, YoY, same period last year), percentage of total using MDX; named sets; add Time Intelligence; implement ranking and percentile; define MDX script to import partial PowerPivot model
  • Select an appropriate model for data analysis
    • Select Tabular versus Multidimensional based on scalability needs, traditional hierarchical, data volume; select appropriate organisational BI, such as corporate BI or PowerBI, and team and personal BI needs and data status
Manage, maintain and troubleshoot a SQL Server Analysis Services (SSAS) database
  • Process data models
    • Define processing of tables or partitions for tabular and multidimensional models; define processing of databases, cubes and dimensions for multidimensional models; select full processing versus incremental processing; define remote processing; define lazy aggregations; automate with Analysis Management Objects (AMO) or XML for Analysis (XMLA); process and manage partitions by using PowerShell
  • Install and maintain an SSAS instance
    • Install SSAS; install development tools; identify development and production installation considerations; upgrade SSAS instance; define data file and program file location; plan for Administrator accounts; define server and database-level security; support scale-out read-only; update SSAS (service packs); install and maintain each instance type of Analysis Services, including PowerPivot; restore and import PowerPivot; back up and restore by using PowerShell
Build a tabular data model
  • Implement a tabular data model
    • Define tables, import data, define calculated columns, define relationships, define hierarchies and perspectives, manage visibility of columns and tables, embed links, optimise BISM for Power View, mark a date table, sort a column by another column
  • Implement data access for a tabular data model
    • Manage partitions, processing, select xVelocity versus DirectQuery for data access
Build a report with SQL Server Reporting Services (SSRS)
  • Design a report
    • Select report components (crosstab report, Tablix, design chart, data visualisation components), design report templates (Report Definition Language), identify the data source and parameters; design a grouping structure; drilldown reports, drillthrough reports; determine if any expressions are required to display data that is not coming directly from the data source
  • Manage a report environment
    • Manage subscriptions and subscription settings; define data-driven subscriptions; manage data sources; integrate SharePoint Server; define email delivery settings; manage the number of snapshots; manage schedules, running jobs and report server logs; manage report server databases; manage the encryption keys; set up the execution log reporting; review the reports; configure site-level settings; design report lifecycle; automate management of reporting services; create a report organisation structure; install and configure reporting services; deploy custom assemblies
  • Configure report data sources and datasets
    • Select appropriate query types (stored procedure versus table versus text only); configure parameterised connection strings (dynamic connection strings); define filter location (dataset versus query); configure data source options, for example, extract and connect to multiple data sources; shared and embedded data sources and datasets; use custom expressions in data sources; connect to Microsoft Azure SQL database; connect to Microsoft Azure Marketplace; implement DAX and MDX queries to retrieve appropriate data sets; work with non-relational data sources, such as XML or SharePoint lists; connect to HDInsight Server
Plan business intelligence (BI) infrastructure
  • Plan for performance
    • Optimise 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 named queries in a data source view; analyse and optimise performance, including Multidimensional Expression (MDX) and Data Analysis Expression (DAX) queries; understand the difference between partitioning for load performance versus query performance in SSAS; appropriately index a fact table; optimise Analysis Services cubes in SQL Server Data Tools; create aggregations
Design BI infrastructure
  • Design a high availability and disaster recovery strategy
    • Design a recovery strategy, back up and restore SSAS databases, back up and restore SSRS databases, move and restore the SSIS Catalogue, design an AlwaysON solution
Design a reporting solution
  • Design a Reporting Services dataset
    • Design appropriate data query parameters, create appropriate SQL queries, create appropriate DAX queries for an application, manage data rights and security, extract data from analysis services by using MDX queries, balance query-based processing versus filter-based processing, manage data sets through the use of stored procedures
  • Manage Excel Services/reporting for SharePoint
    • Configure data refresh schedules for PowerPivot published to SharePoint, publish BI info to SharePoint, use SharePoint to accomplish BI administrative tasks, install and configure Power View, publish PowerPivot and Power View to SharePoint
  • Design BI reporting solution architecture
    • Linked drill-down reports, drill-through reports and sub reports; design report migration strategies; access report services API; design code-behind strategies; identify when to use Reporting Services (RS), Report Builder (RB) or Power View; design and implement context transfer when interlinking all types of reports (RS, RB, Power View, Excel); implement BI tools for reporting in SharePoint (Excel Services versus PowerView versus Reporting Services); select a subscription strategy; enable Data Alerts; design map visualisation
Design BI data models
  • Design the data warehouse
    • Design a data model that is optimised for reporting; design and 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 SQL Server to design, implement and maintain a data warehouse, including partitioning, slowly changing dimensions (SCD), change data capture (CDC), Index Views and column store indexes; 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; implement incremental data load; choose between performing aggregation operations in the SSIS pipeline or the relational engine
  • Design cube architecture
    • 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; identify the aggregation method for a measure in a MOLAP cube; performance tune a MOLAP cube using aggregations; design a data source view; design for cube drill-through and write back actions; choose the correct grain of data to store in a measure group; design analysis services processing by using indexes, indexed views and order-by statements
Design an ETL solution
  • Design SSIS package execution
    • Use the new project deployment model; pass values at execution time; share parameters between packages; plan for incremental loads versus full loads; optimise execution by using Balanced Data Distributor (BDD); choose optimal processing strategy (including Script transform, flat file incremental loads and Derived Column transform)
  • Plan to deploy SSIS solutions
    • 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 (including recoverability), decide between performing aggregation operations in the SSIS pipeline or the relational engine, plan to automate SSIS deployment, plan the administration of the SSIS Catalogue database

Preparation options

Instructor-led training
Find a Microsoft Learning Partner near you
Exam prep videos

Microsoft Certification PREP Talk: Exam 466

In this episode of Prep tips from certification experts, James Seymour, Certification Planner and Matthew Roche, Senior Program Manager in the SQL Server product group, discuss the relevancy of the skills being measured in Exam 466.

Exam Prep: 70-466: MCSE: Business Intelligence, SQL Server

This Exam Prep session focuses on what you need to know to pass Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server. The session, led by Microsoft Certified Trainer (MCT), Armando Lacerda, walks you through the objectives covered in the exam, gives you some general exam-taking tips and highlights best practices for working with SQL Server technology.

Microsoft Certification PREP Talk: Exam 467

In this episode of Prep tips from certification experts, James Seymour, Certification Planner and Matthew Roche, Senior Program Manager in the SQL Server product group, discuss the relevancy of the skills being measured in Exam 467.

From the community

Who should take this exam?

This exam is for individuals looking to maintain their MCSE: Data Platform certification. It is based on the exam objectives from 466 and 467.

More information about exams

Preparing for an exam

We recommend that you review this exam preparation guide in its entirety and familiarise yourself with the resources on this website before you schedule your exam. See the Microsoft Certification exam overview for information about registration, videos of typical exam question formats and other preparation resources. For information on exam policies and scoring, see the Microsoft Certification exam policies and FAQs.

Note

This preparation guide is subject to change at any time without prior notice and at the sole discretion of Microsoft. Microsoft exams might include adaptive testing technology and simulation items. Microsoft does not identify the format in which exams are presented. Please use this preparation guide to prepare for the exam, regardless of its format. To help you prepare for this exam, Microsoft recommends that you have hands-on experience with the product and that you use the specified training resources. These training resources do not necessarily cover all of the topics listed in the "Skills measured" section.