Thursday, June 14, 2012 11:09 AM
i am new to MDX.Can u just give me details of MDX, about
1. What is the main purpose of MDX query?
2. Basic syntax for MDX.
3. Why we can not go for SQL query?
Thursday, June 14, 2012 12:15 PM
OLAP databases, such as Analysis Services, store data differently than relational databases. Therefore, to tap into the power an OLAP database can provide, a different querying language is needed. That's the function of MDX. SQL is limiting as only tables can be returned with well-defined columns and rows. A Cellset, the output of an MDX query, return back values with well-defined business context when the dimensions that are used to create your cube are designed properly.
For the structure of an MDX query, I suggest you start with the series of articles that William Pearson published a ways back. http://www.databasejournal.com/features/mssql/article.php/10894_1495511_2 That should get you started.
- Marked As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Thursday, June 21, 2012 1:22 AM
Thursday, June 14, 2012 12:28 PMAgreed with Martin, just adding one point that, SQL can return data set in two dimensions(as martin said columns and rows) and MDX can return data set more than two dimensions like rows, columns, sections, pages etc.
Thursday, June 14, 2012 12:32 PM
You are correct but it should be duly noted that the SSMS does not supports anything beyond 2 axes.
Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog
Thursday, June 14, 2012 12:56 PM
Not just SSMS, but almost all clients don't understand the information returned in each cell. An Excel PivotTable is one of the only clients that tries to do anything with something other than the Value property of a cell and one of the few that interprets Actions. (SSRS has to flatten the results to a dataset to interpret) Just wonder where Microsoft BI would be today if a choice had been made years ago to build a true, powerful SSAS client instead of trying to make Excel that tool. Over the years, Excel has become more and more powerful (i.e. Sets in 2010) but the changes are really, really, really slow in coming. IMHO, end delivery is far and away the weakest component of the MSBI platform, largely because of it's ties to Sharepoint and Excel. Not that I give SSRS high praise either. But this really isn't the forum to get on my soapbox.