Inhoud ICT-cursus 70-467 Designing Business Intelligence Solutions with Microsoft SQL Server

Inhoud van de ICT-cursus 70-467 Designing Business Intelligence Solutions with Microsoft SQL Server | De Kantooropleider

Tijdens de ICT-cursus 70-467 Designing Business Intelligence Solutions with Microsoft SQL Server worden de volgende onderdelen behandeld:

Designing BI Solutions: BI Infrastructure Design

  • start the course
  • describe the elements of a BI infrastructure and explain data sources
  • describe a data warehouse and ETL for a BI infrastructure
  • describe analytical data models and the reporting and analysis element in a BI infrastructure
  • describe the role Windows Server, SQL Server, SharePoint, and Office applications play in a BI infrastructure
  • describe the considerations and guidelines for updating an existing BI solution
  • describe the key features in a BI project
  • describe the applications, development, and test infrastructure as well as the personnel to build the BI project
  • plan system sizing and workload for a BI infrastructure
  • describe the different types of server topologies for a BI solution
  • plan for scaling out a BI solution
  • plan for high availability for a BI solution
  • determine the MCR, CPU, and RAM requirements for a data warehouse database
  • determine the storage requirements for a data warehouse database
  • describe the key tasks and steps in designing a data warehouse database in a BI project
  • design a dimensional model for the business process using a matrix design and document it
  • design dimension keys, attributes, and hierarchies, given a scenario
  • design slowly changing data and unknown values for a dimension
  • design time dimension tables based on a scenario
  • design self-referencing dimension tables based on a scenario
  • describe the types of columns in a fact table and how to determine the level of grain
  • describe the types of measures in a fact table and the types of fact tables commonly used
  • describe causes of data warehouse I/O activity and the considerations for placement of database files
  • describe table partitioning, including benefits, and create a partitioned table
  • describe the considerations for using indexes and the types of indexes
  • create indexes and assess their performance
  • describe data compression and its benefits
  • implement compression
  • describe the guidelines for planning views and create views based on them
  • understand the considerations involved in BI solution design

Designing BI Solutions: Extract, Transform, and Load

  • start the course
  • describe the common ETL data flow architectures used in a BI Project
  • describe the considerations that affect the design of data extraction from the data sources
  • describe how to minimize the load times and hardware resource utilization, and plan the extraction windows
  • identify where transformations should take place and determine which transformation should be used
  • design the ETL solution to support slowly changing dimensions (SCD)
  • design ETL solutions to support change data capture
  • handle invalid rows by redirecting the rows
  • use the Balanced Data Distributor, BDD, transformation to optimize package execution
  • determine whether to use an incremental load or a full load on the data
  • determine if aggregation operations should be completed directly in the SSIS pipeline or in the relational database
  • load partitioned fact tables using best practices for an optimal load
  • handle errors through error handling techniques and log audit information
  • describe the differences between the package deployment model and the project deployment model, and project deployment model features
  • use parameters in packages, including sharing them
  • use the Integration Service Deployment Wizard to deploy projects to the SSISDB catalog using the project deployment model
  • set up a server environment to specify runtime values for packages within a project
  • use package configuration files to modify the properties of package elements at run time
  • build the SSIS project to create a package deployment utility with the packages for deployment
  • copy the deployment folder and run the Package Installation Wizard to install packages to the file system or instance
  • configure security on packages requiring different security requirements
  • manage the SSIS catalog, including the modifying the catalog properties
  • use catalog stored procedures and views to manage SSIS objects
  • backup and restore the SSIS catalog, including moving it
  • design ETL solutions, including package and project deployment and managing the ETL solution, in a scenario

Designing BI Solutions: Data Models

  • start the course
  • describe the two types of BISM data models and how they can be used to create a unified Analysis Services platform
  • describe the considerations for choosing an analytical data model for an SSAS solution
  • choose between a star and snowflake schema for a multidimensional data model
  • identify how to design a relational model for a Data Mart
  • determine the appropriate Proactive Caching method within SSAS for different scenarios
  • describe how to plan for a multidimensional cube
  • design data sources for analytical models, including the types of data source and the considerations
  • create data sources and data source views, including relationships, for analytical models
  • use the cube wizard to create a cube for a multidimensional model
  • configure measures, including identifying the aggregation method
  • define measure groups
  • design and configure dimensions in a multidimensional model
  • design and configure relationships between tables
  • design and create aggregations to improve cube performance
  • design and configure cubes for drill-through
  • design indexes, index views, and order by statement for SSAS processing
  • import tables and configure relationships and measures for a tabular model
  • design and configure attributes and dimensions for a tabular model
  • choose the appropriate partitioning strategy for a cube
  • design aggregations strategies for separate partitions
  • create partitions for a cube, including specifying the aggregations and storage
  • configure binding options for partitions
  • configure data compression options on fact table partitions
  • design and configure write back for a cube
  • design data models and SSAS solutions in a given scenario

