Business Case

Metadata can assist you with defining the scope and characteristics of data elements, as well as the rules for their usage and application. This article provides you with the steps and framework to build a FDI metadata dictionary of your own.

The Approach

1. Create an OAC database connection

Navigate to the data pane in FDI, click Create, and then click Connection.

Select connection
Select connection

Select the database wallet file and fill in the details. 

Create connection
Create connection

2. Create dataset

After you set up the connection, click Create, and then click Dataset.

create_ds
create_ds

Choose the database connection that you created earlier and select Manual Query.

New dataset
New dataset

Enter the following query:

SELECT
    t.tdm_display_version,
    f.functional_area_code,
    t.table_name,
    t.description table_description,
    t.pk_cols,
    c.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
    )

You can test this query in SQL Developer. This dataset provides the table level details such as column name, primary key, and column description. 

SQL developer
SQL developer

Refresh the query in the dataset to view the dataset with table-level details.

create_ds_pillar
create_ds_pillar

Click OK to save the dataset.

Save dataset
Save dataset

You can use the following logical query to create pillar-wise subject area details. This query uses the prebuilt subject areas.

SELECT
   "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 Description" ,
   "Content Explorer - Subject Areas"."Subject Area"."Subject Area Name" ,
   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,
  "Content Explorer - Subject Areas"."Release"."Release Code"
FROM "Content Explorer - Subject Areas"
ORDER BY 9 DESC  NULLS LAST
FETCH FIRST 500001 ROWS ONLY

Similarily, you need to now create the dataset for the FDI pillar and subject area-level details. You can use the above query to create the dataset. 

logical_query_ds
logical_query_ds

Join the two datasets as follows on a common join attribute module code: 

join_ds
join_ds

3. Schedule the dataset

In this example, the dataset is scheduled to start at a specific time and you must create this per the FDI load cycle. Monitor your daily incremental data load timings and identify a time which is safely outside the zone of load window for the schedule. 

Schedule DS
Schedule DS

4. Create the reports

You can create reports as follows:

In this visualization, the first tab displays the table-level details from the first dataset.

db_page_1
db_page_1

Similarily, you can create a second workbook canvas that provides navigation from the pillar to subject area level details.

DB_page_2
DB_page_2

Call to Action

This article presents steps to create a metadata dictionary on ADW tables residing in FDI. To learn more, view the product documentation, view additional Oracle Analytics articles at blogs.oracle.com/analytics, and follow us on Twitter@OracleAnalytics.