Course : SQL Server, Business Intelligence
Practical course - 5d
- 35h00 - Ref. SBU
|
Download in PDF format
Share this course by email
 | Understanding the suite's architecture |
| Change Data Capture, Data Quality and Master Data Services concepts |
| Create a control flow and implement data transformations with SQL Server Integration Services (SSIS) ETL |
| Create an Analysis Services database and set up analysis dimensions |
| Understanding the concepts of Power Pivot and Power View, DAX queries for analysis |
| Creating and formatting reports with SSRS |
| Discover Power BI |
Intended audience
Business intelligence project managers, developers and analysts, administrators deploying and managing SQL Server BI-based solutions.
Prerequisites
Basic knowledge of RDBMS, SQL Server and SQL language. Basic knowledge of data warehouse modeling principles.
Practical details
Teaching methods
Workshops based on real-life business scenarios.
Course schedule
1 Introduction to business intelligence (BI)
- The reasons for initiating BI projects.
- What is a data warehouse?
- The components of a data warehouse (DW) solution.
- The steps involved in modeling a DW (Ralph Kimball).
- Understand the principles of modeling (star, flake, constellation).
- SQL Server BI, data warehouse platform.
- Architecture of SQL Server BI tools.
Demonstration
Examples of implementing and using SQL Server Business Intelligence.
2 Data Quality and Master Data Management (MDM)
- The notion of data quality repository.
- Objectives of Master Data Management. The application of management rules to ensure data validity.
- Master Data Services.
- Master Data Management component DQS Cleansing.
- Data deduplication.
Example
Presentation of quality models.
3 Integration Services (SSIS), manipulated objects
- Understand the principles and model of Extract-Transform-Load (ETL). Overview.
- Package and workflow concepts.
- Control flow and package definition.
- Control flow tasks: SQL script, e-mail, cube update.
- The "Change Data Capture" task.
- Task add-in (filewatcher).
- Sequence container.
- ForEach loop container.
Hands-on work
Create and modify control flows.
4 Integration Services (SSIS), know how to feed tables
- Sources, destinations and processing.
- Various transformations: conditional splitting, derived column, grouping...
- Slowly changing dimensions.
- Package deployment and execution.
- Packet scheduling and configuration.
- Logging, security.
Exercise
Feeding a table. Transformations. Creating and using packages. Using logging.
5 Analysis Services (SSAS), build cubes and star diagrams
- Introduction to multidimensional cubes.
- SQL Server Analysis Services (SSAS) tabular models.
- Use of dimension tables and fact tables.
- Introduction to tabular cubes and Power Pivot.
- Create cubes in SQL Server Data Tools (SSDT).
- Dimensional design.
- User hierarchies.
- Attribute relationships.
- Composite wrenches.
Exercise
Create an Analysis Services database. Setting up dimensions. Cube creation.
6 SSAS, advanced elements
- Introduction to the MDX language.
- Calculated members and appointed ensembles.
- Extraction and reporting.
- Sheet music and aggregation design.
- Graphical DMX prediction queries.
- Backup and restore cubes.
- Incremental updates and cube security.
Hands-on work
Handling the MDX language. Writing queries. Perform simple and complex calculations. Save and restore cubes.
7 Data science with R and SQL Server
- Introducing data science.
- Introduction to the R language.
- Introducing SQL Server R Services.
Group discussion
Illustration of presentations with demonstrations. Exercise: R development, execution of example scripts.
8 Reporting Services (SSRS), build reports
- The report server.
- Report Designer versus Report Builder
- Use Tablix (tables and matrices).
- Formatting elements.
- Conditional formatting.
- Simple presentation elements.
Exercise
Édition de requêtes. Utilisation et mise en forme des tablix.
9 SSRS, advanced features
- Enhance reports with graphs and gauges.
- Using parameters.
- Sorting and filtering.
- Advanced analysis elements: expressions, sparkline, KPIs...
- MDX cube reporting, data extraction with MDX.
- Actions and sub-reports.
Exercise
Create reports including graphics. Integration of parameters and sorting. Use of gauges and indicators. Report on an Analysis Services source.
10 SSRS, deploy and manage reports
- Configuration manager.
- Native server management.
- Report deployment.
- Export reports in Excel, PDF and Word.
- Caching, instant report capture.
- Safety.
- Linked reports, KPIs, mobile report presentation and Power BI.
Exercise
Publication de rapports. Exporter un rapport sous Excel. Création et gestion d'abonnement. Création d'un Key Performance Indicators (KPI).
PARTICIPANTS
Business intelligence project managers, developers and analysts, administrators deploying and managing SQL Server BI-based solutions.
PREREQUISITES
Basic knowledge of RDBMS, SQL Server and SQL language. Basic knowledge of data warehouse modeling principles.
TRAINER QUALIFICATIONS
The experts leading the training are specialists in the covered subjects. They have been approved by our instructional teams for both their professional knowledge and their teaching ability, for each course they teach. They have at least five to ten years of experience in their field and hold (or have held) decision-making positions in companies.
ASSESSMENT TERMS
The trainer evaluates each participant’s academic progress throughout the training using multiple choice, scenarios, hands-on work and more.
Participants also complete a placement test before and after the course to measure the skills they’ve developed.
TERMS AND DEADLINES
Registration must be completed 24 hours before the start of the training.
ACCESSIBILITY FOR PEOPLE WITH DISABILITIES
Do you need special accessibility accommodations? Contact Mrs. Fosse, Disability Manager, at psh-accueil@orsys.fr to review your request and its feasibility.
Business intelligence project managers, developers and analysts, administrators deploying and managing SQL Server BI-based solutions.
PREREQUISITES
Basic knowledge of RDBMS, SQL Server and SQL language. Basic knowledge of data warehouse modeling principles.
TRAINER QUALIFICATIONS
The experts leading the training are specialists in the covered subjects. They have been approved by our instructional teams for both their professional knowledge and their teaching ability, for each course they teach. They have at least five to ten years of experience in their field and hold (or have held) decision-making positions in companies.
ASSESSMENT TERMS
The trainer evaluates each participant’s academic progress throughout the training using multiple choice, scenarios, hands-on work and more.
Participants also complete a placement test before and after the course to measure the skills they’ve developed.
TEACHING AIDS AND TECHNICAL RESOURCES
• The main teaching aids and instructional methods used in the training are audiovisual aids, documentation and course material, hands-on application exercises and corrected exercises for practical training courses, case studies and coverage of real cases for training seminars.
• At the end of each course or seminar, ORSYS provides participants with a course evaluation questionnaire that is analysed by our instructional teams.
• A check-in sheet for each half-day of attendance is provided at the end of the training, along with a course completion certificate if the trainee attended the entire session.
• The main teaching aids and instructional methods used in the training are audiovisual aids, documentation and course material, hands-on application exercises and corrected exercises for practical training courses, case studies and coverage of real cases for training seminars.
• At the end of each course or seminar, ORSYS provides participants with a course evaluation questionnaire that is analysed by our instructional teams.
• A check-in sheet for each half-day of attendance is provided at the end of the training, along with a course completion certificate if the trainee attended the entire session.
TERMS AND DEADLINES
Registration must be completed 24 hours before the start of the training.
ACCESSIBILITY FOR PEOPLE WITH DISABILITIES
Do you need special accessibility accommodations? Contact Mrs. Fosse, Disability Manager, at psh-accueil@orsys.fr to review your request and its feasibility.
Dates and locations
Select your location or opt for the remote class then choose your date.
Remote class
Dernières places
Date garantie en présentiel ou à distance
Session garantie