Exam 70-466: Implementing Data Models and Reports with Microsoft SQL Server

The Microsoft 70-466: Implementing Data Models and Reports with Microsoft SQL Server 70-466 is specifically for participants to gain the knowledge and skills for making the appropriate job role decisions around implementing data models and reports with Microsoft SQL Server 2012. Achieving this certification puts you in an advantageous position and helps you gain mastery of Microsoft SQL Server

However, the preparations don’t come easy. You need constant practice to clear the exam. Here we provide you with our Tutorials and Preparatory Guide that will support you throughout your exam journey. This guide will define your rodmap to success.

Who should take the 70-466 exam?

This exam is intended for business intelligence (BI) developers who focus on creating BI solutions that require implementing multi-dimensional data models, implementing and maintaining OLAP cubes, and creating information displays used in business decision making.

Key Responsibilities

Primary responsibilities include:

Firstly, Implementing analytical data models, such as OLAP cubes.
Secondly, Implementing reports, and managing report delivery.
Thirdly, Creating business performance dashboards.
Also, Supporting data mining and predictive analysis.

Certification Details

The Microsoft 70-466 exam is a part of the requirements for: MCSE: Data Management and Analytics. On successful completion of Microsoft 70-464, 70-465 and 70-466 you earn the MCSE: Data Management and Analytics certification.

Exam Details : Microsoft 70-466

Before embarking on your preparations, it’s always beneficial to be thorough with all the important exam information. You should know the details should beforehand. Therefore we have provided all the necessary information you may need before applying for the 70-466 exam

The Microsoft 70-466 Implementing Data Models and Reports with Microsoft SQL exam comprises of 40-60 questions. Further, the exam duration is only 120 minutes. The exam fee is 165 USD. Moreover, the exam is available in English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil) languages.

Exam Name	Implementing Data Models and Reports with Microsoft SQL Server 2012
Exam Code	70-466
Exam Duration	120 mins
Number of Questions	40-60 Questions
Exam Format	Multiple Choice and Multi-Response Questions
Exam Type	Server
Exam Fee	$165 USD
Exam Language	English, Chinese (Simplified), French, German, Japanese, Portuguese (Brazil)
Credit Towards	MCSE

How to Schedule the exam?

The Microsoft 70-466 can be scheduled as follows-

For non-students interested in technology at Pearson Vue
For students or instructors at Certiport

70-466 Exam Retake Policy

Microsoft allows 5 attempts per year for a specific exam. The exam retake policy for Microsoft 70-466 Exam is as follows-

If a candidate is not able to clear the exam in the first attempt, the candidate must wait at least 24 hours before retaking the exam.
If a candidate does not clear the exam the second time, candidate must wait at least 14 days before retaking the exam a third time.
A 14-day waiting period is also there for the fourth and fifth subsequent exam retakes.

Cancellation and Reschedule Policy

Microsoft allows free cancelling of the Exam within 24 hours prior to your scheduled appointment. But, if you fail to show up on your exam day without rescheduling or cancelling your exam, Microsoft reserves all rights to forfeit your entire exam fee. Rescheduling and Cancellation of your scheduled exams can be done through the Certification Dashboard.

Microsoft 70-466 Exam FAQ

It is essential to have complete clarity about the exam before beginning. Visit the Microsoft 70-466 FAQ

Course Outline: Microsoft 70-466 exam

The Official Exam Guide covers the descriptive details about the exam domains. These domains cover various subtopics. This is to help candidates prepare for the exam by identifying specific content within each topic that may be tested. Further, there are 4 domains in this exam-

Domain 1- Build an analysis services multidimensional database (35-40%)

Design dimensions and measures

