Building Ad-hoc Reporting Solutions with SQL Server 2005 Report Builder and Analysis Services OLAP

 

Tian Ying He, Carolyn Chau

Microsoft Corporation

October 2006

Applies to:
   Microsoft .NET Framework 2.0
   Microsoft SQL Server 2005 Reporting Services
   Microsoft SQL Server 2005 Analysis Services

Summary: This paper introduces Microsoft SQL Server 2005 Report Builder and demonstrates how to build an end-to-end ad hoc reporting solution for enterprise customers using Report Builder and Microsoft SQL Server 2005 Analysis Services OLAP. We also highlight a few product limitations, as well as enterprise considerations. It is based on a real-world implementation by Microsoft Business Intelligence Center of Excellence. (14 printed pages)

Click here to download the Word version of this article, Ad-hocRptSol.doc.

Contents

Introduction
Designing an Analysis Services OLAP Cube
Building a Report Model
Deploying a Report Model to a Report Server
Authoring Ad-hoc Reports Using Report Builder
Publishing Reports
Conclusion
More Information

Introduction

Many organizations have adopted Microsoft SQL Server Reporting Services and Analysis Services OLAP as part of their business-intelligence platform. SQL Server 2005 Reporting Services introduces a new end-user product, Report Builder, that enables business users to create ad-hoc reports based on information stored in both relational and OLAP data stores.

Report Builder Overview

SQL Server 2005 Report Builder is a ClickOnce application developed using the Microsoft .NET Framework version 2.0 and Windows Forms technology. It is included in SQL Server 2005 Enterprise, Standard, and Workgroup editions. With Report Builder, business users can create their own reports without having to understand the underlying database schema, Transact-SQL or MDX query syntax, or developer tools such as Microsoft Visual Studio.

Report Builder comes with templates for table, matrix, and chart reports. Ad-hoc reports can be created by selecting a report-layout template and dragging data fields to a report-design area. Report Builder supports commonly used reporting features, such as filtering, grouping, sorting, and the creation of formulas. Reports created by using Report Builder can be saved to a Reporting Services report server. They then can be managed using Report Manager and SQL Server Management Studio, in the same way as other reports developed using SQL Server Business Intelligence Development Studio and Report Designer.

Report Builder not only provides a simple report-authoring experience, but it also makes the database schema transparent to business users by creating a business-oriented semantic layer called a report model. Report models are described in an XML-based Semantic Model Definition Language (SMDL). For details on SMDL, please refer to SQL Server Books Online. SQL Server Reporting Services reports, including reports created using Report Builder, are saved using Report Definition Language (RDL), an XML-based language used to define reports.

SQL Server Reporting Services provides a role-based security model. Only individuals who have assigned permissions can access Report Builder, which is accessed either through a URL or from Report Manager. Reports created using Report Builder can also be secured based on user's role assignments.

An overview of the SQL Server 2005 ad-hoc reporting architecture is shown in Figure 1, which includes the following major components:

  • Model Designer is a model creation and editing tool that is part of SQL Server BI Development Studio. Model Designer can be used to edit models based on relational data sources only. In the current release, it does not support editing models based on OLAP data sources.
  • Report Manager is a Web-based application for managing Reporting Services reports.
  • Report Builder is a report-authoring tool for creating and publishing ad-hoc reports.
  • Report Model is a metadata layer that stores business-oriented models of underlying data-source schemas.
  • Semantic Query Engine is a component of the report server that executes and builds MDX or Transact-SQL queries against data sources.
  • OLAP and RDBMS data sources contain information about data and the relationships between that data.

Aa964121.adhocrptsol01(en-US,SQL.90).gif

Figure 1. SQL Server 2005 ad-hoc reporting architecture

Understanding the Product Limitations

