MDX Training Part 2

Advanced MDX Query Techniques

This video training course delves into the advanced concepts of MDX (Multidimensional Expressions) queries. Building upon the foundational knowledge gained in MDX training Part 1, this course equips you with the skills to effectively query and retrieve data from Multi-Dimensional OLAP cubes using SSAS (SQL Server Analysis Services).

This online MDX training is a crucial component of your SSAS Training journey, which falls under the broader umbrella of MSBI Training (Microsoft Business Intelligence). Mastering MDX is essential for gaining expert-level proficiency in multidimensional data analysis.

Course Content:

• Working with Hierarchies:
  • Descendants Function: Navigate up and down the hierarchy.
  • Ascendants Functions: Traverse the hierarchy in the opposite direction.
  • Parent, Ancestor, Siblings: Explore hierarchical relationships.
  • FirstSibling, LastSibling, Firstchild, Lastchld: Access members within a hierarchy.
  • item, PrevMember, NextMember: Iterate through hierarchy members.
  • Lag, Lead: Analyze data across time periods within a hierarchy.
  • Hierarchize Function: Create hierarchies dynamically.
  • CurrentMember: Identify the current member in a hierarchy.
  • Ordinal: Determine the position of a member in a hierarchy.
  • More on Order Function: Control the order of members.
  • Rank Function: Assign ranks to members based on a defined order.
• More MDX Functions:
  • iif Function: Implement conditional logic.
  • Switch Function: Select values based on multiple conditions.
  • Crossjoin, Union, Except, Intersect: Combine and manipulate datasets.
  • DrilldownMember and DrilldownLevel: Navigate the cube hierarchy.
  • DrilldownLevelTop and DrilldownLevelBottom: Access top and bottom members in a drilldown.
  • DrilldownMemberTop, DrilldownMemberBottom: Retrieve top and bottom members.
  • Generate Function: Create a series of values.
• Working with Time Intelligence:
  • Adding Time Intelligence to a Dimension:
    • YTD (Year-to-Date)
    • QTD (Quarter-to-Date)
    • MTD (Month-to-Date)
    • WTD (Week-to-Date)
    • PeriodsToDate
    • OpeningPeriod, ClosingPeriod
    • Parallelperiod
    • LastPeriods
• Business Scenarios:
  • Percent Sales Contribution: Calculate the percentage sales contribution of products at various levels (product, subcategory, category).
  • Product Segmentation: Divide products into classes (A, B, C) based on sales contribution thresholds (e.g., >20%, 10-20%, <10%).
  • Monthly Sales Analysis: Determine monthly sales and average daily sales per month.
  • Top Selling Products: Identify the top 5 best-selling products per country.
  • Sales Comparisons: Compare sales figures for specific periods (e.g., first 6 months of the year, current year vs. previous year, current month vs. previous three months).
  • Inventory Analysis: Calculate opening and closing stock levels.
  • Product Ranking: Rank products based on sales performance.
  • Regional Sales Summary: Generate summary sales details for all countries except a specified country (e.g., USA), including state-wise sales.

After completing this online MDX training course, you will be able to confidently write complex MDX expressions and leverage the power of SSAS for advanced business intelligence analysis.