SQL Server Analysis Services (SSAS)

This page covers the SQL Server Analysis Services, also called as SSAS topics along with the MDX queries. It includes creating cube, accessing cube data, wriiting queries against the OLAP cube.

  1. Create a New Project
  2. How to Create Data Source
  3. Create Data Source View
  4. Add or Remove Tables from Data Source View
  5. Difference between Star Schema and Snow Flake Schema
  6. Create Dimensions
  7. Creating OLAP Cube
  8. How to Create Cube Dimensions
  9. Difference between Cube Dimension and Database Dimension
  10. How to Create Named Calculations
  11. Creating Measure Groups

TIP: We can use the SSIS ETL tool to transform the data and load into the OLAP cube. Next, use the SSRS reporting tool to design reports from cube dimensions and measure groups.

Multidimensional Expressions (MDX)

This MDX or multidimensional expressions page shows the list of available functions to work with the cube. For the query demonstration, we use the Adventure Works cube.

MDX Functions

  1. Ascendants
  2. AVG
  3. BottomCount
  4. BottomPercent
  5. BottomSum
  6. Children
  7. ClosingPeriod
  8. Cousin
  9. FirstChild
  10. FirstSibling
  11. Head
  12. LastChild
  13. LastSibling
  14. LastPeriods
  15. Lag
  16. Lead
  17. NextMember
  18. Non Empty
  19. NonEmpty Function
  20. OpeningPeriod
  21. ParallelPeriod
  22. Parent
  23. PeriodsToDate
  24. PrevMember
  25. Siblings
  26. Tail
  27. TopCount
  28. TopPercent
  29. TopSum

TIP: If you are working with Power BI tool, instead of MDX, you need DAX functions. So, Please refer to the Power BI tutorial to understand the DAX methods.

Comments are closed.