There are a few limitations when creating Reporting Services reports using Report Builder against Analysis Services OLAP data sources.

  • Report Builder models generated against an Analysis Services cube cannot be edited using Model Designer. Also, modifying the report-model (.smdl) file directly is not supported. If you want to provide user-friendly names for entities, such as dimensions, attributes, or measures, you must make the modifications in the cubes directly.
  • There is a limit to the number of entities and their relationships that can be explored in Report Builder. This is limited to paths that equate to navigating from a dimension to a related measure group, and then to a second related dimension.
  • Report Builder is limited to a single data source per model, while reports created using Report Designer can support multiple data sources.
  • Report Builder doesn't support all of the reporting features available in Report Designer, such as expressions in text boxes, freeform layout, and nested filters.
  • The parent-child attribute relationships in a cube are not visible in a report model.
  • The sort order that is applied by Analysis Services is not preserved by the reports. This issue is especially noticeable for time attributes.
  • In releases prior to SQL Server 2005 Service Pack 1 (SP1), you cannot add images such as company logos or other graphics to your Report Builder reports.
  • In releases prior to SP1, when generating a report model from an OLAP data source, dates in the generated MDX queries might have the month and day parts transposed.
  • Formulas that use explicit aggregate functions over a set, instead of using server aggregates, return null values.

Note   If the above limitations are too restrictive and prevent you from delivering a solution that meets your business needs, an alternative is to create a model directly against the cube source, which is a relational database. You can actually share the Data Source View that you built in the cube, but you will lose calculations, KPIs, and so on, that you defined in the cube.

Development Life Cycle of SQL Server Ad-hoc Reporting Solutions

Ad-hoc reporting capabilities are highly valued by information consumers. With SQL Server 2005, ad-hoc reports can be built against both OLAP and relational data sources. Figure 2 describes major steps in delivering an end-to-end solution for ad-hoc reporting using SQL Server 2005 Reporting Services and Analysis Services, starting with analyzing business requirements.

Aa964121.adhocrptsol02(en-US,SQL.90).gif

Figure 2. Development life cycle of SQL Server 2005 ad-hoc reporting

In this paper, we will discuss how to design, build, and deploy report models, and how to author and publish Report Builder reports to a report server.

Designing an Analysis Services OLAP Cube

Although a report model can be generated to expose the measures and dimensions of an OLAP cube, without careful design, you might find that the model generated doesn't provide a very friendly user experience for ad-hoc reporting.

Because a report model will be used by business users, it is important that it present data in the way that is business-oriented, instead of data-centric. For relational databases, SQL Server 2005 provides Model Designer, a tool that allows a report model to be manipulated and customized for use with Report Builder. At present, Model Designer doesn't support OLAP cubes as a data source. Also, it is not recommended that you directly edit the report-model (.smdl) file. The best approach is to design your OLAP cube in such a way as to be meaningful to your business users. In the following sections, we will discuss various components and design implications when building an OLAP cube for ad-hoc reporting.

Measure Groups and Dimensions

The group and dimension names that you assign to your OLAP cube are what will appear in the report model. Therefore, it is a good idea to give your cube elements names that will be meaningful for your report authors. The relationships between measure groups and dimensions will be converted to roles in the model and will affect the navigation of data elements in Report Builder. The Report Builder user interface respects the underlying data relationship between model items. Arbitrary navigation between entities in Report Builder is not possible. Unlike SQL Server Management Studio or SQL Server BI Development Studio, Report Builder shows only the data elements that are possible to navigate. The following screen shot depicts how measure groups and dimensions are associated in an OLAP cube.

Click here for larger image

Figure 3. Relationships between measure groups and dimensions in an OLAP cube

Perspectives

Cubes can be very complex, while business users might have to interact with only a subset of the cube objects that are related to their business needs. In Analysis Services, you can create a perspective, which is a view of a subset of cube objects. Perspectives can be business-oriented or application-specific. Perspectives control the visibility of objects that are contained in a cube.

When you build a model from a cube, the perspectives become report-model perspectives. Measures, dimensions, and calculations must be exposed by way of cube perspectives in order to be included in the report model. This is an important step to be taken before generating the report model. The following screen shot shows cube perspectives such as "Direct Sales," "Channel Sales," "Sales Summary," and "Finance."

Click here for larger image

Figure 4. Perspectives of an OLAP cube (Click on the image for a larger picture)

KPIs and Calculations

By default, Key Performance Indicators (KPIs) and calculations are not associated with measure groups, so they will not be included in the report model. In order for KPIs and calculations to be included in a report model and to be consumable by ad-hoc reporting, they must be associated with measure groups. To associate KPIs and calculations with measure groups, you can use the Calculation Properties dialog box, as shown in Figure 5.

