For Jim to define the solution and the data architecture, he needs access to the database system. He approaches Jean Trenary for getting access to the data warehouse and gets the access.
Once Jim gets the access to the system, he starts exploring every table related to sales available in the warehouse. This helps him to understand the nature of the data stored in the table and how it is linked to other tables in the warehouse.
He lists the dimensions and fact tables in the data warehouse which he would need to cater to all the sales related requirements.
Dimension tables are as follows:
|
Table Name |
Description |
|
DimCustomer |
This table stores all customer related data. The customer data is captured only for the online channel. It stores the demographic information, like FirstName, MiddleName, LastName, Birthdate, and Gender. The primary key in this table is CustomerKey. |
|
DimDate |
This table stores calendar data. It stores the date, month, year, week, fiscal year, fiscal quarter, fiscal semester, and so on. The primary key in this table is DateKey. |
|
DimDepartmentGroup |
This table contains the name of the department. The primary key in this table is DepartmentGroupKey. |
|
DimEmployee |
This table stores employee data. It stores the employees' demographic information, like FirstName, LastName, Birthdate, and Gender. Along with that, it also stores the employees' leave count, department name, territoryid, and so on. The employee is responsible for taking orders from the reseller. The primary key in this table is EmployeeKey. |
|
DimReseller |
Reseller data is captured in this table. The table contains details like Name, BusinessType, Address, Yearopened, numbers of employee they have, and so on. The primary key in this table is ResellerKey. |
|
DimGeography |
This table contains geographical information, like city, region, state province, and country. The primary key in this table is GeographyKey. It also has a foreign key, that is SalesTerritoryKey. |
|
DimProduct |
This table contains product related information, like name, color, size, class, style, model name, weight, list price, and dealer price. The primary key in this table is ProductKey. The foreign key in this table is ProductSubCategoryKey. |
|
DimProductCategory |
This table contains name of the product category. The primary key in this table is ProductCategoryKey. |
|
DimProductSubCategory |
This table contains a product's sub-category name. The primary key in this table is ProductSubCategoryKey. The foreign key in this table is ProductCategoryKey. |
|
DimSalesTerritory |
This table contains sales territory data, like region, country, and group. The primary key in this table is SalesTerritoryKey. |
Fact tables are as follows:
|
Table Name |
Description |
|
FactSalesQuota |
This table contains sales quota. The sales quota is available at month and employee level. It is available only for the Reseller Channel. The primary key in this table is SalesQuotaKey. The foreign keys in this table are EmployeeKey and DateKey. |
|
FactResellerSales |
This table stores transactional data. It stores the sales from the reseller channel. It stores information to link to different dimension table. Along with that, it also contains unit price, order quantity, sales amount, tax amount, freight, order date, ship date, and due date. It doesn’t have a primary key, but has the following foreign keys: ProductKey, OrderDateKey, DueDateKey, ShipDateKey, ResellerKey, EmployeeKey, and SalesTerritoryKey.
|
|
FactInternetSales |
This table stores transactional data. It stores the sales from the online channel. It stores necessary information to link to different dimension table. Along with that, it also contains unit price, order quantity, sales amount, tax amount, freight, order date, ship date, and due date. It doesn’t have a primary key, but has the following foreign keys: ProductKey, OrderDateKey, DueDateKey, ShipDateKey, and SalesTerritoryKey. |
As per the understanding of data structure, Jim prepares the following data architecture for sales application:
For Jim, the approach is to create a single application for extraction and transformation. This approach works well as it is easy to manage than creating multiple extraction applications to fetch the data. This approach has another advantage in such scenario where data is limited and the data refresh requirements are not frequent.
The need initially is for one application. The application should also have transactional detail. In this scenario, it is not necessary to have an aggregation layer and hence 2-layer data architecture is more suitable.
Jim now works on a good data model, and following best practices, he creates a data model based on star schema for better performance. The model is shown diagrammatically as follows:
