Skip to main content

How to import and export Apache Ossie semantic models

This guide tells you how to import an Apache Ossie YAML document into a Coginiti project as .smdl entities. It also tells you how to export a Coginiti semantic layer as an Ossie document for a different tool. Use this guide when you move a semantic model between Coginiti and a different Ossie tool. The guide tells you what to examine before the conversion. The guide also tells you what to correct after the conversion, and what data the conversion does not keep.

info

Semantic layer concepts: Semantic Layer Overview
Semantic model reference: Semantic Model Reference
All mapping tables and message codes: Apache Ossie Conversion Reference

Prerequisites​

  • Coginiti Team or Coginiti Enterprise. The Ossie converter is not available in Coginiti Pro.
  • Write permission for the project that receives the import, or read permission for the project that you export
  • For an import: an Ossie YAML file with version "0.1.1" or "0.2.0.dev0", smaller than 16 MB. A 0.2.0.dev0 file holds 1 model
  • For an export: a project with a configured semantic layer

How it works​

Ossie and Coginiti use different words for the same objects. An Ossie semantic model is a Coginiti semantic layer. A dataset is an entity. A field is a dimension. A metric is a measure. The conversion reference shows all the words.

The shape of a document depends on the version. A 0.2.0.dev0 document holds 1 model. The keys name, datasets, relationships and metrics are at the top level, next to version. A 0.1.1 document holds 1 or more models in a semantic_model list. The exporter writes 1 document for 1 semantic layer.

Each import and each export makes a list of messages. Each message has a code, for example dropped_is_time, and a severity:

  • An error stops the full conversion. The conversion does not make files.
  • A warning tells you that the conversion was successful, but did not keep some data, or changed some data.
  • An info message tells you that the conversion changed the shape of an object, or removed the description of the model. The message tells you how.

Coginiti does not keep the messages after you close the dialog. The counts at the top of the dialog include only fields, metrics and joins that the conversion did not import. Keys, markers, synonyms and name changes that the conversion removed do not change the counts. If you must keep a record of the changes, keep a copy of the message list. Do this before you close the dialog.

Import an Ossie model​

Step 1: Examine the file​

Examine the YAML file for each item in the checklist. Each item can stop the import or can remove data.

ItemEffect
version is "0.1.1" or "0.2.0.dev0"A different value stops the import
A 0.2.0.dev0 file has name and datasets at the top level, and no semantic_model listA semantic_model list in a 0.2.0.dev0 file stops the import with wrapped_semantic_model. Only 0.1.1 uses the list
The file contains 1 document, without --- in the middle, repeated keys, or <<: merge keysEach of these stops the import
Each model name and each dataset name is a valid file nameThe name must not contain /, \, <, >, ", |, ?, *, : or a control character. The name must not be empty, . or ... The name must not end with a dot or a space. The name must not be a Windows device name such as con, nul, aux or com1
Each dataset name, field name, metric name and relationship name is an SQL identifierThe name must start with a letter or an underscore. The name must hold only letters, digits and underscores. Thus, Sales 2024 and order.items stop the import
Each dataset name is unique in all models, without regard to letter caseOrders and orders are the same name
Each source with 3 parts starts with the database of the project environmentA different database stops the import
Each join column and each key column is defined as a fieldIf not, the importer removes the join, or makes the key shorter
Each metric contains only SUM, COUNT, AVG, MIN, MAX or MEDIAN, and each column is in one of theseThe importer imports a ratio or a metric over 2 datasets as more than 1 measure. The importer does not import a nested aggregate or a column that is not in an aggregate
Each 2 datasets that a metric uses have a relationshipIf not, the importer does not import the metric
Each expression has an ANSI_SQL entry, an OSSIE_SQL_2026 entry, or an entry for your platformIf not, the importer does not import the field or the metric. A DAX, SIGMA, MDX or HOLISTICS_AQL entry alone is not sufficient
Each field expression is SQL, and is not an aggregateA field is a row expression. The importer does not import a formula of a different tool, or a SUM(...) field
Each dataset has a source that is not emptyAn empty source stops the import. In a 0.2.0.dev0 file, the schema check reports schema_violation. A source of spaces only, or an empty source in a 0.1.1 file, gives source_not_importable
In a 0.2.0.dev0 file, no name, datasets list, primary_key or unique_keys entry is emptyThe schema check stops the import with schema_violation
Each anchor makes only a small number of copiesAn anchor that makes many copies stops the import
You know the entities that the semantic layer already containsA name conflict stops the import
tip

First, import the file into an empty test project. Read the messages and correct the file. Then import the file into the real project.

Step 2: Open the import dialog​

  1. In the catalog, right-click the project or a folder in the project. This item receives the entities.
  2. Select Import Apache Ossie Model….
  3. Select the YAML file.

The menu item is not shown for a published project. The import does not accept a file larger than 16 MB. Divide a file of this size into more than 1 document.

Step 3: Examine the preview​

The dialog does the conversion before it writes files. The dialog shows:

  • The Ossie spec version of the file and the target platform.
  • The Environment selector. The importer uses the platform of the environment to select the SQL dialect. The importer uses the database of the environment to examine sources with 3 parts. Keep the value Project default, if the file was not written for a different environment of the project.
  • The .smdl files that the conversion made. Click a file to read the content.
  • The message list, with a filter for severity.
  • A conflict banner, if a new file has the name of an entity that already exists. Letter case is not important. The Import button is not available until you remove the conflict.

One model makes 1 .smdl file for each dataset, for example orders.smdl. A 0.2.0.dev0 file always holds 1 model. Only a 0.1.1 file can hold 2 or more models. Then the import makes 1 folder for each model, for example sales/orders.smdl and hr/employees.smdl. All entities go into the same semantic layer. Thus, each dataset name must be unique in the full document.

Step 4: Import​

Click Import. Coginiti makes the .smdl files in the same way as a manual asset creation. Coginiti does the usual permission tests and records the usual audit events. The importer only adds entities. The importer does not replace an entity. To import a file again, first delete the entities from the earlier import.

Step 5: Correct the data that the importer did not keep​

The importer keeps the schema and the table of each source. The importer keeps the dimensions with their expressions. The importer makes a measure from a metric over 1 dataset, and a derived measure from a ratio. The importer makes a many_to_one relationship from each join. Some data is not correct after the import. Do the manual steps that follow.

Set the measure types. A 0.1.1 file does not have a datatype. Thus, each column and each measure gets the type text, also sums and counts. The types Time and Opaque, and a missing type, also become text. The type DateTimeTz does not keep the time zone. No message reports a changed type. Open each measure and set the correct type.

Set the relationship types. Ossie does not have a join type. Each join becomes many_to_one, with the from dataset on the many side. Set one_to_one where it is applicable. The columns in from_columns and to_columns make pairs by position. Make sure that the pairs are correct.

Examine each expr. A field can have 1 expression for each SQL dialect. Coginiti keeps 1 expression. The importer selects the entry for your platform, if the platform is SNOWFLAKE, BIGQUERY or DATABRICKS. If there is no entry for your platform, the importer selects ANSI_SQL. If there is no ANSI_SQL entry, the importer selects OSSIE_SQL_2026, the portable SQL of the Ossie spec. The importer removes the other entries without a message.

Keep the names of the decomposed metrics. A ratio becomes 1 hidden measure for each aggregate and 1 derived measure with the formula. An example of a ratio is SUM(store_sales.ss_ext_sales_price) / COUNT(DISTINCT customer.c_customer_sk). The message metric_decomposed shows the names of the parts. Do not change the names of the hidden measures, because the formula uses these names.

Examine the warnings. These warnings occur most frequently:

  • relationship_excluded, for a join with a column that is not defined as a field. The importer removed the join. Queries over the 2 datasets fail. Add the column as a field on the 2 datasets and import the file again. Set the field to hidden if the field is not a business column.
  • primary_key_column_dropped, for a key column that is not defined as a field. The entity gets a shorter key, and Coginiti uses this key. Define each key column as a field, and import the file again.
  • metric_not_importable, for a metric with OVER (...), IGNORE NULLS, RESPECT NULLS or WITHIN GROUP, or with a FILTER condition on a different dataset. A standard aggregate with the same name gives different results. Thus, the importer makes no measure. Make the measure manually in Coginiti.
  • foreign_extension_dropped, for a custom_extensions entry of a different vendor. Read the content of the entry before you discard the original file. For example, a Databricks row filter changes each result.

The conversion reference shows each import message with the cause and the action.

Export a semantic layer​

