Show External Tables in data model

Toad Data Modeler
1

I'm reverse engineering from a Redshift database that contains external tables that are based on files in Amazon S3. It looks like the external tables do get pulled into the workspace and are listed under external tables but not able to be pulled onto the model/ diagram to depict them as part of the logical model.

Is there a way to do this?

Thanks in Advance!

2

Hi Vinay,

Unfortunately external tables are unable to display on workspace for now.

Regards,

Mario

3

Hi,
you can review External Tables in Model explorer
image

Regards
Daril

4

Hi Daril & Mario,
Thanks for your replies. I do see all of the tables under the model explorer but it looks they are not part of the ER diagram that is reverse engineered and also are not able to be pulled into the diagram.

A lot of the tables at my client are Redshift external tables on Amazon S3 because of the many benefits and are not able to display the full model.

It is great that they are listed under external tables section but are there any workarounds to get them into the diagram? Essentially being able to depict them similar to normal tables or views on the diagram?

Thanks!
Vinay

5

Can I ask why do you want to place External Tables on Workspaces? Do you want model some link/relationship between External Tables and other objects (e.g. View)? Or do you want only to see visual representation of your External Tables on Worskpace?

Workaround: You can place other objects on workspace instead of External Table which can't be added to workspace - for example object "Label Rectangle" with using name of your external table. We can make macro for you which can automatically add one "Label Rectangle" for each your External Table.

Regards,
Mario

6

Hi Mario,
Yes, the purpose is to both show the relationship with views created on top of those as well as show a visual representation of the facts & dimension tables when the model is documented for analysts. In this current database, almost 80% of the tables are external tables so not having them depicted would be a missed opportunity.

I think the Macro you suggested would definitely help ! But would it have the attributes listed and would I be able to manually draw lines afterwards to link them up visually? There arent any actual foreign keys so this would just be for the logical model.

Thanks for your help!

Vinay

7

Hi Vinay,

I prepared a user package for you. Download file DisplayExternalTables.txg. It is user package with Macro.

How to install user package:

  • Run TDM and switch on expert mode (Main Menu > Settings > Options > General > Expert Mode checkbox)
  • In Main Menu should be appeared new menu item “Expert Mode”.
  • Go to “Main Menu > Expert Mode > Customization > Import Custom Package” and find and import user package “DisplayExternalTables.txg”.
  • After this restart TDM.

In fact this process only copy package to location defined in TDM. By Default it is “C:\Users\USERNAMEXY\Documents\Toad Data Modeler\Standard Installation\Packages{DCB5CB9B-CF65-4350-86B5-285D246FC5AC}”.

Now you can run TDM, open your Redshift model and in Main Menu "Macros > All" select item "Display External Tables on WorkSpace".

For uninstall simply delete this user package in Documents path.

Regards,

Mario

8

You can edit macro in linked user package too.

In Script Explorer (Main Menu > Expert Mode > Customization > Script Explorer) you can find macro "DisplayExternalTablesOnWorkSpace" in path “Macros”. Doubleclick on this macro to open it, then you can edit code and save it by click on "Save" button.

Mario