Import and Export Data from an OSI YAML File

Starting in Strategy (July 2026), importing and exporting data using an OSI YAML file is available out-of-the-box. Create Mosaic models from external YAML files in Apache Ossie (OSI formerly) format in Mosaic Studio. Use OSI files to preserve your semantic model information between tools.

Starting in Strategy One (June 2026), you can import your OSI YAML files into Mosaic Studio to easily use your Snowflake OSI data. You can also export your Mosaic model file to an OSI YAML file. Use OSI files to preserve your semantic model information between tools.

Prerequisite

If you are using Strategy One (June 2026), importing and exporting data from an OSI YAML file is a preview feature. To enable this functionality, contact Strategy Support.

Import a Snowflake OSI YAML File

See also OSI Import Considerations.

  1. Create a Mosaic model.

  2. In the Sources tab, click Import OSI YAML File.

    If you add a table or model, Import OSI YAML File is not available.

  3. Click Browse files.

  4. Select your OSI YAML file and click Open.

    Source mapping displays.

  5. Expand the Data Source drop-down list and choose an existing source or click Create New Source.

    If a source match is found, Mosaic Studio will automatically populate.

  6. If you click Create New Source, find your data source type, enter the required fields, and click Save.

  7. Expand the Namespace drop-down list and choose an existing namespace or create a new one.

  8. To use the FFSQL editor for an OSI source, in the Import OSI YAML File dialog, hover over a source row and click Edit .

  9. Edit your SQL and click Update.

  10. After your sources are mapped with no errors, click Proceed.

Export to OSI YAML File

See also OSI Export Considerations.

  1. In the Library Navigation pane, click Data.

  2. Right-click your model and choose Export to OSI YAML File.

    You can also click Info and choose Export to OSI YAML File.

  3. Click Export.

    The YAML file automatically downloads to your machine.

OSI Import Considerations

Review the following considerations when importing an Apache Ossie YAML model into a Mosaic model. The import feature takes the YAML document and creates Mosaic datasets with tables, attributes, fact metrics, and semantic metrics.

Dataset/Table Creation Limitations

Datasource Mapping Requirements

  • Requirement: Every unique datasource referenced in OSI source fields must have a corresponding entry in dataSourceMappings.

  • Source field parsing: The datasource name is the first segment before the schema/table (requires at least three segments).

  • Examples:

    • "EATWH2.TPCDSX1.STORE" → datasource = EATWH2

    • "Tutorial Postgres.public.my_table" → datasource = Tutorial Postgres

    • "public.my_table" → datasource = null (only two segments, no datasource prefix)

  • Validation: If a dataset's datasource cannot be resolved, the import fails immediately with:

    Copy 
    MS_E_DATASET_OSI_DATA_SOURCE_NOT_RESOLVED: 
      Datasource '<name>' not found in mappings for dataset '<dataset>'
      

  • Edge case: Custom SQL sources (those starting with SELECT or WITH) use a wildcard datasource mapping:

    • If dataSourceMappings contains "*", it uses that ID.

    • Otherwise, it uses the first entry in the mappings list.

  • Impact:

    • Missing datasource mappings cause hard failures (not warnings).

    • Two-segment sources (for example, schema.table) cannot be imported unless renamed to a three-segment format.

    • Custom SQL requires explicit wildcard or arbitrary first-mapping fallback.

Namespace Handling

  • Behavior: The namespace (schema/catalog) segment is optional in generated pipelines. If the OSI source field has only one or two segments , the namespace is set to empty string.

  • Impact:

    • Two-segment sources (such as public.orders) treat the first segment as namespace, not datasource.

    • Database connections that require explicit schema names may fail at publish time.

    • Default schema resolution depends on the datasource's connection settings.

Column Data Type Inference

  • Limitation: Physical table column data types are inferred from OSI field metadata only, not from actual database schema introspection.

  • Inference rules:

    • Non-dimension fields (facts) → double (precision 20, scale 4)

    • Dimension fields with is_time: true → timestamp (precision 29, scale 3)

    • All other dimension fields → utf8_char (string, precision 10, scale 0)

  • Actual type resolution: Data types are re-resolved at publish time when Intelligence Server queries the datasource metadata. The import-time types serve as placeholders only.

