Create a Report Model
Objective and Business Scenario
A Report Model represents an abstract layer consisting of metadata about an underlying physical database. The published model enables information consumers to assemble reports without understanding query languages (or queries in general), databases (in general, or the "plumbing and tanks" that store and move their own), and so forth. Report Models also provide a means whereby we can provide just the data we wish for consumers to be able to access – and no more. A Report Model is made up of entities, or "masters" of sorts, which consist of report items that are related in nature. The model allows us to both group and name the items in a way that makes sense to the end users, alleviating the need for them to understand database names and structures that might not be intuitively recognizable to them.
Report Model entities are quite flexible. Combined with a forehanded approach to gathering business requirements, and coupled with effective (and imaginative) planning and design of the model, entities can make data exploration and report building easy and efficient for even inexperienced consumers. (The importance of Report Model design in the delivery of a quality user experience is not to be underestimated). Entities present the report items, as well as predefined relationships between entities and predefined calculations (consumers can also create new fields and calculations based upon the data we have defined within the Report Model), within user-friendly groups within Report Builder. As we noted earlier, consumers can preview, print, publish and export their reports from Report Builder, too.
We will begin by creating a new Report Model Project within the Business Intelligence Development Studio, wherein we will create a Data Source pointed to a database sample provided with MSSQL Server 2005. This way, anyone with access to the installed application set and its samples can complete the steps in the practice session.
NOTE: For more exposure to the MSSQL Server Business Intelligence Development Studio itself, and the myriad design, development and other evolutions we can perform within this powerful interface, see articles in this and my other Database Journal series, Introduction to MSSQL Server Analysis Services. In this article, we will be commenting only on the features relevant to our immediate practice exercise, to allow us to get to the focus of our session more efficiently.
Create a New Report Model Project
We begin our preparation with the creation of a new Report Model Project. Report Model Projects contain a Data Source definition file, a Data Source View definition file, and a Model definition file. We will gain exposure to each of these components in the respective section of the procedure that follows.
1. Click the Start button.
2. Select Microsoft SQL Server 2005 within the Program group of the menu.
3. Click SQL Server Business Intelligence Development Studio, as depicted in Illustration 1.
Illustration 1: Opening SQL Server Business Intelligence Development Studio
The Microsoft Visual Studio 2005 development environment opens, beginning with the Start page, as shown in Illustration 2.
Illustration 2: The Start Page, Microsoft Visual Studio 2005 Development Environment (Compressed View)
4. Close the Start Page tab.
5. Select File --> New on the Visual Studio main menu.
6. Select Project from the cascading menu, as depicted in Illustration 3.
Illustration 3: Beginning a New Project ...
The New Project dialog appears.
7. Select Business Intelligence Projects in the Project types pane of the dialog.
8. Select Report Model Project in the Templates pane, as shown in Illustration 4.
Illustration 4: Select Report Model Project
NOTE: The templates that appear in your template pane may differ, depending upon which SQL Server 2005 components are installed in your environment, as well as whether additional templates (for Business Intelligence Projects or other types of projects that can be created) have been defined in Visual Studio.
9. Change the project Name (currently displaying a default) to the following:
RS028 Intro Report Model
The Solution Name changes to match the project Name by default.
10. Navigate to a convenient location to store the Project and Solution files, modifying the Location box accordingly (Visual Studio will create a directory based upon our input here).
The New Project dialog appears similar to that depicted in Illustration 5.
Illustration 5: The New Project Dialog, with our Input
11. Click OK to accept our input and to create the new Report Model Project.
The New Project dialog closes, as Visual Studio creates the project. Both solution and project RS028 Intro Report Model appears in the Solution Explorer as shown in Illustration 6.
Illustration 6: The New Report Model Solution and Project Appear
Solution Explorer presents a tree view of the objects contained in the solution, the primary management unit within the Business Intelligence Development Studio, which can contain multiple projects. Individual projects, such as the one we have created, themselves contain folders for the objects that can be defined for projects of a similar type.
As we can see in the present instance, the Report Model Project template, upon which our RS028 Intro Report Model Project was based, contains the following folders:
- Data Sources
- Data Source Views
- Report Models
Anytime we create a new Report Model Project, the Solution Explorer and the Properties window are visible and docked, as we see them in the present case. The following windows are hidden and docked, initially, and appear on the right, bottom, or left side of the development environment, depending upon where they are docked:
- Server Explorer
- Task List
- Error List
Viewing any of the hidden windows is as simple as positioning the pointer over it – its mouseover behavior is to reappear. We can also click the Auto Hide button (depicted for the Solution Explorer in Illustration 7) to hide or unhide a window.
Illustration 7: The Auto Hide Button – Solution Explorer
Finally, we can always open a closed window by selecting it from the View menu atop the development environment, as shown in Illustration 8.
Illustration 8: Reopen Closed Windows from the View Menu ...
Having created a new Report Model Project, we are ready to define a Data Source and Data Source View.