Using Semantic Layer Generators

Go Up to Exporting the Data Model

Overview

Use ER/Studio Data Architect to export a physical data model to one of the following industry-standard semantic formats used by modern BI and analytics-engineering tools:

• Microsoft Power BI Semantic Layer Generator (TMDL). Generates a Power BI Project (.pbip) or an editable TMDL folder.
• Open Semantic Interchange (OSI) Generator (Beta). Generates a single _osi.yaml file.
• DBT Labs Semantic Layer Generator (Beta). Generates a single _dbt.yml file.

All three formats are produced by a single sample macro called Physical Model Export to Semantic Formats. Users can choose the target format when running the macro. This macro reads a physical model (and, when present, its linked logical model for extra business context), and writes the corresponding files to a folder you select.

Running the generator

To export a physical data model to a semantic format

1. In ER/Studio Data Architect, open a diagram that contains at least one physical model. If the diagram also contains a logical model, the Physical Model Export to Semantic Formats macro uses the logical model content to enrich the output.
2. Verify that you have the related external tool to consume the output:

   • Power BI Desktop (for .pbip projects) or Tabular Editor (for TMDL folders)
   • a dbt project (for the dbt Beta output)
   • an OSI-aware catalog or tool (for the OSI Beta output)

3. Run the generator by opening the diagram containing the physical model you want to export.
4. On the Macros tab, expand Sample Macros > Semantic Generators.
5. If the diagram contains more than one physical model, the ERDA Extractor opens allowing you to select the model from a list before clicking OK. This step is skipped when there is only one physical model and you can simply double-click or select and then run the Physical Model to Export to Semantic Formats.

   

6. In the Export dialog, click Browse, and then select the Output folder where you want to export the file.
7. Select the appropriate export format, whether dbt YAML (BETA), TMDL - Power BI / Analysis Services (default), or OSI YAML (BETA).

   

8. Click OK, and then follow any format-specific prompts. When the export is complete, a confirmation message appears showing the output location and a summary of table and relationship counts.

Understanding what details are captured during the export

Regardless of the format chosen, the ERDA generator reads the information from the selected physical model as shown in the following table.

Category	Detail Captured
Tables	Physical name Logical name Schema Description Submodels to which the table belongs
Columns	Name Logical name Data type Ordinal position Primary-key and foreign-key flags Nullability Default value Check constraint Description Privacy classification (from the Primary Classification user-defined property) Reference values (code lists up to 50 values per column)
Keys	Primary keys (PK) Alternate keys (AK) Inversion entries (IE)
Relationships	Type Cardinality Mandatory flag Parent/child tables Foreign-key column pairs Verb phrase Inverse phrase Definition
Logical enrichment	When a logical model exists in the same diagram, the macro pulls in logical names and definitions to enrich the output. For example, as descriptions and Q&A synonyms.

Using the Microsoft Power BI Semantic Layer Generator

This generator exports the physical model to TMDL (Tabular Model Definition Language), the text format used by Power BI and Analysis Services. Table and column descriptions are emitted as TMDL /// descriptions, and logical names are written as Q&A synonyms so natural-language questions in Power BI can match business terminology.

After choosing TMDL — Power BI / Analysis Services in the Export dialog, choose how you want the TMDL delivered.

Step 1: Choose the output form

• Power BI Project. Select this option to receive a complete Power BI project with a report scaffold, ready to open in Power BI Desktop. This option offers star-schema scoping when the model contains fact tables (see Step 2).
• TMDL folder. Select this option to receive the raw, editable TMDL files. You choose which tables to include.

  

Step 2 (Power BI Project only): Choose the star-schema scope

If you choose Power BI Project and the model contains fact tables, you can scope the export around the star schema. Fact tables are detected from the model's dimensional table-type markings (FACT); if the model has no such markings, tables whose physical name begins with fact are treated as facts.

Option	Result
Entire Model (Single project)	One .pbip containing all tables.
Entire Model (Multiple projects with one fact per project)	One .pbip per fact table; each project contains that fact plus the dimension tables it references.
Select Facts	Select specific fact tables. Each selected fact becomes its own project.

A "star" is the fact table plus every dimension table it references, followed recursively (so snowflake dimensions are included). Relationships in the generated model are filtered to only those tables that were exported.

If the model has no fact tables, the whole model is exported as a single project and this step is skipped.

Step 2 (TMDL Folder only): Select tables

If you choose the TMDL folder, the system displays a checklist of every table by physical name with the logical name in brackets. All tables are selected by default. Use Select all and Clear all to toggle the list, then clear all unwanted tables.

Understanding the Power BI Project output structure

Power BI Project (.pbip)

<model>.pbip                   
<model>.Report\                (report scaffold: .platform, definition.pbir)
<model>.SemanticModel\         (.platform, definition.pbism)
  definition\                  
     database.tmdl             (database declaration)
     model.tmdl                (model + relationships)
     tables\<TABLE>.tmdl       (one file per table: columns + partition)
     cultures\en-US.tmdl       (descriptions + Q&A synonyms)

Open the .pbip file directly in Power BI Desktop.

TMDL folder

<model>_tmdl\
database.tmdl
model.tmdl
tables\<TABLE>.tmdl
cultures\en-US.tmdl

Open in Tabular Editor with File > Open > From Folder.

Using the Semantic Interchange (OSI) Generator (Beta)

The OSI generator exports the physical model as a single, full-fidelity schema YAML file suitable for import into a data catalog or other semantic-layer tooling that supports the Open Semantic Interchange format.

To generate OSI output

1. Run the macro and select your physical model and data dictionary.
2. In the Export dialog, select OSI YAML [BETA], and then set the output folder.
3. Click OK.

Output

A single file:

<model>_osi.yaml

It contains one dataset per table (with columns, keys, and column-level metadata such as descriptions, privacy classification, and reference values) plus the relationships between them. The completion message reports the dataset and relationship counts.

Using the DBT Labs Semantic Layer Generator (Beta)

The dbt generator exports the physical model as a dbt schema YAML file (a models: file). It includes the following column-level tests and metadata derived from the model:

• not_null tests for non-nullable columns
• relationships tests derived from foreign keys
• accepted_values tests generated from column reference values (code lists)
• meta blocks carrying model metadata (such as logical names, descriptions, and privacy classification)

To generate dbt output

1. Run the macro and select your physical model and data dictionary.
2. In the Export dialog, select dbt YAML [BETA], and then set the output folder.
3. Click OK.

Output

A single file:

<model>_dbt.yml

See Also

• Printing the Data Model