Mapping data from data sources to the database
The stores_demo demonstration database is the primary data source for the sales_demo database.
The following table shows the relationship between data warehousing business terms and the data sources. It also shows the data source for each column and table of the sales_demo database.
| Business Term | Data Source | Table.Column Name |
|---|---|---|
| Sales Fact Table: | ||
| product code | sales.product_code | |
| customer code | sales.customer_code | |
| district code | sales.district_code | |
| time code | sales.time_code | |
| revenue | stores_demo:items.total_price | sales.revenue |
| units sold | stores_demo:items.quantity | sales.units_sold |
| cost | costs.lst (per unit) | sales.cost |
| net profit | calculated: revenue minus cost | sales.net_profit |
| Product Dimension Table: | ||
| product | stores_demo:catalog.catalog_num | product.product_code |
| product name | stores_demo:stock.manu_code and stores_demo:stock.description | product.product_name |
| product line | stores_demo:orders.stock_num | product.product_line_code |
| product line name | stores_demo:stock.description | product.product_line_name |
| vendor | stores_demo:orders.manu_code | product.vendor_code |
| vendor name | stores_demo:manufact.manu_name | product.vendor_name |
| Customer Dimension Table: | ||
| customer | stores_demo:orders.customer_num | customer.customer_code |
| customer name | stores_demo:customer.fname plus stores_demo:customer.lname | customer.customer_name |
| company | stores_demo:customer.company | customer.company_name |
| Geography Dimension Table: | ||
| district code | generated | geography.district_code |
| district | stores_demo:customer.city | geography.district_name |
| state | stores_demo:customer.state | geography.state_code |
| state name | stores_demo.state.sname | geography.state_name |
| region | derived: If state = "CA" THEN region = 1, ELSE region = 2 | geography.region |
| Time Dimension Table: | ||
| time code | generated | time.time_code |
| order date | stores_demo:orders.order_date | time.order_date |
| month | derived from order date generated | time.month_name time.month.code |
| quarter | derived from order date generated | time.quarter_name time.quarter_code |
| year | derived from order date | time.year |
Several files with a .unl suffix contain the data that is loaded into the sales_demo database. The files that contain the SQL statements that create and load the database have a .sql suffix.
If your database server runs on UNIX™, you can access the *.sql and *.unl files from the directory $ONEDB_HOME/demo/dbaccess.
If your database server runs on Windows™, you can access the *.sql and *.unl files from the directory %ONEDB_HOME%\demo\dbaccess.