Creating the Data Source View
After creating a data source, you can set up the data source view on which the report model will be based.
|Figure 5. The Data Source Wizard: You use the Data Source Wizard to set up a data source view.|
- Right click on the Data Source Views folder in the Solution Explorer pane and choose "Add New Data Source View." The Data Source Wizard starts (see Figure 5).
- Click Next to advance to the "Select a Data Source" step. Select the Adventure Works data source you created in the previous section. Click Next to move on to the "Select Tables and Views" step.
- For the sake of simplicity, this exploratory ad-hoc reporting model will be limited to five tables. These tables will allow users to browse sales order data by Product, Sales Territory, and Store dimensions. Select the Production.Product, Sales.SalesOrderDetail, Sales.SalesOrderHeader, Sales.SalesTerritory, and Sales.Store tables and then click Next.
- Accept the default name of Adventure Works and click Finish. The Data Source View wizard closes and BI Studio loads the Adventure Works DSV in the Data Source View Designer.
Don't be alarmed if the DSV Diagram pane doesn't show all the table joins. This lack is related to the complexity of the AventureWorks database schema. For example, the table Sales.Store
is indirectly related to Sales.SalesOrderHeader
through the Sales.Customer
table because Adventure Works supports two types of customers: individuals and stores. Since we didn't select the Sales.Customer
table, the relationship between the Sales.Store
tables won't be shown in the DSV Designer.
DSV gives you a lot of flexibility to adjust the database schema to fit your needs. For example, suppose that instead of bringing up the entire sales order history (some 30,000 plus orders), your ad-hoc requirements allow users to report only on orders placed in the current quarter. As a report designer, you should pay attention to the performance implications of your report queries. You can implement horizontal data filtering at the data source (by using views or stored procedures) or at the DSV level (with named queries). Further, assume that the Adventure Works security requirements rule out database changes, so the only option left to filter data is to use a DSV named query. You can filter the Sales.SalesOrderHeader
table by replacing it with a named query as follows:
|Figure 6. Creating a DSV Named Query: Use DSV named queries to implement virtual DSV tables.|
- In the DSV diagram, right-click on the SalesOrderHeader table and choose "Replace Table With New Named Query." The Create Named Query designer appears (see Figure 6).
- Change the named query SQL statement to filter data for the current quarter. For this example you can hardcode the year parameter to 2004 because that's the last year for which the Adventure Works sample database contains data. Change the named query name to Sales Order and click OK to return to the DSV designer.
- You can annotate the DSV schema with custom table relations. For example, join the tables Sales Order and Stores by dragging the CustomerID column from the Sales Order table and dropping it onto the column of the same name in the Store table. Next, join the Order Details and Product tables by dragging the ProductID column from the Order Details table and dropping it onto the ProductID column of the Product table.
DSV also allows you to create expression-based table columns in the form of named calculations. One caveat: You must write such expressions using the syntax supported by the underlying data source. For example, to calculate the order total, right click on the Order Details
table, choose New Named Calculation, and enter the following T-SQL expression in the Create New Named Calculation dialog:
|Figure 7. The Adventure Works Data Source View: The figure shows how your DSV diagram should look after completing the steps described thus far in this article.|
(isnull(([UnitPrice] * ((1.0) --
[UnitPriceDiscount])) * [OrderQty], (0.0)))
After creating the named calculation, you'll see it appear at the end of the table when you browse the table data (right-click on the table and choose Explore Data).
At this point, assuming that all is well, your DSV diagram should look similar to the one shown in Figure 7
Now that you've created the Adventure Works DSV, you finalize the ad-hoc model by implementing the semantic layer.