Lesson 3: Creating a Report Model Using Management Studio
New: 5 December 2005
Creating a report model in Report Manager or Management Studio is a two step process: you first need to create the data source, and then you can generate the model. A data source is a connection string that indicates the location of the data that is to be used by Reporting Services to create a report model and by the report server when running Report Builder reports. In this lesson, you will use Management Studio to create a data source and report model, and then view the report model in Report Builder.
To open Management Studio
Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.
The Connect to Server dialog box appears.
Note
If the Connect to Server dialog box does not appear, in Object Explorer, click Connect, and then select the server type to which you want to connect.
In the Server type drop-down list, select Reporting Services.
In the Server name drop-down list, select the server where you want to create your model.
In the Authentication drop-down list, select Windows Authentication.
Click Connect.
When the connection is established, the Microsoft SQL Server Management Studio window appears.
To create a data source
In Object Explorer, expand Home.
Right-click the Data Sources folder, and select New Data Source.
The New Data Source dialog box appears.
In the Name box, type:
MS_ASCube
In the Description box, type:
This is the Report Manager data source file for the SSAS cube.
Verify that the Enable this data source for reports check box is selected.
In the Select a page area, click Connection.
In the Data source type drop-down list, select Microsoft SQL Server Analysis Services.
Note
The connection type must be either Microsoft SQL Server or Microsoft SQL Server Analysis Services (SSAS) for it to be used as a model source. To create a report model based on a SQL Server database, select Microsoft SQL Server.
In the Connection string box, type
Data Source=localhost;Initial Catalog="Adventure Works DW"
Select Windows integrated security.
Click OK.
The MS_ASCube data source is created and appears in the Data Sources folder.
To create a report model
In the Data Sources folder, right-click MS_ASCube, and then select Generate model.
The Generate Model dialog box appears.
In the Name box, type the following:
MS_ASCubeReportModel
In the Data Source name box, verify that Home/Data Sources/MS_ASCube appears.
In the Generate the model in this folder box, specify the folder where you want to save the model.
Note
If you have previously deployed models, a Models folder probably exists on the report server.
In the Description box, type:
This is the AdventureWorks AS cube-based model created using Management Studio.
Click OK.
The MS_ASCubeReportModel model is created in the folder that you specified. You can move the report model to another folder on the report server by right-clicking the report model, selecting Move, clicking the ellipsis (…) button, selecting a folder on the report server, clicking OK, and then clicking OK again.
To explore the model in Report Builder
Open your Internet browser.
In the Address bar, type the URL for your report server.
By default, the URL is http://<servername>/reports.
Press Enter.
The Reporting Services Home page opens in your browser.
Click Report Builder.
The Report Builder window appears.
In the Getting Started pane, locate MS_ASCubeReportModel.
This report model appears exactly the same as the model that you created in Lesson 2. Notice that the there are several items nested below the MS_ASCubeReportModel model. These nested items are perspectives. When a report model is created from an Analysis Services cube, the cubes in the SSAS database are displayed as perspectives in Report Builder. The top level model cannot be used to generate reports; instead, you must select one of the nested perspectives.
Select the Channel Sales perspective, and then click OK.
On the Explorer pane, notice that the name of the perspective that you selected appears at the top of the pane, and the cube contents are displayed as entities and fields.
To exit Report Builder, on the File menu, click Exit.
Next Steps
Congratulations, you have successfully completed this tutorial. You created two SSAS cube-based report models, one by using Report Manager and the other by using Management Studio.
See Also
Concepts
SQL Server 2005 Analysis Services Tutorial
Other Resources
Generating Models Using Report Management Tools
Working with Report Builder (Ad Hoc Reports)
Introducing SQL Server Management Studio
Developing Analysis Services Solutions and Projects
Working with Data Sources (Analysis Services)
Working with Data Sources How-to Topics (SSAS)