Audience Profile

This is an upgrade exam allowing the candidate to upgrade their MCITP: Business Intelligence Developer 2008 certification to MCSE: Business Intelligence.


Build an Analysis Services Database

  • 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

  • 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 SQL Server Data Tools (SSDT) to build the cube; use SSDT 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

  • Create Multidimensional Expressions (MDX) queries.

    • 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 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

Manage, Maintain, and Troubleshoot an SSAS Database

  • 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

Build a Tabular Data Model

  • 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 Power View; mark a date table; sort a column by another column

Build a Report with SQL Server Reporting Services (SSRS)

  • 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

  • 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

  • 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

Plan Business Intelligence (BI) Infrastructure

  • 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 vs. query performance in SSAS; appropriately index a fact table; optimize Analysis Services cubes in UDM; create aggregations using Usage-Based Optimizations

  • Plan for scalability.

    • This objective may include but is not limited to: Multidimensional OLAP (MOLAP); Relational OLAP (ROLAP); Hybrid OLAP (HOLAP)

Design BI Infrastructure

  • Design a security strategy.

    • This objective may include but is not limited to: configure security and impersonation between database, analysis services, and front end; implement Dynamic Dimension Security within a cube; configure security for an extranet environment; configure Kerberos security; plan and 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 backup strategy.

    • This objective may include but is not limited to: design a high availability (HA) and disaster recovery strategy

Design a Reporting Solution

  • 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 vs. filter-based processing; managing data sets through the use of stored procedures

  • Design a data acquisition strategy.

    • This objective may include but is not limited to: identify the data sources that need 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; which data can be retained for how long (regulatory compliance, data archiving, aging); design a data movement strategy; profile source data

  • 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 and implement context transfer when interlinking all types of reports (Reporting Services, Report Builder, Crescent, Excel, PowerPivot); implement BI tools for reporting in SharePoint (Excel Services vs. Performance Point vs. Reporting Services); select a subscription strategy

Design BI Data Models

  • 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 a partitioning strategy for the data warehouse and cube; design the data file layout for a data warehouse for maximum performance; 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 and create MDX calculations.

    • 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

Design an ETL Solution

  • 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; understand how SSIS packages/projects interact with environments; choose between performing aggregation operations in the SSIS pipeline or the relational engine