MORE than a project or initiative - we are creating a data driven company!
Our goal is to democratize data and help you to make better decisions and achieve superior results.
Scroll down to continue reading...
Our goal is to democratize data and help you to make better decisions and achieve superior results.
GLN's Data & Analytics team has built many datasets which are validated and ready to use. SAP Tables in the Trusted catalogs are viewable by all Databricks users, but require sophisticated SQL queries to join and filter them and translate the SAP technical names into meaningful column names. Refined datasets built by the GLN Data & Analytics team accomplish that task for you, and can be found in the "corporate_glndatawarehouse_refined_prd" catalog. These tables can be made available to you by request from the Data Governance team. Here we give you a brief synopses of what you can find in each dataset, so you can request access to it by the table name.
The datasets listed below are found in the Corporate_GLNDataWarehouse_refined_prd catalog. Each table is identified by a schema name, a dot, and the table name. Contact the Data Governance team to request access to any of these tables.
The "CDPS" report from APO lists the block schedules for each plant, showing the total capacity of each block and capacity available for orders. The report is run by Robotic Process Automation (RPA) each morning and the results loaded to this table as a daily snapshot.
Another table generated by an RPA which runs the SAP COOIS transaction, which brings production order status. Daily snapshots are kept.
An RPA runs the SAP-BI report which brings raw material (scrap) inventory and loads it to this table. Daily snapshots are kept.
An RPA runs the SAP-BI report of production, with a daily snapshot of it stored here.
An RPA runs the SAP-BI Corporate Sales Report and stores the inventory in this table as a daily snapshot.
Each time a mill scheduler changes the block schedule in APO, they record the reason for the change, to provide insight into how often block schedules are changed and why.
Daily snapshots of the block schedule are captured in the table global_apo_gln.tb_rolling_dates, in the customer_trusted_prd catalog. This table takes that data and builds a row for each historical block to show when the block first appeared in the schedule, and the changes made in the block from one day to the next.
Daily snapshot of stock balances located at ports for export.
Historical pegging log data uploaded from the robinhood data warehouse. The pegging data from SAP-APO shows the source of supply pegged to each requirement. A "requirement" can be a sales order, a VMI reservation, or dependent demand for a cutting order, for example. This is a log which contains a timestamped entry of each pegging relationship each time that relationship changed, as observed by the robinhood data warehouse.
The robinhood data warehouse maintains a table of sales orders, which also contains VMI reservations only found in APO. The pegging data is used to identify the quantity of the order pegged to Stock, QM Lots, Production Orders, Planned Orders, or stock transfers. The column "req_type" identifies whether a row is a sales order or a VMI reservation. Note a daily snapshot of the sales orders from this table is then stored in the table global_robinhood_gln.tb_gln_rh_sales_orders_snapshots, which allows time series analysis of sales orders.
While the tb_pegging_log_rh table brings you the historical log of all changes to pegging relationships over time, it is not useful for knowing what is pegged to open requirements TODAY. This table brings only the current pegging relationship observed this morning. While the tb_rh_sales_orders table summarizes the pegged sources of supply by sales order line item, the pegging log provides the detailed breakdown of each source of supply by schedule line. If the order is on delivery, this table will have each individual delivery and its pegged source of supply, along with a reference to the sales order.
Detailed freight payments made to carriers by shipment and delivery. There is a separate row for each component of the freight price, enabling detailed analysis of exactly what we paid for freight and why. This is a detail table that must be joined to the tb_bi_gln_internal_freight_summary table to get the header data associated with the shipment.
This brings the header data for each shipment and delivery line item. It must be joined to the tb_bi_gln_internal_freight_detailed table to see the itemized freight amounts paid for the shipment.
Shipment details at the Shipment, Delivery, and Delivery item number level. Delivery line item data is enriched with the sales orders line item, shipment, and master data. For a truck shipment, the table includes actual time the shipment arrived at the customer site. For rail shipments, the delivery time is calculated as the time we sent the EDI transaction to the rail carrier to pick up the fully loaded car + transit days.
Shipments aggregated to the shipment number level. It is a summary of the tb_bi_gln_shipment_delivery_window that contains shipments only, unshipped deliveries are excluded.
For each rolling mill, this defines the frequency in days with which each block resource (AKA "rolling family") is produced.
This customer master joins each Sold-to to each of it's associated Ship-to business partners. The Inside and Outside Sales Representatives, Sales Manager, and Sales Director are aslo included, for both the sold-to and the ship-to. Note there is a sold_to_deletion_flag and a ship_to_deletion flag to indicate whether each is currently marked for deletion. To exclude deleted master data, include a filter to select only rows where the deletion flag IS NULL.
This simple table provides the inside and outside sales representative, sales manager, sales_director, and other support personnel associated with a customer number. Note this table does not differentiate between sold-to and ship-tos. To understand whether a given row is for a sold-to versus ship-to, you can join it to the tb_md_gln_customer_master.
This table brings the material characteristics for materials which are not products (MRO or production materials), by plant and SKU, as defined in the Gerdau Materials Engineering (GME) system.
This is a consolidated view of the material master in SAP. It is by plant and SKU, and includes all the material characteristic values. It contains only the materials for North American plants.
This brings the material characteristics at the SKU level (Class 300 screen in SAP). The tb_md_gln_material_master contains these also, but they duplicated for each plant in that table.
This brings the block resource and planning group by plant and SKU. The block resource, also known as "rolling family", can vary by plant within a given SKU.
Bill-of-Material (BOM) and Routing data for products. Here you can see all the components which make the product, including co-products. It also identifies the work center on which it is produced and for rolled products, the tons per hour.
This is a simple cross reference of QMOS plant name to SAP Plant code. It is useful for joining QMOS tables to SAP tables.
Vendor master data for GLN.
Identifies all global Gerdau employees by name, clock number, email address, and User ID. Customer Master Business Partners will reference an employee by Clock Number, for example. This table is used to translate that clock number to a name in that table.
A view which brings metallics (scrap) tickets.
A view which brings details for metallics (scrap) tickets.
This replaces the old SAP-BI global report known as the "Price Management Report". It brings invoicing data and has been proven to be more accurate than the old SAP-BI report.
Production order confirmations, showing the tons produced by batch number for each production order.
Production costs by plant and cost element and month/year.
Detailed information from QMOS about heats produced in the melt shop.
Actual production, PEX production, and Rolling Forecast (RF) production.
Detailed information from QMOS about rolling mill production.
Provides revenue and costs by sales order line item. Using this table, you can determine the calculated profitability of each sales order line item.
An aggregated snapshot of the sales order backlog. This replaces the old SAP-BI report known as the "Daily Position Report". This table is the basis for the "Daily Dashboard" Power BI report. Daily snapshots are kept, allowing time series analysis of the backlog. The key figures place the backlog into monthly buckets based on the planned goods issue dates on the sales order schedule lines, providing insight as to when the backlog is expected to ship.
This contains the sales order line items used to build the tb_bi_gln_backlog_snapshots table. It is replaced each day, and is useful for analyzing the order details that were aggregated for the latest snapshot calcluated in the tb_bi_gln_backlog_snapshots table.
For all open orders and VMI reservations, this table calculates two columns designed to indicate the quality of the order's confirmation. The calculated columns are "actual_supply_ordinal" and "requested_supply_ordinal". The term "ordinal" indicates the upcoming rolling of the product: 1st rolling, 2nd rolling, or 3rd rolling. The "actual_supply_ordinal" returns the ordinal based on the order's current pegging. If the order is pegged to stock, the "ordinal" is 0 (zero). If it pegged to a stock transer, the ordinal is "T". If it is pegged to the 1st rolling, it is a "1", and if that rolling is closed, it gets a "1C". The requested_supply_ordinal uses the requested date of the order to calculate whether the order should have rolled in the 1st, 2nd, or 3rd rolling. If the requested date is before the 1st rolling, it gets a 0 (zero).
This is a snapshot of only the open orders pegged to stock, taken from the global_robinhood_gln.tb_gln_rh_sales_orders table. It is used to determine how long orders have been holding stock. This data is used by the "Nudge" project, which seeks to prompt customers to take delivery of orders holding stock for too long.
Provides visibility into order fill rate performance to trigger tactical plans to deliver on our promise. Key Figures provided are Overall fill rate percentage by period (daily, weekly, monthly), order fill rate by customer, plant, segment, and product. Can also show fill rate trends over time. There is a row for each shipment made against the sales order.