Course 50578A: MDX Query Language for SQL Server Analytical Services (OLAP) - course RUB 35,990. from Specialist, training 24 academic hours, date of May 21, 2023.

Special purpose teacher, holder of prestigious international status Microsoft Certified Master. Graduate of Moscow State Technical University named after N.E. Bauman.

Module 1. Family of business intelligence technologies. An overview of Microsoft's data analytics technologies. Life cycle of data in business analytics systems (2 ac. h.)

- Why data analysis technologies (BI, OLAP, DWH, ETL) can be useful
- What you should pay attention to when implementing a business intelligence system
- Scope and tools
- Laboratory work Study of the ETL process using the example of an integration services package
- Study of the ETL process using the example of an integration services package

Module 2. Introduction to the MDX multidimensional query language. We start with the simplest, get used to the multidimensional model (2 ac. h.)

- Differences between a multidimensional model and a tabular one
- Data warehouses and data marts
- Introduction to MDX language
- MDX editor
- Lab Introducing the Query Editor (Management Studio) Introducing the Business Intelligent Development Studio Editor Introducing the SQL Profiler Tracer MDX Basics
- Introduction to the Query Editor (Management Studio)
- Introduction to the Business Intelligent Development Studio editor
- Introduction to the SQL Profiler tracer
- MDX Basics

Module 3. Dimensions are the skeleton of a multidimensional model. Dimensions, tuples, cells and other elements of a multidimensional data model (3 ac. h.)

- Dimensions
- Dimensional attributes
- Hierarchies
- Dimension Members
- Tuples
- Cells
- Sets
- Assignments
- Functions
- Lab Dimension Properties Dimension Attributes Hierarchies in one dimension Hierarchies over multiple dimensions Root level of hierarchy Function Members Reference -- members Syntax errors Partial tuple references Multi-axis display Crossjoin Auto-exists mode Exists mechanism Eliminate duplicate tuples
- Dimension properties
- Dimension attributes
- Hierarchies in one dimension
- Hierarchies over multiple dimensions
- Root level of hierarchy
- Members function
- Reference members
- Syntax errors
- Links to partial tuples
- Display on multiple axes
- Crossjoin
- Auto-exists mode
- Mechanism Exists
- Elimination of duplicate tuples

Module 4. Navigating a multidimensional model. Navigation, filtering, sorting functions and their combination (3 ac. h.)

- Navigation in reports
- Relative positions
- Navigation features
- Sorting management
- Filtering results
- Combination of sets
- Lab Navigating Hierarchy Relative Positioning Using the Members Function Hierarchical Functions
- Hierarchy navigation
- Relative positioning
- Using the Members function
- Hierarchical functions

Module 5. Multidimensional calculations. Using expressions, statistical functions and other mechanisms for calculations (3 ac. h.)

- Expressions
- Computed members
- Formatting the result (WITH)
- Dynamic expressions
- IIF function
- Statistical functions
- Comparing tuples with parameters
- Lab Exploring uses of the WITH rule Creating calculated members Calculating percentages Formatting
- Explore options for using the WITH rule
- Creating calculated members
- Calculation of percentages
- Formatting

Module 6. Working with time measurements, multiple calendars and divergent horizons. Working with time measurement, time calculations, navigation along the time axis, calculated time terms and aggregates (3 ac. h.)

- Time measurement
- Multiple calendars
- Time functions
- Parallel periods
- Opening periods
- Closing periods
- Recent periods
- Year-to-Date mechanism
- Calculated measures on top of the time axis
- Comparison of periods
- Sum function
- Aggregate functions
- Max and Min functions on the time axis
- Lab ParallelPeriod OpeningPeriod ClosingPeriod LastPeriod Year-To-Date Calculated measures on top of time axis Comparison periods Function Sum Aggregation Function Max Function Min - - - Using Crossjoin with parallel periods
- ParallelPeriod
-OpeningPeriod
-ClosingPeriod
-LastPeriod
-Year-To-Date
- Calculated measures on top of the time axis
- Comparison of periods
- Sum function
- Aggregation
- Max function
- Min function
- Using Crossjoin in conjunction with parallel periods

Module 7. Practical application of MDX. Combining studied technologies to solve practical problems (3 ac. h.)

- OR logic
- AND logic
- Combination of OR and AND on different hierarchies
- Complex scenarios for using AND and OR
- Using the NonEmpty function
- Average values
- Working with the last date
- Parallel periods with multiple dates
- Checking the current context
- Descendants function options
- Ranking values
- Laboratory work

Module 8. Other uses of MDX: reports. Using MDX queries to build reports in SQL Server Reporting Services and PerformancePoint applications (3 ac. h.)

- Creating a report in SQL Server Reporting Services (SSRS)
- Creating a connection to a cube
- Working with multidimensional queries in the MDX designer
- Creation of non-standard queries
- Transfer of parameters
- PerformancePoint Panels
- Laboratory work Report in SSRS Connecting to a cube Creating an MDX query in the designer Creating a non-standard query Passing parameters
- Report to SSRS
- Connection with cube
- Creating an MDX query in the constructor
- Creating a custom request
- Transfer of parameters

Module 9. Performance indicators (KPIs) and multidimensional queries in Business Intelligence Development Studio. Calculations, indicators and other applications of MDX in BIDS (2 ac. h.)

- Create named calculations in BIDS
- Creating performance indicators in BIDS
- Lab work Creating a named expression Creating a sheet indicator Creating a target indicator Using the IIF mechanism in KPI
- Create a named expression
- Creating a sheet indicator
- Creating a target indicator
- Using the IIF mechanism in KPI