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.

Where does SSAS fit in Business?

A typical high-level business architecture follows the following model.

  • Step 1: Data coming from different sources (Excel, Files, Databases, etc).
  • Step 2: Use ETL tools like SSIS or Informatica to transform the Step 1 data and load it into a data warehouse.
  • Step 3: If the data is not that huge, you can slice and dice the data here. However, if the data is huge, load it into the SSAS cubes.
  • Step 4: Use any reporting tool, including SSRS, Power BI, or Tableau, to design reports based on this data.

Multidimensional Expressions (MDX)

MDX is short for Multidimensional Expressions, a powerful query language for working with OLAP cubes (SSAS data). If your goal is to work with the SSAS cube Measures and Dimensions, you must rely on these MDX queries. For example, you can find the average sales of all members or siblings.

As we all know, a cube holds multidimensional data, including columns, rows, pages, sections, etc. So, when we query an OLAP cube, we must explicitly specify what we want to display in the result set. For example, Sales and Profits (Measures) on the Columns and Country or Category (Dimensions) on the Rows. By this, the MDX query shows two columns (sales and profits) and one row field (category or country).

TIP: The first step is to open SQL Server Management Studio and connect to Analysis Services using localhost or the named instance. Next, write a query against the OLAP cube.

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.