Finance | Project Operations, Human Resources, ...

Create a custom table with the data from SQLDICTIONARY

(0) Share

Report

Posted on by AstridMalanka

449

Hello,

I need the table SQLDICTIONARY to be exported to the lake but since it is a system table, I understand it cannot be exported. And it is needed on some entities schema.

How can I create a custom table that will get the data from SQLDICTIONARY, since the table id is not the same in every environment?

Thanks

All responses (3)

Answers (0)

AstridMalanka 449 on at

Like (0)

Report

RE: Create a custom table with the data from SQLDICTIONARY

also, now that I think about it, I am not sure it will work, even if I manage to create the entity.

Data Lake exports tables and creates views for the entity schema, so it wont be exporting the table anyway because it is a system table.
AstridMalanka 449 on at

Like (0)

Report

RE: Create a custom table with the data from SQLDICTIONARY

Hi,

I did something similar for inventory transaction table, never thought about doing it for a system table, I can try that.

The reason is that we are moving all our systems to F&O and we are exporting the entities to the lake to merge with our current data, but some entities have views that need that sql table.

For example I got errors building the following views.

INVENTITEMPRICEID

INVENTITEMPRICESIMID

and the reason:

Invalid object name 'SQLDICTIONARY'.
Suggested answer

André Arnaud de Cal... 291,391 Super User 2024 Season 2 on at

Like (0)

Report

RE: Create a custom table with the data from SQLDICTIONARY

Hi Astrid,

Can you explain the business requirement for having table IDs in a Data Lake? What is the underlying reason? What data is exported with table IDs in a column?

There is a workaround to get system tables in a data entity. First create an entity using the wizard with the naming you need for the system table, but use e.g. the physical table CustTable. When the wizard is completed, you can remove all fields and datasources and build the data entity with any table you need. Be aware that if there is no natural key, you would need to use it for export purposes only. Also rebuild the staging table when you changed the contents of the data entity.