Implementing the Semantic Layer
As an ad-hoc model designer, you will spend most of your time designing and refining the semantic layer. To jumpstart model generation, the RS team has provided (you guessed it!) a handy Wizard.
|Figure 8. The Report Model Wizard: Use the Report Model Wizard to generate the raw semantic layer.|
- Right-click on the Report Models folder and choose Add New Report Model to start the Report Model wizard. Click Next to advance to the Select Data Source View step.
- Select the Adventure Works DSV you've just created and click Next to move on to the "Select report model generation rules" step (see Figure 8).
The Report Model Wizard generates the raw model in two passes. Each pass contains rules that could be applied when the pass is run. In Pass 1, it discovers and creates entity objects. Optionally, you can create various expression-based attributes. For example, when you select the "Create count aggregates" option, the Report Model Wizard generates count aggregates, such as # of Orders
. Similarly, if you check the "Create numeric aggregates" rule, the Wizard creates the aggregate attribute on numeric fields such as sum
Pass 2 refines the model. For example, Pass 2 assigns appropriate attribute formats (currency, dates, etc).
- Click Next to advance to the "Update statistics" step. The Report Model Wizard relies on correct database statistics (table row count, uniqueness of the column, etc) to derive to an accurate semantic model. It stores the statistics in custom properties in the data source view definition. In this step, the Report Model Wizard asks you to confirm if the database statistics are up to date. Select the "Update statistics before generating option" when you run the wizard for the first time or whenever the DSV definition has changed. Click Next to advance to the Completing the Wizard step.
|Figure 9. The Model Designer: You use the Model Designer to design the semantic model.|
- Name the semantic model Current Month Sales. The model name is important because this is how end users identify the model in the Report Builder. Click Run to generate the model.
- You can edit a generated model by double-clicking on its name in the Solution Explorer. Double-click on the Current Month Sales model to open it in the Model Designer (see Figure 9).
The Model Designer lists the model entities in the left pane. When you select an entity, the Model Designer displays its fields in the right pane. As mentioned earlier, the field type could be either an attribute or a role. The Designer prefixes expression-based attributes with a number sign (#
), and denotes standard attributes with an "a."
If you have selected the "Create date variations" rule, the wizard generates automatically date parts (day, month, year, etc.) for columns of a date data type. For example, the Ship Date attribute includes the typical date hierarchy (day, month, quarter, and year), as well as the automatically generated First Ship Date and Last Ship Date.
Glancing at the Model Designer, you can easily see the roles defined for the selected entity. For example, the Sales Order entity has Customer and Territory roles that reflect the table relationships defined in the DSV diagram.
The only thing that the Model Wizard doesn't do is read your mind, so you need to spend some time fine-tuning the model. Here are a few final touches.
- Click on the Sales Order entity and change the SortDirection property of the Order Date to Descending so that reports will show the most recent orders first.
- Enable drillthrough on the product name by selecting the Product entity and set the EnableDrillthrough property to True.