Exam 70-467: Designing Business Intelligence Solutions with Microsoft SQL Server 2012

The Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70-467) examination is conducted by Microsoft. This examination prepares candidates for the Microsoft Exam 70-467. Also, this course will provide all the skills and knowledge for the following areas – Performance planning and Scalability planning.

Target Audience

This certification will bring the best out of those candidates who are into business intelligence (BI) architecture, Also, for those who are responsible for the overall design of a BI infrastructure and how it relates to other data systems in use.

Exam Overview

The Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70-467) consists of approximately 45-55 questions. This test format is multiple choice and multiple choice multiple answer. You may also see several scenario questions. The examination will cost you $165 USD. Also, the total time duration given to complete the examination is 120 minutes.

Exam Registration

For registering yourself for Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70-467)  examination you are required to follow the following steps:

• Click on Schedule your exam on the official Microsoft page.
• Login in your Microsoft account using your email id, if you haven’t created an account on Microsoft you are required to signup first before login in.
• Follow the instructions given the site and select the available date and time slot and make the payment.

Exam Policies

There are a few policies and regulations that candidates should adhere to. It not mandatory to go through these policies. However, it is highly recommended to read these policies so as to avoid any kind of discomfort in the future.

Exam Retake Policy

If the candidate failed to achieve the passing score, then he/she has to wait for 24 hours before reapplying to the examination. The candidate can go to their certificate dashboard and reschedule the exam themselves. The candidate can reappear for the examination only five times. Failure in the second attempt will result in a waiting time of 14 days before rescheduling your third attempt. The waiting period for the fourth and the fifth attempts will also be 14 days. 

Exam Cancellation Policy

Microsoft offers candidates to cancel or reschedule their exams within a minimum of 24 hours prior to the exam date. However, to prevent any cancellation fee, you must cancel or reschedule your exam, at least 6 business days prior to the date scheduled for your exam. Also, if you fail to appear in the exam, then you will not receive any refund of the exam fee.

Re-certification Policy

Microsoft certification is expected to expire when the products are out of mainstream support although the person`s certification will be recognized. Officially, the certification will never expire.

You can go through the full policies at: https://docs.microsoft.com/en-us/learn/certifications/certification-exam-policies

Clarify your doubts, visit Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70-467) FAQ.

Course Outline for Exam 70-467

The Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70-467) exam covers the following topics –

Plan business intelligence (BI) infrastructure (15–20%)

Plan for performance

• optimize batch procedures: extract, transform, load (ETL) in SQL Server Integration (Microsoft Documentation: Extract, transform, and load (ETL))
• Services (SSIS)/SQL and processing phase in Analysis Services (Microsoft Documentation: Analysis Services Processing Task)
• configure Proactively caching within SQL Server Analysis Services (SSAS) for different scenarios (Microsoft Documentation: Partitions – Proactive Caching)
• understand the performance consequences of named queries in a data source view (Microsoft Documentation: Define Named Queries in a Data Source View (Analysis Services))
• analyze and optimize performance, including Multidimensional Expression (MDX) and Data Analysis Expression (DAX) queries (Microsoft Documentation: DAX for multidimensional models)
• understand the difference between partitioning for load performance versus query performance in SSAS (Microsoft Documentation: Partitioned Tables and Indexes, Create and Manage a Local Partition)
• appropriately index a fact table
• optimize Analysis Services cubes in SQL Server Data Tools
• create aggregations (Microsoft Documentation: Use Aggregate Functions)

Plan for scalability

• change binding options for partitions (Microsoft Documentation: Processing Options and Settings (Analysis Services), Data Sources and Bindings (SSAS Multidimensional)
• choose the appropriate Multidimensional OLAP(MOLAP) (Microsoft Documentation: Set Partition Storage (Analysis Services – Multidimensional))
• Relational OLAP (ROLAP), and Hybrid OLAP (HOLAP) storage modes

Plan and manage upgrades

• plan change management for a BI solution (Microsoft Documentation: BI solution architecture)

Maintain server health

• draft an automation strategy

Design BI infrastructure (15–20%)

Design a security strategy

• configure security and impersonation between SQL Server service, analysis services, and front end (Microsoft Documentation: Impersonation)
• implement Dynamic Dimension Security within a cube (Microsoft Documentation: Implement row-level security in an Analysis Services tabular model)
• configure security for an extranet environment
• configure Kerberos security (Microsoft Documentation: Kerberos Authentication Overview)
• design authentication mechanisms
• design security tests
• build secure solutions end to end
• design roles for calculated measures (Microsoft Documentation: Create and manage measures)
• understand the tradeoffs between regular SSAS security and dynamic security (Microsoft Documentation: Implement row-level security in an Analysis Services tabular model)

Design a SQL partitioning strategy

• choose the proper partitioning strategy for the data warehouse and cube (Microsoft Documentation: Partitions in Multidimensional Models)
• implement a parallel load to fact tables by using partition switching (Microsoft Documentation: Tabular model partitions)
• use data compression (Microsoft Documentation: Data Compression)

Design a high availability and disaster recovery strategy

• draft a recovery strategy (Microsoft Documentation: Business continuity and database recovery – SQL Server)
• back up and restore SSAS databases (Microsoft Documentation: Backup and Restore of Analysis Services Databases)
• back up and restore SSRS databases (Microsoft Documentation: Backup and Restore Operations for Reporting Services)
• move and restore the SSIS Catalog (Microsoft Documentation: SSIS Catalog)
• design an AlwaysON solution. (Microsoft Documentation: Always On availability groups: a high-availability and disaster-recovery solution)

Design a logging and auditing strategy

• draft a new SSIS logging infrastructure (for example, information available through the catalog views) (Microsoft Documentation: Integration Services (SSIS) Logging)
• validate data is balancing and reconciling correctly.

Design a reporting solution (20–25%)

Design a Reporting Services dataset

• draft appropriate data query parameters (Microsoft Documentation: Use query parameters to customize responses)
• create appropriate SQL queries (Microsoft Documentation: Create and query database objects)
• create appropriate DAX queries for an application (Microsoft Documentation: DAX overview)
• manage data rights and security
• extract data from analysis services by using MDX queries(Microsoft Documentation: MDX Query – The Basic Query)
• balance query-based processing versus filter-based processing (Microsoft Documentation: Query Processing Architecture Guide)
• manage data sets through the use of stored procedures (Microsoft Documentation: Manage Data Collection)

Manage Excel Services/reporting for SharePoint

• configure data refresh schedules for PowerPivot published to SharePoint (Microsoft Documentation: Configure scheduled data refresh for Power Pivot by using the unattended data refresh account)
• publish BI info to SharePoint (Microsoft Documentation: Embed the Power BI project report in SharePoint Online)
• use SharePoint to accomplish BI administrative tasks
• install and configure Power View (Microsoft Documentation: Activate the report server and Power View integration features in SharePoint)
• publish PowerPivot and Power View to SharePoint (Microsoft Documentation: Power Pivot for SharePoint)

Design a data acquisition strategy

• identify the data sources that need to be used to pull in the data (Microsoft Documentation: Data sources in Power BI Desktop)
• determine the changes (incremental data) in the data source (time window) (Microsoft Documentation: Using incremental refresh with dataflows)
• identify the relationship and dependencies between the data sources (Microsoft Documentation: Relationships)
• determine who can access which data
• determine 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

• choose the appropriate reporting services requirements (including native mode and SharePoint mode) (Microsoft Documentation: Comparing native and SharePoint Reporting Services report servers)

Design BI reporting solution architecture

• linked drill-down reports, drill-through reports, and subreports (Microsoft Documentation: Drillthrough, Drilldown, Subreports, and Nested Data Regions)
• design report migration strategies (Microsoft Documentation: Plan for report design and report deployment | Reporting Services)
• access report services API (Microsoft Documentation: Develop with the REST APIs for Reporting Services)
• design code-behind strategies
• identify when to use Reporting Services (RS), Report Builder (RB), or Power View (Microsoft Documentation: Finding, Viewing, and Managing Reports (Report Builder and SSRS ))
• design and implement context transfer when interlinking all types of reports (RS, RB, Power View, Excel) (Microsoft Documentation: Power View: Explore, visualize, and present your data)
• implement BI tools for reporting in SharePoint (Excel Services versus PowerView versus Reporting Services) (Microsoft Documentation: Comparing native and SharePoint Reporting Services report servers)
• select a subscription strategy
• enable Data Alerts (Microsoft Documentation: Data alerts in the Power BI service)
• draft map visualization

Design BI data models (30–35%)

Design the data warehouse

• draft a data model that is optimized for reporting (Microsoft Documentation: Optimization guide for Power BI)
• design and build a cube on top (Microsoft Documentation: Create a Cube Using the Cube Wizard)
• draft enterprise data warehouse (EDW) and OLAP cubes (Microsoft Documentation: Overview of Service Manager OLAP cubes for advanced analytics)
• choose between natural keys and surrogate keys when designing the data warehouse (Microsoft Documentation: Understand star schema and the importance for Power BI)
• use SQL Server to design, implement, and maintain a data warehouse, including partitioning
• slowly changing dimensions (SCD) (Microsoft Documentation: Slowly Changing Dimension Transformation)
• change data capture (CDC), Index Views, and column store indexes (Microsoft Documentation: About Change Data Capture (SQL Server))
• identify design best practices
• implement a many-to-many relationship in an OLAP cube (Microsoft Documentation: Defining a Many-to-Many Relationship)
• design a data mart/warehouse in reverse from an Analysis Services cube (Microsoft Documentation: Overview of Service Manager OLAP cubes for advanced analytics)
• implement incremental data load (Microsoft Documentation: Perform an Incremental Load of Multiple Tables)
• choose between performing aggregation operations in the SSIS pipeline or the relational engine (Microsoft Documentation: Aggregate Transformation)

Design a schema

• multidimensional modeling starting from a star or snowflake schema (Microsoft Documentation: Dimensions – Introduction, nderstand star schema and the importance for Power BI)
• design relational modeling for a Data Mart

Design cube architecture

• partition cubes and build aggregation strategies for the separate partitions (Microsoft Documentation: Partitions (Analysis Services – Multidimensional Data))
• design a data model (Microsoft Documentation: Design a data model in Power BI )
• choose the proper partitioning strategy for the data warehouse and cube (Microsoft Documentation: Partitions (Analysis Services – Multidimensional Data))
• design the data file layout
• identify the aggregation method for a measure in a MOLAP cube (Microsoft Documentation: Aggregations and Aggregation Designs)
• performance tune a MOLAP cube using aggregations
• design a data source view (Microsoft Documentation: Defining a Data Source View)
• design for cube drill-through and write back actions (Microsoft Documentation: Defining and Using a Drillthrough Action)
• choose the correct grain of data to store in a measure group (Microsoft Documentation: Defining Dimension Granularity within a Measure Group)
• draft analysis services processing by using indexes, indexed views, and order by statements (Microsoft Documentation: Processing Options and Settings (Analysis Services))

Design fact tables

• draft a data warehouse that supports many to many dimensions with factless fact tables (Microsoft Documentation: Understand star schema and the importance for Power BI)

Design BI semantic models

• plan for a multidimensional cube (Microsoft Documentation: Overview of Service Manger OLAP cubes for advanced analytics)
• support a many-to-many relationship between tables (Microsoft Documentation: Defining a Many-to-Many Relationship)
• choose between multidimensional and tabular, depending on the type of data and workload (Microsoft Documentation: Comparing tabular and multidimensional solutions)

Design and create MDX calculations

• draft MDX queries (Microsoft Documentation: MDX Query – The Basic Query)
• identify the structures of MDX and the common functions (tuples, sets, TopCount, SCOPE, VisualTotals, and more) (Microsoft Documentation: Working with Members, Tuples, and Sets (MDX))
• create calculated members in an MDX statement (Microsoft Documentation: MDX Calculated Members – Building Calculated Members)
• identify which MDX statement would return the required result
• implement a custom MDX or logical solution for a pre-prepared case task

Design an ETL solution (10–15%)

Design SSIS package execution

• use the new project deployment model (Microsoft Documentation: Deploy Integration Services (SSIS) Projects and Packages)
• pass values at execution time (Microsoft Documentation: Integration Services (SSIS) Package and Project Parameters)
• share parameters between packages
• plan for incremental loads versus full loads (Microsoft Documentation: Perform an Incremental Load of Multiple Tables)
• optimize execution by using Balanced Data Distributor (BDD) (Microsoft Documentation: Balanced Data Distributor Transformation)
• choose optimal processing strategy (including Script transform, flat-file incremental loads, and Derived Column transform) (Microsoft Documentation: Change Data Capture (SSIS), Derived Column Transformation)

Plan to deploy SSIS solutions

• deploy the package to another server with different security requirements (Microsoft Documentation: Deploy Integration Services (SSIS) Projects and Packages)
• 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) (Microsoft Documentation: Integration Services (SSIS) Projects and Solutions, SSIS Catalog)
• decide between performing aggregation operations in the SSIS pipeline or the relational engine (Microsoft Documentation: Aggregate Transformation, Data Flow Performance Features)
• plan to automate SSIS deployment (Microsoft Documentation: Deploy Integration Services (SSIS) Projects and Packages)
• plan the administration of the SSIS Catalog database. (Microsoft Documentation: SSIS Catalog)

Design package configurations for SSIS packages

• avoid repeating configuration information entered in SSIS packages, and use configuration files (Microsoft Documentation: Enable and configure package configurations)

Preparatory Guide for Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70-467)

Refer the Official Exam Guide

There is nothing wrong to say that the guide serves as complete coverage of the Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70-467) exam and its related domains. Candidates who are looking for a comprehensive review of information must visit and bookmark this guide so that they can get access to it anywhere and anytime they want.

Learning Resources

Instructor-led Training

Microsoft provides this 5-day instructor-led course for the candidates appearing in Designing Business Intelligence Solutions with Microsoft SQL Server 2012 (70-467) exam. This training helps the candidate to gain knowledge in the following domains:

• Plan a BI solution.
• Plan SQL Server BI infrastructure.
• Design a data warehouse.
• Draw an extract, transform and load (ETL) solution.
• Design analytical data models.
• Plan a BI delivery solution.
• Design a Reporting Services solution.
• Design a Microsoft Excel-based reporting solution.
• Plan a SharePoint Server BI solution.
• Monitor and optimize a BI solution.
• Operate a BI solution.

Prerequisite required for the Instructor-Lead training

This training will help the candidate to get a piece of better knowledge for preparing for the examination, however, there are few important skills that are required beforehand:

• A basic understanding of dimensional modeling (star schema) for data warehouses
• Basic server hardware knowledge
• The ability to create Integration Services packages that include control flows and data flows and a basic multidimensional cube with Analysis Services
• Should be able to create create a basic tabular model with PowerPivot and Analysis Services
• The ability to create Reporting Services reports with Report Designer
• The ability to implement authentication and permissions in the SQL Server database engine, Analysis Services, and Reporting Services
• Familiarity with SharePoint Server and Microsoft Office applications – particularly Excel

Microsoft Books

Microsoft provides reference books that can be helpful while studying for the exam. These books contain various useful resources that can be used while studying. Visit Microsoft Press books, to find relevant books to get a deeper insight into the exam objectives, and hence crack the test, and earn your certification. You can also buy these books from leading online stores. These books will help you prepare for the examination in a structural way.

Reference Books

Apart from the official Microsoft Books, there are plenty of more study books available in the market. Here, we have provided you with more books for your reference.

• Microsoft SQL 2012 BI MCSE Exam package (70-467) Designing Business Intelligence Solutions with Microsoft SQL Server 2012 CD-ROM

Join Microsoft Community

A healthy discussion is always beneficial, no matter where it is done. The prospects of getting resolutions to an issue increase steeply when a greater number of people are involved. These discussions make the studies more comprehensive. Forums work really well to build a community that is essential for understanding others. Interacting with people of the same goal puts you one step forward in achieving those goals. It is suggested that you should join Microsoft Community.

Evaluate with Practice Tests

The final step to success is to practice what you have learned. Taking a practice test is a great way to diversify your study strategy and ensure the best possible results for the final exam. Further, analyzing the practice test is very important so as to ensure complete preparation. We provide you with free sample papers to help you excel in the examination. Start practicing now!