Aa964121.adhocrptsol05(en-US,SQL.90).gif

Figure 5. Associating calculations with measure groups

InstanceSelection Property of Attributes

A popular feature in Report Builder is to have drop-down filters for reports based on certain dimension attributes, such as location and time. The dimension-attributes property InstanceSelection is used to set the report-model properties InstanceSelection (on entities) and ValueSelection (on attributes). To enable drop-down filters for certain dimension attributes in Report Builder, the InstanceSelection property of those dimension attributes must be set to DropDown in your OLAP cube. See Figure 6.

Aa964121.adhocrptsol06(en-US,SQL.90).gif

Figure 6. InstanceSelection property of dimension attributes

Fact Dimensions

When generating a report model based on an OLAP cube, fact dimensions will be combined with related measure groups, instead of resulting in a different report-model entity. For example, given a cube with a "Customer" measure group and a "Customer Dimension" fact dimension, both are based on the same fact table. The report model generated will contain a single entity with all the measures of "Customer" and all the dimension attributes of "Customer Dimension." This could result in a bad user experience, because dimension attributes and measures will be listed in the same folder in Report Builder. We recommend creation of non-fact dimensions in the cube, to support ad-hoc reporting using Report Builder.

Naming Conventions

Because the names of OLAP cube objects will be used directly in the report model, we recommend that you use business-friendly names whenever possible. Keep in mind that renaming cube objects can be costly, because a cube can be used by other applications. Changing the names in a cube that is being used in another application could cause the application to break.

Data Dictionary

A data dictionary must be created and published for ad-hoc reporting. At minimum, it should provide descriptions of data fields and the relationships between data fields.

Clickthrough

Clickthrough features of Report Builder are enabled on measure attributes and aggregate attributes by default, regardless of what drillthrough action is defined in the cube.

Building a Report Model

From an Analysis Services OLAP cube, a report model can be automatically generated using either Report Manager or SQL Server Management Studio. Unlike models generated from relational data sources, report models generated from OLAP cubes cannot be edited using Model Designer. You can edit the .smdl file directly, but we do not recommend this. If there are any schema changes in the OLAP cube, the model must be regenerated.

You must be a database administrator on the Analysis Services database to generate the report model. You must first connect to the database, before a report model can be generated. You will then define a data source for Reporting Services and generate the model.

Generating a Report Model Using Report Manager

To create a report model using Report Manager, you first must define a data source. Open Report Manager by specifying the URL of the report server, which by default is http://MyServer/Reports (or https://localhost/Reports if you open an Internet browser on the report server itself). Click the New Data Source button on the toolbar, and type a name for the data source, such as "Adventure Works". Under Connection type, select Microsoft SQL Server Analysis Services. Under Connection string, type a connection string to the OLAP cube—for example, "Data Source=(local); Initial Catalog=Adventure Works DW". Under Connect using, click Windows integrated security or one of the other authentication methods, and then click Apply. A data source will be created.

To generate the model, select the new data source. The Properties tab of the data source should open, or you can select it. Click the Generate Model button, as shown in Figure 7. You will be prompted for a model name and a location in which to store the model.

Click here for larger image

Figure 7. Generate a report model with Report Manager (Click on the image for a larger picture)

Generating a Report Model Using SQL Server Management Studio

To create a report model using SQL Server Management Studio, you must first connect to a report server. After that, right click the Home folder and select Create New Data Source. Type the name of the data source, such as "Adventure Works". Select the Connection page, and under Data source type, select Microsoft SQL Server Analysis Services. Under Connection string, type a connection string to the OLAP cube—for example, "Data Source=(local); Initial Catalog=Adventure Works DW". Click Windows integrated security, and then click OK to finish. See Figure 8.

Click here for larger image

Figure 8. Create a data source with SQL Server Management Studio (Click on the image for a larger picture)

Finally, right-click the new data source, and then select Generate Model.

Versioning of a Report Model

Although a report model created from a relational data source inherits a unique GUID, item identifiers in Analysis Services–based models are textual. Therefore, each time a model is generated, the item will receive the same identifier. The reports created on a previous model will run seamlessly against the new version of the model. If there are any updates on the OLAP cube schema, such as renaming objects, ad-hoc reports created using old models might no longer work.

