Course: Implementing Data Models and Reports with Microsoft SQL Server (exam 70-466)

$0.00
$0.00 incl. vat

ADD_TO_CART_INFO_FOR_BUSINESS

duration: 36 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 the knowledge that is needed to create advanced reports using PowerPivot and tabular data models. The course also provides an introduction to create data visualizations by using Power View and SQL Server Reporting.

Among subjects that covered are the key features of business intelligence, developer tools, SSRS, SQL Server, report security, data alerts, cubes, MultiDimensional eXpressions, data mining, PowerPivot and far more.

Result

After completing this course you can create business intelligence solutions and develop advanced reporting and data visualization. You are also prepared for the MCSE exam 70-466

Prerequisites

You have completed the MCSA: SQL Server 2012.

Target audience

Database Administrators

Content

Implementing Data Models and Reports with Microsoft SQL Server (exam 70-466)

36 hours

Microsoft SQL Server 2014: Design and Install Analysis Services and Tools

  • start the course
  • explain the functionality that OLAP and data mining provide for business intelligence
  • explain the functionality that tabular mode provides for business intelligence
  • explain the functionality of SQL Server BI features with SharePoint, including PowerPivot
  • identify the different BI features to install based on a scenario
  • identify the appropriate data model to use for data analysis
  • identify the development and production considerations for installing SSAS
  • install SSAS in multidimensional mode
  • install SSAS in tabular mode
  • install PowerPivot for SharePoint with use in SSAS
  • upgrade an SSAS instance
  • update an SSAS instance
  • configure server and database level security for an SSAS instance
  • describe the environments available for building, deploying, and managing Analysis Services
  • install SQL Server Data Tools – Business Intelligence
  • describe the SQL Server Data Tools environment
  • deploy SQL Server Management Studio
  • describe the SQL Server Tools environment
  • design and install SQL Server Analysis Services

Microsoft SQL Server 2014: Designing Multidimensional Models and Data Sources

  • start the course
  • describe the logical architecture
  • describe the architecture of the cube, including what it contains
  • identify the different types of schemas that are used for a cube
  • describe the different types of data sources
  • create a data source
  • create a data source view
  • create a new project in SQL Server Data Tools, or SSDT, using a template
  • create a new project in SQL Server Data Tools, or SSDT, using an existing database
  • identify the folders, file types, and templates used in a project
  • describe a dimension and how it is used in a multidimensional model
  • describe what measures and measure groups are in a multidimensional model
  • describe measures and aggregation behavior
  • describe many-to-many relationships in Analysis Services
  • describe fact relationships in Analysis Services
  • describe referenced relationships in Analysis Services
  • design patterns for representing business facts and dimensions
  • design dimensions to support multiple related measure groups
  • identify the attributes for dimensions
  • describe what user-defined hierarchies are
  • define granularity of dimension relationships
  • describe and design multidimensional models, including dimensions, cubes, and measures

Microsoft SQL Server: Implement and Configure Cubes

  • start the course
  • create a new cube using the Cube Wizard
  • create an empty cube
  • create a cube using a template
  • view and navigate the cube schema
  • browse cube data and metadata to examine the structure of the cube
  • check data, calculations, formatting, and security of the database objects
  • create an action on a selected cube
  • view and change cube dimension properties
  • view and change cube hierarchy properties
  • view and change cube attribute properties
  • add dimensions to cubes through Cube Designer
  • add measures to cubes through Cube Designer
  • add measure groups through Cube Designer
  • define translations in a cube
  • create perspectives for a cube
  • implement many-to-many relationships in a cube
  • implement fact relationships in a cube
  • implement role-playing relationships in a cube
  • create and modify cubes in Cube Designers

Microsoft SQL Server 2014: Design and Implement Dimensions

  • start the course
  • create a dimension using the Dimension Wizard
  • configure a dimension structure using the Dimension Designer
  • configure dimension attribute relationships using the Dimension Designer
  • develop a custom attribute on a dimension
  • create user-defined hierarchies in a dimension
  • define a translation for dimension metadata
  • create a time dimension using the Dimension Wizard
  • modify a time dimension using the Dimension Designer
  • manage parent-child dimensions
  • create linked dimensions
  • create a basic measure
  • create a measure group
  • design granularity
  • create linked measure groups
  • use aggregation functions to create an additive measure
  • use aggregation functions to create a nonadditive measure
  • explain how to define a semiadditive measure
  • use aggregation functions to create a semiadditive measure
  • configure measure properties
  • configure measure group properties
  • design and create dimensions and measures given different scenarios

