You are probably familiar with the T-SQL language that we use to query data from SQL Server, that is, OLTP. But Analysis Services requires a different language for querying OLAP cubes, which is called Multidimensional Expressions or, simply, MDX. For more information on Querying Multidimensional Data with MDX, check http://msdn.microsoft.com/en-us/library/bb500184.aspx.
Note
Another language used in SSAS is the prediction query language called Data Mining Extensions or DMX. DMX is used in data mining model to predict unknown values in a dataset. You also probably heard about Data Analysis Expressions or DAX, which is a formula language used in PowerPivot workbooks in Excel.
You need to learn and understand MDX if you work with multidimensional data. If you aren't familiar with MDX at the moment, reporting services comes with a query designer for multidimensional data.
Let's create a dataset using the query designer:
- From the Dataset Properties dialog box, click on the Query Designer button underneath the query box.
- We can change the cube by clicking on the ellipsis button next to the cube textbox on top of the left-hand side of the query designer.
- That displays the Cube Selection dialog box. Select the cube appropriate to your report as shown in the following screenshot:
- Get yourself familiarized with the buttons by hovering and reading the title of the buttons which indicates the associated actions.