Custom SQL Sources

  • Support: Datasets with source starting with SELECT, WITH, or (SELECT are imported as custom SQL pipelines (not single-table pipelines).

  • Limitations:

    • No datasource prefix extraction (uses wildcard/first mapping).

    • Namespace is always empty.

    • Column list is derived from OSI fields, not parsed from SQL.

  • Impact: If the custom SQL references columns are not listed in OSI fields, those columns are unavailable for expressions.

  • Example:

    Copy 
    datasets:
      - name: sales_summary
        source: |
          SELECT 
            region, 
            SUM(amount) as total_sales,
            COUNT(*) as order_count
          FROM orders 
          GROUP BY region
        fields:
          - name: region
            dimension: {}
          - name: total_sales
            # Missing: order_count

    • Metric expressions can reference region and total_sales, but not order_count (even though it's in the SQL).

Expression Import Limitations

ANSI SQL Only

  • Limitation: Only the ANSI_SQL dialect is imported. Other dialects are ignored.

  • Impact:

    • Snowflake-specific syntax (for example, FLATTEN, LATERAL, VARIANT) is rejected

    • PostgreSQL extensions (for example, JSONB, ARRAY, regex operators) are not supported

    • Multi-dialect OSI files require ANSI fallback expressions for import

Local Parser Coverage

The local Trino-based SQL parser handles standard SQL expressions without requiring an I-Server round-trip.

  • Supported patterns:

    • Aggregates: SUM, AVG, COUNT, MIN, MAX, STDDEV, VARIANCE, MEDIAN, MODE, PRODUCT, RANK, NTILE, FIRST, LAST, CORR

    • Arithmetic: +, -, *, /, %, unary -

    • Comparisons: =, <>, <, >, <=, >=

    • Logical: AND, OR, NOT

    • Conditional: CASE WHEN ... THEN ... ELSE ... END, IF(...)

    • Null handling: COALESCE, ISNULL, ISNOTNULL, NULLTOZERO, ZEROTONULL

    • String functions: CONCAT, LENGTH, LOWER, UPPER, SUBSTRING, LEFT, RIGHT, TRIM, LTRIM, RTRIM, REPLACE, POSITION, REPEAT, SPLIT

    • Math functions: ABS, ROUND, FLOOR, CEILING, SQRT, POWER, EXP, LN, LOG, LOG10, MOD, trigonometric functions

    • Date/time: EXTRACT, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DAYOFWEEK, DAYOFYEAR, WEEK, QUARTER, DATEDIFF, ADDDAYS, ADDMONTHS

    • Type conversion: CAST, TONUMBER, TOSTRING, TODATETIME

    • Window functions: OVER (PARTITION BY ... ORDER BY ...) with ROWS/RANGE frames

  • Unsupported patterns (fall back to Intelligence Server):

    • Subqueries in expressions: (SELECT MAX(x) FROM t)

    • Common table expressions (CTEs): WITH cte AS (...) SELECT ...

    • Array/JSON functions: ARRAY_AGG, JSON_EXTRACT, UNNEST

    • Recursive CTEs

    • Custom user-defined functions (UDFs)

    • Vendor-specific syntax not in Trino's grammar

  • Error handling:

    • If local parse fails, the metric is not dropped; it falls back to Intelligence Server parsing.

    • If Intelligence Server parse also fails, the metric is skipped with a warning log entry.

Column Reference Resolution

  • Limitation: Table-qualified column names (for example, public.orders.amount) are stripped during import because the local parser resolves columns by bare name only.

  • How it works:

    1. OSI export emits qualified names: AVG(public.lu_employee.salary)

    2. Import strips qualifier prefixes: AVG(salary)

    3. Local parser resolves salary against the created fact/attribute map (case-insensitive)

  • Impact:

    • Multi-table metrics with same-named columns from different tables cannot be distinguished

    • Name collisions produce incorrect metric expressions (for example, SELECT orders.amount + returns.amount resolves both to the first amount fact)

  • Example failure:

    Copy 
    datasets:
      - name: orders
        fields:
          - name: amount  # maps to fact "amount"
      - name: returns
        fields:
          - name: amount  # also maps to fact "amount"
    metrics:
      - name: Net Revenue
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: SUM(orders.amount) - SUM(returns.amount)

    • After import: both orders.amount and returns.amount resolve to the same fact object → produces SUM(amount) - SUM(amount) = always zero.

Strategy Bracket Notation

  • Behavior:Strategy-specific bracket-quoted identifiers (for example, [Revenue], [Date]@[Date ID]) are converted to ANSI SQL equivalents during export. On import, bracket notation fails the local Trino parser but may succeed via Intelligence Server fallback.

    • Export: [POPULATION AGE] → "POPULATION AGE" (ANSI double-quote)

    • Export: [COUNTY_DESC] → COUNTY_DESC (bare identifier)

    • Import: Local parser rejects brackets → falls back to Intelligence Server (which understands native Strategy syntax)

  • Impact: Bracket notation metrics will bypass local parsing (slower path).

  • Example:

    Copy 
    # ⚠️ Works via I-Server fallback (not local parser)
    expression: SUM([Revenue])

    # ✅ Preferred (works with both local parser and I-Server)
    expression: SUM(Revenue)

Attribute Import Limitations

Complex Expressions Not Supported

  • Limitation: Attribute form expressions using CASE, function calls, or arithmetic are rejected at creation time.

  • Behavior: Attributes with complex form expressions are skipped (not created), with a warning logged.

  • Why: Intelligence Server's attribute form validator requires simple column references only. Derived expressions must use calculated attributes, not form expressions.

  • Impact: OSI exports containing attributes with the following are silently dropped on import:

    • CASE WHEN dimension = 'A' THEN 'Category A' ELSE 'Other' END

    • CONCAT(first_name, ' ', last_name)

    • EXTRACT(YEAR FROM date_column)

Time Role Loss

  • Limitation: OSI only preserves the is_time: true/false flag. The original Strategy time role (Year, Month, Day, Hour, and so on) is not preserved in the OSI format.

  • Default import behavior: All is_time: true attributes are assigned a generic date role (DssTimeRoleDate).

  • Impact:

    • Export→Import→Export cycle loses time granularity (Year attribute becomes generic Date attribute)

    • Time-based drill hierarchies may not work correctly if specific roles (DayOfWeek, MonthOfYear) are required

Multi-Table Attribute Grouping

  • Behavior: Fields with the same name across multiple datasets are grouped into a single attribute with multiple expression modules (one per table).

  • Example:

    Copy 
    datasets:
      - name: orders
        fields:
          - name: Customer
            expression: customer_id
      - name: returns
        fields:
          - name: Customer
            expression: customer_fk

    • Import creates one attribute Customer with two form expressions:

      • Module 1 (orders table): customer_id

      • Module 2 (returns table): customer_fk

  • Impact:

    • Intended for shared dimensions (for example, Customer appearing in both fact tables).

    • Breaks if the same name refers to different logical entities in different tables.

Lookup Table Assignment

  • Rule: The first table where a field appears becomes the attribute's lookup table (primary/dimension table).

  • Impact: Relationship direction is determined by dataset order in the OSI file.

  • Example:

    Copy 
    datasets:
      - name: orders       # appears first
        fields:
          - name: Customer
      - name: customers    # appears second
        fields:
          - name: Customer

    • Lookup table = orders (wrong — should be customers)

    • Exported relationship: customers → orders (backward)

  • Correct approach: List dimension tables before fact tables in the OSI document.

Metric Import Limitations

Fact Metric vs. Semantic Metric Collision

  • Behavior: Non-dimension fields (facts) are automatically converted to fact metrics (for example, SUM(amount)). Semantic metrics with the same name are skipped unless they have a non-trivial expression.

  • Collision detection logic: If all three are affirmative, the semantic metric is dropped (fact metric takes precedence).

    1. Is there a semantic metric named X and a non-dimension field named X?

    2. Is the semantic metric's expression a trivial self-reference (for example, SUM(X) or bare X)?

    3. Do all column references in the expression resolve to existing fields?

  • Example:

    Copy 
    datasets:
      - name: orders
        fields:
          - name: Revenue  # non-dimension → becomes fact metric
    metrics:
      - name: Revenue
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: SUM(Revenue)  # trivial self-reference

    • Import creates one metric: the fact metric Revenue. The semantic metric is ignored.

  • Non-trivial example (both created):

    Copy 
    metrics:
      - name: Revenue
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: SUM(Revenue) * 1.1  # non-trivial → both metrics created

    • Outcome: Two metrics — fact metric Revenue and semantic metric Revenue (collision).

  • Impact: Duplicate metric names cause Intelligence Server validation errors at commit time.

Metric Description Override

  • Behavior: If a fact metric and semantic metric share the same name, the fact metric's description is replaced with the semantic metric's description.

  • Rationale: Semantic metric descriptions typically describe aggregation logic (for example, "Sum of item sales price"), which is more meaningful than the raw column description.

  • Impact: Fact metric descriptions are lost if a same-named semantic metric exists.

Unresolvable Column References

  • Behavior: If a semantic metric references columns that don't exist as fields in any dataset, the metric may still be created but will fail at publish time.

  • Example:

    Copy 
    datasets:
      - name: orders_view  # materialized view
        fields:
          - name: total_amount  # derived column in view
    metrics:
      - name: Revenue Growth
        expression:
          dialects:
            - dialect: ANSI_SQL
              expression: SUM(order_amount) / SUM(prior_year_amount)
              # order_amount and prior_year_amount exist in the source table
              # but not in the view's field list

  • Outcome:

    • Local parser fails (column resolver has no entry for order_amount).

    • Falls back to Intelligence Server parser.

    • Intelligence Server accepts the expression (assumes valid SQL).

    • Publish fails when Intelligence Server tries to validate against actual datasource schema.

  • Impact: False success at import time; delayed failure at publish/execution.

Relationship Import Limitations

Relationship Reconstruction

  • Relationships are not explicitly stored as relationship objects. Instead, they are reconstructed by:

    1. Identifying foreign-key fields (from-side of relationship) in the OSI relationships array

    2. Adding those FK expressions directly to the corresponding to-side attribute's form

    3. Skipping FK field creation as standalone attributes

  • Example:

    Copy 
    relationships:
      - name: orders_to_customers
        from: orders
        to: customers
        from_columns: [customer_fk]
        to_columns: [customer_id]

    • Import outcome:

      • customer_fk field in orders → not created as attribute

      • customer_id field in customers → created as attribute Customer

      • Customer attribute ID form gets two expression modules:

      • Module 1 (customers table): customer_id

      • Module 2 (orders table): customer_fk

  • Impact:

    • Foreign key columns are "absorbed" into dimension attributes (not standalone)

    • One-to-many relationships are preserved via multi-table attribute forms

    • Many-to-many relationships (junction tables) are not supported (require explicit relationship objects, which OSI import doesn't create)

Cardinality Not Preserved

  • Limitation: OSI relationships have no cardinality property. Import assumes all relationships are one-to-many (FK → PK).

  • Impact:

    • Many-to-many relationships (junction tables) cannot be represented

    • One-to-one relationships are treated as one-to-many (no optimization)

Transaction Concerns

Partial Failure Behavior

  • Policy: Import attempts to create as many objects as possible, logging warnings for failures.

  • Failure modes:

    • Table creation fails: Subsequent attributes/metrics referencing that table are skipped

    • Attribute creation fails: Logged as warning, metric creation continues

    • Fact metric creation fails: Logged as warning, semantic metrics continue

    • Semantic metric creation fails: Logged as warning, metric deleted (if created)

  • No rollback: If 5 out of 10 metrics fail, the other 5 are retained. The changeset contains partial data.

  • Impact:

    • User must inspect logs to determine which objects were skipped

    • Commit may succeed with incomplete model

    • No atomic "all-or-nothing" guarantee

Round-Trip Fidelity

Information Loss in Export→Import Cycle

  • Preserved:

    • Table structures (name, description)

    • Attribute names, descriptions

    • Attribute lookup tables (first-table rule)

    • Fact metric expressions (single-table facts)

    • Semantic metric ANSI SQL expressions (if parseable)

    • Relationships (reconstructed from attribute forms)

  • Lost:

    • Time roles (Year/Month/Day → generic Date)

    • Metric formatting (number format, currency symbols)

    • Attribute forms beyond ID form (description forms, custom forms)

    • Dataset folders (location in project tree)

    • Security filters

    • Object ACLs (permissions)

    • Custom properties

    • Metric aggregation settings (from-base vs. from-detail)

    • Conditional metrics (filters embedded in metrics)

    • System hierarchy

  • Degraded:

    • Multi-table metrics (qualified column names stripped → may produce incorrect expressions)

    • Attribute form expressions (complex expressions dropped)

    • Data types (inferred from OSI metadata, not database schema)

OSI Export Considerations

The OSI import functionality in Strategy imports OSI YAML using the ASI_SQL dialect. The tables below summarize current limitations and expected behavior, organized by the major limitation areas.

Semantics Loss

These metadata elements exist in the original Strategy Mosaic model but are not preserved in OSI YAML output. The exported YAML may still be functional, but the re-imported model is semantically reduced.

Area Limitation What Is Lost
Metric dimensionality / level specifications Metric levels such as {~+}, {Year+}, and {Customer;Year} are consumed during conversion but not emitted in OSI output. The target level at which the metric should compute.
Attribute hierarchy Attribute-to-attribute parent-child relationships, such as Year > Quarter > Month > Day, are not represented. Dimensional hierarchy structure.
VLDB properties Properties such as NullInclude, UseLookupForAttributes, ZeroCheck, and MetricJoinType are not written to OSI YAML. SQL generation behavior and tuning flags.
Metric conditionality, thresholds, sorts, and subtotals Metric filters, conditional formatting thresholds, default sort order, and custom subtotal definitions are not preserved as metadata. Metric-level behavioral and presentation metadata.
System-generated attribute forms Auto-increment IDs and computed fields are skipped. System-generated attribute forms.

Composite key joins

Relationships requiring multiple join columns are truncated.

All join columns after the first column.
Multi-attribute relationship deduplication Multiple attributes creating relationships between the same pair of datasets are deduplicated. Join columns from subsequent attributes.

Smart attributes

Auto-generated time-interval companion attributes, such as month, quarter, and year interval smart attributes, are omitted.

Smart attribute fields, relationships, and metadata.

Unsupported Patterns in Attribute Forms and Base Metric

These expression patterns either produce no expression in output YAML:

Area Limitation What Happens
Metrics based on previous-period functions PreviousDay, PreviousWeek, PreviousMonth, PreviousQuarter, and PreviousYear are not supported. The metric is exported without an expression field.
Metrics based on Period-to-date accumulation functions MonthToDate, QuarterToDate, and YearToDate are not supported. The metric is exported without an expression field.
Metrics referencing unsupported sub-metrics Metrics that reference another metric using an unsupported pattern cannot be converted. The parent metric also cannot produce a valid expression.
Attribute forms or base metrics with Apply* functions ApplySimple, ApplyAggregation, ApplyComparison, ApplyLogic, ApplyRelative, and ApplyOptional embed custom database-specific SQL. The converter does not extract SQL templates from function properties and handles only registered standard patterns.

Model-Level Considerations

These limitations are caused by the overall model structure, regardless of individual expression quality.

Area Limitation What Happens
Linked models The model consists entirely of linked tables from other models and has no tables of its own. Export produces a placeholder dataset with no meaningful content.
Datasets with no fields A table has no attributes referencing it and no facts assigned. The dataset is emitted with no fields.
Custom SQL tables A table uses a freeform custom SQL query as its source. The source field contains the full SQL statement rather than a db.schema.table reference.
Tables with no physical table metadata Physical table information is missing, such as for programmatically created tables, legacy migrated tables, or incomplete draft tables. The source defaults to unknown. Metrics referencing facts on these tables may also lack schema.table.column qualification.
Multi-datasource DBMS pattern mismatch The model spans multiple datasources with different database types, such as PostgreSQL and Snowflake. The converter uses a single ANSI pattern for all metrics.

Read-only models / no export access

The user lacks read access to the model.

The user lacks read access to the model.