Read OLAP cube and display in simple SSRS report

Article
04/04/2015

Introduction

This article is for beginner who wants 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 display through SSRS report.

Background

Data warehouse contains several fact and dimension table. these table contains huge record set. accessing facts/measures from these table 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 takes many hour 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 article one must have available cube in SQL Server Analysis Service Engine. Below images shows how cube should available in analysis service.

In simple SSRS report with MDX query , we are reading data from Adventure Works Cube. Below MDX query will 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.

1. Create New report server project