Microsoft SQL Server: Implement Partitions and Custom Logic

  • start the course
  • describe partitions in a multidimensional model, including the types Local and Remote, aggregations, and design strategy
  • describe the storage modes used for partitions, including multidimensional online analytical processing or MOLAP, relational online analytical processing or ROLAP, and hybrid online analytical processing or HOLAP
  • describe proactive caching for partitions
  • create a local partition by filtering a fact table
  • create a local partition using a table, view, or named query
  • set up the configuration for remote partitions, including specifying valid server names, creating and deploying a secondary database, and enabling features in SQL Server Management Studio or SSMS
  • create a remote partition
  • enable writeback on a partition in Cube Designer and SQL Server Management Studio or SSMS
  • browse, delete, disable, and convert writeback data
  • design aggregations for a partition using the Aggregation Design Wizard
  • edit and delete partitions in a multidimensional model
  • merge partitions in a multidimensional model
  • describe and display KPIs
  • describe and display KPIs
  • create calculated members using the Cube Designer
  • create named sets using the Cube Designer
  • define time intelligence calculations using the Business Intelligence Wizard
  • create relative measures, including using growth, YoY, same period last year
  • implement rank, percentile, and percentage of total using MDX
  • design and create partitions and implement custom logic into a multidimensional model

Microsoft SQL Server 2014: MDX Queries, Process Models, and Deploy Databases

  • start the course
  • describe how Multidimensional Expressions or MDX queries and expressions are used in SQL Server Analysis Services or SSAS
  • identify what is required in a SELECT statement to specify a result set and to identify the cube, query axis, and slicer axis
  • identify the parts of the SELECT statement syntax
  • use a tuple to identify a slice of data in a cube for a SELECT statement
  • use tuple functions in a SELECT statement to return a tuple
  • identify and use number functions in an MDX SELECT statement to return specific results
  • identify and use set functions in an MDX SELECT statement to return specific results
  • use the TopCount function in an MDX SELECT statement
  • specify the edges of a cellset to restrict the returned data that is visible to the client
  • use the slicer axis to filter the data returned by an MDX SELECT query
  • use the scope statement to limit the scope of a Multidimensional Expressions or MDX statement to a specified subcube
  • use the CASE statement to return specific results from multiple comparisons
  • describe how processing and reprocessing affects SQL Server Analysis Services or SSAS objects, specifically partitions, databases, cubes, and dimensions
  • describe the different processing options available for SQL Server Analysis Services or SSAS objects, specifically full processing and incremental processing
  • automate the processing of SQL Server Analysis Services or SSAS objects using Analysis Management Objects or AMO
  • use XML for Analysis or XMLA to process SQL Server Analysis Services or SSAS objects
  • use PowerShell to process and manage partitions
  • implement remote processing of SQL Server Analysis Services or SSAS objects
  • use the deployment wizard to deploy a SQL Server Analysis Services or SSAS database
  • test a database deployment
  • use the Synchronize Database Wizard to synchronize metadata and data between two SQL Server Analysis Services or SSAS databases
  • create MDX queries to return specific results and process data models and deploy databases

Microsoft SQL Server 2014: Manage, Maintain, and Troubleshoot SSAS

  • start the course
  • optimize performance by changing the design of the cube or dimension
  • monitor SQL Server Analysis Services or SSAS using different performance counters
  • monitor SQL Server Analysis Services or SSAS using different Dynamic Management Views or DMVs
  • optimize Multidimensional Expressions or MDX queries using various methods
  • optimize Multidimensional Expressions or MDX queries in the calculations
  • monitor and clear the SQL Server Analysis Services or SSAS cache
  • use SQL Profiler to create a trace to troubleshoot SQL Server Analysis Services or SSAS issues
  • view error and events logs to troubleshoot SQL Server Analysis Services or SSAS instances
  • identify issues with incorrect relationships
  • identify issues with incorrect aggregations
  • create roles to implement SQL Server Analysis Services or SSAS database permissions
  • troubleshoot dynamic security issues in SQL Server Analysis Services or SSAS
  • prepare to backup a multidimensional database
  • backup a multidimensional database using the Backup window in SQL Server Management Studio or SSMS
  • backup a multidimensional database using PowerShell
  • restore a multidimensional database
  • process SQL Server Analysis Services or SSAS objects, deploy SSAS database, and manage an SSAS instance

Microsoft SQL Server 2014: Create and Manage Tabular Data Models

  • start the course
  • use a project template to create a new tabular model and describe the Tabular Model Designer Interface
  • import data into a tabular model using the Table Import Wizard
  • define relationships between tables using the model designer
  • create hierarchies to define relationships between columns in a table
  • hide and freeze columns to manage their visibility
  • sort and filter columns
  • create perspectives to define viewable subsets of a model
  • create calculated columns to aggregate data, including using Data Analysis Expressions or DAX
  • create measures using a table's measure grid, including using Data Analysis Expressions or DAX to create the formula
  • create Key Performance Indicators or KPIs
  • create Data Analysis Expressions or DAX to implement tabular objects into a tabular model
  • implement time intelligence using different functions to support Business Intelligence or BI analysis, including specifying a Mark Table
  • implement context modification
  • create partitions using Partitions Manager
  • manage partitions using Partitions Manager
  • process partitions using different modes
  • configure the mode for data access for a tabular model
  • configure server roles and SQL Server Analysis Services or SSAS database roles to implement security
  • configure roles to grant permissions to a tabular model database
  • implement dynamic security for custom security approaches
  • implement row-level permissions
  • back up a tabular database
  • restore and import a PowerPivot model into an SSAS instance
  • define a Multidimensional Expression or MDX script to import a partial PowerPivot model
  • implement a tabular model, business logic, and data access as well as manage security

