CertKit – 70-466: Implementing SQL Data Models and Reports

In de online cursus CertKit – 70-466: Implementing Data Models and Reports with Microsoft SQL Server van Sinteno leer je business intelligence oplossingen te creëren. Je doet de kennis op om geavanceerde rapportages te maken met behulp van PowerPivot en tabular datamodellen. Ook maak je uitgebreid kennis met het opzetten van datavisualisaties door gebruik te maken van PowerView en SQL Server Reporting.

Onderwerpen die onder andere in de online cursus CertKit – 70-466: Implementing Data Models and Reports with Microsoft SQL Server aan bod komen zijn de belangrijkste kenmerken rondom business intelligence, developer tools, SSRS, SQL Server, report security, data alerts, cubes, MultiDimensional eXpressions, data mining, PowerPivot en nog veel meer.

Doelgroep

Databasebeheerder

Let op: Deze training blijft wel bestaan maar de mogelijkheid om het bijbehorende examen af te leggen is per 31 januari 2021 komen te vervallen. 
Een groot aantal Microsoft trajecten zijn namelijk aan het veranderen. Veel MCSA en MCSE trajecten maken plaats voor Role-Based trajecten.

De vervanger van deze online training is nog niet bekend.

Resultaat

Je kunt zelfstandig business intelligence oplossingen creëren en geavanceerde rapportages en datavisualisaties ontwikkelen. Daarnaast ben je voorbereid op het MCSE examen 70-466.

Voorkennis

U heeft de MCSA: SQL Server 2012 certificering behaald.

Inhoud

Tijdens de online cursus CertKit – 70-466: Implementing Data Models and Reports with Microsoft SQL Server worden de volgende onderwerpen behandeld:

Design and Install Analysis Services and Tools

• Start the course
• Explain the functionality:
  • That OLAP and data mining provide for business intelligence
  • That tabular mode provides for business intelligence
  • Of SQL Server BI features with SharePoint, including PowerPivot
• Identify the:
  • Different BI features to install based on a scenari0
  • Appropriate data model to use for data analysis
  • Development and production considerations for installing SSAS
• Install:
  • SSAS in multidimensional mode
  • SSAS in tabular mode
  • 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

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
  • Data source viewNew project in SQL Server Data Tools, or SSDT, using a template
  • 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
  • What measures and measure groups are in a multidimensional model
  • Measures and aggregation behavior
  • Many-to-many relationships in Analysis Services
  • Fact relationships in Analysis Services
  • 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

Implement and Configure Cubes

• Start the course
• Create
  • A new cube using the Cube Wizard
  • An empty cube
  • 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
  • Hierarchy properties
  • Attribute properties
• Add:
  • Dimensions to cubes through Cube Designer
  • Measures to cubes through Cube Designer
  • Measure groups through Cube Designer
• Define translations in a cube
• Create perspectives for a cube
• Implement:
  • Many-to-many relationships in a cube
  • Fact relationships in a cube
  • Role-playing relationships in a cube
• Create and modify cubes in Cube Designers

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
  • A basic measure
  • 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

Implement Partitions and Custom Logic

• Start the course
• Describe:
  • Partitions in a multidimensional model, including the types Local and Remote, aggregations, and design strategy
  • 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
  • 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

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
  • Scope statement to limit the scope of a Multidimensional Expressions or MDX statement to a specified subcube
  • 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

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

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
  • Calculated columns to aggregate data, including using Data Analysis Expressions or DAX
  • Measures using a table’s measure grid, including using Data Analysis Expressions or DAX to create the formula
  • Key Performance Indicators or KPIs
  • 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
  • Server roles and SQL Server Analysis Services or SSAS database roles to implement security
  • Roles to grant permissions to a tabular model database
• mplement 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

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
  • What needs to be considered during planning to create a report, including the format, structure, data, calculations, and delivery among others
  • 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
  • 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
  • Specifying a stored procedure in the Query Designer or selecting a predefined one
  • 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

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

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

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
  • An e-mail subscription in Report Manager
  • A data-driven subscription in Report Manager
• Modify settings for subscriptions and delete them
• Create a:
  • Shedule for a report
  • Shared schedule for a report when Reporting Services is in Native mode
  • 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

Inbegrepen

Duur

36 uur

Beschikbare taal

Engels

Online toegang

6 maanden

Home

• 
• Buffer
• Pin It