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
Geen opmerkingen:
Een reactie posten