Chapter 12

MDX Statements and ADO MD Objects

Review Questions

1. What MDX extensions are required for creating a local cube?

CREATE CUBE and INSERT INTO

2. Your cube contains a Geography dimension that has the levels Region and City. The regions are Eastern, Central, and Western. Each region contains several cities. How can you specify the names of the regions on an axis in your MDX statement?

You can explicitly list each region on an axis, or you can use the Children function as follows:

{[Geography].[Eastern], [Geography].[Central], [Geography].[Western]}

or

[Geography].Children

3. Using the scenario in the previous question, how can you list all of the regions and cities on the same axis?

Use the Members function to generate a list of all regions and cities in the [Geography] dimension as follows:

[Geography].Members

4. You want to create tuples that combine all cities in the Eastern region and the years 1997 and 1998 on the COLUMNS axis. How can you do this without explicitly listing each combination?

Use the Crossjoin function as follows:

 Crossjoin({[1997],[1998]}, {Descendants([Region].[Eastern], [Cities])}) 

5. Your cube contains two measures, ListPrice and Cost, about the items you sell. You want to include a calculated value, which is not a measure in the cube, in a report. How can you do this?

Create a calculated member called Profit by subtracting Cost from ListPrice. Use the following calculated member on one of the axes in the MDX query:

 WITH MEMBER [Measures].[ Profit] AS   ([Measures].[ListPrice] - [Measures].[Cost]) 

6. Describe the steps you should follow to create a local cube containing information for cost by region for each quarter in 1997. The default measure for the source cube is [Total Sales].

First create an MDX query that displays [Geography].children on the COLUMNS axis and{Q1:Q4} on the ROWS axis. Additionally, the query should specify Measures.[Cost] in the WHERE clause.

Write a CREATE CUBE statement based on the results of the MDX query.

Write an INSERT INTO statement to populate the local cube.

Include the CREATE CUBE and INSERT INTO statements in a connection string when opening a connection to the database in a custom application.

7. You need to build an application that presents the contents of a cube on a Web page. How can you populate a PivotTable Service-style chart with individual cell values?

Use the Item method of the Cellset object to identify an individual cell. Then retrieve the cell values by using the FormattedValue property.

8. Users want to build new OLAP cubes to address a particular business scenario. A single user will use these new cubes. How can you provide this functionality?

Use MDX CREATE CUBE and INSERT INTO statements to create and populate a local cube for offline analysis.

9. Write a query that reads the quarterly unit sales for each store in the state of Washington.

 SELECT Descendants([Store].[WA], [Store Name]) ON COLUMNS, [1997].Children ON ROWS   FROM Sal es WHERE (Measures.[Unit Sales])  

10. How do you return the first cell in a cellset?

Cellset.Item(0) or Cellset.Item(0, 0)

11. Name the Cellset object property that contains the slicer dimensions.

FilterAxis