Introduction
This tip is for beginners who want to read data from SSAS cube. Numbers are available in Cube under Dimension and Measure Group. MDX (multi dimension expression) query is used to read data from SSAS cube. This article will show how cube data can be displayed through SSRS report.
Background
Data warehouse contains several fact and dimension tables. These tables contain huge record set. Accessing facts/measures from these tables in small relational database is easy. It can be done using T-SQL query. But when dataset is very large, it becomes complex to display in SSRS report. SSRS reports take many hours to display such report. So that these heavy data set are implemented in SSAS cube. MDX query is used to read data from cube. MDX query gets record set very quickly. Hence large report takes very small time to display in SSRS report.
Using the Code
To use this tip, one must have available cube in SQL Server Analysis Service Engine. Below images show how cube should be available in analysis service.
In simple SSRS report with MDX query, we are reading data from Adventure Works Cube. Below MDX query will be used in simple SSRS report to get Internet sales data.
SELECT NON EMPTY
{
[Measures].[Internet Freight Cost],
[Measures].[Internet Sales Amount],
[Measures].[Internet Average Sales Amount],
[Measures].[Internet Average Unit Price]
} ON COLUMNS FROM [Adventure Works]
Below images describe step by step process to show MDX result in SSRS report.
- Create New report server project.
- Create New data source points to SQL Server analysis service database.
- Create New Data set for Internet sales report using MDX query.
- Create New report file to display MDX result.
- Design table and assign required column in SSRS report.
Points of Interest
This is very simple project to learn reading of Cube data through MDX and display in SSRS report.
History
- 18th July, 2014: Initial version
