Foodmart 2005 Sample Project
Sample database is based on the workings of one fictional organization, a multinational chain of grocery stores: FoodMart. We use a variation of the same sample database that shipped with SQL Server 2000, FoodMart 2000. You can find the new FoodMart 2005 sample database at http://www.e-tservice.com/downloads.html
FoodMart sells food and other items in stores in the United States, Canada, and Mexico. The company has a number of warehouses from which it distributes items to its stores. The FoodMart IT department collects data about its operations in a SQL Server database and uses Analysis Services to analyze data.
Customer Data
Information about customers - their names and addresses - is kept in the Customer table in a relational database. In the multidimensional database, you will find this information in the Customers dimension. The Customers dimension with a Customers hierarchy enables a user to browse by geographical categories. The attribute hierarchies Education, Gender, Marital Status, Occupation, and Yearly Income provide additional information about the customers.
Store Data
Information about individual Food Mart stores is collected in the Stores table and the corresponding Store dimension. It includes store location, name, manager, size (in square feet), and store type, such as supermarket or small grocery. The Store dimension has a user-defined hierarchy, Stores, that you can use to browse by geographical categories, and attribute hierarchies, such as Store Sqft, Store Type, Store Manager.
Product and Warehouse Data
FoodMart products are first delivered to one of the warehouses, from there to the stores, and finally, sold to the customers. Information about the products is collected in two tables: product class and product. These tables form the basis for the Product dimension, which has a single user-defined hierarchy, Product, and two attribute hierarchies SRP and SKU.
Warehouse data is kept in the warehouse table, which is the basis for a Warehouse dimension that has a single user-defined hierarchy: Warehouses.
Time Data
You can use the FoodMart 2005 data warehouse to analyze the business operations of the FoodMart organization by periods of time. All the information related to time and dates is stored in the time-by-day table and the corresponding Time dimension. The Time dimension has two user-defined hierarchies: Time, which you can use to browse by calendar time, and Time By Week, which you can use to browse data by weeks.
Account Data
To analyze the financial state of the FoodMart 2005 enterprise, you can structure your finances based on accounts, such as assets, liabilities, and so on. All the information related to accounts is stored in the account table and the corresponding Account dimension. The Account dimension has a single parent-child hierarchy, Accounts, and an Account Type attribute hierarchy, both of which provide information about types of accounts.
Currency Data
A multinational corporation, such as the FoodMart enterprise, should be able to track operations in different currencies. To support multiple currencies, the FoodMart 2005 data warehouse contains a currency table and corresponding Currency dimension.
Employee Data
Information about all employees of the FoodMart organization is stored in three tables: employee, position, and department. These form the basis for the Employee and Department dimensions.
To analyze aspects of FoodMart's performance, we are going to work with this data in four cubes, already created for you in the multidimensional database: Warehouse and Sales, HR, Budget, and Sales and Employees.
The Warehouse and Sales Cube
The Warehouse and Sales cube contains four measure groups: Warehouse, Sales, Rates, and Warehouse Inventory. The Warehouse and Sales measure groups have two partitions each: the Rates and Warehouse Inventory measure groups, based on a single partition.
We will use the Warehouse and Sales cube to get information, such as which products were sold to which customers in which stores. We will also get the total number of items sold and the dollar amount of gross sales. In addition, we can find the gross sales for an individual store or for all the stores in a region. We can also use our data to answer questions. For example, how many units of what product were shipped to which store? How many units were ordered? How much were expenses for a specific warehouse for the past quarter? How many items remain on warehouse shelves at the end of the year?
The HR Cube
The HR cube is based on the salary-by-day table. It includes dimensions such as Department, Time, and Employee.
We use the HR cube to answer questions, such as, what sort of educational levels do our managers have? What is the average educational level of our employees, broken out by country?
The Budget Cube
The Budget cube is based on the expense-fact table. It includes dimensions such as Currency, Account, and Promotion. This cube includes the linked measure group Sales, which is linked from the Warehouse and Sales cube. It gives you the ability to perform budgeting analysis against current sales.
The Sales and Employees Cube
The Sales and Employees cube is based on the sales_fact-1997 and sales_fact-1998 tables. We have introduced this cube to show you how to build dynamic security for sales data of individual employees of the FoodMart organization.
back