WFMRetail
These query subjects are exclusive to the WFMRetail metadata model. This model is tailored for the retail industry and is built around a denormalized dimension (Location).
| Query Subject | Comments | Package | Folder/ Namespace | Metadata Model Reference | Database Reference |
|---|---|---|---|---|---|
| Location | Denormalized conformed Location hierarchy, up to the Store level. | WFMRetail | Location > Department | VIEW_WBI_LOCATION_DIMENSION.DEPARTMENT_NAME | |
| Location > Driver | VIEW_WBI_LOCATION_DIMENSION.DRIVER_NAME | ||||
| Location > Store | VIEW_WBI_LOCATION_DIMENSION.STORE_NAME | ||||
| Location > Keys > SKDGRP_ID | VIEW_WBI_LOCATION_DIMENSION.SKDGRP_ID | ||||
| Location > Keys > WBT_ID | VIEW_WBI_LOCATION_DIMENSION.WBT_ID | ||||
| Location > Keys > STORE_SKDGRP_ID | VIEW_WBI_LOCATION_DIMENSION.STORE_SKDGRP_ID | ||||
| Location > 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. | WFMRetail | Date > Date | DATE_VALUE.DTVAL_DATE | |
| Date > Year | DATE_VALUE.DTVAL_YEAR | ||||
| Date > Quarter | DATE_VALUE.DTVAL_QUARTER | ||||
| Date > Month | DATE_VALUE.DTVAL_MONTH | ||||
| Date > Day In Month | DATE_VALUE.DTVAL_DAY_IN_MTH | ||||
| Date > Day In Quarter | DATE_VALUE.DTVAL_DAY_IN_QTR | ||||
| Date > Day In Year | DATE_VALUE.DTVAL_DAY_IN_YR | ||||
| Date > Week In Month | DATE_VALUE.DTVAL_WK_IN_MTH | ||||
| Date > Week In Quarter | DATE_VALUE.DTVAL_WK_IN_QTR | ||||
| Date > Week In Year | DATE_VALUE.DTVAL_WK_IN_YR | ||||
| Date > Month In Quarter | DATE_VALUE.DTVAL_MTH_IN_QTR | ||||
| Date > Keys > DTVAL_ID | DATE_VALUE.DTVAL_ID | ||||
| Actuals | Fact table containing Actual metrics for example, sales data | WFMRetail | KPIs | Acutals > Actual Payroll | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'PAYROLL') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) |
| Actuals > Actual Returns | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'RETURNS') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) | ||||
| Actuals > Actual Revenue | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'REVENUE') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) | ||||
| Actuals > Actual Transactions | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'TRANSACTIONS') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) | ||||
| Actuals > Actual Units | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'UNIT') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) | ||||
| Actuals > Average Hourly Rate | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'AHR') then ( SO_RESULTS_DETAIL.RESDET_VOLUME ) else ( 0 ) | ||||
| Actuals > Keys > RESDET_DATE | SO_RESULTS_DETAIL.RESDET_DATE | ||||
| Actuals > Keys > SKDGRP_ID | SO_RESULTS_DETAIL.SKDGRP_ID | ||||
| Forecast | Fact table containing Forecasted metrics for example, sales data. | WFMRetail | KPIs | Forecast > Forecasted Payroll | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'PAYROLL') then ( FORECAST.CALC_FCAST_VALUE ) else (0) |
| Forecast > Forecasted Returns | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'RETURNS') then ( FORECAST.CALC_FCAST_VALUE ) else (0) | ||||
| Forecast > Forecasted Revenue | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'REVENUE') then ( FORECAST.CALC_FCAST_VALUE ) else (0) | ||||
| Forecast > Forecasted Transactions | if (SO_VOLUME_TYPE.VOLTYP_NAME = 'TRANSACTIONS') then ( FORECAST.CALC_FCAST_VALUE ) else (0) | ||||
| Forecast > Forecasted Units | if ( SO_VOLUME_TYPE.VOLTYP_NAME = 'UNIT' ) then ( FORECAST.CALC_FCAST_VALUE ) else ( 0 ) | ||||
| Forecast > Keys > DIST_ID | FORECAST.DIST_ID | ||||
| Forecast > Keys > FCAST_DATE | FORECAST.FCAST_DATE | ||||
| Forecast > Keys > FCAST_ID | FORECAST.FCAST_ID | ||||
| Forecast > Keys > SKDGRP_ID | FORECAST.SKDGRP_ID | ||||
| Forecast > Keys > VOLTYP_ID | FORECAST.VOLTYP_ID | ||||
| Schedule | Fact table containing Schedule metrics for published schedules. | WFMRetail | KPIs | Schedule > Scheduled Break Hours | EMPLOYEE_SCHED_DTL.BREAK_MINUTES / 60 |
| Schedule > Scheduled Overtime Hours | EMPLOYEE_SCHED_DTL.OT_MINUTES / 60 | ||||
| Schedule > Scheduled Paid Hours | EMPLOYEE_SCHED_DTL.PAID_MINUTES / 60 | ||||
| Schedule > Scheduled Paid Time Off Hours | EMPLOYEE_SCHED_DTL.PAID_TIME_OFF_MINUTES / 60 | ||||
| Schedule > Scheduled Work Hours | EMPLOYEE_SCHED_DTL.WORKING_MINUTES / 60 | ||||
| Schedule > Total Scheduled Hours | EMPLOYEE_SCHED_DTL.TOTAL_MINUTES / 60 | ||||
| Schedule > Keys > EMP_ID | EMPLOYEE_SCHED_DTL.EMP_ID | ||||
| Schedule > Keys > ESCHD_STATUS | EMPLOYEE_SCHED_DTL.ESCHD_STATUS | ||||
| Schedule > Keys > ESCHD_TYPE | EMPLOYEE_SCHED_DTL.ESCHD_TYPE | ||||
| Schedule > Keys > ESCHD_WORK_DATE | EMPLOYEE_SCHED_DTL.ESCHD_WORK_DATE | ||||
| Schedule > Keys > TCODE_RPT_ISPRDCTV | EMPLOYEE_SCHED_DTL.TCODE_RPT_ISPRDCTV | ||||
| Schedule > Keys > WBT_ID | EMPLOYEE_SCHED_DTL.WBT_ID | ||||
| Staffing | Fact table containing the Required Staffing metrics for published schedules. | WFMRetail | KPIs | Staffing > 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 |
| Staffing > Keys > INTREQ_DATE | SO_INTERVAL_REQ.INTREQ_DATE | ||||
| Staffing > Keys > SKDGRP_ID | SO_INTERVAL_REQ.SKDGRP_ID | ||||
| Work | Fact table containing Work metrics. | WFMRetail | KPIs | Work > Break Hours | WORK_DETAIL.BREAK_MINUTES / 60 |
| Work > Estimated Payroll Cost | WORK_DETAIL.AVG_HOURLY_RATE * WORK_DETAIL.TOTAL_MINUTES / 60 | ||||
| Work > Overtime Work Hours | WORK_DETAIL.OT_MINUTES / 60 | ||||
| Work > Paid Hours | WORK_DETAIL.PAID_MINUTES / 60 | ||||
| Work > Paid Time Off Hours | WORK_DETAIL.PAID_TIME_OFF_MINUTES / 60 | ||||
| Work > Productive Work Hours | WORK_DETAIL.PRDCTV_MINUTES / 60 | ||||
| Work > Total Work Hours | WORK_DETAIL.TOTAL_MINUTES / 60 | ||||
| Work > Keys > EMP_ID | WORK_SUMMARY.EMP_ID | ||||
| Work > Keys > WBT_ID | WORK_DETAIL.WBT_ID | ||||
| Work > Keys > WRKS_WORK_DATE | WORK_DETAIL.WRKS_WORK_DATE |