SAP dbt Package
This dbt package transforms data from Fivetran's SAP connector into analytics-ready tables.
Resources
- Number of materialized models¹: 148
- Connector documentation
- dbt package documentation
What does this dbt package do?
This package enables you to recreate common SAP extractor reports, provide a star schema for analyzing sales and purchase orders, and generate compatibility views. It creates enriched models with metrics focused on general ledger balances, sales and procurement transactions, and master data attributes.
Note: This package produces modeled tables that leverage SAP data from Fivetran's SAP connectors, like LDP SAP Netweaver, HVA SAP or SAP ERP on HANA.
Output schema
Final output tables are generated in the following target schema:
<your_database>.<connector/schema_name>_sap
Final output tables
By default, this package materializes the following final tables:
Extractor Reports
| Table | Description |
|---|---|
| sap__0comp_code_attr | Extracts company code master data with currency, fiscal year variant, chart of accounts, country, and credit control area attributes from the t001 source to support financial organizational reporting and multi-company analysis. Example Analytics Questions:
|
| sap__0comp_code_text | Provides multi-language company code names and descriptions from the t001 source with language keys to enable localized financial reporting and support international company hierarchies. Example Analytics Questions:
|
| sap__0company_text | Extracts company entity names from the t880 source providing company-level text descriptions to support corporate structure analysis and consolidated reporting hierarchies. Example Analytics Questions:
|
| sap__0customer_attr | Consolidates customer master data from the kna1 source with demographic information (name, address, city, postal code, country, region), classification attributes (industry, account group, customer class), contact details (phone, tax numbers), and vendor linkages to support customer segmentation and CRM analytics. Example Analytics Questions:
|
| sap__0employee_attr | Extracts employee work relationship data from personnel administration with organizational assignments (company code, plant, personnel area, cost center, organizational unit), position details (job, position, employee group/subgroup), compensation information (pay scale type/area/group/level, annual salary currency, employment percentage), and validity periods to analyze workforce structure and payroll planning. Example Analytics Questions:
|
| sap__0fi_gl_10 | Aggregates GL transaction figures from the leading ledger with period-level debit/credit amounts, accumulated balances, and turnover by account, company code, cost center, profit center, segment, and other organizational dimensions to support P&L analysis, balance sheet reporting, and variance analysis. Example Analytics Questions:
|
| sap__0fi_gl_14 | Streams detailed GL line items from the leading ledger with document-level details (document number, line number, posting date, document date), multi-currency amounts (transaction, local, global currencies), debit/credit indicators, posting keys, account assignments (GL account, cost center, profit center, order), and clearance information to enable granular financial transaction analysis and audit trails. Example Analytics Questions:
|
| sap__0gl_account_attr | Extracts GL account master data from the ska1 source with chart of accounts assignments, account groups, P&L statement accounts, and balance sheet classifications to support account hierarchy reporting and financial statement mapping. Example Analytics Questions:
|
| sap__0material_attr | Consolidates material master attributes from the mara source with material type, base unit of measure, material group, gross/net weights, volume, size dimensions, and classification indicators to support inventory management, procurement planning, and product analytics. Example Analytics Questions:
|
| sap__0vendor_attr | Extracts vendor master attributes from the lfa1 source with vendor names, addresses (street, city, postal code, country, region), contact details (telephone, fax), account group classifications, industry keys, payment terms, and tax information to support supplier management, procurement analytics, and vendor performance tracking. Example Analytics Questions:
|
| sap__0vendor_text | Provides multi-language vendor names and text descriptions from the lfa1 source with language keys to enable localized vendor reporting and support international supplier communications. Example Analytics Questions:
|
Sales and Procurement
| Table | Description |
|---|---|
| sap__dim_customer | Dimensional customer table with English-labeled fields including customer number, country, customer name, city, and surrogate key from the kna1 source to enable customer-centric dimensional reporting and simplified business user analytics. Example Analytics Questions:
|
| sap__dim_material | Dimensional material table with English-labeled fields combining material master data (mara), material descriptions (makt), and material type attributes (t134/t134t) including material number, description, type, base unit of measure, gross weight, and surrogate key to enable product-centric dimensional analysis. Example Analytics Questions:
|
| sap__dim_plant | Dimensional plant/branch table with English-labeled fields from the t001w source including plant identifier, plant name, country key, and related attributes to enable plant-centric operational and logistics reporting. Example Analytics Questions:
|
| sap__dim_purchasing_order | Dimensional purchasing document header table with English-labeled fields combining purchase order attributes (ekko) and reference data (dd07l/dd07t/t024/t161) including document category, type, status, purchasing group, payment terms, and cancellation reason to enable procurement header-level dimensional analysis. Example Analytics Questions:
|
| sap__dim_purchasing_organization | Dimensional purchasing organization table with English-labeled fields from t024e and t024et sources including organization identifier, description, and company code assignment to enable purchasing organization-level procurement analytics and reporting. Example Analytics Questions:
|
| sap__dim_rejection_reason | Dimensional rejection reason table with English-labeled fields from tvag and tvagt sources including reason code and description to enable rejection reason analysis for sales and procurement documents. Example Analytics Questions:
|
| sap__dim_vendor | Dimensional vendor table with English-labeled fields from the lfa1 source including vendor identifier, country, vendor names, city, sort field, authorization group, industry classification, and account group to enable supplier-centric dimensional reporting and vendor performance analytics. Example Analytics Questions:
|
| sap__fact_purchasing_order | Fact table consolidating purchasing order line-level metrics from ekbe, eket, ekko, ekpo, and t001w sources with quantity metrics (ordered, open, delivered, late, canceled), amount metrics (order value, open amount, delivered amount, late amount, canceled amount, invoiced amount), delivery performance (delivery late days, late lead days, delivery completed), and item counts to measure procurement efficiency, supplier performance, and purchase order fulfillment. Example Analytics Questions:
|
| sap__fact_sales_order | Fact table consolidating sales order line-level data from vbak, vbap, vbuk, and vbup sources with document attributes (document number, item, category, type, order reason, delivery block), organizational dimensions (sales organization, distribution channel, division, plant, customer), product details (material, material group, product hierarchy), quantity and pricing metrics (order quantity, net value, net price, gross weight), dates (document date, requested delivery date, created date), and status indicators (delivery status, overall status, item delivery status) to measure sales performance and order fulfillment. Example Analytics Questions:
|
Compatibility Views
Views that replicate the structure and data of native SAP tables, enabling seamless migration and integration for existing SAP-based processes and reports.
| Table | Description |
|---|---|
| bsad | Customer Line Items Cleared/Archived (BSAD) compatibility view - Represents cleared customer items in the classic BSAD format via a compatibility view; depending on configuration it may include archived content. |
| bsid | Customer Line Items (BSID) compatibility view (S/4HANA) - Represents open customer items in the classic BSID format, provided via a compatibility view (logically equivalent to the classic FI line item/index content). |
| coep | Controlling Object Line Items (COEP) compatibility view - Provides detailed line item data for controlling documents, combining original COEP table data with derived data from ACDOCA. |
| cosp | Cost Object Line Items Summary (COSP) compatibility view - Provides aggregated period-wise cost and quantity data for cost objects, combining archived COSP_BAK data with derived ACDOCA data. |
| coss | Cost Object Summary (COSS) compatibility view - Provides period-wise cost and quantity data for cost objects from controlling documents, replicating the native SAP COSS table structure. |
| faglflexa | Financial General Ledger Line Items (FAGLFLEXA) compatibility view - Provides detailed line item data from financial accounting documents, maintaining compatibility with native SAP FAGLFLEXA table. |
| marc | Material Master Plant Data (MARC) compatibility view - Contains plant-specific material master data including MRP, procurement, and stock information, replicating the native SAP MARC table structure. |
| mchb | Batch Stocks (MCHB) compatibility view - Contains batch-specific stock quantities and valuations for each material and storage location, maintaining compatibility with native SAP MCHB table. |
| mkpf | Material Document Header (MKPF) compatibility view - Contains header-level information for material documents from goods movements, replicating the native SAP MKPF table structure. |
| mseg | Material Document Line Items (MSEG) compatibility view - Contains detailed line item information for all material movement transactions, maintaining compatibility with native SAP MSEG table. |
¹ Each Quickstart transformation job run materializes these models if all components of this data model are enabled. This count includes all staging, intermediate, and final models materialized as view, table, or incremental.
Prerequisites
To use this dbt package, you must have the following:
- At least one Fivetran SAP connection:
- A BigQuery, Snowflake, Redshift, PostgreSQL, Databricks destination.
How do I use the dbt package?
You can either add this dbt package in the Fivetran dashboard or import it into your dbt project:
- To add the package in the Fivetran dashboard, follow our Quickstart guide.
- To add the package to your dbt project, follow the setup instructions in the dbt package's README file to use this package.
How is this package maintained and can I contribute?
Package Maintenance
The Fivetran team maintaining this package only maintains the latest version of the package. We highly recommend you stay consistent with the latest version of the package and refer to the CHANGELOG and release notes for more information on changes across versions.
Contributions
A small team of analytics engineers at Fivetran develops these dbt packages. However, the packages are made better by community contributions.
We highly encourage and welcome contributions to this package. Learn how to contribute to a package in dbt's Contributing to an external dbt package article.