I am trying to create a data dictionary for business users in Incorta, it should give details of all the business schemas and the filed names, label, description and source column/formula. Is there any inbuilt Audit dashboard or Incorta tables from where I can pull this data?
I did used the inspector tool and been able to build a dashboard which gives details about the business schema name, folders, views and columns in it with source tables.
But by default it gives all the business schemas, is it possible to filter business schemas based on roles granted to logged in user?
I can get the logged in user with $user but not sure how do I filter business schema names to only show where the user has read access?
Incorta Metadata can be loaded into incorta by using the incorta metadata database as the data source.
1. Business schema and physical schema are stored in an Incorta metadata table "SCHEMA". You can use this formula column to filter schema.
if(IncortaMetadata.SCHEMA.SCHEMATYPE=0, 'Physical Schema', 'Business Schema')
2. User permission to a schema is stored in Incorta metadata table "PERMISSION".
When PERMISSION.CONTENTTYPE=5, the record in the PERMISSION table is created for sharing a schema, and the PERMISSION.CONTENTID will store the SCHEMA ID. Create a formula column called SCEHMA_ID to join to the above table.
int(if(IncortaMetadata.USER_PERMISSION.CONTENTTYPE=5, IncortaMetadata.USER_PERMISSION.CONTENTID, -1))
PERMISSION.CODE describes what kind of sharing to the user or a group. Here is a formula column can be used:
case(IncortaMetadata.USER_PERMISSION.CODE=1,'Can View' ,IncortaMetadata.USER_PERMISSION.CODE=3,'Can Share' ,IncortaMetadata.USER_PERMISSION.CODE=7,'Can Edit',IncortaMetadata.USER_PERMISSION.CODE=-1,'Permission revoked','')
PERMISSION.DESTINATIONTYPE=0 means that it is shared with a user, and PERMISSION.DESTINATIONID is a user ID.
PERMISSION.DESTINATIONTYPE=1 means that it is shared with a group, and PERMISSION.DESTINATIONID is a group ID.
3. The following MV expose the group membership to the user level for those schema that are shared via groups.
select P.ID , P.CREATIONDATE , P.CONTENTTYPE , P.CONTENTID , P.CODE , P.CREATORID , CASE WHEN P.DESTINATIONTYPE=0 THEN 'DIRECT USER' ELSE 'VIA GROUP' END SHARED_VIA , CASE WHEN P.DESTINATIONTYPE=0 THEN NULL ELSE P.DESTINATIONID END SHARED_VIA_GROUPID , CASE WHEN P.DESTINATIONTYPE=0 THEN P.DESTINATIONID ELSE GU.USERID END SHARED_WITH_USERID from IncortaMetadata.PERMISSION P left join IncortaMetadata.GROUP_USER GU on P.DESTINATIONID = GU.GROUPID AND P.DESTINATIONTYPE=1