Lesson 1: Creating a Report Model in Model Designer

  • Article

To generate a model, you need to create a report model project. In this lesson, you will create a report model project using the Business Intelligence Development Studio. A report model project is a container for the model and consists of one or more data source (.ds) files, one or more data source view (.dsv) files, and one or more report model (.smdl) files. Only one data source and data source view can be referenced in an .smdl file. After you create a report model project, you will deploy the report model to your report server, and then use the report model to create a basic report using Report Builder.

Note

For the purposes of this tutorial, you will use the data source view that is automatically generated for you. To learn more about manipulating data source views, see Working with Data Source Views (Analysis Services), Working with Data Source Views How-to Topics (SSAS), and Lesson 1: Defining a Data Source View within an Analysis Services Project.

To create a Report Model Project in Business Intelligence Development Studio

  1. Click Start, point to All Programs, point to Microsoft SQL Server 2005, and then click SQL Server Business Intelligence Development Studio.

  2. In the Microsoft Visual Studio window, click File, point to New, and then click Project.

  3. Click Report Model Project.

  4. In the Name box, type Adventure Works Model.

  5. Click OK.

To create a data source

  1. In Solution Explorer, right-click Data Sources, and then select Add New Data Source.

    The Data Source Wizard opens.

  2. On the Welcome to the Data Source Wizard page, click Next.

  3. On the Select how to define the connection page, verify that Create a data source based on an existing or new connection is selected and the click New.

    The Connection Manager dialog box opens.

    Note

    The Provider drop-down box is grayed out because only SQLClient Data Provider is supported. Model Designer can only generate models based on SQL Server databases.

  4. In the Server name list, select the name of the computer where the AdventureWorks database is stored.

  5. Verify that Use Windows Authentication is selected.

  6. In the Connect to a database area, verify that Select or enter a database name is selected, and then select AdventureWorks from the list.

  7. To verify that the connection works, click Test Connection.

  8. Click OK.

  9. On the Select how to define the connection page, verify that the connection you just created is selected, and then click Next.

  10. On the Completing the Wizard page, verify that AdventureWorks appears in the Data source name box.

  11. Click Finish.

    The Data Source Wizard closes, and the AdventureWorks.ds file appears in Solution Explorer. Next, you will create a data source view file.

To create a data source view

  1. In Solution Explorer, right-click the Data Source Views folder and select Add New Data Source View.

    The Data Source ViewWizard opens.

  2. On the Welcome to the Data Source View Wizard page, click Next.

  3. On the Select a Data Source page, verify that the AdventureWorks data source you created in the previous procedure is selected.

  4. Click Next.

  5. On the Select Tables and Views page, select all the tables in the Available objects area, and then click the arrow button.

    Note

    Views can be added to a report model. They are excluded from this tutorial for simplicity of the resulting model.

  6. Click Next.

  7. On the Completing the Wizard page, verify that AdventureWorks appears in the Name box, and then click Finish.

    The AdventureWorks.dsv file appears in Solution Explorer. Next, you will create the report model.

To create a report model

  1. In Solution Explorer, right-click Report Models and then select Add New Report Model.

    The Report Model Wizard opens.

  2. On the Welcome to the Report Model Wizard page, click Next.

  3. On the Select Data Source View page, verify that the AdventureWorks data source view you created in the previous procedure is selected.

  4. Click Next.

  5. In the Select model language drop-down list of the Select report model generation rules page, select a language for the model.

    The language you select tells Model Designer which language-specific code it should use when generating user-friendly names for the tables and columns in your database. After you complete the wizard, you can set the Culture property on the model.

    Note

    If you select a language other than the default after you change the model generation rules, you will lose any changes you have made to the rules. If you lose your changes, you need to select or clear the check boxes again for each rule that you changed.

  6. Accept the default rules.

    Note

    By changing the rules used to create the model, you control what is generated.

  7. Click Next.

  8. On the Collect Model Statistics page, verify that the Update model statistics before generating option is selected, and then click Next.

    Note

    To create a report model, the Report Model wizard collects statistics about the number of unique instances for each field and their cardinality information. Collecting these statistics does not affect your database statistics or performance.

  9. On the Completing the Wizard page, verify that AdventureWorks appears in the Name box, and then click Run.

    The report model is created.

  10. When the wizard is finished, click Finish.

    The AdventureWorks.smdl file appears in Solution Explorer. You have successfully created a report model. In the next procedure, you will explore the model.

To explore the report model

  1. In the Model pane, select the Customer entity.

    Note

    The report model consists of multiple entities. All entities, and any folders that you create, are listed in the Model pane. The Model pane is also called the Tree view.

  2. View the center pane. The contents of the Customer entity appear. The attributes include: #Customers, #CustomerID, Account Number, Customer Type, and Modified Date.

    Note

    The center pane is called the List view. The attributes, roles, and perspectives contained within a selected entity are displayed in List view.

  3. In the List view, select the #Customers attribute, and then view the contents of the Properties window.

    Note

    If the Properties window is not displayed, on the View menu, select Properties Window.

  4. In the List view, select the Customer Type attribute, and then view the contents of the Properties window.

  5. In the List view, select the Sales Order Headers role, and then view the contents of the Properties window.

  6. On the File menu, click Save All.

    Next, you will deploy the report model to the report server.

To deploy the report model

  1. On the Project menu, click AdventureWorks Model Properties.

    Note

    The default deployment location is the local server at https://localhost/reportserver and the default deployment location for a model is in the /Models folder located off the root of the report server. If these are not the deployment properties you want to use, specify the properties specific to your environment.

  2. After you confirm the deployment properties that you want to use, click OK.

  3. On the Build menu, click Deploy AdventureWorks Model.

    If the deployment is successful, a Deploy succeeded message is displayed in the Output window. To view the Output window, on the View menu, click Output. If the deployment fails, you need to troubleshoot the reported error.

    In the next procedure, you will test the report model in Report Builder.

To use the model in Report Builder

  1. Open your browser.

  2. In the Address bar, type https://localhost/reports.

    The Home page for SQL Server Reporting Services opens.

  3. On the Report Manager toolbar, click Report Builder.

  4. In the Getting Started pane, select AdventureWorks and then click OK.

  5. In the Entities list, select Sales Order Header.

  6. From the Entities list, drag the Sales Person entity to the design area.

    Notice that the Sales Person National ID Number and Commission Pct fields are added to the design area.

  7. In the Fields list, double-click Total Total Due.

    Notice that the Total Total Due field is not formatted as a monetary value.

  8. To see the results of your query, click Run Report.

  9. On the File menu, click Exit.

    You do not need to save the report.

Next Steps

In the next lesson, you will add a description to the model that you just created. See Lesson 2: Adding a Description to a Model.

See Also

Tasks

Tutorial: Refining a Report Model in Model Designer

Concepts

Reporting Services Tutorials

Other Resources

Creating a Report Model Project

Help and Information

Getting SQL Server 2005 Assistance