70-467 Study Guide reformatted

Introduction

In this blogpost I've gathered and reformatted the study guide of the Microsoft exam 70-467 for SQL Server 2014. This exam is the last (depending on the sequence you take the exams, off course) of the MCSE BI certification. I decided to reformat the guide for a better overview of the topics covered for the exam. Hope that it helps studying the exam topics.

1. Plan business intelligence (BI) infrastructure (15-20%)

• Plan for performance
• 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
• Analyze and optimize performances of 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
• Optimize Analysis Services cubes in SQL Server Data Tools.
• Create aggregations.
• Understand performance consequences of named queries in a data source view.
• Plan for scalability
• Multidimensional OLAP (MOLAP).
• Relational OLAP (ROLAP).
• Hybrid OLAP (HOLAP).
• Change binding options for partitions.
• Plan and manage upgrades
•  Plan change management for a BI solution.
• Maintain server health
• Design an automation strategy. 

2. Design BI infrastructure (15-20%)

• Design a security strategy
• Configure security and impersonation between database, analysis services and frontend.
• Implement Dynamic Dimension Security within a cube.
• Configure security for an extranet environment.
• Configure Kerberos security.
• Skills in authentication mechanisms.
• Ability to build secure solutions end to end.
• Design security roles for calculated measures.
• Understand the tradeoffs between regular SSAS security and dynamic security
• Design a SQL partitioning strategy
• Choose the proper partitioning strategy for the data warehouse and cube.
• Implement a parallel load to fact tables by using partition switching.
• Use data compression in fact table.
• Design optimal data compression.
• 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 Catalog.
• Design an AlwaysON solution.
• Design a logging and auditing strategy
• Design a new SSIS logging infrastructure (for  example, information available through the catalog views).
• Validate data is balancing and reconciling correctly.

3. Design a reporting solution (20-25%)

• Design a Reporting Services dataset
• Data query parameters
• Managing data rights and security.
• Extracting data from analysis services.
• Balancing querybased processing versus filter-based processing.
• Managing data sets through the use of stored procedure.
• Create appropriate DAX queries for an application.
• Extract data from analysis services by using MDX queries
• 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 a data acquisition strategy
• 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.
• What data can be retained for how long (regulatory compliance, data archiving, aging).
• Design a data movement strategy .
• Profile source data.
• Customize data acquisition using DAX with reporting services data sources.
• Plan and manage reporting services configuration
• Native mode.
• Choose the appropriate reporting services requirements (including native mode and SharePoint mode).
• Design BI reporting solution architecture
• 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, ReportBuilder, or Crescent.
• design/implement context transfer when interlinking all types of reports (RS, RB, Crescent, Excel, PowerPivot).
• Implement BI tools for reporting in SharePoint Excel Services versus Performance Point versus Reporting Services).
• Select a subscription strategy.
• Identify when to use Reporting Services, Report Builder, or Power View.
• Design/implement context transfer when interlinking all types of reports (RS, RB, Power view, Excel).
• Implement BI tools for reporting in SharePoint (Excel Services versus Power View versus Reporting
• Services).
• Enable Data Alerts.
• Design map visualization.

4. Design BI data models (30-35%)

• Design the data warehouse
• Design a data model that is optimized 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 the facilities available in SQL Server to design, implement and maintain a data warehouse (partitioning, slowly changing dimensions (SCD), change data capture (CDC), Clustered Index Views, etc.).
• 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 (or empty Analysis Services cube that was created referring requirements).
• Choose between performing aggregation operations in the SSIS pipeline or the relational engine.
• Use SQL Server to design, implement, and maintain a data warehouse (including partitioning, slowly.
• Changing dimensions [SCD], change data capture  [CDC], Index Views, and columnstore indexes).
• Design a data mart/warehouse in reverse from an Analysis Services cube.
• Design a schema
• Multidimensional modeling starting from a star or snowflake schema.
• Relational modeling for a Data Mart.
• 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.
• Given a requirement, identify the aggregation method that should be selected for a measure in a MOLAP cube.
• Performance tune a MOLAP cube using aggregations.
• Design a data source view.
• 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 fact tables
• Design a data warehouse that supports many to many dimensions with factless fact tables.
• Design BI semantic models Revised task – new full definition:
• Plan for a multidimensional cube.
• Write a UDM model with many to many (this is related to MDX/BISM code, but it is a good example for exercises).
• Choose between UDM and BISM depending on the type of data and workload.
• Plan for a multidimensional cube.
• Support a many to-many relationship between tables.
• Choose between multidimensional and tabular depending on the type of data and workload.
• Design and create MDX calculations
• MDX authoring.
• Identify the structures of MDX and the common functions (tuples, sets, topcount, SCOPE etc.) .
• Identify which MDX statement would return the required result (single result and multiple MDX options provided to test taker).
• Implement a custom MDX or logical solution for a pre-prepared case task.
• Create calculated members in an MDX statement.

5. Design an ETL solution (10-15%)

• Design SSIS package execution
• Using new project deployment model.
• Passing values at execution time.
• share parameters between packages.
• Plan for incremental loads vs. full loads.
• Optimize 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 Catalog database.
• Design package configurations for SSIS packages
• Avoid repeating configuration information entered in SSIS packages and use configuration file.

Conclusion

This blogpost is about reformatting of the study guide for the 70-467 exam.

Greetz,

Hennie