Chapter 1: Tabular Mode Introduction
- Databases : Types; OLTP, OLAP, DWH
- Need for OLAP Databases and Cubes
- OLTP versus OLAP Databases
- DWH versus OLAP Databases
- SQL Server Components & Features
- SQL Server Analysis Services (SSAS)
- SSAS Modes of Implementation
- Tabular Mode: Advantages
- Tabular Mode for OLAP Databases
- SQL Server (Instance) Installation
- Tabular Mode Server Installation
- Management Studio (SSMS) Installation
- Tabular Mode Server Connections
- Server Properties; Data Directories
- Visual Studio Tool: Purpose, Installation
|
Chapter 5: Cube Deployment & MDX
- OLAP Cube Deployments: Phases
- Builds and Rebuilds - Output Results
- Deployment Phase & Process Phase
- Processing Options; Transactions
- Processing Options : Donot Process
- Deploy All and Deploy Changes Only
- Cube Processsing Modes: In-Memory
- Direct Query Options For Deployment
- Cube Access from SSMS : Cube Browser
- XMLA Scripts : OLAP DB Scripting
- MDX: Multi Dimensional Expressions
- MDX Queries : Auto & Manual Modes
- MDX Queries: ORDER, TOP, WHERE
- MDX Queries: MEMBERS, FILTERS
- Generating MDX Queries from SSMS
|
Chapter 9: DAX - Level 4
- Statistical DAX Functions
- COUNTROWS, COUNTX
- DISTINCTCOUNT
- COUNTBLANK
- COUNT, COUNTA
- MIN, MINA & MINX
- MAX, MAXA & MAXA
- AVERAGE, AVERAGEA, AVERAGEX
- Parent and Child Functions
- PATH, PATHITEM
- PATHCONTAINS
- PATHLENGTH
- Math & Trig Functions
- SUM Vs SUMX
|
Chapter 2: Tabular Mode Cube Design
- Using Visual Studio Tool : Tabular Mode
- Cube Design - BISM and Explorer
- Workspace and In-Memory Database
- Cube Architecture: Measure Groups
- Measures and Aggregations in Cube
- Dimension Tables and Realtime Use
- Attributes (Columns) and Members
- Data Models For Cube Design
- Data Source : Creations; Data Imports
- Entity Selection and Data Source View
- Data Models : Entity Relationships
- Adding and Editing Relationships
- Aggregated Measures and Attributes
- Identify Measure Groups, Dimensions
- Analyse in Excel. Cube Reports
|
Chapter 6: DAX - Level 1
- DAX: Data Analysis Expressions
- Role of DAX in Data Analytics
- Implementations of DAX in OLAP
- DAX : Entities & Naming Format
- DAX Data Types and Operators
- DAX Expressions : Purpose, Usage
- DAX Queries: Purpose, Usage
- DAX Functions : High Level Usage
- DAX Expressions : Calculated Columns
- DAX Column for Cube Design
- ISBLANK() and IF() Functions
- CALCUALTE () and SUM Functions
- Understanding DAX Measures
- Column Operations and Data Types
- Calculation Operations, In-Memory
|
Chapter 10: DAX - Level 3
- Time Intelligence Functions
- OPENINGBALANCEMONTH
- OPENINGBALANCEQUARTER
- OPENINGBALANCEYEAR
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- PREVIOUSDAY, PREVIOUSMONTH
- FIRSTNONBLANK, NEXTMONTH
- PREVIOUSQUARTER
- TOTALMTD, TOTALQTD
- TOTALYTD, NEXTDAY
- PARALLELPERIOD
- SAMEPERIODLASTYEAR
|
Chapter 3: Cube Objects
- OLAP Cube Design Concepts
- OLAP Cube Objects in Tabular Mode
- Understanding Grid View
- Grid View Versus Relation View
- Insert / Rename / Freeze Columns
- Relationship Edits and Rules
- Hierarchies: Creation & Precautions
- Hierarchies Usage for Data Analytics
- Perspectives : Purpose & Creation
- Using Hierarchies and Perspectives
- Excel Analytics with Hierarchies
- Excel Analytics with Perspectives
- Cube Partitions: Creation with SQL
- Entity Partitions and Tuning Concepts
- Grouping Entities - Snowflake
|
Chapter 7: DAX - Level 2
- DAX Functions and Real-time Use
- Filter Functions in DAX
- ALL, ALLSELECTED
- ALLEXCEPT, CALCULATE
- CALCULATETABLE, FILTER
- LOOKUPVALUE, OFFSET
- Calculation Groups and Use
- Cube Calculations & Deployments
- Table Manipulation DAX Functions
- ADDCOLUMNS & SUMMARIZE
- GROUPBY, DATATABLE
- UNION, EXCEPT
- INTERSECT, ROW
- NATURALINNERJOIN
- NATURALOUTERJOIN
|
Chapter 11: DAX - Level 4
- Date & Time Functions
- CALENDAR, CALENDARAUTO
- DATE, DATEDIFF, TIME
- DATEVALUE, NOW
- DAY, YEAR, MONTH
- QUARTER, HOUR
- MINUTE, SECOND
- EDATE, EOMONTH
- DAX Calculations with Joins
- KPI: Key Performance Indicator
- Using KPIs with DAX Concepts
- Using KPIs with MDX Queries
- KPIs Versus DAX Calculations
- Translations & Cube Access
|
Chapter 4: CUBE Data Models
- Data Modelling Concepts @ Databases
- Data Models with OLAP Databases
- Dimensions & Fact Table Identification
- STAR Schema : Design Patterns
- STAR Schema : Advantages & Cautions
- SNOWFLAKE Schema : Design Patterns
- STAR Versus SNOWFLAKE Schemas
- OLTP Database Sources for OLAP
- Cube Design with STAR Schema
- STAR Schema Data Models with OLAP
- SNOWFLAKE Data Models with OLAP
- Snowflake Schema : Data Sources
- Snowflake Schema : Cube Design
- Azure Data Sources with OLAP Cubes
- Using Azure SQL Databases with STAR
|
Chapter 8: DAX - Level 3
- Logical DAX Functions
- IFERROR, SWITCH
- IF and AND (&&)
- OR(||), NOT
- COALESCE
- USERPRINCIPALNAME()
- USERELATIONSHIP
- RELATEDTABLE
- Text DAX Functions
- UPPER, LOWER
- LEFT, RIGHT, LEN
- CONCATENATE
- CONCATENATEX
- ROW, SEARCH, REPT
- MID, FIND, EXACT
|
Chapter 12: Power BI Integrations
- Using OLAP Cubes with Power BI
- Storage Modes, Power BI In-Memory
- Import and Connect LIVE Options
- Creating Calendar Table in Power BI
- Cumulative Total/ Running Total
- Display Top Products for each region
- DAX shortcuts in Power BI Desktop
- Performance Advantages with OLAP
- MDX Queries with SSMS Tool
- Using MDX and DAX in Power BI
- RLS: Row Level Security & DAX
- Implementing RLS in Tabular Mode
- Implementing RLS in Power BI
- DAX Expressions for RLS
|
* Above curriculum applicable for registrations from July 22nd, 2023
