WFMRetail
These query subjects are unique to the WFMRetail package. This model is tailored
for the retail industry and is built around a denormalized dimension
(Location).
Location
Denormalized conformed location hierarchy, up to the store level.
| Metadata model reference | Database reference |
|---|---|
| Department | VIEW_WBI_LOCATION_DIMENSION.DEPARTMENT_NAME |
| Driver | VIEW_WBI_LOCATION_DIMENSION.DRIVER_NAME |
| Store | VIEW_WBI_LOCATION_DIMENSION.STORE_NAME |
| Keys > SKDGRP_ID | VIEW_WBI_LOCATION_DIMENSION.SKDGRP_ID |
| Keys > WBT_ID | VIEW_WBI_LOCATION_DIMENSION.WBT_ID |
| Keys > STORE_SKDGRP_ID | VIEW_WBI_LOCATION_DIMENSION.STORE_SKDGRP_ID |
| Keys > STORE_WBT_ID | VIEW_WBI_LOCATION_DIMENSION.STORE_WBT_ID |
Date
Denormalized conformed date dimension. Every calendar date 2 years in the past, and 10 years in the future.
| Metadata model reference | Database reference |
|---|---|
| Date | DATE_VALUE.DTVAL_DATE |
| Year | DATE_VALUE.DTVAL_YEAR |
| Quarter | DATE_VALUE.DTVAL_QUARTER |
| Month | DATE_VALUE.DTVAL_MONTH |
| Day In Month | DATE_VALUE.DTVAL_DAY_IN_MTH |
| Day In Quarter | DATE_VALUE.DTVAL_DAY_IN_QTR |
| Day In Year | DATE_VALUE.DTVAL_DAY_IN_YR |
| Week In Month | DATE_VALUE.DTVAL_WK_IN_MTH |
| Week In Quarter | DATE_VALUE.DTVAL_WK_IN_QTR |
| Week In Year | DATE_VALUE.DTVAL_WK_IN_YR |
| Month In Quarter | DATE_VALUE.DTVAL_MTH_IN_QTR |
| Keys > DTVAL_ID | DATE_VALUE.DTVAL_ID |
Actuals
Under . Fact table containing actual metrics for example, sales data
| Metadata model reference | Database reference |
|---|---|
| Actual Payroll | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'PAYROLL') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) |
| Actual Returns | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'RETURNS') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) |
| Actual Revenue | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'REVENUE') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) |
| Actual Transactions | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'TRANSACTIONS') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) |
| Actual Units | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'UNIT') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) |
| Average Hourly Rate | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'AHR') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) |
| Keys > RESDET_DATE | SO_RESULTS_DETAIL.RESDET_DATE |
| Keys > SKDGRP_ID | SO_RESULTS_DETAIL.SKDGRP_ID |
Forecast
Under . Fact table containing forecasted metrics for example, sales data.
| Metadata model reference | Database reference |
|---|---|
| Forecasted Payroll | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'PAYROLL') then ( FORECAST.CALC_FCAST_VALUE ) else (0) |
| Forecasted Returns | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'RETURNS') then ( FORECAST.CALC_FCAST_VALUE ) else (0) |
| Forecasted Revenue | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'REVENUE') then ( FORECAST.CALC_FCAST_VALUE ) else (0) |
| Forecasted Transactions | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'TRANSACTIONS') then ( FORECAST.CALC_FCAST_VALUE ) else (0) |
| Forecasted Units | if ( SO_VOLUME_TYPE.VOLTYP_NAME = 'UNIT' ) then ( FORECAST.CALC_FCAST_VALUE ) else ( 0 ) |
| Keys > DIST_ID | FORECAST.DIST_ID |
| Keys > FCAST_DATE | FORECAST.FCAST_DATE |
| Keys > FCAST_ID | FORECAST.FCAST_ID |
| Keys > SKDGRP_ID | FORECAST.SKDGRP_ID |
| Keys > VOLTYP_ID | FORECAST.VOLTYP_ID |
Schedule
Under . Fact table containing Schedule metrics for published schedules.
| Metadata model reference | Database reference |
|---|---|
| Scheduled Break Hours | EMPLOYEE_SCHED_DTL.BREAK_MINUTES / 60 |
| Scheduled Overtime Hours | EMPLOYEE_SCHED_DTL.OT_MINUTES / 60 |
| Scheduled Paid Hours | EMPLOYEE_SCHED_DTL.PAID_MINUTES / 60 |
| Scheduled Paid Time Off Hours | EMPLOYEE_SCHED_DTL.PAID_TIME_OFF_MINUTES / 60 |
| Scheduled Work Hours | EMPLOYEE_SCHED_DTL.WORKING_MINUTES / 60 |
| Total Scheduled Hours | EMPLOYEE_SCHED_DTL.TOTAL_MINUTES / 60 |
| Keys > EMP_ID | EMPLOYEE_SCHED_DTL.EMP_ID |
| Keys > ESCHD_STATUS | EMPLOYEE_SCHED_DTL.ESCHD_STATUS |
| Keys > ESCHD_TYPE | EMPLOYEE_SCHED_DTL.ESCHD_TYPE |
| Keys > ESCHD_WORK_DATE | EMPLOYEE_SCHED_DTL.ESCHD_WORK_DATE |
| Keys > TCODE_RPT_ISPRDCTV | EMPLOYEE_SCHED_DTL.TCODE_RPT_ISPRDCTV |
| Keys > WBT_ID | EMPLOYEE_SCHED_DTL.WBT_ID |
Staffing
Under . Fact table containing the required staffing metrics for published schedules.
| Metadata model reference | Database reference |
|---|---|
| Forecasted Labor Hours | CASE WHEN SO_INTERVAL_REQ.INVTYP_NAME = '15' then ( SO_INTERVAL_REQ.INTREQ_TOTAL_REQ / 4 ) WHEN SO_INTERVAL_REQ.INVTYP_NAME = '30' then ( SO_INTERVAL_REQ.INTREQ_TOTAL_REQ / 2 ) WHEN SO_INTERVAL_REQ.INVTYP_NAME = '60' then ( SO_INTERVAL_REQ.INTREQ_TOTAL_REQ ) else ( SO_INTERVAL_REQ.INTREQ_TOTAL_REQ * 24 ) END |
| Keys > INTREQ_DATE | SO_INTERVAL_REQ.INTREQ_DATE |
| Keys > SKDGRP_ID | SO_INTERVAL_REQ.SKDGRP_ID |
Work
Under . Fact table containing work metrics.
| Metadata model reference | Database reference |
|---|---|
| Break Hours | WORK_DETAIL.BREAK_MINUTES / 60 |
| Estimated Payroll Cost | WORK_DETAIL.AVG_HOURLY_RATE * WORK_DETAIL.TOTAL_MINUTES / 60 |
| Overtime Work Hours | WORK_DETAIL.OT_MINUTES / 60 |
| Paid Hours | WORK_DETAIL.PAID_MINUTES / 60 |
| Paid Time Off Hours | WORK_DETAIL.PAID_TIME_OFF_MINUTES / 60 |
| Productive Work Hours | WORK_DETAIL.PRDCTV_MINUTES / 60 |
| Total Work Hours | WORK_DETAIL.TOTAL_MINUTES / 60 |
| Keys > EMP_ID | WORK_SUMMARY.EMP_ID |
| Keys > WBT_ID | WORK_DETAIL.WBT_ID |
| Keys > WRKS_WORK_DATE | WORK_DETAIL.WRKS_WORK_DATE |