With input from:
Krithika Raghavan, Senior Director, Oracle Fusion Data Intelligence
Rohan Lobo, Consulting Solutions Architect, Oracle Fusion Data Intelligence
Background
Create your own data dictionary to provide object mapping for functional areas, pillars, subject areas, presentation objects, physical objects, and a column level description of tables in Oracle Autonomous Data Warehouse (ADW).
Introduction
Oracle Fusion Data Intelligence (FDI) is a cloud-native analytics application that features prebuilt analytics, Artificial Intelligence (AI) and Machine Learning (ML) models, which are ready to use, and designed to simplify the process of gaining valuable deep insights from your Oracle Fusion applications to drive better decisions.
Steps to Create a Data Dictionary in FDI
1. Create a dataset in OAC.
2. Select Local Subject Area.
3. Click Manual Query.
4. Copy and paste the following query filtered on ERP and click OK.
SELECT
"CONTENT EXPLORER - SUBJECT AREAS"."RELEASE"."RELEASE CODE",
"CONTENT EXPLORER - SUBJECT AREAS"."APPLICATION"."APPLICATION NAME",
"CONTENT EXPLORER - SUBJECT AREAS"."MODULE"."ACTIVE FLAG",
"CONTENT EXPLORER - SUBJECT AREAS"."MODULE"."MODULE NAME",
"CONTENT EXPLORER - SUBJECT AREAS"."SUBJECT AREA"."SUBJECT AREA NAME",
"CONTENT EXPLORER - SUBJECT AREAS"."PRESENTATION TABLE"."PRESENTATION TABLE NAME",
"CONTENT EXPLORER - SUBJECT AREAS"."PRESENTATION COLUMN"."PRESENTATION COLUMN NAME",
"CONTENT EXPLORER - SUBJECT AREAS"."PRESENTATION COLUMN"."PRESENTATION COLUMN DESCRIPTION",
DESCRIPTOR_IDOF("CONTENT EXPLORER - SUBJECT AREAS"."APPLICATION"."APPLICATION NAME") PILLAR,
DESCRIPTOR_IDOF("CONTENT EXPLORER - SUBJECT AREAS"."MODULE"."MODULE NAME") MODULE_CODE,
DESCRIPTOR_IDOF("CONTENT EXPLORER - SUBJECT AREAS"."SUBJECT AREA"."SUBJECT AREA DESCRIPTION") SUBJECTAREA
FROM "CONTENT EXPLORER - SUBJECT AREAS"
WHERE PILLAR='ERP'
5. Save the dataset and create a workbook.
6. Create a canvas with all the columns.
7. Export the data (.csv) and save the file.
The file is now available in your downloads.
8. Connect to the OAX_USER schema and import the Custom Metadata file that you previously downloaded.
9. Download the ERP, HCM, SCM, and CX data lineage files from the Oracle Help Center.
10. Import the downloaded files into the OAX_USER schema.
11. Use the following code to create the CUSTOM_METADATA materialized view:
CREATE MATERIALIZED VIEW "OAX_USER"."CUSTOM_METADATA" ("PILLAR", "APPLICATIONNAME", "MODULENAME", "SUBJECTAREANAME", "PRESENTATIONTABLENAME", "PHYSICALTABLE", "TABLE_NAME", "TABLE_DESCRIPTION", "COLUMN_NAME", "COL_DESCRIPTION", "PK_COLS", "DATA_TYPE", "LENGTH")
DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255
COLUMN STORE COMPRESS FOR QUERY HIGH ROW LEVEL LOCKING LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DATA"
BUILD IMMEDIATE
USING INDEX
REFRESH FORCE ON DEMAND
USING DEFAULT LOCAL ROLLBACK SEGMENT
USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION DISABLE QUERY REWRITE
AS
WITH TAB_DEF AS (
SELECT
T.TDM_DISPLAY_VERSION,
F.FUNCTIONAL_AREA_CODE,
TRIM(T.TABLE_NAME) TABLE_NAME,
T.DESCRIPTION TABLE_DESCRIPTION,
T.PK_COLS,
TRIM(C.COLUMN_NAME) COLUMN_NAME,
C.DESCRIPTION COL_DESCRIPTION,
C.DATA_TYPE,
C.LENGTH
FROM
DW_WH_COLUMN_DETAILS C,
DW_WH_TABLE_DETAILS T,
DW_DMD_FUNC_AREA_TABLE_MAP F
WHERE
F.DATASOURCE_CODE = 'FUSION'
AND T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_NAME = F.TABLE_NAME
AND T.TDM_DISPLAY_VERSION = (
SELECT
MAX(TDM_DISPLAY_VERSION)
FROM
DW_WH_TABLE_DETAILS
WHERE TDM_DISPLAY_VERSION <> 99.99
)
)
,CON_EXP AS (
SELECT
RELEASECODE,
APPLICATIONNAME,
MODULENAME,
ACTIVEFLAG,
SUBJECTAREANAME,
TRIM(PRESENTATIONTABLENAME) PRESENTATIONTABLENAME,
TRIM(PRESENTATIONCOLUMNNAME) PRESENTATIONCOLUMNNAME,
PRESENTATIONCOLUMNDESCRIPTION,
PILLAR,
MODULE_CODE,
SUBJECTAREA
FROM
CUSTOM_CONTENT_EXPLORER
WHERE
PILLAR='ERP'
AND SUBJECTAREA NOT LIKE 'EBS%'
)
,ERP AS (
SELECT
TRIM(SUBJECTAREA) SUBJECTAREA,
TRIM(PRESENTATIONTABLE) PRESENTATIONTABLE,
TRIM(PRESENTATIONCOLUMN) PRESENTATIONCOLUMN,
TRIM(PHYSICALTABLE) PHYSICALTABLE,
TRIM(PHYSICALCOLUMN) PHYSICALCOLUMN
FROM
CUSTOM_ERP_LINEAGE
)
,ERP_LIN AS (
SELECT
C.RELEASECODE,
C.PILLAR,
C.APPLICATIONNAME,
C.MODULENAME,
C.ACTIVEFLAG,
C.SUBJECTAREANAME,
C.PRESENTATIONTABLENAME,
C.PRESENTATIONCOLUMNNAME,
C.MODULE_CODE,
E.PHYSICALTABLE,
E.PHYSICALCOLUMN
FROM
CON_EXP C,
ERP E
WHERE
C.SUBJECTAREA=E.SUBJECTAREA(+)
AND C.PRESENTATIONTABLENAME=E.PRESENTATIONTABLE(+)
AND C.PRESENTATIONCOLUMNNAME=E.PRESENTATIONCOLUMN(+)
)
SELECT DISTINCT
C.PILLAR,
C.APPLICATIONNAME,
C.MODULENAME,
C.SUBJECTAREANAME,
C.PRESENTATIONTABLENAME,
C.PHYSICALTABLE,
T.TABLE_NAME,
T.TABLE_DESCRIPTION,
T.COLUMN_NAME,
T.COL_DESCRIPTION,
T.PK_COLS,
T.DATA_TYPE,
T.LENGTH
FROM
ERP_LIN C,
TAB_DEF T
WHERE
1=1
AND C.PHYSICALTABLE= T.TABLE_NAME(+)
12. Create a connection in OAC.
13. Provide the connection details, upload the wallet file, and click Save.
14. Create a dataset using the Data Dictionary connection.
15. Use the following code in Manual Query, click OK, and save the dataset.
SELECT PILLAR, APPLICATIONNAME, MODULENAME, SUBJECTAREANAME, PRESENTATIONTABLENAME, PHYSICALTABLE, TABLE_NAME, TABLE_DESCRIPTION, COLUMN_NAME, COL_DESCRIPTION, PK_COLS, DATA_TYPE, LENGTH FROM CUSTOM_METADATA
16. Create a workbook with the required columns.
17. Repeat all the previous steps for SCM, HCM, and CX to create the data dictionary.
Summary
This article presents an approach to create a data dictionary to provide mapping of objects for functional areas, pillars, subject areas, and presentation objects to physical objects with column level description of tables in ADW.
References
- For additional information, see the blog article authored by Abhishek Bajpai and Navnit Mishra, Creating your own metadata dictionary in FDI.
- To learn more, see the FDI documentation.
Call to Action
Now that you’ve read this post, try it yourself and let us know your results in the Oracle Analytics Community, where you can also ask questions and post ideas.