Clarity

Expand all | Collapse all

Datamart Tables

Jump to Best Answer
  • 1.  Datamart Tables

    Posted 08-14-2015 12:23 PM

    I'm wondering if anyone has a list of all the tables that are populated from the Datamart Extraction Job.

    This is the list that I have compiled based on my research, but I was wondering if I am missing any other tables.

     

    NBI_DIM_OBS

    NBI_DIM_FISCAL_TIME

    NBI_DIM_CALENDAR_TIME

    NBI_DIM_OBS_FLAT

    NBI_PRT_FACTS

    NBI_PROJECT_CURRENT_FACTS

    NBI_R_FACTS

    NBI_ROLLUP_SQL

    NBI_PM_PT_FACTS

    NBI_FM_PT_FACTS

    NBI_RT_FACTS

    NBI_PM_PROJECT_TIME_SUMMARY

    NBI_FM_PROJECT_TIME_SUMMARY

    NBI_RESOURCE_TIME_SUMMARY



  • 2.  Re: Datamart Tables
    Best Answer

    Posted 08-14-2015 02:40 PM

    The list of tables looks good.

    In addition to the tables, here is a query that will give you a list of portlets that reference the NBI tables.

    I used this on an Oracle database for 14.2 and it shows portlets that have queries with these tables. 

    If you have any user-defined queries that includes the text 'NBI' , these portlets will be listed as well. 

     

     

     

    select p.portlet_code as portlet_code,

    n.name as portlet_name,

    gg.query_code as nsql_code,

    'grid' as portlet_type

    from cmn_portlets p

    join cmn_captions_nls n

    on n.language_code = 'en'

    and n.table_name = 'CMN_PORTLETS'

    and n.pk_id = p.id

    join cmn_grids g

    on p.id = g.portlet_id

    join cmn_gg_nsql_queries gg

    on gg.cmn_nsql_queries_id = g.dal_id

    and g.dal_type = 'nsql'

    and g.principal_type = 'SYSTEM'

    join cmn_nsql_queries q

    on gg.cmn_nsql_queries_id = q.id

    and upper(q.nsql_text) like '%NBI%'

    union all

    select p.portlet_code as portlet_code,

    n.name as portlet_name,

    gg.query_code as nsql_code,

    'graph' as portlet_type

    from cmn_portlets p

    join cmn_captions_nls n

    on n.language_code = 'en'

    and n.table_name = 'CMN_PORTLETS'

    and n.pk_id = p.id

    join cmn_graphs g

    on p.id = g.portlet_id

    join cmn_gg_nsql_queries gg

    on gg.cmn_nsql_queries_id = g.dal_id

    and g.dal_type = 'nsql'