Developing SQL Data Models (20768C)
The primary audience for this course are database professionals who need to fulfil BI Developer role to create enterprise BI solutions.
Primary responsibilities will include:
- Implementing multidimensional databases by using SQL Server Analysis Services
- Creating tabular semantic data models for analysis by using SQL Server Analysis Services
Before attending this course, students must have:
- Experience of querying data using Transact-SQL
After completing this course, students will be able to:
- Describe the components, architecture, and nature of a BI solution
- Create a multidimensional database with Analysis Services
- Implement dimensions in a cube
- Implement measures and measure groups in a cube
- Use MDX syntax
- Customize a cube
- Implement a tabular database
- Use DAX to query a tabular model
- Use data mining for predictive analysis
Module 1: Introduction to Business Intelligence and Data Modeling
This module introduces key BI concepts and the Microsoft BI product suite.
- Introduction to Business Intelligence
- The Microsoft business intelligence platform
Module 2: Creating Multidimensional Databases
This module describes how to create multidimensional databases using SQL Server Analysis Services.
- Introduction to Multidimensional Analysis
- Data Sources and Data Source Views
- Overview of Cube Security
- Configure SSAS
- Monitoring SSAS
Module 3: Working with Cubes and Dimensions
This module describes how to implement dimensions in a cube.
- Configuring Dimensions
- Defining Attribute Hierarchies
- Implementing Sorting and Grouping Attributes
- Slowly Changing Dimensions
Module 4: Working with Measures and Measure Groups
This module describes how to implement measures and measure groups in a cube.
- Working with Measures
- Working with Measure Groups
Module 5: Introduction to MDX
This module describes the MDX syntax and how to use MDX.
- MDX fundamentals
- Adding Calculations to a Cube
- Using MDX to Query a Cube
Module 6: Customizing Cube Functionality
This module describes how to customize a cube.
- Implementing Key Performance Indicators
- Implementing Actions
- Implementing Perspectives
- Implementing Translations
Module 7: Implementing a Tabular Data Model by Using Analysis Services
This module describes how to implement a tabular data model in Power Pivot.
- Introduction to Tabular Data Models
- Creating a Tabular Data Model
- Using an Analysis Services Tabular Data Model in an Enterprise BI Solution
Module 8: Introduction to Data Analysis Expression (DAX)
This module describes how to use DAX to create measures and calculated columns in a tabular data model.
- DAX Fundamentals
- Using DAX to Create Calculated Columns and Measures in a Tabular Data Model
Module 9: Performing Predictive Analysis with Data Mining
This module describes how to use data mining for predictive analysis.
- Overview of Data Mining
- Creating a Custom Data Mining Solution
- Validating a Data Mining Model
- Connecting to and Consuming a Data-Mining Model
- Using the Data Mining add-in for Excel