With the query designer, you can build a dataset by dragging-and-dropping metadata from your cube to the designer. There is no easier way to build a dataset based on a cube than by the drag-and-drop method.
In a basic cube model, you have facts and dimensions which are attributes that correspond to a particular business process or transaction.
Let's take sales as a business process. The facts in sales are prices and quantities. Facts are usually numeric. On the other hand, dimensions are the context in which the sales transaction happened. In this case, the dimensions are date when the actual sales took place, the product sold, the sales person, and other related attributes.
To fully understand the performance of your sales, you need some kind of metrics; you need to measure the sales by means of aggregation (sum, average, and so on.). These metrics are represented by numeric values called measures.
For our purpose, let's focus on measures and dimensions:
- Select the Sales Summary cube from the Cube Selection dialog box. Click on OK.
- Select the Sales Territory dimension, and drag it to the designer as shown in the following screenshot:
You may notice that it doesn't give you any data except for the three columns: Group, Country, and Region. You may also notice the message in the middle of the designer that says No rows found. Click to execute the query. What we have so far is the context of the Sales Territory, where sales occurred. Now we need the measure:
- Expand the Measure metadata. Then expand Sales Summary. Select and drag-and-drop the Sales Amount measure to the designer as shown in the following screenshot:
- Now click on OK in the designer. Going back to the Dataset Properties dialog box, you may notice that the query designer converted the design to a text query. This is the MDX language. Move around metadata in the query designer and pay attention to how that changes the MDX query. This is one way of learning the MDX language.
- Name the dataset
dsSalesTerritory. Click on OK when done to close the Dataset Properties dialog box.