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.
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. A0.2.0.dev0file 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.
| Item | Effect |
|---|---|
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 list | A 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 keys | Each of these stops the import |
| Each model name and each dataset name is a valid file name | The 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 identifier | The 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 case | Orders and orders are the same name |
Each source with 3 parts starts with the database of the project environment | A different database stops the import |
| Each join column and each key column is defined as a field | If 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 these | The 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 relationship | If 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 platform | If 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 aggregate | A 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 empty | An 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 empty | The schema check stops the import with schema_violation |
| Each anchor makes only a small number of copies | An anchor that makes many copies stops the import |
| You know the entities that the semantic layer already contains | A name conflict stops the import |
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
- In the catalog, right-click the project or a folder in the project. This item receives the entities.
- Select Import Apache Ossie Model….
- 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
.smdlfiles 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 withOVER (...),IGNORE NULLS,RESPECT NULLSorWITHIN GROUP, or with aFILTERcondition 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 acustom_extensionsentry 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
- Make sure that each entity has a
table_name, aquery, 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. - 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. - Make sure that each relationship
exprhas 1 or morefrom.x = to.yequalities, connected withAND. A top-levelORis not permitted. - 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. - Make sure that no
schema_namecontains a dot. A schema with a dot becomes a source with 3 parts. Coginiti does not import a source with 3 parts. - Make sure that no 2 entities have names that differ only in letter case, for example
Ordersandorders. The exporter writes the document, but a later import into Coginiti refuses it.
Step 2: Open the export dialog
- In the catalog, right-click the item that you export:
- an
.smdlfile. 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.
- an
- 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_SQLlabel. On other platforms, the exporter writes 1ANSI_SQLentry. 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 withvendor_name: COGINITIstores these settings:- labels
- hidden flags
- measure filters
- derived measure formulas
- the entity of each measure
- the join types
one_to_oneandone_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
custommeasure, a relationship without an equality or withOR, and an entity without a source. The exporter did not export these items; type = "number". The type returns asfloat;- 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_extensionsentry of a different vendor - each dialect entry that the importer did not select
unique_keys,is_time, and the modeldescriptionandai_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. A0.1.1file returns as a0.2.0.dev0file; - the model structure of a
0.1.1file 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
| Problem | Action |
|---|---|
The import stops with unsupported_version | Set version to "0.2.0.dev0" |
The import stops with wrapped_semantic_model | The 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 entry | Version 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/2 | The 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 entry | Version 0.2.0.dev0 does not permit an empty value. Add the value, or remove the empty entry |
The import stops with layer_conflict | Change 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_mismatch | Remove 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 dot | Each 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 nul | These are Windows device names. A dataset becomes a file. Change the name |
The import stops with source_not_importable | A 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 warnings | These 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_importable | The 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_datatype | The 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_elided | The 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 datasets | Add a relationship between the 2 datasets |
The importer does not import a running total or an IGNORE NULLS metric | Make the measure manually in Coginiti. No standard aggregate gives the same result |
The importer does not import a FILTER (WHERE ...) metric on Snowflake or BigQuery | These 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 entries | Add an ANSI_SQL or an OSSIE_SQL_2026 entry to each expression |
| The importer removes a join column or a key column | Define the column as a field. Set the field to hidden if necessary |
| The exporter changes the name of a metric | Give each measure a unique name in the semantic layer |
The export fails with nothing_exportable and the message the layer has no exportable datasets | Give 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 asset | The 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_importable | The 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_dropped | A 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_shared | Two 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 entity | A 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
custommeasure - a join without an equality
- unique keys
is_time- AI instructions
- the extensions of a different vendor
The converter does not keep these items.