How I may help
LinkedIn Profile Email me!
Call me using Skype client on your machine

Reload this page Adventureworks Sample Database

Here is an analysis of the AdventureWorks sample database from Microsoft Codeplex.
Included is an analysis of how to handle and estimate data volume growth for stress tests of this database.

Related Topics:

 

Topics this page:

  • Northwind/Pubs
  • Overview
  • 2005 Version
  • DB Types
  • 2008 Installer
  • Install SQL
  • 2008 vs. 2005
  • Entity Model
  • Table Stats.
  • Data Warehouse
  • Schema Tables
  • Business Processes
  • Handling Growth
  • Estimating Growth
  • Verifying Estimates
  • Generate App
  • Your comments???

    		•  

    Site Map List all pages on this site 
    About this site About this site 
    Go to first topic Go to Bottom of this page


    Screen: Max • 1024 • 800

    Set this at top of window. Overview

    Set this at top of window. Variations

    Set this at top of window. Versions

    Set this at top of window. The 2014 Version

    Set this at top of window. The 2012 Version

    Set this at top of window. The 2008 Version

    Set this at top of window. The 2005 Version

    Set this at top of window. Business Processes (Transactions) Using Data

    Set this at top of window. Handling Future Growth

    Set this at top of window. Estimating Future Growth

      Currency exchange rate values change so quickly that OLTP apps should obtain this information from a real-time web service rather than retrieving them from a database table. The settlement values are for consistency of a single value during off-line processing.

      Because values in these look-up tables are rather static, OLTP apps can leverage processing power on clients by downloading these files to maintain domain intrgrity at each client.

      The small size yet frequent use of these tables mean that they can be resident in memory and thus not need indexing since the server can scan through them faster than looking up an index.

      The slow growth of these tables mean that they can be created without the fill space some tables use to more quickly accomodate for future growth. Tables with no fill impose less memory overhead and perform faster. When rows are occassionally added, the table can be rebuilt quickly due to their small size.

    1. Not much growth (in rows) is expected within reference tables such as:

      1. AddressTypeID in Person.AddressType
      2. Person.StateProvince
      3. Person.CountryRegion
      4. Person.CountryRegionCurrency
      5. PhoneNumberTypeID in Person.PhoneNumberType
      6. ContactTypeID in Person.ContactType
      7. CurrencyCode in Sales.Currency
      8. Sales.CurrencyRate
      9. Sales.SalesReason
      10. Sales.SalesTaxRate
      11. Purchasing.ShipMethod
      12. Production.UnitMeasure
      13. Production.ScrapReason
      14. Production.Culture
      15. HumanResources.Shift

    2. Some tables have a direct correlation:

      1. There should be always one ProductModelIllustration row for each combination of ProductModel and ProductIlluration because that's why "Link" (associative) type tables exists -- to maintain many-to-many junction relationships.
      2. Other such Link type tables include:
        • One Person.PersonCreditCard row per Person and CreditCard combination
        • One Sales.SpecialOfferProduct row per Product and Sales.SpecialOffer combination
        • One Production.ProductDocument row per Product and Document combination
        • One Production.ProductProductPhoto row per Product and ProductPhoto combination
      3. One Production.TransactionHistoryArchive row for each Production.TransactionID
      4. One Person.BusinessEntityAddress row for each Person.Address row

    3. Some tables grow (or shrink) according to technical linkages:

      1. There should be at least one Person.Password row per Person as long as a password is required by the application inserting Person rows.
      2. This is also the case with PersonPhone and EmailAddress.
        The AdventureWorks2008 DB by default has only one PersonPhone and EmailAddress per Person. But nowdays most people have several phone numbers and email addresses.
      3. Purchasing.PurchaseOrderDetail rows per Purchasing.PurchaseOrderHeader row
      4. Purchasing.PurchaseOrderHeader rows per Purchasing.Vendor row
      5. Sales.CreditCard rows per BusinessEntityID in Sales.PersonCreditCard row
      6. Sales.SalesOrderHeader rows per BusinessEntityID value in table BusinessEntity

    4. Some changes in table size reflect management-directed activity -- causitive items which cause other ratios to change:

      1. When hiring occurs, the number of Employee and Salesperson rows increase.
      2. When wage increases (or decreases) occur, the number of EmployeePayHistory rows per Employee increase more .
      3. Whenever there is a reorganization, the number of Department and EmployeeDepartmentHistory rows multiply .
      4. When store openings are planned, the number of Store rows increase.
      5. Whenever there is a sales territory change, the number of SalesTerritory rows may change. But the number of SalesTerritoryHistory rows would multiply.
      6. With each sales quota change, the number of SalesPersonQuotaHistory rows will muliply.
      7. When new products are added, the number of Product and ProductModel rows increase.
      8. Whenever there is a product re-categorization, the number of ProductCategory and Production.ProductSubCategory rows per Product would change.
      9. As the organization expands internationally into more cultures (or withdraws from them) the number of Production.ProductDescription and Production.ProductModelProductDescriptionCulture rows per Product would change.
      10. Management desire to achieve lower total product support costs with more customer self-service would likely increase the number of Production.Document and Production.Illustration rows per product .
      11. Changes in floor layout may impact the number of Production.Location rows.
      12. Vendors are added or removed based on management decisions.

    5. Some changes in master : detail row ratios over time reflect choices among opposing management philosophies and strategies over time:

      1. More Production.ProductInventory rows per Product row may reflect higher inventory turns which improves cash flow and thus return on capital invested.
      2. Changes in JobCandidate rows per Employee row may reflect more internal competition among employees or a lower JobCandidate : Employee ratio to reflect more efficient hiring.
      3. More Production.TransactionHistory rows per Product row may reflect more customer interaction.
        A lower ratio may reflect better pre-planning.
      4. Fewer Production.BillOfMaterial rows per Product row may reflect simplification for better efficiency.
        A higher ratio may reflect greater variety which justify higher margins/profit.
      5. Fewer Production.WorkOrderRouting rows per Production.Location row may reflect simplification for better efficiency.
        More rows may reflect greater customization which justify higher margins/profit.
      6. More Production.ProductPhoto and Production.ProductProductPhoto rows per Product row may reflect simplification for better efficiency.
        More rows may mean reduce customer hesitancy to buy instantly.

    6. In the "real world", some data are the result of factors external to the organization:

      1. The number of Customer and WorkOrder rows depends on whether activities and strategies directed by management translate into actual sales (work orders).
      2. The number of ShoppingCartItem and ProductReview rows are also dependent on customer behavior.
      3. The number of Production.ProductCostHistory and Production.ProductListPriceHistory rows per Product row would increase due to higher inflation.

    Set this at top of window. Verifying Data Volume Estimates

    Set this at top of window. Application Generation

    Set this at top of window. Extending AdventureWorks to a Web Store

    Set screen Share This

    Go to Top of this page.
    Previous topic this page
    Next topic this page

    Set screen Next


    Go to Top of this page.
    Previous topic this page
    Next topic this page
    Performance Engineer Life Role Site Map Performance and Capacity Management... RSS feed validator Syndicate this list of links:

    Feed Validity Checked RSS 2.0 XML feed
    Feed Validity Checked Atom 1.0 XML feed

    		 

    How I may help

    Send a message with your email client program


    Your rating of this page:
    Low High




    Your first name:

    Your family name:

    Your location (city, country):

    Your Email address: 

         		  Top of Page Go to top of page
    Previous topic this page

    Human verify:
     
    Please retype: