zondag 31 augustus 2014

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