The following steps will take you through an exercise similar to the previous one so that you can compare the operational is2005sbs database schema to the data warehouse is2005sbsDW database schema, using a database entity relation diagram. You will execute a query that will return the same results as before with less effort. As before, this query answers the question, "What were the total Mountain Bike sales for stores in the State of Washington during calendar year 2003?"
Using the same technique as you did for the operational is2005sbs database, you will create a database diagram for the is2005sbsDW database, including the tables needed to run the query.
To simplify many of the data transformation steps thus far, referential constraints and relationships between tables in the is2005sbsDW database were removed. This is a common practice when manipulating large sets of data in multiple tables. The process of adding and removing relationships can easily be scripted. You will execute a script to add relationships before creating a diagram and then remove the relationships with a script at the conclusion of this exercise.
Using the File > Open > File menu in SSMS, locate and open the Chap12 - is2005sbsDW Add Relationships.sql script file.
Execute this script, using the red exclamation icon on the toolbar.
Close this tab in SSMS when the script execution has completed.
Using the Object Browser in SSMS, expand Databases and is2005sbsDW.
Right-click Database Diagrams and choose New Database Diagram from the menu. As before, a dialog box might appear, asking whether you want to create diagramming objects in the database. If this dialog box is presented, click Yes.
The Add Table dialog box displays all of the tables in the is2005sbsDW database. Select the following tables from this dialog box and add them to the diagram:
FactResellerSales
DimReseller
DimGeography
DimProduct
DimProductSubCategory
DimTime
Note the simplicity of the following diagram compared to the diagram from the "Create a Database Diagram" exercise.
Note | It takes only six tables to support the same query in the data warehouse that took nine tables in the operational database. This is because dimension data and business facts have been consolidated. |
From the toolbar or File menu, click Save.
Name the diagram ResellerSales.
To open a new query window, right-click the is2005sbsDW database in the Object Browser and choose New Query.
On the File menu, click File, Open, and then File.
Navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap12\ folder and open Chap12 - is2005sbsDW Reseller Sales query.sql.
This Transact-SQL query references all of the tables you added to the database diagram. Review the syntax of this query:
USE is2005sbsDW SELECT DimReseller.ResellerName , SUM(FactResellerSales.SalesAmount) AS SalesAmount FROM FactResellerSales INNER JOIN DimReseller ON FactResellerSales.ResellerKey = DimReseller.ResellerKey INNER JOIN DimGeography ON DimReseller.GeographyKey = DimGeography.GeographyKey INNER JOIN DimProduct ON FactResellerSales.ProductKey = DimProduct.ProductKey INNER JOIN DimProductSubCategory ON DimProduct.ProductSubCategoryKey = DimProductSubCategory.ProductSubCategoryKey INNER JOIN DimTime ON FactResellerSales.OrderDateKey = DimTime.TimeKey WHEREDimTime.CalendarYear = '2003' AND DimProductSubCategory.ProductSubCategoryName = 'Mountain Bike' AND DimGeography.StateProvinceName = 'Washington' GROUP BY DimProductSubCategory.ProductSubCategoryName, DimReseller.ResellerName ORDER BY DimReseller.ResellerName
Execute the query by clicking Execute on the SQL Editor toolbar and make note of the time it takes for the query to complete. This is displayed in the status bar below the query window. The query returns 15 rows showing the stores and the total Mountain Bike sales in Washington State for calendar year 2003. This query should take about two-thirds as long as the first.
Note | Compared with the previous query for the is2005sbs database, this query is simpler. It uses fewer tables, and the calendar year value uses an exact value from the DimTime table. The operational database required the year to be derived from the OrderDate datetime type column. |
The final step is to set the is2005sbsDW database back to its previous state with no relationships. This step is necessary to repeat the exercises for previous chapters.
On the File menu, click File, Open, and then File.
Navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap12\ folder and open Chap12 - is2005sbsDW Remove Relationships.sql.
Execute the script, using the exclamation button on the toolbar.
Close the script file when execution is complete.