Course: Designing Business Intelligence Solutions with Microsoft SQL Server (exam 70-467)

$0.00

$0.00 incl. vat

duration: 27 hours |

Language: English (US) |

access duration: 180 days |

Details

In this SQL Server training course you will learn to create business intelligence solutions. You will learn about the design, installation and maintaining the various components of the BI platform.

Among subjects that covered are data modeling, data warehouse architectuur, SSIS, SSRS, multidimensional cubes, SSAS security, PowerPivot, MDX/DAX, data Mining and far more.

Result

After completing this course you are familiar with designing business intelligence solutions based on SQL Server 2012. You are also prepared for the MCSE exam 70-467.

Note: As of January 31, 2021, this certification will expire. If you would like advice on which training to take then, please contact our support team.

Prerequisites

You have completed MCSA: SQL Server 2012. You also have completed this course: Implementing Data Models and Reports with Microsoft SQL Server 2012 (exam 70-466).

Target audience

Database Administrators

Content

Designing Business Intelligence Solutions with Microsoft SQL Server (exam 70-467)

27 hours

Microsoft SQL Server 2014 - 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

Microsoft SQL Server 2014 - 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

Microsoft SQL Server 2014 - 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

Microsoft SQL Server 2014 - 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

Microsoft SQL Server 2014 - 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

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

Microsoft SQL Server 2014 - 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

Course options

We offer several optional training products to enhance your learning experience. If you are planning to use our training course in preperation for an official exam then whe highly recommend using these optional training products to ensure an optimal learning experience. Sometimes there is only a practice exam or/and practice lab available.

Optional practice exam (trial exam)

To supplement this training course you may add a special practice exam. This practice exam comprises a number of trial exams which are very similar to the real exam, both in terms of form and content. This is the ultimate way to test whether you are ready for the exam.

Optional practice lab

To supplement this training course you may add a special practice lab. You perform the tasks on real hardware and/or software applicable to your Lab. The labs are fully hosted in our cloud. The only thing you need to use our practice labs is a web browser. In the LiveLab environment you will find exercises which you can start immediately. The lab enviromentconsist of complete networks containing for example, clients, servers,etc. This is the ultimate way to gain extensive hands-on experience.

WHY_ICTTRAININGEN

Via ons opleidingsconcept bespaar je tot 80% op trainingen

Start met leren wanneer je wilt. Je bepaalt zelf het gewenste tempo

Spar met medecursisten en profileer je als autoriteit in je vakgebied.

Ontvang na succesvolle afronding van je cursus het officiële certificaat van deelname van Icttrainingen.nl

Krijg inzicht in uitgebreide voortgangsinformatie van jezelf of je medewerkers

Kennis opdoen met interactieve e-learning en uitgebreide praktijkopdrachten door gecertificeerde docenten

Orderproces

Once we have processed your order and payment, we will give you access to your courses. If you still have any questions about our ordering process, please refer to the button below.

What is included?

Certificate of participation	Yes
Monitor Progress	Yes
Award Winning E-learning	Yes
Examination possible	Yes
Mobile ready	Yes
Sharing knowledge	Unlimited access to our IT professionals community
Study advice	Our consultants are here for you to advice about your study career and options
Study materials	Certified teachers with in depth knowledge about the subject.
Service	World's best service

Platform

Na bestelling van je training krijg je toegang tot ons innovatieve leerplatform. Hier vind je al je gekochte (of gevolgde) trainingen, kan je eventueel cursisten aanmaken en krijg je toegang tot uitgebreide voortgangsinformatie.

FAQ

Niet gevonden wat je zocht? Bekijk alle vragen of neem contact op.

Cursusnaam
Looptijd
Rentebedrag financiering
Totaal bedrag
Maandbedrag

Vendors

Professions