/datasphere-view-template

name: datasphere-view-template
description: Generate SAP Datasphere view templates for different modeling scenarios
allowed-tools:
  - Read
  - AskUserQuestion
argument-hint: "<graphical|sql|sqlscript> [dimension|fact|analytic|staging]"
arguments:
  - name: type
    description: "View type: graphical, sql, sqlscript"
    required: true
  - name: purpose
    description: "View purpose: dimension, fact, analytic, staging"
    required: false

Shell Snippet Notes

• Shell snippets assume Bash on Linux/macOS, WSL2, or Git Bash.
• Install the command-specific tooling shown near each snippet before running it.
• Confirm before running commands that delete files, change ownership, deploy, or modify remote systems.

Output Contract

Return a view/modeling template, source assumptions, performance considerations, validation checklist, and pending tenant checks. Default to guidance and avoid modifying Datasphere artifacts or local files unless the user explicitly confirms the target.

Generate a Datasphere view template based on the requested type and purpose.

Available View Types

graphical

Graphical view using the visual editor with drag-and-drop modeling.

sql

SQL view using standard ANSI SQL syntax.

sqlscript

SQLScript view for procedural logic and complex transformations.

---

Template: graphical (dimension)

Graphical View - Dimension

**View Name**: `dim_<entity>` **Semantic Usage**: Dimension **Purpose**: Master data for analytical reporting

Configuration Steps

1. **Create View**

• Open Data Builder > Views
• Click "New Graphical View"
• Select source table/view

2. **Configure Columns**

   Columns:
     - Name: <ENTITY>_ID
       Key: Yes
       Semantic: Identifier

     - Name: <ENTITY>_NAME
       Key: No
       Semantic: Text

     - Name: <ENTITY>_DESC
       Key: No
       Semantic: Description

     - Name: VALID_FROM
       Key: No
       Semantic: Business Date (from)

     - Name: VALID_TO
       Key: No
       Semantic: Business Date (to)

3. **Set Semantic Usage**

• Semantic Usage: **Dimension**
• Expose for Consumption: **Yes**

4. **Configure Associations**

   Associations:
     - Target: Text Entity (if separate)
       Type: Text Association
       Join: <ENTITY>_ID = TEXT_ENTITY_ID
       Language: LANGUAGE_KEY

5. **Add Hierarchy (Optional)**

   Hierarchy:
     Type: Level-Based
     Levels:
       - Level 1: REGION
       - Level 2: COUNTRY
       - Level 3: CITY

---

Template: graphical (fact)

Graphical View - Fact

**View Name**: `fact_<subject>` **Semantic Usage**: Fact **Purpose**: Transactional data with measures

Configuration Steps

1. **Create View**

• Open Data Builder > Views
• Click "New Graphical View"
• Add fact table as source

2. **Add Joins** (to dimensions)

   Joins:
     - Type: Left Outer
       Source: fact_sales
       Target: dim_product
       On: PRODUCT_ID = PRODUCT_ID

     - Type: Left Outer
       Source: fact_sales
       Target: dim_customer
       On: CUSTOMER_ID = CUSTOMER_ID

3. **Configure Measures**

   Measures:
     - Name: QUANTITY
       Aggregation: SUM

     - Name: REVENUE
       Aggregation: SUM

     - Name: UNIT_PRICE
       Aggregation: AVG

     - Name: DISCOUNT_AMOUNT
       Aggregation: SUM

4. **Configure Dimensions** (Foreign Keys)

   Dimension Columns:
     - Name: PRODUCT_ID
       Association: dim_product

     - Name: CUSTOMER_ID
       Association: dim_customer

     - Name: DATE_KEY
       Association: dim_time

5. **Set Semantic Usage**

• Semantic Usage: **Fact**
• Expose for Consumption: **Yes**

---

Template: sql (dimension)

SQL View - Dimension

/*
 * Dimension View: dim_product
 * Source: raw_product (ERP product master)
 * Purpose: Product master data for analytics
 * Author: <author>
 * Created: <date>
 */

SELECT
    -- Key Column (Primary Identifier)
    PRODUCT_ID AS "Product ID",

    -- Descriptive Attributes
    PRODUCT_NAME AS "Product Name",
    PRODUCT_DESCRIPTION AS "Product Description",

    -- Category Hierarchy
    CATEGORY_L1 AS "Category Level 1",
    CATEGORY_L2 AS "Category Level 2",
    CATEGORY_L3 AS "Category Level 3",

    -- Additional Attributes
    BRAND AS "Brand",
    PRODUCT_GROUP AS "Product Group",
    UNIT_OF_MEASURE AS "Base Unit",

    -- Status and Validity
    PRODUCT_STATUS AS "Status",
    VALID_FROM AS "Valid From",
    VALID_TO AS "Valid To",

    -- Audit Fields
    LAST_MODIFIED AS "Last Modified"

FROM "raw_product"

WHERE
    -- Active products only
    PRODUCT_STATUS = 'ACTIVE'
    AND CURRENT_DATE BETWEEN VALID_FROM AND COALESCE(VALID_TO, '9999-12-31')

ORDER BY PRODUCT_ID;

/*
 * Post-Creation Configuration:
 * 1. Set Semantic Usage = Dimension
 * 2. Mark PRODUCT_ID as Key
 * 3. Create text association if separate text table
 * 4. Add hierarchy for category
 */

---

Template: sql (fact)

SQL View - Fact

/*
 * Fact View: fact_sales
 * Sources: raw_sales_header, raw_sales_item
 * Purpose: Sales transactions for analytics
 * Author: <author>
 * Created: <date>
 */

SELECT
    -- Transaction Keys
    h.SALES_ORDER_ID AS "Sales Order ID",
    i.LINE_ITEM_NUMBER AS "Line Item",

    -- Dimension Keys (for associations)
    i.PRODUCT_ID AS "Product ID",
    h.CUSTOMER_ID AS "Customer ID",
    h.SALES_ORG AS "Sales Organization",
    CAST(h.ORDER_DATE AS DATE) AS "Order Date",

    -- Measures (aggregatable)
    i.QUANTITY AS "Quantity",
    i.NET_PRICE AS "Net Price",
    i.GROSS_AMOUNT AS "Gross Amount",
    i.NET_AMOUNT AS "Net Amount",
    i.TAX_AMOUNT AS "Tax Amount",
    i.DISCOUNT_AMOUNT AS "Discount Amount",

    -- Calculated Measures
    (i.NET_AMOUNT / NULLIF(i.QUANTITY, 0)) AS "Avg Unit Price",

    -- Currency (for conversion)
    h.DOCUMENT_CURRENCY AS "Document Currency",

    -- Document Status
    h.ORDER_STATUS AS "Order S