given a requirement, identify the dimension/measure group relationship that should be selected (Microsoft Documentation: Dimension Relationships)
design patterns for representing business facts and dimensions (many-to-many relationships) (Microsoft Documentation: Defining a Many-to-Many Relationship)
design dimensions to support multiple related measure groups (many related fact tables)
handle degenerate dimensions in a cube (Microsoft Documentation: Error Configuration for Cube, Partition, and Dimension Processing)
identify the attributes for dimensions (Microsoft Documentation: Dimension Attribute Properties Reference)
identify the measures (Microsoft Documentation: Measures and Measure Groups)
aggregation behavior for the measures (Microsoft Documentation: Use Aggregate Functions)
build hierarchies
define the granularity of dimension relationships. (Microsoft Documentation: Defining Dimension Granularity within a Measure Group)

Implement and configure dimensions in a cube

translations (Microsoft Documentation: Translations in Multidimensional Models (Analysis Services))
define attribute relationships (Microsoft Documentation: Attribute Relationships)
implement hierarchies (Microsoft Documentation: User Hierarchies)
implement SQL Server Analysis Services (SSAS) dimensions and cubes (Microsoft Documentation: Dimensions – Introduction)
create the Attribute Relationships that should be made for a given set of attributes in a dimension (Microsoft Documentation: Attribute Relationships)
develop new custom attributes on dimensions (Microsoft Documentation: Custom Member Formulas for Attributes in a Dimension)
detect possible design flaws in attribute relationships
implement time dimensions in cubes (Microsoft Documentation: Define Time Intelligence Calculations using the Business Intelligence Wizard)
manage SSAS parent-child dimensions (Microsoft Documentation: Database Dimensions – Finance Account of parent-child type)
dimension type (Microsoft Documentation: Database Dimension Properties – Types)

Design a schema to support cube architecture

multidimensional modeling starting from a star schema
relational modeling for a data source view (Microsoft Documentation: Defining a Data Source View (Analysis Services))
choose or create a topology
identify the appropriate data types with correct precision and size (Microsoft Documentation: Data Sources and Bindings (SSAS Multidimensional), Data Types in Analysis Services)

Create and configure measures

logically group measures and configure Measure Group Properties (Microsoft Documentation: Configure Measure Group Properties)
select appropriate aggregation functions (Microsoft Documentation: Use Aggregate Functions)
format measures (Microsoft Documentation: Modifying Measures)
design the measure group for the correct granularity (Microsoft Documentation: Defining Dimension Granularity within a Measure Group)

Implement a cube

use SQL Server Data Tools – Business Intelligence (SSDT-BI) to build the cube (Microsoft Documentation: Creating Multidimensional Models Using SQL Server Data Tools, Create a Cube Using the Cube Wizard)
use SSDTBI to do non-additive or semi-additive measures in a cube (Microsoft Documentation: Define Semiadditive Behavior)
define measures (Microsoft Documentation: Measures and Measure Groups)
specify perspectives (Microsoft Documentation: Defining and Browsing Perspectives)
define translations (Microsoft Documentation: Translations in Multidimensional Models (Analysis Services))
define dimension usage (Microsoft Documentation: Dimension Relationships)
define cube-specific dimension properties (Microsoft Documentation: Define Cube Dimension Properties)
define measure groups (Microsoft Documentation: Measures and Measure Groups)
implement reference dimensions (Microsoft Documentation: Dimensions – Introduction)
implement many-to-many relationships (Microsoft Documentation: Define a Many-to-Many Relationship and Many-to-Many Relationship Properties)
implement fact relationships (Microsoft Documentation: Defining a Fact Relationship)
implement role-playing relationships
create and manage linked measure groups and linked dimensions (Microsoft Documentation: Linked Measure Groups, Linked Dimensions)
create actions

Create Multidimensional Expressions (MDX) and Data Analysis Expressions (DAX) queries