Saving a Report Model

You can save the report model to a local drive as an .smdl file. In an enterprise environment, this feature is especially useful when you develop report models in a development environment and want to deploy them to a production environment. You can save a report model by using either Report Manager or SQL Server Management Studio. Use the Edit option to save the model. You can then specify a model name for the .smdl file. You can also do this programmatically by using SQL Server Reporting Services Web Services.

Deploying a Report Model to a Report Server

SQL Server 2005 Report Services provides various tools with which to deploy your report model. Developers might prefer SQL Server BI Development Studio or Report Manager, while system administrators might favor SQL Server Management Studio or customized installation packages. Report models can be deployed either manually by using tools or programmatically by using the Report Server Web service.

Deploying a Report Model Manually

A report model can be deployed manually by using the following SQL Server tools:

Report Manager

Because a report model is stored in an XML file, you can deploy the model by uploading the .smdl file to the report server. In Report Manager, select the Upload File button from the toolbar. On the next page, select the model file and click OK. Please note that this works only for Analysis Services models or models that were previously published. You cannot take an .smdl file based on a relational database from a Model Designer project and upload it to a report server directly.

SQL Server Management Studio

In SQL Server Management Studio, right-click the Home folder and select Import File. In the Name field, type the name you want to use for the model, and then select the .smdl file—for example, AdventureWorks.smdl. You can choose to overwrite an existing model.

SQL Server BI Development Studio

For report models based on a relational database, you can use Model Designer to edit and deploy the model to a report server. Presently, Model Designer does not support Analysis Services–based models. As a result, you cannot use SQL Server BI Development Studio to deploy report models generated against an Analysis Services OLAP cube.

After a model is deployed to the report server using either Report Manager or Management Studio, you must update the data source of the model by going to the property page of the model. You can do this using either Report Manager or SQL Server Management Studio. To do this in Report Manager, select the report model and click the Data Source link. On the next page, click the Browse button and select the data source, and then click OK to finish. To update a data source using Management Studio, connect to the report server, right-click the model, and select Properties. A Model Properties dialog box will be displayed. Select the Shared Data Source page and click the Browse button to choose a data source. Click OK.

Deploying Report Model Programmatically

Enterprise customers often create an installation package to automate the deployment process. You can use the Report Server Web service to accomplish this. The following Visual Basic code example shows how to upload a model to the report server, create a data source, and associate the data source with the model.

Uploading Report Model
Dim DataSourceName As String = "/Adventure Works"
Dim ModelName As String = "/Adventure Works Model"
Dim MyServer As String = "MyReportServer"
Dim rs As New ReportingService2005
 rs.Url = "http://" + MyServer + "/reportserver/reportservice2005.asmx"
 rs.Credentials = System.Net.CredentialCache.DefaultCredentials
 Dim props = Nothing
 Dim modelPath As String = "./Adventure Works Model.smdl"
 Dim fs As FileStream
 fs = File.OpenRead(modelPath)
 Dim modelDefinition As Byte() = New [Byte](fs.Length) {}
 fs.Read(modelDefinition, 0, CInt(fs.Length))
 fs.Close()
 Try
    rs.CreateModel("Adventure Works Model", "/", modelDefinition, props)
 Catch e As SoapException
    Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +
      " (" + e.Detail.Item("Message").InnerText + ")")
 End Try

Creating Data Source
Dim dsDefinition As New DataSourceDefinition
 dsDefinition.Extension = "OLEDB-MD"
 dsDefinition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
 dsDefinition.ConnectString = "data source=" + MyServer + ";initial catalog=Adventure Works DW"
 dsDefinition.ImpersonateUserSpecified = True
 dsDefinition.Enabled = True
 dsDefinition.EnabledSpecified = True
 Try
    rs.CreateDataSource("Adventure Works", "/", False, dsDefinition, props)
 Catch e As SoapException
    Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +
      " (" + e.Detail.Item("Message").InnerText + ")")
 End Try

