I need some advice about SSRS reporting using SSAS (Tabular mode) as source

Question

This may not be specifically a programming question but am completely new to SSAS though i know SSRS and relational databases.

I have an SSRS report that has been using an SQL Server relational database as source. Because of some business requirements, the source for this report is now changing to an SQL Server 2012 analysis services cube. The SSAS server mode is "Tabular" and the Cube's DirectQueryMode is "InMemory".

From what i have so far figured, my T-SQL query for the report with not work with an SSAS source.

QUESTION: What query language should i re-write my queries in now? DAX or MDX (i have no experience with both of them, so i can't tell why i should use one over the other).

Besides DAX & MDX, is there something else that is better suited for querying SSAS (Tabular mode) for SSRS reporting?

Answer 1

While either language is capable of querying SSAS in Tabular mode, one thing to keep in mind is that the DAX would be the native language of the Tabular model.

Note that one thing that can help in testing your query, is that Excel is able connect to SSAS in either Multidimensional or tabular mode. Excel will allow you to browse the data as a pivot table as a drag and drop environment while it generates an MDX query in the background. It uses MDX since leverages its ability to query OLAP cubes.

Which also makes testing calculated measures easy, since you can run the measure vs. manually calculating it in excel to verify things line up.