Ivan Paniagua Monroy {Blog about BI, SQL, Azure & Microsoft.NET Technologies}

Archivo para agosto, 2012

ADVENTURE WORKS DATA BASE/ ETL / DATA QUALITY

BUSINESS SCENARIO

Adventure Works uses various software applications to manage different aspects of the business, and each application has its own data store. Specifically:

  • Internet sales are processed through an e-commerce web application. This data source contains data about customers and the orders that they have placed through the e-commerce web application
  • Reseller sales are processed by sales representatives, who use a reseller sales application. This data source contains data about resellers and the orders that they have placed through Adventure Works reseller account managers. Details of the sales employees themselves are stored in a separate human resources system.
  • Reseller payments are processed by an accounting application. Details of these payments are exported to comma-delimited files.
  • Products are managed in a product catalog and inventory system. This database contains data about products that Adventure Works sells, and that products are organized into categories and subcategories.
  • Some business partners, such as the marketing agency that Adventure Works uses to conduct marketing campaigns, provide data to Adventure Works through cloud-based data stores.

This distribution of data has made it difficult for business users to answer key questions about the overall performance of the business.

Additional requirements include:

  • The marketing department has requested demographic data about population by gender in the international sales territories in which Adventure Works operates.
  • The data stewards have noticed some data quality issues in the customer data, and requested that you provide a way for them to cleanse data so that the data base is based on consistent and reliable data. The data stewards have provided you with an Excel workbook containing some examples of the issues found in the data.
  • The data stewards are also concerned that customer data may include duplicate entries for the same customer that they would like to resolve.
  • The solution you are building for Adventure Works Cycles consumes product data from an application database. However, product data is created and updated in various systems throughout the enterprise, and you need to ensure that there is a single, consistent definition for each product.

THE CHALLENGE

Your challenge is to study the business scenario and requirements, and then design an solution to help Adventure Works build an enterprise data base