Microsoft SQL Server 2008 Business Intelligence Development and Maintenance: Developing SSRS Reports

Lesson 2: Creating a Dataset from a Data Source

Estimated lesson time: 20 minutes

Up to now in this chapter, you have worked with reports that contain single sets of data and that were defined by the Report Wizard. In this lesson, you will learn how to create datasets without the wizard and how to add additional datasets. You can use these datasets to enhance your reports and create composite reports.

Creating a New Report Dataset

A dataset is a collection of data. A dataset, like a table, consists of rows and columns. SSRS uses one or more datasets as the source of the report items. Note that this is a two-dimensional table, without nested tables, such as a SQL Server table. This distinction is important because when accessing multidimensional sources such as online analytical processing (OLAP) cubes, sources with nested tables such as data mining models, or sources with hierarchical data such as XML, you have to write the source query so that the result is flattened to a two-dimensional table.

A dataset has three main elements:

• Data source A data source configures the driver and connection properties to connect to the data.

• Command type The command type can be text, a table direct (which is equivalent to a SELECT * FROM table query), or a stored procedure. For many data sources—including a SQL Server relational database, SSAS cubes and mining models, and SSRS models—you can use graphical query designers.

• Query string Text commands use the query language of the data provider. For example, you use Transact-SQL (T-SQL) for the relational engine and Multidimensional Expressions (MDX) or Data Mining Extensions (DMX) for SSAS. You can call stored procedures by name.

A data source defines how SSRS connects to an underlying data source. SSRS supports nearly any source you might need, including SQL Server, SSAS, XML, report server model, SQL Server Integration Services (SSIS) package, Microsoft .NET Framework data providers, any OLE DB provider, and any Open Database Connectivity (ODBC) driver. In addition, you can create and install custom data processing extensions.

Datasets have other properties that are either optional or automatically generated when you configure the core properties. These properties are Timeout, Fields, Options, Parameters, and Filters.

Choosing a Shared or Private Data Source

You configure datasets in the Report Designer Design tab. But before you configure a dataset, you need to decide whether you want to use a shared data source or a private data source to connect to SQL Server or another data provider.

A shared data source is a report server item that has the following configuration information: the type of data provider, the connection string, and the security configuration to connect to the database or data provider. A shared data source is stored independently from the report and can be used to configure datasets in multiple reports.

You can also use private data sources. A private data source has the same provider type, connection string, and security configuration that a shared data source has. However, a private data source is not stored independently from the report but instead is embedded in the report. A private data source can be used only within the report for a single dataset.

Creating a Dataset

You create a new dataset by following these steps:

1. In the Report Designer, click the Design tab.

2. In the Report Data window, click New, and then select Dataset. You can create a data source while creating the dataset, or you can create a new data source from the New menu. Alternatively, if you already have a dataset created from a specific data source, you can add a new dataset from the same data source by right-clicking the data source and then clicking the Add Dataset option in the tree view of the data elements in the Report Data window.

3. In the resulting Dataset Properties dialog box, configure the name of the dataset and the data source.

4. To be able to use the graphical designer, leave the default query type as Text or use the Stored Procedure type and leave the query string empty. Click the Query Designer button.

You are probably already familiar with the Transact-SQL Query Designer. In the next section, you learn about the SSAS MDX Query Designer.

Working with an SSAS-Based Dataset

There are two query designers that work with SSAS data sources. The first is the Multidimensional Expressions (MDX) Query Designer, which lets you query OLAP cubes by using the MDX language. The second is the Data Mining Extensions (DMX) Query Designer. DMX is the language that lets you query and work with data mining models. In Chapter 9, you created a report that used a dataset based on a DMX query.

The Query Designer lets you switch between MDX and DMX by using the Command Type button on the Data pane toolbar. The Command Type button acts as a toggle between MDX and DMX. The Command Type MDX button deletes the previous DMX query and sets the designer to help you create a multidimensional query. And the Command Type DMX button deletes the MDX query and helps you create data mining queries.

Both designers have two modes: Design mode and Query mode. Design mode lets you create the query graphically, dragging different multidimensional structures or data mining elements. In Query mode, you can manually enter your MDX or DMX queries. When you work in Design mode to build an MDX query, the Query Designer automatically flattens the results of the query into two dimensions, using only the first two axes, which in MDX are named COLUMNS and ROWS. For a DMX query, the Query Designer does not flatten nested tables. After you create the DMX query graphically, do not forget to add the DMX keyword FLATTENED right after the SELECT and before the first column reference in the select list. Otherwise, you will get an error when trying to use a column that is actually a nested table. If you write an MDX or a DMX query manually, be sure to create a two-dimensional result.

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).

Practice: Creating Report Datasets

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 Chapter 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.

EXERCISE 1: Create the Relational Dataset and Add a Report

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. In BIDS, open the TK 448 Ch10 SSRS Purchasing project you created in the practice for Lesson 1 of this chapter.

2. 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.

3. 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.

4. 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.

5. In the Design tab, in the Report Data window, select New Dataset from the New menu. This will let you create a new dataset.

6. 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.

7. 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 dropdown list. Click OK.

8. 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;

9. Still in the Query Designer, click the Run button (displayed as an exclamation mark [!]) to execute the query, and then verify the results.

10. Click OK to exit the Query Designer, and then click OK again to exit the Dataset Properties dialog box.

EXERCISE 2: Create the Multidimensional Dataset

In this exercise, you create a multidimensional data set.

1. In the Design tab, in the Report Data window, select New Dataset from the New menu to create a new dataset.

2. In the Dataset Properties dialog box, in the Query pane, type TMDimensional as the dataset name. Click the New button 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.

3. In the Data Source Properties dialog box, in the General pane, type the name TMDimensionalDataSource as the data source name. Select the Use Shared Data Source Reference option, and then select the TMOLAP data source from the dropdown list. Click OK.

4. In the Dataset Properties dialog box, in the Query pane, click the Query Designer button to display the Query Designer dialog box.

5. Click the Cube Selection button to select the TargetMailCube, and then click OK.

6. From the Metadata tree, drag the following attributes from the TargetMailDim dimension and from Measures into the query area: Bike Buyer, Number Cars Owned, Gender, Marital Status, English Education, and English Occupation. Click the Design Mode button to show the query designed graphically. Your query should look like this:

   SELECT
     NON EMPTY { [Measures].[Bike Buyer] } ON COLUMNS
    ,NON EMPTY {(
      [TargetMailDim].[Number Cars Owned].[Number Cars Owned].ALLMEMBERS *
      [TargetMailDim].[Gender].[Gender].ALLMEMBERS *
      [TargetMailDim].[Marital Status].[Marital Status].ALLMEMBERS *
      [TargetMailDim].[English Education].[English Education].ALLMEMBERS *
      [TargetMailDim].[English Occupation].[English Occupation].ALLMEMBERS
     )}
     DIMENSION PROPERTIES
        MEMBER_CAPTION
       ,MEMBER_UNIQUE_NAME ON ROWS
   FROM [TargetMailCube]
    CELL PROPERTIES
       VALUE
      ,BACK_COLOR
      ,FORE_COLOR
      ,FORMATTED_VALUE
      ,FORMAT_STRING
      ,FONT_NAME
      ,FONT_SIZE
      ,FONT_FLAGS;

7. Click the Click To Execute The Query link in the Data pane of the Query Designer to execute the query, and then verify the results.

8. Click OK to exit the Query Designer, and OK again to exit the Dataset Properties dialog box. Save the solution and close BIDS.