Reviewing and Comparing a Data Warehouse Database Schema


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?"

Creating a Database Diagram

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.

Add Relationships to the Database
  1. Using the File > Open > File menu in SSMS, locate and open the image from book Chap12 - is2005sbsDW Add Relationships.sql script file.

  2. Execute this script, using the red exclamation icon on the toolbar.

  3. Close this tab in SSMS when the script execution has completed.

Create a New Diagram for the is2005sbsDW Database

  1. Using the Object Browser in SSMS, expand Databases and is2005sbsDW.

  2. 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.

  3. 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.

image from book

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.

Save the Diagram
  1. From the toolbar or File menu, click Save.

  2. Name the diagram ResellerSales.

Query the is2005sbsDW database
  1. To open a new query window, right-click the is2005sbsDW database in the Object Browser and choose New Query.

  2. On the File menu, click File, Open, and then File.

  3. Navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap12\ folder and open image from book Chap12 - is2005sbsDW Reseller Sales query.sql.

  4. 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 

  5. 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.

Reset the Relationships for is2005sbsDW
  1. On the File menu, click File, Open, and then File.

  2. Navigate to the C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005sbs\Chap12\ folder and open image from book Chap12 - is2005sbsDW Remove Relationships.sql.

  3. Execute the script, using the exclamation button on the toolbar.

  4. Close the script file when execution is complete.




Microsoft SQL Server 2005 Integration Services Step by Step
MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step
ISBN: 0735624054
EAN: 2147483647
Year: 2007
Pages: 152

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net