Microsoft SQL Server 2014: Design Reports and Create Data Sources and Datasets

  • start the course
  • describe how a report server is interacted with depending on the deployment topology and configuration
  • describe what needs to be considered during planning to create a report, including the format, structure, data, calculations, and delivery among others
  • describe the different report components for planning a report, including crosstab, Tablix, design chart, and data visualization components
  • choose and create a new Reporting Services project in SQL Server Data Tools or SSDT
  • install Reporting Services using different topologies
  • configure reporting services
  • create a shared data source
  • create a shared data source that connects to Microsoft Azure Database
  • create a shared data source that connects to a non-relational database
  • use custom expressions in a data source
  • create an embedded data source
  • configure a parameterized connection string to connect to a database
  • connect to the Microsoft Azure Marketplace in a data source
  • create an embedded dataset in a report by specifying a text query type
  • create an embedded dataset in a report by specifying a stored procedure in the Query Designer or selecting a predefined one
  • create an embedded dataset in a report by using a table query type
  • specify a shared dataset in a report
  • define the filter options for a dataset
  • define parameters for a dataset
  • use a Multidimensional Expressions or MDX query to create a dataset using the Multidimensional MDX Query Designer
  • use a Data Access Expressions or DAX query to create a dataset using the DAX Query Designer
  • create data sources and datasets in Reporting Services

Microsoft SQL Server 2014: Report Formatting and Interactivity

  • start the course
  • create a basic table report
  • create a basic matrix report
  • format fields and column headers in a table and matrix report
  • add images, maps, charts, and indicators to a report
  • add lists, text boxes, and other visuals to a report, including headers and footers
  • group data in a report and add line totals and grand totals
  • implement group variables and report variables to a report
  • define expressions to retrieve, calculate, display, sort, filter, and parameterize data
  • use the Report Wizard to create a new report
  • add interactive sorting to a report
  • implement filtering and parameter lists to control report data
  • add a drillthrough action to a report
  • add a DrillDown action to a report
  • use the Show/Hide property for a report region, including creating an expression
  • add a bookmark link and hyperlinks to a report
  • add a document map to a report
  • create a dynamic report using parameters
  • create a new report in the Report Builder by selecting the appropriate report layout and adding data sources and datasets
  • filter, group, and sort data for a report in the Report Builder
  • add images, maps, charts, and indicators to a report in the Report Builder
  • add interactive features to a report in the Report Builder
  • modify an existing report that was created in SQL Server Data Tools Report Designer
  • format reports, including adding interactivity in Report Designer and Report Builder

Microsoft SQL Server 2014: Manage Reporting Services

  • start the course
  • describe Report Server security architecture and site-level security
  • create server-level roles and assign users to the roles to allow report server access
  • create item-level roles to grant permissions to users
  • secure a report using different types of roles
  • secure a shared data source
  • configure stored credentials to allow access to external data for a report
  • configure SharePoint groups and permissions for access to Report Server item
  • manage encryption keys for Reporting Services
  • create snapshots and add them to report history
  • create a report history
  • manage a reporting services database, including backup and restore, copying, and managing connections
  • view Reporting Services log files
  • monitor Reporting Services with SQL Profiler
  • use Windows Reliability and Performance Monitor to monitor a report server
  • configure security and manage the Report Server

Microsoft SQL Server 2014: Process Report and Create Subscriptions and Schedules

  • start the course
  • publish reports to a Report Server
  • publish reports to a SharePoint library
  • deploy custom assemblies with reports
  • save reports using Report Builder to a report server and SharePoint library
  • find and view reports in Report Manager and a browser
  • describe what requirements need to be met before a subscription can be created, the different extensions, and their parts
  • create a standard subscription for a report in Report Manager
  • create an e-mail subscription in Report Manager
  • create a data-driven subscription in Report Manager
  • modify settings for subscriptions and delete them
  • create a schedule for a report
  • create a shared schedule for a report when Reporting Services is in Native mode
  • create a shared schedule for a report when Reporting Services is in SharePoint mode
  • manage schedules, including pausing, resuming, and changing settings
  • monitor the status of running jobs on the Report Server
  • configure security, process and view reports, manage subscriptions and schedules, and manage the Report Server

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 immediatelyThe 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.

read more about the order process

What is included?

Certificate of participation Yes
Monitor Progress Yes
Award Winning E-learning Yes
Examination possibleYes
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.

Life Long Learning

Follow multiple courses? Read more about our Life Long Learning concept

read more

Contact us

Need training advise? Contact us!


contact