Creating a Multidimensional Query in Visual C#.NET

Generate Code 128 Code Set A in Visual C#.NET Creating a Multidimensional Query

To create multidimensional queries, you need to be familiar with the MDX Query Designer. Figure 10-3 shows the different areas of the MDX Query Designer.

In Figure 10-3, the main areas of the MDX Query Designer are marked with the numbers 1 through 5, which are used in the following list to describe the features of the designer window in greater detail:

To change the cube or perspective, use the Cube Selection button (1). To add a hierarchy to the query, expand the dimension in the Metadata pane (2), and then drag the hierarchy to the Data pane (5). To add a level to the query, expand the dimension in the Metadata pane (2), and then drag the level or levels to the Data pane (5). To delete a level from the query, drag the column header from the Data pane (5) to any spot on the screen that is outside the Data pane. To delete a hierarchy from the query, drag each of the elements of the hierarchy from the column header in the Data pane (5) to any spot on the screen that is outside the Data pane. To add a measure to the query, expand the dimension in the Metadata pane (2), and then drag the hierarchy to the Data pane (5).

To filter the query, expand the dimension in the Metadata pane (2), and then drag the level or hierarchy from the Metadata pane to the Filter area (4). In the Filter area, select the operator and filter expression to filter the dataset. To create a calculated member, right-click in the Calculated Members area (3), and then click New Calculated Member. Name the calculated member, and then write the MDX expression to calculate the cell. Drag the calculated member to the Data pane (5).

ne of my customers recently had a problem delivering on the company s plan to share financial reports with partners on an extranet. The problem was that

the reports from the relational data sources were simply too slow. The query to summarize accounting information for a partner for multiple years was taking about a minute. As a solution, we introduced a finance unified Dimensional Model (uDM) cube. With proper aggregations and data, SSAS was able to return the results hundreds of times faster than SQL Server. The MDX query we created took only fractions of a second. And the partners were very pleased to be able to receive the reports on the extranet.

In this practice, you will use BIDS to create two reports without using the Report Wizard. One of the reports will have a relational data source, and the other will have an SSAS OLAP cube data source. You will use these datasets in the next lesson as well. This practice requires the completed and deployed solution named TK 448 Data Mining from Exercise 3 in the Lesson 1 practice for 9. If you did not complete the practices from the previous chapter, you can find the solution in the ..\Source\Ch 09\ folder. Simply open and deploy the solution.

In this exercise, you create two shared data sources, a relational one and multidimensional one, as well as a relational dataset. This exercise will help you create the report interface in the next practice.

1. 2.

In BIDS, open the TK 448 Ch10 SSRS Purchasing project you created in the practice for Lesson 1 of this chapter. In Solution Explorer, right-click the Shared Data Sources folder, and then select Add New Data Source. Name the data source tmrelational, click Edit, and then configure

the following options: Set the connection type to Microsoft SQL Server, the server name to (local), the database to AdventureWorksDW2008, and authentication to Windows Authentication. Click OK twice to save the new shared data source.

In Solution Explorer, right-click the Shared Data Sources folder, and then click Add New Data Source. Name this data source tmOLap, and use the following options for the connection: set the type to Microsoft SQL Server Analysis Services, the server name to (local), the database to TK 448 Mining Models Project, and authentication to Windows Authentication. Click OK twice to save your changes. In Solution Explorer, right-click the Reports folder, select Add, and then select New Item. Do not select the Add New Report option that option starts the Report Wizard. In the Add New Item dialog box, select the Report template from Report Project Categories list. Type targetmail.rdl for the report name, and then click Add. In the Design tab, in the Report Data window, select New Dataset from the New menu. This will let you create a new dataset. In the Dataset Properties dialog box, in the Query pane, type tmrelational as the dataset name. Click New next to the Data Source box to create a new data source for this dataset. Instead of creating a new data source, you will add a reference to a shared data source. In the Data Source Properties dialog box, in the General pane, type the name tmrelationalDatasource as the data source name. Select the Use Shared Data Source Reference option, and then select the TMRelational data source from the drop-down list. Click OK. In the Dataset Properties dialog box, in the Query pane, click Query Designer to display the Query Designer dialog box. Start by clicking the Add Table button (the rightmost button on the toolbar) to select the vTargetMail view with the following columns: CustomerKey, FirstName, LastName, MaritalStatus, Gender, EnglishEducation, EnglishOccupation, NumberCarsOwned, and BikeBuyer. Your query should look like this:

SELECT CustomerKey ,FirstName ,LastName ,MaritalStatus ,Gender ,EnglishEducation ,EnglishOccupation ,NumberCarsOwned ,BikeBuyer FROM dbo.vTargetMail;

5. 6.