Associating Report Model with Data Source
Dim ds() As DataSource
 ds = rs.GetItemDataSources(ModelName)
 Dim dsref As New DataSourceReference
 dsref.Reference = DataSourceName
 ds(0).Item = dsref
 Try
   rs.SetItemDataSources("/Adventure Works Model", ds)
 Catch e As SoapException
   Console.WriteLine("Error : " + e.Detail.Item("ErrorCode").InnerText +
      " (" + e.Detail.Item("Message").InnerText + ")")
 End Try

Model Security

Report models can be secured at different levels; choose the right level for your environment and business needs. You can use Report Manager or SQL Server Management Studio to set security on the model and Report Builder. However, to set security on a model item, you must use SQL Server Management Studio.

  • Model level—Control access to the report model within the report-server folder hierarchy. A user must have permission to view and run the model.
  • Model Item level—Control access to individual entities and perspectives within the model. Securing model items is optional.
  • Report Builder level—Control access to Report Builder.

For more information about securing models and controlling access to Report Builder, see Updating Role-Based Security for Report Builder Access in MSDN.

Authoring Ad-hoc Reports Using Report Builder

After a report model is deployed to the report server, business users can start creating ad-hoc reports using SQL Server 2005 Report Builder.

Access to Report Builder

One of the commonly used approaches to control access to Report Builder is through Windows security groups. For example, you can create a security group named "AdventureWorksAnalysts" for Report Builder users. To give users access to Report Builder, you must perform the following tasks:

  1. Assign the "Execute Report Definition" task to the security group. This can be done by adding the security group to either the "Administrator" or "System User" predefined system role in SQL Server Reporting Services.
  2. Associate this security group with an item-level role called "Report Builder." The "Report Builder" role is a predefined role that includes permissions to create reports, load reports to Report Builder, view and navigate the model, and save reports to a SQL Server report server.

Starting Report Builder

Report Builder is a ClickOnce Windows Forms application. It can be started from either Report Manager or the Report Builder URL. Report Builder is automatically installed on the client's computer when it is started for the first time. In Report Manager, go to http://MyServer/Reports and click the Report Builder button on the Home page to install and start Report Builder.

Selecting a Perspective and a Template

Report Builder will load with all the available report-model perspectives and templates, as shown in Figure 9. To begin, users select a model and a report layout (Table, Matrix, or Chart). When the user selects an OLAP cube–based model, all the perspectives are listed. Users cannot select the top-level model; they must select a perspective, which then loads its entities and fields in the Entities and Fields panes. If a Report Builder report contains items that are not within the single perspective, an error can occur when attempting to run the report.

Click here for larger image

Figure 9. Selecting a perspective and a template (Click on the image for a larger picture)

Adding Data Elements

After selecting a perspective and a template, users create a report by dragging data elements from the field list to the design area. As fields are dropped onto the template, Report Builder builds the query in the background. Figure 10 shows how Report Builder would appear if a user selected the Customer perspective.

Click here for larger image

Figure 10. Creating and editing reports in Report Builder (Click on the image for a larger picture)

Within the Customer perspective, there are four entities. An entity is a collection of related data that has fields that uniquely identify an instance of the entity. The fields belonging to the Location entity are listed in the Fields pane. Report Builder preserves the relationship of the underlying data and uses it to control the navigation among entities. To test the report, click the Run Report button. The report is processed on the report server and then rendered in Report Builder. To continue revising the report, click the Design Report button. If there is an error, Report Builder will display a dialog box with error messages.

Applying Filters

One of the most commonly used features in ad-hoc reporting is filtering. To add filters to a report, select a field, and then click the Filter button. If the InstanceSelection attribute enables drop-down, filter values will be automatically populated, as shown in Figure 11. Report Builder uses only distinct values to populate the drop-down list. You can specify a hidden filter or a visible filter. To let users select filter criteria at run time, add a filter prompt to the report. Right-click on the filter label, and then and select Prompt.

Click here for larger image

Figure 11. Adding drop-down filters to the report (Click on the image for a larger picture)

Although Report Builder supports drop-down filters, it does not support hierarchies in the cube and cannot include nested filters. To do this, you have to create a report using Report Designer.

Sorting and Grouping

You can specify grouping and sorting on report fields by clicking the Sort and Group button on the toolbar.

Formatting

