In-depth understanding and examples for Microsoft SQL Server Analysis Services (SSAS) Multidimensional Expressions (MDX) Language - to query Multidimensional and Tabular Cubes. The MDX Training course includes over 100 MDX Functions with real-world examples and Report Integrations.
| 100+ MDX Functions | Nested SubQueries | Real-time Scenarios |
| Common Calculations | Time Intelligence | Advanced MDX |
Pre-requisites: Particiant needs to have (working) knowledge on SSAS
DAY 1MDX Fundamentals Real-time Usage of MDX Language MDX Queries MDX Expressions MDX Constructs MDX Implementations with OLAP Databases Internal Working of MDX Queries - Multidimensional Mode and Tabular Mode Cubes Furmula Engine and Straige Engine Components for MDX Query Processing MDX Query Parser Attribute Store and Hierarchy Store Fact Data and Aggregations Vertipaq Storage for MDX Processing (SSAS Tabular Mode) Client libraries for MDX Query Executions ADOMD.NET, SMOP and OLE DB (MSOLAP.dll) Protocol Usage for MDX Queries in SSMS and SSDT Tools Basic MDX Query Format * AXIS Options and Usage Axis Aliases : Columns, Rows, Pages, Section and Chapter MDX Quries with MEMBERS, CHILDREN, All, PARENT, ORDERBY, BETWEEN, EXCEPT, CROSSJOIN Options and Cube Slice |
DAY 2Dimension Types : Private and Shared Dimensions Dimension Usage with MDX WHERE versus FILTER TUPLE and SET Representations with Examples Members and Member Sets Named Sets Member Names UNION and SET Operations UNION with ALL Flag NON EMPTY and NONEMPTY Functions Granular Control ORDER with FILTER, LEFT and TOP Functions Member Validity with Slices, Filters and Axis ROOT Function Tuple Expressions DESCENDANTS Function Level Expressions Member Sets with Numeric and Distance Attributes SELF, AFTER, BEFORE SELF_BEFORE_AFTER, SELF_AFTER_BEFORE, SELF_AND_BEFORE SELF_AND_AFTER TOP, HEADCOUNT, TOPCOUNT |
DAY 3ADDCALCULATEDMEMBERS * Set Level and Member Level Functions * BOTTOMSUM and TOPSUM Functions * BOTTOMCOUNT and TOPCOUNT Functions * * TOPPERCENT and BOTTOMPERCENT Functions * NONEMPTY and CROSSJOIN Functions NONEMOTYCROSSJOIN Function * EXTRACT with NONEMPTY FUNCTIONS * * ASCENDANTS and DESCENDANTS Functions * UNION and INTERSECT Functions * * EXISTS * EXCEPT * EXTACT * FILTER HIERARCHIZE FUNCTIONS * Descendants Distinct * HEAD and TAIL Functions * GENERATE * ORDER * STRIPCALCULATEDMEMBERS * MEASUREGROUPMEASURES * STRIPCALCULATEDMEMBERS * SUBSET and UNORDER Functions * MEMBERTOSTR and STRTOSET |
DAY 4KPI FUNCTIONS : KPIGOAL * KPIVALUE * KPISTATUS * KPITREND * NON KPI MEAUSRES WITH KPI MEASURES IN A SET ** METADATA FUNCTIONS : AXIS * SetToStr * COUNT (Dimension) * COUNT (Hierarchy Levels) * COUNT (Tuples) * HIERARCHY * LEVEL * LEVELS * NAME * UNIQUENAME * ORDINAL * ANCESTOR * ANCESTORS * ASCEDANTS * CHILDREN * COUSIN * CURRENT * CURRENTMEMBER * CURRENTORDINAL * DATAMEMBER * DEFAULTMEMBER * FIRSTCHILD * FIRSTSIBLING * ISANCESTOR * ISGENERATION * ISLEAF * ISSIBLING * LAG * LASTCHILD * LASTSIBLING * LEAD * LINKMEMBER * LOOKUPCODE * NEXTMEMBER * PARENT * PREVMEMBER * PROPERTIES * SIBLINGS * UNKNOWNMEMBER Functions |
DAY 5MDX TIME FUNCTIONS : OPENINGPERIOD * PARALLELPERIOD * PERIODSTODATE * CLOSINGPERIOD * LASTPERIODS * QTD * WTD * YTD * MTD * Calculating Running Totals using PERIODSTODATE and Aggregates * MDX concepts SIBLING, PARALLE PERIODS AND CHILDREN (FIRST CHILD, LAST CHILD, FIRST MEMBER, LAST MEMBER) * AGGREGATE * SUM * AVG * COALESCEEMPTY * CORRELATION * COUNT (SET) * DISTINCTCOUNT * MAX * MIN * RANK * MEDIAN * THIS SCOPE * USERNAME MDX Sub Queries with Aggregations and TIME Functions * Using Time Functions with MDX Joins * MEMBERTOSTR * NAMETOSET * ROOT * SETTOSTR * STRTOMEMBER * STRTOSET * STRTOTUPLE * STRTOVALUE * TUPLETOST * |
DAY 6UserName * Leaves * This * ClosingPeriod * LastPeriods * Mtd * OpeningPeriod * ParallelPeriod * PeriodsToDate * Qtd * Wtd * Ytd * KPICurrentTimeMember * KPIGoal * KPIStatus * KPITrend * KPIValue * KPIWeight * IIf * IsEmpty * MemberValue * ValidMeasure * Value * ****** More details to be added by August 2nd, 2019 ******* |
