Relevant useful links
Columns for a specific table or model
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, POSITION FROM SYS.COLUMNS where SCHEMA_NAME = '_SYS_BIC' and TABLE_NAME = '???' order by position;
An alternative in case of authorization problems:
SELECT SCHEMA_NAME, VIEW_NAME, POSITION, COLUMN_NAME, DATA_TYPE_NAME, LENGTH, SCALE FROM VIEW_COLUMNS WHERE SCHEMA_NAME = '_SYS_BIC' AND VIEW_NAME = '???' ORDER BY POSITION;
Inactive models
How-To see inactive models to be reactivated… To fix the issue, please redeploy the model.
SELECT package_id, object_name, object_suffix, activated_at FROM "_SYS_REPO"."ACTIVE_OBJECT" WHERE object_status != 0 AND (package_id LIKE '???.%' OR package_id LIKE '???.%') ORDER BY package_id;
SELECT * FROM views WHERE is_valid = 'FALSE' AND comments IS NOT NULL AND view_name LIKE '???%' AND view_name NOT LIKE '???%' ORDER BY view_name;
The relevant tables
To get all the tables, use SQL:
SELECT * FROM "SYS"."TABLES";
Schema | Table | Note |
---|---|---|
_SYS_REPO | ACTIVE_OBJECT | Main data for all objects |
_SYS_REPO | ACTIVE_OBJECTCROSSREF | Connection between objects but not between nodes |
_SYS_BI | BIMC_ALL_CUBES | All cubes |
_SYS_BI | BIMC_DIMENSIONS | All fields |
History
To get historical data for the models, please use table: OBJECT_HISTORY. The XML version of the model is available in the field: CVERSION. The SQL to get the historical version can be for example following:
SELECT * FROM "_SYS_REPO"."OBJECT_HISTORY" WHERE package_id = '<package ID>' AND object_name = '<model name>' ORDER BY version_id;