Users can add a variety of formatting to their reports. In design mode, you can format text boxes, fields in the report layout, and data. To do so, you can either use the Formatting toolbar or right-click the selected item to display the Format dialog box. Report Builder supports formatting such as number, alignment, font, border, and fill. The following illustration shows some of the options available on the Number tab.

Aa964121.adhocrptsol12(en-US,SQL.90).gif

Figure 12. Format dialog box

Formulas

Formulas or expressions are calculations performed on values in a report. A formula can contain functions, operators, constants, and references to fields or entities.

Although Report Builder provides the functionality to add formulas to reports, there are certain limitations. For enterprise BI solutions, you might want to consider implementing calculations in the OLAP cubes whenever possible. Because OLAP cubes are preprocessed and data in the cube can be consumed by multiple applications, applying calculations in the cube can improve query performance, increase system scalability, and enforce consistent business rules across different applications.

Report Builder vs. Report Designer

SQL Server 2005 comes with two report-authoring tools, Report Builder and Report Designer. The differences between these two products are shown in the following table.

Report Builder Report Designer
Targeted at business users Targeted at IT pros and developers
Ad-hoc reports Managed reports
Auto-generates queries using semantic layer on top of data source Native queries (Transact-SQL, MDX)
Reports built on templates Freeform (nested, banded) reports
ClickOnce application, easy to deploy and manage Integrated into Visual Studio
Cannot import Report Designer reports Can work with reports built in Report Builder

Performance Considerations

When creating ad-hoc reports using Report Builder against OLAP data sources, MDX queries are automatically generated by the tool. Report Builder allows users to create reports freely; how users navigate the report model will determine the structure of the MDX query, which could affect report performance. For example, it is possible to navigate by way of two different roles from a dimension or apply separate filters on columns, which might require using aggregate functions over a huge data set. One should be very careful not to just drag fields from any old node in the tree that looks like it might be what you're looking for. The instances of that entity to which your fields are referring will depend on the path you used to get there. Also, Report Builder's ability to generate clickthrough reports on any value could result in huge result sets that affect the performance negatively.

Due to those performance considerations, it is recommended to consider deploying ad-hoc reporting to a separate environment from managed reports for enterprise customers.

To track the MDX queries generated by Report Builder, you can use SQL Profiler to capture the incoming SQL commands or enable query logging by changing the <RStrace> setting in the web.config file located in your ReportServer install directory.

Publishing Reports

Like other Reporting Services reports, Report Builder reports are saved in RDL format. When you create a report in Report Builder, you are really creating a report definition, which is a file that contains the definition and metadata used to generate the final query. The data of a report is generated at run time. By default, the report definition is saved as an .rdl file on the report server from which Report Builder was started. The actual data displayed when the report runs is not saved in the .rdl file.

When you save an .rdl file to the report server, this is called publishing a report. Publish the report definition to report server when you want regularly to run the report and view the current data, or if you want to share the report with others in your organization. Ad-hoc reports can be saved to a user's local drive, if the user chooses not to publish the report. The report can be edited at a later time and published to a different report server.

With the right permissions, an ad-hoc report can be published to report server directly. You can then assign permissions to the report, to control who has access to your reports. Figure 13 shows the sample Report Builder report rendered in Report Manager.

Click here for larger image

Figure 13. Ad-hoc report published on a report server (Click on the image for a larger picture)

Report Builder reports are exactly like reports created using Report Designer, except that Report Builder must use a model as a data source. After a report is published or saved, however, it will work the same way as other Reporting Services reports. It can be opened and edited using either Report Builder or Report Designer. Keep in mind that Report Designer provides more formatting and processing functionality than Report Builder. In other words, Report Designer can read and modify reports created using Report Builder, but not vice versa. After an .rdl file has been modified in Report Designer, it cannot be edited in Report Builder.

Conclusion

In this article, we have provided an overview of SQL Server 2005 Reporting Services Report Builder tool and walked you through major steps to build an end-to-end ad-hoc reporting solution against an Analysis Services OLAP cube. We also highlighted a few product limitations, as well as enterprise considerations. Integrating with SQL Server 2005 Analysis Services, Report Builder gives users the power to generate their own reports against their organization's OLAP data stores.

More Information