Designing BI Solutions: Reporting Services

  • start the course
  • describe how reporting and analysis fit into a BI solution and how data sources are used
  • describe the key characteristics of common reporting scenarios used for BI solutions
  • describe the tools offered to create and delivery reports
  • describe how SharePoint can be used for reporting and describe the guidelines for choosing the correct reporting tool
  • describe the Reporting Services infrastructure
  • describe the key considerations for reports in a BI solution
  • describe the Reporting Services modes and when to choose a mode, given a scenario
  • plan for report delivery, including planning for security
  • design for specific report types, including interactive, graphical, and report rendering
  • describe the requirements, hardware and software, for the different SSRS configurations
  • describe the considerations for designing data source and datasets for a report
  • design a data movement strategy and profile source data
  • create a shared or embedded data source
  • create stored credentials to determine who can access the data in the report
  • design for data retention
  • customize data acquisition using DAX with reporting services data sources
  • create a dataset using query parameters
  • manage datasets using store procedures
  • create appropriate DAX queries for an application
  • extract data from analysis services by using MDX queries
  • manage data rights and security for a dataset
  • design and create a report template to ensure report consistency
  • design and create linked reports, including sub-reports
  • design and create a drill-through report
  • design map visualization and create a report with a map
  • design a Reporting Services solution, including designing and planning the infrastructure, data sources, datasets, and different report types

Designing BI Solutions: SharePoint Integration

  • start the course
  • describe how SharePoint Server can fit into a BI Solution and the supported topologies
  • describe the features provided when SharePoint is integrated with a BI Solution
  • describe the steps taken to fully integrate SharePoint Server with a BI solution
  • install PowerPivot for SharePoint
  • install the PowerPivot for SharePoint add-in to add additional functionality
  • configure PowerPivot using the PowerPivot for SharePoint 2013 Configuration tool
  • install Reporting Services in SharePoint mode to enable and use Power View
  • install the Reporting Services add-in for SharePoint
  • enable Report Server content types to a SharePoint library
  • use a PowerPivot workbook and publish it to SharePoint
  • create and publish a report using Power View to SharePoint
  • describe the two types of schedules, what you can accomplish with them, and how they work
  • describe the requirements for security for report processing and scheduling, including credentials for data sources and accounts
  • create and manage a schedule for Reporting Services in Native mode using Report Manager
  • create and manage a schedule for Reporting Services in SharePoint mode using Central Administration
  • plan for data refresh for PowerPivot in SharePoint, including security
  • configure data refresh schedules for PowerPivot published to SharePoint and perform an interactive refresh
  • set different processing options for reports in a SharePoint library, including caching, time-out, history options and limits, and database time-out options
  • plan a subscription strategy for report delivery
  • create a subscription for Report Servers in Native mode
  • create a subscription for Report Servers in SharePoint mode to deliver a report to a library
  • describe what data alerts are and how to use them
  • create a data alert for a report in a SharePoint library
  • demonstrate how to integrate SharePoint into Microsoft SQL Server 2014

Designing BI Solutions: MDX Queries and Performance

  • start the course
  • describe the key concepts in MDX
  • describe the basic structure of an MDX query
  • describe the common MDX functions
  • use MDX set functions
  • use MDX set functions
  • use MDX tuple functions
  • use the CALCULATE and SCOPE statements as well as the THIS function
  • create query-scoped calculated members using the WITH keyword
  • create session-scoped calculated members using the CREATE MEMBER statement
  • perform a simple case statement to compare an expression to a set of simple expressions to return specific values
  • perform a searched case statement to evaluate a set of Boolean expressions to return specific values
  • create MDX calculations to obtain the desired results
  • use Data Collector to monitor a SQL Server instance by gathering performance-related data
  • use Performance Monitor to troubleshoot data warehouse performance issues
  • use Resource Governor to balance load resources for an ETL load process
  • use Performance Monitor to troubleshoot SSAS performance
  • use SQL Server Profiler to analyze and optimize performance of MDX queries
  • use SQL Server Profiler to analyze and optimize performance of DAX queries
  • use Performance Monitor to monitor SSRS performance
  • configure memory settings for Reporting Services to ensure optimal performance
  • configure caching for a report and monitor its affects using Performance Monitor
  • monitor SQL Server instances to optimize performance in different scenarios

Designing BI Solutions: Availability and Recovery

  • start the course
  • describe the different authentication mechanisms supported by SSAS
  • configure SSAS to allow for impersonation using Kerberos
  • configure SSAS for trusted delegation using Kerberos
  • use SQL Server Profiler to test for impersonated or delegated identity
  • describe granting access to external users using HTTP and verify the prerequisites in IIS
  • configure external access to SSAS using HTTP by creating the application pool, virtual directory in IIS, and configure IIS authentication
  • configure external access to SSAS using HTTP by granting data access permissions and then testing the configuration
  • implement Dynamic Dimension Security within a cube
  • create roles and define permissions to grant access at different levels for multidimensional models
  • describe the high availability solutions that can be used by SQL Server
  • describe how AlwaysON Failover Cluster Instances can be used to ensure high availability for a SQL Server instance
  • describe how AlwaysON Availability Groups can be used for high availability for a SQL Server instance
  • describe how AlwaysON Availability Groups can be used with SSAS and SSRS
  • design a recovery strategy for SSAS and SSRS databases
  • perform a backup of SSAS databases
  • perform a backup of the SSRS databases
  • perform a restore of a SSAS database
  • perform a restore of the SSRS databases
  • configure logging within an ETL package, container, and task to capture run-time information
  • enable logging for package execution on the SSIS server
  • view log entries that are written after a package is run in the Log Events Window
  • view package execution reports provided by the SSIS catalog
  • manage security, high availability, back up and recovery, and logging for a BI infrastructure
Print pagina