Sample Outdoors organization and schemas

The Outdoors Companies

Five of the six companies are regionally-based companies, whose sales region is identified in the company name. These five companies sell the complete line of outdoor products including:

• Camping Equipment
• Golf Equipment
• Mountaineering Equipment
• Personal Accessories
• Outdoor Protection

The Accessory Company

GO Accessories Inc. operates differently than the five outdoor companies and stands apart in several other areas:

• GO Accessories has its own collection of accessories, differentiated from the outdoor companies by brand, name, price, color and size.
• GO Accessories sells from a single branch to all regions and retailers.
• GO Accessories functions both as an operating company based in Geneva, and as owner or part owner of three GO subsidiaries in Europe. This offers a complex structure that is useful in samples that show office-of-finance type reporting.

Unbalanced hierarchy

The ownership by GO Accessories of the European companies supports applications designed for office-of-finance reporting. Samples applications can include inter-company eliminations and consolidation up to the highest level node - GO Consolidated. In this structure, aggregations of the European organizations occur through GO Accessories.

The resulting unbalanced hierarchy has one additional level, as shown in the following table. At level 3 of the organization structure, GO Central Europe (GOCEU) is at the same level as the operations department for GO Americas (GOAMXOP).

Balanced hierarchy - view

You can use views to balance the organization hierarchy by side-stepping the relationship between GO Accessories and the European companies. Use a view when you want to create samples that show metrics and aggregations that are distinct for each corporate entity.

Gosales VIEW_BALANCED_ORG

SELECT TOP 100 PERCENT ORGANIZATION_1.ORGANIZATION_PARENT AS ORG_LEVEL1_CODE, 
       gosaleshr.ORGANIZATION.ORGANIZATION_PARENT AS ORG_LEVEL2_CODE, 
       gosaleshr.ORGANIZATION.ORGANIZATION_CODE
FROM   gosaleshr.ORGANIZATION ORGANIZATION_1 RIGHT OUTER JOIN
       gosaleshr.ORGANIZATION ON ORGANIZATION_1.ORGANIZATION_CODE = 
       gosaleshr.ORGANIZATION.ORGANIZATION_PARENT
WHERE (gosaleshr.ORGANIZATION.ORGANIZATION_CODE BETWEEN N'006' AND
       N'8820')
ORDER BY ORGANIZATION_1.ORGANIZATION_PARENT,
         gosaleshr.ORGANIZATION.ORGANIZATION_PARENT DESC,  
         gosaleshr.ORGANIZATION.ORGANIZATION_CODE

gosalesdw.VIEW_BALANCED_ORG

SELECT gosalesdw.GO_ORG_DIM.ORGANIZATION_KEY, GO_ORG_DIM_1.ORGANIZATION_PARENT AS
				ORG_LEVEL1_CODE, GO_ORG_NAME_LOOKUP_1.ORGANIZATION_NAME_EN AS ORG_LEVEL1_NAME,
       gosalesdw.GO_ORG_DIM.ORGANIZATION_PARENT AS ORG_LEVEL2_CODE, 
       GO_ORG_NAME_LOOKUP_2.ORGANIZATION_NAME_EN AS ORG_LEVEL2_NAME,
       gosalesdw.GO_ORG_DIM.ORGANIZATION_CODE AS ORG_CODE, 
       gosalesdw.GO_ORG_NAME_LOOKUP.ORGANIZATION_NAME_EN AS ORG_NAME
FROM gosalesdw.GO_ORG_NAME_LOOKUP GO_ORG_NAME_LOOKUP_2 INNER JOIN
     gosalesdw.GO_ORG_DIM INNER JOIN
     gosalesdw.GO_ORG_NAME_LOOKUP ON 
     gosalesdw.GO_ORG_DIM.ORGANIZATION_CODE = 
			gosalesdw.GO_ORG_NAME_LOOKUP.ORGANIZATION_CODE ON 
     GO_ORG_NAME_LOOKUP_2.ORGANIZATION_CODE = 
     gosalesdw.GO_ORG_DIM.ORGANIZATION_PARENT INNER JOIN
     gosalesdw.GO_ORG_NAME_LOOKUP GO_ORG_NAME_LOOKUP_1 INNER JOIN
     gosalesdw.GO_ORG_DIM GO_ORG_DIM_1 ON GO_ORG_NAME_LOOKUP_1.ORGANIZATION_CODE = 
			GO_ORG_DIM_1.ORGANIZATION_PARENT ON 
     gosalesdw.GO_ORG_DIM.ORGANIZATION_PARENT =  
     GO_ORG_DIM_1.ORGANIZATION_CODE
WHERE (gosalesdw.GO_ORG_DIM.ORGANIZATION_CODE BETWEEN N'006' AND N'8820')

GO Departments

The Sample Outdoors companies are divided into operating and corporate departments. Each organization has the same structure.

Applications for financial and planning data use the department structure in the sample budgets and forecasts.

Sales branches under operations, and HR departments under corporate, are the richest sources of data for querying and analysis.

GO Operations

Metrics such as revenue, quantities, and cost of goods, aggregate through levels from sales rep to branch to operations under each corporate banner.

Data from other departments ties into the sales data. For example, the marketing department implements sales campaigns.

Human Resources

Human resources data exists for metrics such as compensation, benefits, training and surveys. Employees are paid hourly, or receive salaries. Compensation may including commission and bonuses. Vacation and sick days are part of the benefits package.

Each Sample Outdoors organization is staffed by a variety of employees including personnel for marketing, finance, sales, as well as those in other departments. Employee history exists for employees that change positions or have new managers.

In the warehouse data (GOSALESDW), the employee history is contained in a slowly changing dimension, in which an employee code may be a repeating value and the employee key is unique.

In the transactional data (GOSALES), employee history requires filtering on a date range in order to assign transaction records to employees in the appropriate time period. Filtering also prohibits double-counting that can occur with repeating employee codes. For example, to capture an accurate history of an employee at the time of training, the date range is qualified.

WHERE (TRAINING_DETAILS.TRAINING_DATE BETWEEN
EMPLOYEE_HISTORY.RECORD_START_DATE AND EMPLOYEE_HISTORY.RECORD_END_DATE)

Alternatively, you can select active records by filtering out employee history, which returns only the current manager or job position of the employee.

WHERE (EMPLOYEE_HISTORY.RECORD_END_DATE IS NULL)