Step 1: Examine the semantic layer​

  1. Make sure that each entity has a table_name, a query, or a CoginitiScript block that resolves. The exporter does not export other entities. The exporter also does not export the measures and the relationships of these entities.
  2. Make sure that no measure has aggregation_type = "custom". The exporter exports a derived measure as the expanded SQL. A custom measure does not have an Ossie form.
  3. Make sure that each relationship expr has 1 or more from.x = to.y equalities, connected with AND. A top-level OR is not permitted.
  4. Make sure that no 2 entities have the same measure name. If 2 entities have the same measure name, the exporter changes 1 name, for example to customers_row_count.
  5. Make sure that no schema_name contains a dot. A schema with a dot becomes a source with 3 parts. Coginiti does not import a source with 3 parts.
  6. Make sure that no 2 entities have names that differ only in letter case, for example Orders and orders. The exporter writes the document, but a later import into Coginiti refuses it.

Step 2: Open the export dialog​

  1. In the catalog, right-click the item that you export:
    • an .smdl file. The exporter exports the semantic layer that contains the file;
    • a folder. The exporter exports the semantic layer that contains the folder, or the 1 layer that is in the folder;
    • a project with 1 semantic layer. The exporter exports that layer.
  2. Select Export Apache Ossie Model….

An Ossie document holds 1 model. Thus, 1 export makes 1 document for 1 semantic layer. If the project or the folder holds 2 or more layers, the export stops. The message is, for example, the project holds 2 semantic layers (sales, hr); an Ossie document holds one, so export a layer folder or an .smdl asset. To export a full project, export each layer 1 at a time.

Step 3: Examine the summary and save the file​

The dialog shows the counts of the entities, dimensions, measures and relationships that the exporter wrote. The dialog shows the count of the items that do not have an Ossie form. The dialog also shows the message list. Click Save and select a location for the YAML file. The name of the file is the name of the semantic layer, for example sales.yaml.

The exporter always writes version: 0.2.0.dev0. The model keys are at the top level of the document. The model name is the name of the semantic layer. A table entity becomes source: schema.table, without a database part. A query entity becomes a source with the query text.

Step 4: Give the file to the users of the other tool​

Tell the users of the other tool 2 things:

  • The platform of the SQL. On a Snowflake, BigQuery or Databricks layer, the exporter writes each expression 2 times. One copy has your platform label. One copy has the ANSI_SQL label. On other platforms, the exporter writes 1 ANSI_SQL entry. The file does not record that the SQL was written for a platform, for example Redshift.

  • Do not change custom_extensions, if the file will return to Coginiti. An entry with vendor_name: COGINITI stores these settings:

    • labels
    • hidden flags
    • measure filters
    • derived measure formulas
    • the entity of each measure
    • the join types one_to_one and one_to_many
    • added join conditions
    • CoginitiScript block references

    Other tools ignore this entry. If a tool removes the entry, all these settings are removed at the same time.

Other tools see a filtered measure as SUM(CASE WHEN ... THEN ... END). Other tools see a derived measure as 1 expanded SQL string. Other tools see a one_to_many join as a many-to-one join. Other tools see a join with an added condition as only the equality part. For the first 2 items, the results are the same in each tool. For the last item, the join returns more rows in other tools.

Each metric has an entity key in the COGINITI entry. The key tells Coginiti the entity of the measure. Two types of measure return only with this key. A row count with expr = "1" is exported as COUNT(1), which names no table. A measure with a filter or an expr that uses a joined entity, for example filter = "customers.region = 'EU'", names 2 tables. If a tool removes the entry, these measures do not return into a layer with 2 or more entities. The import reports metric_not_importable.

A derived measure that uses 2 entities, for example revenue / customers.customer_count, is exported with the message measure_approximated. The SQL gives correct results only if the other tool aggregates each part at the grain of its entity. Coginiti does this. Tell the users of the other tool about the message.

Round trips​

Export from Coginiti, then import into Coginiti. Export a semantic layer. Then import the file into an empty project. A layer without special settings returns fully. Labels, hidden flags, filters, join types and the entity of each measure are stored in the COGINITI entry. These items do not return:

  • a custom measure, a relationship without an equality or with OR, and an entity without a source. The exporter did not export these items;
  • type = "number". The type returns as float;
  • the platform, the label and the version of the semantic layer. The exporter does not write these items.

Import from Ossie, then export to Ossie. Import a file from a different tool. Then export the file. The new file is smaller than the original file. The export does not report the difference. The import messages were the only record. The converter does not keep these items:

  • each custom_extensions entry of a different vendor
  • each dialect entry that the importer did not select
  • unique_keys, is_time, and the model description and ai_context
  • the database part of each source
  • each window clause and each nested aggregate
  • each join on a column that was not defined as a field
  • the version 0.1.1. A 0.1.1 file returns as a 0.2.0.dev0 file;
  • the model structure of a 0.1.1 file with 2 or more models. All models go into 1 layer. The layer returns as 1 model.

