How to: Create a Report Model Using Model Designer

You can generate models from a SQL Server database or an Oracle database by using Model Designer. First, you need to create a report model project in Business Intelligence Development Studio. Next, you create a data source file and a data source view file. Finally, you create a semantic model file.

To create a report model project

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

2. On the File menu, point to New, and then click Project.

3. In the Project Types list, click Business Intelligence Projects.

4. In the Templates list, click Report Model Project.

5. Type a name and location for the project, or click Browse and select a location.

6. Click OK.

   A new project is displayed in the Solution Explorer window.

To create a data source

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

   The Data Source Wizard opens.

   Note

   Alternatively, you can launch the Report Model Wizard first and then create the data source and data source view files while you create the report model.

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

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

4. In Server Name, select the name of the server that you want to connect to.

5. Select the connection method that Report Builder users will use to connect to the database.

   • Windows Authentication: Select this option when you want the operating system to authenticate SQL Server users. This option allows SQL Server to use Windows security features, such as password encryption, to authenticate users. It is strongly recommended that you select this option.
   • SQL Server Authentication: Select this option when you want users to use a SQL Server login account that you created. Users must supply a valid SQL Server login name and password.

   Warning

   Whenever possible, use Windows Authentication.

6. In the Select or enter a database name list, select the name of the database that you want to use.

7. To verify the connection, click Test Connection.

8. Click OK and then click OK again.

9. Click Next.

10. On the Completing the Wizard page, a default data source name is displayed in the Data source name box. Type a different name, or use the default name. The default name is the name of the database that the connection uses.

11. To complete the wizard, click Finish.

    The new data source appears in the Data Sources folder in Solution Explorer.

To create a data source view

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

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

3. On the Select a Data Source page, select a data source from the Relational data sources list. If no data sources are listed, you need to create one by clicking the New Data Source button.

   Important

   When generating the data source view for your Oracle model, make sure you click the Advanced button on the Select a Data Source page of the Data Source View Wizard and, in the Advanced Data Source View Options dialog box, select the schema name to filter the database objects. Then select the database objects that you want to add to your model.

4. Click Next.

5. On the Name Matching page, click Next.

6. On the Select Tables and Views page, select the tables and views that you want to include in your semantic model and then click the arrow buttons to move the objects from the Available objects list to the Included objects list.

   Important

   When creating an Oracle-based report model, do not click the Add Related Tables button when selecting the tables and views that you want to add to the report model. If you click the button, the data source view (.DSV) file will not be generated.

7. Click Next.

8. On the Completing the Wizard page, a default data source view name is displayed. You can type a different name, or use the default name which is the name of the data source for which you are creating the data source view.

9. To complete the wizard, click Finish.

   The new data source view appears in the Data Source Views folder in Solution Explorer.

To create a report model

1. In Solution Explorer, right-click the Report Models folder and click Add New Report Model.

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

3. On the Select Data Source View page, select the data source view that you want to use for your report, and then click OK. If no data source views are listed, you need to create one by clicking the New Data Source View button.

4. On the Select report model generation rules page, select the rules that you want to apply to the model, select the model language that you want to use, and then click Next.

5. On the Update Statistics page, verify that Update statistics before generating is selected. To create a report model, the Report Model Wizard needs to collect basic statistics about the data source before it can create a model.

   Note

   Unless the data source or the data source view have changed, you can select the Use current statistics stored in the data source view option the next time you update the model.

6. Click Next.

7. On the Completing the Wizard page, type a name for the report model, and then click Run.

8. Click Finish.

   The contents of the report model are displayed.

See Also

Concepts

Ad Hoc Reporting with Report Models
Creating a Report Model Project
Working with Model Designer

Other Resources

Working with Data Sources (Analysis Services)
Working with Data Source Views (Analysis Services)

Help and Information

Getting SQL Server 2005 Assistance