identify the structures of MDX and the common functions (tuples, sets, TopCount, SCOPE, and more) (Microsoft Documentation: Working with Members, Tuples, and Sets (MDX, TopCount (MDX))
identify which MDX statement would return the required result (Microsoft Documentation: MDX Query – The Basic Query)
implement a custom MDX or logical solution for a pre-prepared case task
identify the structure of DAX and common functions, including CALCULATE, EVALUATE, and FILTER (Microsoft Documentation: DAX overview)
identify which DAX query would return the required result (Microsoft Documentation: DAX overview)

Implement storage design in a multidimensional model

create aggregations (Microsoft Documentation: Use Aggregate Functions)
create partitions, storage modes (Microsoft Documentation: Partitions – Partition Storage Modes and Processing)
define proactive caching (Microsoft Documentation: Partitions – Proactive Caching)
manage write-back partitions (Microsoft Documentation: Set Partition Writeback)
implement linked cubes (Microsoft Documentation: Linked Measure Groups)
implement distributed cubes

Select an appropriate model for data analysis

select Tabular versus Multidimensional based on scalability needs (Microsoft Documentation: Comparing tabular and multidimensional solutions, High availability and Scalability in Analysis Services)
traditional hierarchical, data volume
select appropriate organizational BI, such as corporate BI, and team and personal BI needs and data status (Microsoft Documentation: Licensing the Power BI service for users in your organization)

Domain 2- Manage, maintain, and troubleshoot a SQL Server Analysis Services (SSAS) database (15-20%)

Analyze data model performance

identify the performance consequences of data source view design (Microsoft Documentation: Defining a Data Source View (Analysis Services))
optimize performance by changing the design of the cube or dimension (Microsoft Documentation: Reviewing Cube and Dimension Properties)
analyze and optimize the performances of an MDX/DAX query (Microsoft Documentation: DAX for multidimensional models)
optimize queries for huge data sets
optimize MDX in the calculations (Microsoft Documentation: MDX Scripting Fundamentals (Analysis Services))
performance monitor counters
select appropriate Dynamic Management Views for Analysis Services (Microsoft Documentation: Dynamic Management Views (DMVs))
analyze and define performance counters (Microsoft Documentation: Performance counters)
monitor growth of the cache
define and view logging options (Microsoft Documentation: Log Properties)

Process data models

define processing of tables or partitions for tabular and multidimensional models (Microsoft Documentation: Processing Options and Settings (Analysis Services), Partitions in Multidimensional Models)
define the processing of databases, cubes, and dimensions for multidimensional models (Microsoft Documentation: Processing Analysis Services Objects)
select full processing versus incremental processing (Microsoft Documentation: Process database, table, or partition (Analysis Services))
define remote processing (Microsoft Documentation: Remote Processing (Analysis Services))
define lazy aggregations (Microsoft Documentation: OLAP properties)
automate with Analysis Management Objects (AMO) or XML for Analysis (XMLA) (Microsoft Documentation: XML for Analysis (XMLA) Reference, Analysis Services documentation)
process and manage partitions by using PowerShell (Microsoft Documentation: Analysis Services PowerShell Reference)

Troubleshoot data analysis issues

use SQL Profiler (Microsoft Documentation: Monitor Analysis Services with SQL Server Profiler)
troubleshoot duplicate key dimension processing errors (Microsoft Documentation: Error Configuration for Cube, Partition, and Dimension Processing)
error logs and event viewer logs of SSAS (Microsoft Documentation: Log operations in Analysis Services)
mismatch of data
incorrect relationships or aggregations (Microsoft Documentation: Attribute Relationships – Define)
dynamic security issues
validate logic and calculations (Microsoft Documentation: Calculation groups)

Deploy SSAS databases

deployment Wizard, implement SSDT-BI, deploy SSMS (Microsoft Documentation: Deploy solutions by using the Deployment Wizard)
test solution post-deployment, decide whether or not to process, test different roles (Microsoft Documentation: Grant cube or model permissions (Analysis Services))

Install and maintain an SSAS instance

install SSAS (Microsoft Documentation: Install sample data and multidimensional projects)
install development tools
identify development and production installation considerations
upgrade SSAS instance (Microsoft Documentation: Upgrade Analysis Services)
define data file and program file location
plan for Administrator accounts (Microsoft Documentation: Grant database permissions (Analysis Services))
define server and database level security
support scale-out read-only (Microsoft Documentation: Azure Analysis Services scale-out, Using Read Scale-Out for Better Performance)
update SSAS (service packs)
install and maintain each instance type of Analysis Services, including PowerPivot (Microsoft Documentation: Determine the Server Mode of an Analysis Services Instance, Install Analysis Services in Power Pivot Mode)
restore and import PowerPivot (Microsoft Documentation: Import from Power Pivot)
back up and restore by using PowerShell

Domain 3- Build a tabular data model (15-20%)

Configure permissions and roles in a tabular model

configure server roles (Microsoft Documentation: Roles and Permissions (Analysis Services))
configure SSAS database roles (Microsoft Documentation: Security Roles (Analysis Services – Multidimensional Data))
implement dynamic security (custom security approaches) (Microsoft Documentation: Authorizing access to objects and operations (Analysis Services))
role-based access (Microsoft Documentation: Roles and Permissions (Analysis Services))
test security permissions (Microsoft Documentation: Roles and Permissions (Analysis Services))
implement cell-level permissions (Microsoft Documentation: Grant custom access to cell data (Analysis Services))

Implement a tabular data model

define tables (Microsoft Documentation: Create a Dimension by Using an Existing Table)
import data (Microsoft Documentation: Multidimensional Model Data Access (Analysis Services – Multidimensional Data))
define calculated columns (Microsoft Documentation: Defining Calculated Members)
define relationships (Microsoft Documentation: Dimension Relationships)
define hierarchies and perspectives (Microsoft Documentation: Attributes and Attribute Hierarchies)
manage the visibility of columns and tables
embed links (Microsoft Documentation: Multidimensional Model Solution Deployment)
optimize BISM for Power View
mark a date table (Microsoft Documentation: Specify Mark as Date Table for use with time intelligence)
sort a column by another column (Microsoft Documentation: Sort Data in a Table)

Implement business logic in a tabular data model

implement measures and KPIs (Microsoft Documentation: Key Performance Indicators (KPIs) in Multidimensional Models)
implement Data Analysis Expressions (DAX)
define relationship navigation
implement time intelligence (Microsoft Documentation: Define Time Intelligence Calculations using the Business Intelligence Wizard)
implement context modification (Microsoft Documentation: Analysis Services Personalization Extensions)

Implement data access for a tabular data model

manage partitions (Microsoft Documentation: Partitions in Multidimensional Models)
processing (Microsoft Documentation: Processing Options and Settings (Analysis Services))
select xVelocity versus DirectQuery for the data access (Microsoft Documentation: DirectQuery mode in tabular models)

Domain 4- Build a report with SQL Server Reporting Services (SSRS) (25-30%)

Design a report

select report components (crosstab report, Tablix, design chart, data visualization components)
design report templates (Report Definition Language) (Microsoft Documentation: Report Definition Language (SSRS))
identify the data source and parameters (Microsoft Documentation: Set Data Source Properties (SSAS Multidimensional))
design a grouping structure (Microsoft Documentation: Automatically Grouping Attribute Members)
drill-down reports
drill-through reports (Microsoft Documentation: Defining and Using a Drillthrough Action)
determine if any expressions are required to display data that is not coming directly from the data source (Microsoft Documentation: Data Sources and Bindings (SSAS Multidimensional))

Implement a report layout

formatting (Microsoft Documentation: Modifying Measures)
apply conditional formatting (Microsoft Documentation: Use conditional formatting in tables)
page configuration
implement headers and footers
implement matrixes, table, chart, images, list, indicators, maps, and groupings in reports (Microsoft Documentation: Tables, Matrices, and Lists (Report Builder and SSRS))
use Report Builder to implement a report layout (Microsoft Documentation: Creating an RDL Layout Report)
create a range of reports using different data regions (Microsoft Documentation: Data Regions and Maps (Report Builder and SSRS))
define custom fields (implementing different parts of the report)
implement collections (global collections)
define expressions (Microsoft Documentation: Expression Examples (Report Builder and SSRS))
implement data visualization components
identify report parts (Microsoft Documentation: Report Parts (Report Builder and SSRS))
implement group variables and report variables (Microsoft Documentation: Built-in Collections – Report and Group Variables References (Report Builder))
design for multiple delivery extension formats (Microsoft Documentation: Reporting Services Delivery Extension Settings)

Configure authentication and authorization for a reporting solution

configure server-level and item-level role-based security (Microsoft Documentation: Role definitions – predefined roles)
configure reporting service security (setup or addition of role) (Microsoft Documentation: Grant user access to a report server)
authenticate against data source, store credential information
describe Report Server security architecture and site-level security
create system-level roles, item-level security (Microsoft Documentation: Role definitions – predefined roles)
create a new role assignment
assign Windows users to roles, secure reports using roles (Microsoft Documentation: Grant user access to a report server)
configure SharePoint groups and permissions (Microsoft Documentation: SharePoint site permissions)
define varying content for different role memberships (Microsoft Documentation: Manage role groups)

Implement interactivity in a report

drill down
drill through (Microsoft Documentation: Defining and Using a Drillthrough Action)
interactive sorting (Microsoft Documentation: Add Interactive Sort to a Table or Matrix (Report Builder and SSRS))
parameters: (databound parameters, multivalue parameters) (Microsoft Documentation: Add a multi-value parameter to a Report)
create dynamic reports in SSRS using parameters (Microsoft Documentation: Report Parameters (Report Builder and Report Designer))
implement show/hide property
actions (jump to report)
filters (Microsoft Documentation: Add a Filter to a Dataset (Report Builder and SSRS))
parameter list (Microsoft Documentation: Report Parameters (Report Builder and Report Designer))
fixed headers (Microsoft Documentation: Page Headers and Footers (Report Builder and SSRS))
document map, embedded HTML (Microsoft Documentation: Create a Document Map (Report Builder and SSRS))

Troubleshoot reporting services issue

query the ReportServer database (Microsoft Documentation: Report Server Database (SSRS Native Mode))
view Reporting Services log files (Microsoft Documentation: Reporting Services Log Files and Sources)
use Windows Reliability and Performance monitor data for troubleshooting
use the ReportServer: define service and web service objects (Microsoft Documentation: Report Server Web Service Endpoints)
monitor for long-running reports, rendering, and connectivity issues (Microsoft Documentation: Process Large Reports)
use SQL Profiler (Microsoft Documentation: Monitor Analysis Services with SQL Server Profiler)
perform data reconciliation for incorrect relationships or aggregations
detect dynamic security issues
validate logic and calculations

Manage a report environment

manage subscriptions and subscription settings (Microsoft Documentation: Subscriptions and Delivery (Reporting Services))
define data-driven subscriptions (Microsoft Documentation: Data-Driven Subscriptions)
manage data sources (Microsoft Documentation: Manage Report Data Sources)
integrate SharePoint Server (Microsoft Documentation: Reporting Services Report Server (SharePoint Mode))
define email delivery settings (Microsoft Documentation: E-Mail Delivery in Reporting Services)
manage the number of snapshots (Microsoft Documentation: Create, Modify, and Delete Snapshots in Report History)
manage schedules, running jobs, and report server logs (Microsoft Documentation: Create, Modify, and Delete Schedules)
manage report server databases (Microsoft Documentation: Report Server Database (SSRS Native Mode))
manage the encryption keys
set up the execution log reporting (Microsoft Documentation: Report Server ExecutionLog and the ExecutionLog3 View)
review the reports
configure site-level settings (Microsoft Documentation: Reporting Services site settings and site features (SharePoint mode))
design report lifecycle
automate the management of reporting services (Microsoft Documentation: Reporting Services Tools)
create a report organization structure (Microsoft Documentation: Create an organization report hierarchy)
install and configure reporting services (Microsoft Documentation: Install SQL Server Reporting Services)
deploy custom assemblies (Microsoft Documentation: Deploying a Custom Assembly)

Configure report data sources and datasets

select appropriate query types (stored procedure versus table versus text only) (Microsoft Documentation: Text-based Query Designer User Interface (Report Builder))
configure parameterized connection strings (dynamic connection strings) (Microsoft Documentation: Create data connection strings – Report Builder & SSRS)
define filter location (dataset versus query) (Microsoft Documentation: Filter, Group, and Sort Data (Report Builder and SSRS))
configure data source options, for example, extract and connect to multiple data sources (Microsoft Documentation: Create, Modify, and Delete Shared Data Sources (SSRS))
shared and embedded data sources and datasets (Microsoft Documentation: Embedded and Shared Datasets (Report Builder and SSRS))
use custom expressions in data sources (Microsoft Documentation: Expression Uses in Reports (Report Builder and SSRS))
connect to Microsoft Azure SQL database
implement DAX and MDX queries to retrieve appropriate data sets (Microsoft Documentation: DAX overview)
work with non-relational data sources, such as XML or SharePoint lists (Microsoft Documentation: Data Sources Supported by Reporting Services (SSRS))

Preparatory Guide for Microsoft Exam 70-466

The key to successfully pass an exam is by preparing right. Preparations demand consistency and determination. There are plenty resources available, for your preparations. You must have the right information and tools to crack the exam. We present you our specially curated preparatory guide to help you achieve the Microsoft 70-466 Exam Certification in the very first attempt. Let’s discuss step by step.

Step 1 – Refer the Official Microsoft 70-466 Exam Guide

Firstly start off by visiting the official website of Microsoft. This is a smarter move because you should always go by the most trusted website to get the authentic information. You can easily locate all the information regarding the 70-466 exam. From exam pattern to all the included modules and study materials are covered on the portal itself. Familiarise yourself with all the objectives and course domains of the exam. Devote enough time to each topic and have in depth knowledge of the subject. Moreover, this will also result in strengthening your preparation.

Step 2 – Study the traditional way through books

Books are the most important ingredient of preparation. Books are a good resource to acquire knowledge without any disturbance. There are a vast variety of books available in the market which you can use to prepare for the Microsoft 70-466 Exam. Therefore, our suggestion to you would be to find books for the Microsoft 70-466 exam from genuine sources.

Step 3 – Go for Training Course

Microsoft provides its own Training Courses for the preparation of this Exam. We know that being a practical exam, it requires hands on training. To solve this issue Microsoft has come up instructor led training course.

The Microsoft instructor led training is similar to the traditional classroom training setting. It features IT experts with in-depth knowledge of the 70-466 exam objectives to help you pass your exam at the first attempt.

Course 20466-D: Implementing Data Models and Reports with SQL Server 2014

The focus of this five-day instructor-led course is on creating managed enterprise BI solutions. It describes how to implement multidimensional and tabular data models, deliver reports with Microsoft SQL Server Reporting Services, create dashboards with Microsoft SharePoint Server PerformancePoint Services, and discover business insights by using data mining.

Skills gained

Firstly, Describe the components, architecture, and nature of a BI solution.
Secondly, Create a multidimensional database with Analysis Services.
Thirdly, Implement dimensions in a cube.
Implement measures and measure groups in a cube.
Also, UseMDX Syntax.
Further, Customize a cube.
Furthermore, Implement a Tabular Data Model in SQL Server Analysis Services.
Moreover, UseDAX to enhance a tabular model.
Additionally, Create reports with Reporting Services.
Not to mention, Enhance reports with charts and parameters.
Likewise, Manage report execution and delivery.
Then, Implement a dashboard in SharePoint Server with PerformancePoint Services.
Lastly, Use Data Mining for Predictive Analysis.

Step 4 – Join Online Forums

Joining a forum is an excellent way of preparing for an exam. You’ll get to connect with other likeminded individuals preparing for the same exam. Such groups allow you and others to share tips, doubts, experiences and challenges with each other. You get to learn from the expertise of your peers and therefore joining a community is an important step while preparing.

Step 5 – Practice Tests

Practice tests are significant to familiarise yourself with the exam format. They keep your preparations under check and are a great way to identify gaps in your knowledge that you can work upon. Further, practice tests provide exact insights about the exam. Also attempting multiple practice tests help you prepare well. There are plenty of practice tests available. Remember to choose the right and authentic ones. Start practising for Microsoft 70-466 Now!