Importing tables and data modeling
- ⚠ Desktop Edition
- ✔ Business Edition
- ✔ Enterprise Edition
This article describes how to use the Table Import Wizard of Tabular Editor 3, to add new tables to the model. There is also a section on how to update the table schema of an existing table. Lastly, we cover how to use the diagram tool to define and edit relationships between tables.
Table Import Wizard
Tabular Editor 3 includes a Table Import Wizard that helps you create a data source in your model, and import tables/views from relational data sources such as a SQL Server database.
Types of TOM Data Sources
Depending on your version of Analysis Services, there are different ways of defining data sources within the model metadata:
- Provider (aka. Legacy): Available in every version of Analysis Services and every compatibility level. Supports a limited range of sources, mostly relational through OLE DB/ODBC drivers. Partitions are usually defined using a SQL statement which is executed natively against the source. Credentials are managed in the Provider Data Source object in the Tabular Object Model and stored and encrypted server-side.
- Structured (aka. Power Query): Available since SQL Server 2017 (compatibility level 1400+). Supports a wider range of data sources than Legacy providers. Partitions are usually defined using M (Power Query) expressions. Credentials are managed in the Structured Data Source object in the Tabular Object Model and needs to be specified upon every deployment to Analysis Services.
- Implicit data sources: Exclusively used by Power BI datasets. No explicit Data Source object is created in the model. Instead, the M (Power Query) expression implicitly defines the data source. Credentials are not stored in the Tabular Object Model, but managed by Power BI Desktop or the Power BI Service.
Note
The Table Import Wizard and Update Table Schema feature of Tabular Editor 2.x only supports Legacy data sources with SQL partitions. In other words, there are no support for Power Query partitions. For this reason, Legacy data sources are usually recommended, as they provide the highest level of interoperability between the developer tools.
Importing new tables
When importing tables (Model menu > Import tables...), Tabular Editor presents you with the options mentioned above (for creating a new data source), as well as a list of data sources already present in the model. Avoid creating new data sources if the tables you want to import are available in one of the data sources already specified in the model.
Tip
A tabular model is generally regarded as an in-memory optimized semantic cache of a relational data warehouse. For this reason, a model should ideally only contain a single data source which would point to a SQL-based data warehouse or data mart.
Creating a new data source
If you need to create a new data source, Tabular Editor provides you with a list of supported data sources:
Note that Analysis Services and Power BI in particular supports a much wider range of data sources, however the sources listed in the screenshot above are the ones that Tabular Editor is able to connect for the purpose of automatically importing table metadata (that is, column names and data types). For data sources not on this list, Tabular Editor 3 can still update table schema by utilising Analysis Services.
Currently, the following data sources are natively supported by Tabular Editor 3:
- SQL Server databases
- Azure SQL databases
- Azure Synapse Analytics (SQL pool and Serverless SQL pool)
- Oracle
- ODBC
- OLE DB
- Snowflake*
- Power BI Dataflow*
- Databricks*
- Fabric OneLake (Direct Lake)*
*=These data sources are only supported as implicit data sources in Power BI data models. They are not available in SSAS / Azure AS.
After choosing one of the data sources on the list, Tabular Editor displays a connection details dialog, allowing you to specify server addresses, credentials, etc. specific to the data source you want to create. The settings that you specify should be those that Tabular Editor should use for establishing a local connection to the source. These settings are saved in your User options (.tmuo) file.
If you want Analysis Services to use different credentials when connecting, you can specify that by editing the data source properties of the Tabular Object Model after importing the tables.
Choosing objects to import
Once your data source has been defined, you get the option of choosing tables/views from a list, or specifying a native query to be executed against the source.
If you select the first option, Tabular Editor will connect to the source and display a list of tables and views that you can preview on the next page:
You can import multiple tables/views at once by checking them on the left side. For each table/view, you may deselect/select columns to import.
Tip
If you are in control of the source, we recommend to always create a view on top of the tables you wish to import. In the view, make sure to correct any names, spellings, etc. to be used in the tabular model, and get rid of any columns not needed by the tabular model (system columns, timestamps, etc.).
Then, in the model, import all columns from this view (basically generating a SELECT * FROM ... statement). This makes maintenance easier, as only need to run a Schema Update in Tabular Editor to determine if anything was changed in the source.
If you change the preview mode to "Schema only" using the dropdown in the top left corner, it is possible to change the imported data type and column name for every source column. This may be useful for example if your source using floating-point values, but you want the data to be imported as fixed-decimal.
On the last page, confirm your selection and choose which type of partitions to create. For provider data sources, the default type of partition to be created is SQL, where as for structured data sources, it is M.
At this point, you should see your tables imported with all columns, data types and source column mappings applied:
Updating table schema
If columns are added/changed in the source, or if you recently modified a partition expression or query, you can use Tabular Editor's Update table schema feature to update the column metadata in your model.
This menu item can be invoked at the model level, as well as on a collection of tables or even individual table partitions.
When using this option, Tabular Editor will connect to all the relevant data sources (prompting for credentials as needed), in order to determine if new columns need to be added or existing column modified or removed.
Important
If a column that was previously imported to your tabular model has been removed or renamed in the source, you must update the table schema in your tabular model. Otherwise data refresh operations may fail.
In the screenshot above, Tabular Editor detected a few new columns, a single data type change, and two columns that were renamed in the source. Note that detection of a column rename only works for simple changes. In other cases, a name change usually results in Tabular Editor detecting a column removal and a column addition, which is the case for the Tax Amount column below, which seems to have been renamed to TaxAmt in the source.
To avoid breaking existing DAX formulas that rely on the [Tax Amount] column, you can hold down the Ctrl-button and click on the two rows in the Schema Change dialog, then right-click in order to combine the column removal and column addition into a single SourceColumn update operation:
If you do not want the name change to be propagated to the imported column (but only want to update the SourceColumn property to reflect the changed name in the data source), you can deselect the Name update operation in the dropdown:
Updating table schema through Analysis Services
By default, Tabular Editor 3 attempts to connect directly to the data source, for purposes of updating the imported table schema. Naturally, this only works when the data source is one that is supported by Tabular Editor 3. If you need to update the schema of a table imported from a data source that is not supported by Tabular Editor 3, you can enable the Use Analysis Services for change detection option under Tools > Preferences > Schema Compare.
When this option is enabled and Tabular Editor 3 is connected to Analysis Services or the Power BI XMLA endpoint, you can update the schema of tables imported from any data source supported by Analysis Services or Power BI.
Note
The Use Analysis Services for change detection option only works while Tabular Editor 3 is connected to Analysis Services or the Power BI XMLA endpoint. For this reason, we recommend that developers always use the Workspace Mode when developing models.
When the Use Analysis Services for change detection option is enabled, Tabular Editor 3 will use the following technique when a schema update is requested:
- A new transaction is created against the connected Analysis Services instance
- A new temporary table is added to the model. This table uses a Power Query partition expression that returns the schema of the original expression, of which a schema update was requested. This is done using the
Table.SchemaM function. - The temporary table is refreshed by Analysis Services. Analysis Services takes care of connecting to the data source in order to retrieve the updated schema.
- Tabular Editor 3 queries the content of the temporary table to obtain the schema metadata.
- The transaction is rolled back, leaving the Analysis Services database or Power BI dataset in the original state it was in before step 1.
- Tabular Editor 3 displays the "Apply Schema Changes" dialog as shown above, in case there are any schema changes.
Using this technique, Tabular Editor 3 makes it possible to import and update tables from data sources otherwise not supported.
Importing new tables through Analysis Services
In order to import a table from a data source otherwise not suported, you can simply copy an existing table from that data source, modify the M expression on the partition query of the copied table, then save your changes to the workspace database and update the table schema as described above.
Working with diagrams
In Tabular Editor 3, diagrams are documents that can be used to visualize and edit the relationships between tables in the model. You can create as many diagrams as you want to visualize certain areas of your model. A diagram can be saved as a stand alone file. See Supported file types for more information.
Note
We recommend creating multiple smaller diagrams over few large diagrams. When a diagram contains more than 20 or so tables, it quickly becomes overwhelming and difficult to understand.
After loading a model in Tabular Editor 3, choose the File > New > Diagram menu option to create a new diagram.
Adding tables
Add initial tables to the diagram in any of the following ways:
- (Multi-)select tables in the TOM Explorer, then right-click and choose Add to diagram.
- (Multi-)select tables in the TOM Explorer, then drag the tables over to the diagram
- Use the Diagram > Add tables... menu option, and (multi-)select the tables you want to add through the dialog box.
To add additional tables to the diagram, use the technique above again, or right-click on an existing table in the diagram and choose one of the following options:
- Add tables that filter this table: Adds all tables to the diagram which may, directly or indirectly through other tables, filter the currently selected table. Useful when starting from a fact table.
- Add all related tables: Adds all tables to the diagram which are directly related to the currently selected table. Useful when starting from a dimension table.
Before proceeding, rearrange and resize the tables in the diagram to suit your preferences, or use the Diagram > Auto-arrange feature to have Tabular Editor 3 lay out the tables automatically.
Modifying relationships using the diagram
To add a new relationship between two tables, locate the column on the fact table (many-side) of the relationship, and drag that column over to the corresponding column on the dimension table (one-side). Confirm the settings for the relationship and hit OK.
To edit an existing relationship, right-click on it and choose Edit relationship. The right-click menu also contains shortcuts for reversing or deleting a relationship, as shown on the screenshot below.
Note
You can also create relationships without using a diagram, through the TOM Explorer. Locate the column from which the relationship should start (many-side / fact-table side), right-click and choose Create > Relationship from. Specify the destination column in the Create Relationship dialog that appears on the screen.
Saving a diagram
To save a diagram, simply use the File > Save (CTRL+S) option. Tabular Editor 3 will prompt you to save the diagram if you close the document or the application while the diagram has unsaved changes.
Tip
The same diagram file can be loaded for different data models. Diagrams reference tables by their names. Any tables not present in the model upon diagram load are simply removed from the diagram.
Note
Every time you add or modify a relationship, you will have to run a "calculate" refresh on the data model, before the relationships can be used when querying the model.