Keep the original file and the original semantic layer. Use the converted copy as a new model. Do not use the converted copy as an exact copy of the original model.

Troubleshooting​

ProblemAction
The import stops with unsupported_versionSet version to "0.2.0.dev0"
The import stops with wrapped_semantic_modelThe file has version: "0.2.0.dev0" and a semantic_model list. Ossie removed the list from 0.2.0.dev0. Move name, datasets and the other model keys to the top level, next to version. Put each other model into a file of its own
The import stops with schema_violation for a 0.1.1 file that has a datatype or a vendor_name: COGINITI entryVersion 0.1.1 does not have these items. Set version to "0.2.0.dev0"
The import stops with schema_violation and a path, for example /datasets/2The first index is 0. Thus, the path points to the third dataset. In a 0.1.1 file, the path is /semantic_model/0/datasets/2. Remove the key that the spec does not permit
The import stops with schema_violation for an empty name, an empty source, an empty datasets list, or an empty primary_key or unique_keys entryVersion 0.2.0.dev0 does not permit an empty value. Add the value, or remove the empty entry
The import stops with layer_conflictChange the name of the dataset, import the file into an empty project, or delete the earlier entity. An import of the same file 2 times always gives this error
The import stops with database_mismatchRemove the database part of the source. As an alternative, import the file into a project with an environment that uses that database
The import stops with invalid_name for a name that has a space or a dotEach dataset name, field name, metric name and relationship name must be an SQL identifier. Use only letters, digits and underscores
The import stops with invalid_name for a name such as con or nulThese are Windows device names. A dataset becomes a file. Change the name
The import stops with source_not_importableA dataset has a source that is empty or has only spaces. Give the dataset a table or a query
The import stops with generated_smdl_invalid or layer_conflict, and shows no warningsThese errors do not return a message list. Correct the error and import the file again to see all messages
The importer does not import a field, and shows field_not_importableThe expression is not SQL, or it is an aggregate. Write the expression again as SQL. Move an aggregate into a metric
The import shows lossy_datatypeThe file has no datatype. Each field and each metric became text, also sums and counts. Add a datatype to each field and each metric. First set version to "0.2.0.dev0"
The converter shows diagnostics_elidedThe response has more than 200 messages. Correct the reported items. Then do the operation again to see the other messages
The importer does not import a metric over 2 datasetsAdd a relationship between the 2 datasets
The importer does not import a running total or an IGNORE NULLS metricMake the measure manually in Coginiti. No standard aggregate gives the same result
The importer does not import a FILTER (WHERE ...) metric on Snowflake or BigQueryThese platforms do not have a FILTER clause. Write the metric as SUM(CASE WHEN ... THEN ... END)
On Postgres, the importer does not import the fields of a file that has only DATABRICKS entriesAdd an ANSI_SQL or an OSSIE_SQL_2026 entry to each expression
The importer removes a join column or a key columnDefine the column as a field. Set the field to hidden if necessary
The exporter changes the name of a metricGive each measure a unique name in the semantic layer
The export fails with nothing_exportable and the message the layer has no exportable datasetsGive 1 or more entities a table, a query, or a CoginitiScript block that resolves
The export stops with a message that ends with export a layer folder or an .smdl assetThe project or the folder holds more than 1 semantic layer. An Ossie document holds 1 model. Right-click the folder of 1 layer or an .smdl file of the layer
The export shows name_not_importableThe name is correct for Coginiti, but a later import refuses it. Change the name, if the document will return to Coginiti
The export shows relationship_not_importable or primary_key_column_droppedA join column or a key column is not a field. Define the column as a field. Set the field to hidden if necessary
The export shows dataset_name_sharedTwo entities of the layer have names that differ only in letter case. The document is correct Ossie, but a later import into Coginiti refuses it. Change 1 name
The importer does not import a COUNT(1) row count, or a measure with a filter on a joined entityA tool removed the COGINITI entry of the metric. The entity key in the entry tells Coginiti the entity of the measure. Keep the entry, or make the measure again after the import

No corrective action is possible for these items:

  • a custom measure
  • a join without an equality
  • unique keys
  • is_time
  • AI instructions
  • the extensions of a different vendor

The converter does not keep these items.