Query PostgreSQL datamart based on flex fields

Create query scripts to query and extract the required information from the PostgreSQL datamart. Make sure these scripts are available to any user (including users with read-only permission) who wants to execute these scripts. This topic lists the functions and sample queries for a few specific use cases.

Important:
  • Log into TeamForge as a Reporting user and run the queries. Note that this is a one-time process.
  • Use the following flex field functions by joining the flex_field_dimension, artifact_flex_field and artifact_transaction_fact (or) artifact_daily_snapshot_fact tables as it depends on the XML data and flex field key generated by the platform.

Sample use cases and queries

Note:
  • Flex field Name, Value, Tracker title and so on that are used in the filtering conditions are case-sensitive.
  • See Case 7 below for building a case-insensitive search using upper() or lower() functions.
Use case 1: Filter Date and Text flex fields
Suppose you want to retrieve data based on the following assumptions:
  • Date flex field named 'CreatedDate' that has a value of '2015-09-02 00:00:00'
  • Text flex field named 'Text102' with part of its value containing the pattern 'soft'
  • Tracker title is 'Tracker101'
Note:
  • You can change the field name, tracker title and values based on the data in your system
  • The date passed as input should be of the format “YYYY-MM-DD HH24:MI:SS”
Sample query
select 
	t.Date as Date,
	t.Project as Project,
	t.Tracker as Tracker,
	t.Planing_folder as Planing_folder,
	'P'||t.Priority as Priority,
	count(distinct t.artifact_key) 
from 

(            select 		b.date_of_trans as Date,
				e.title as Project,c.title as Tracker, 
				d.title as Planing_folder,
				'P'||a.priority as Priority,
				get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) as TextFlexField,
				a.artifact_key
from 
				artifact_transaction_fact a, 
				date_dimension b, 
				artifact_flex_fields ff, 
				tracker_dimension c, 
				pf_dimension d, 
				project_dimension e ,
				flex_field_dimension ffd
where 
				a.trans_date_key=b.date_key 
				and ff.artifact_flex_fields_key = a.flex_fields_key 
				and a.tracker_key=c.tracker_key 
				and a.pf_key=d.pf_key 
				and a.project_key=e.project_key 
				and a.tracker_key=ffd.tracker_key
				and e.title='Project SCD-2 Test'
				and ffd.name in ('Text102')
				and c.title in ('Tracker101') 
				and get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) like 'soft%'

union  

	  select 		b.date_of_trans as Date,
				e.title as Project,c.title as Tracker, 
				d.title as Planing_folder,
				'P'||a.priority as Priority,
				regexp_split_to_table(get_artifact_date_value(ff.flex_fields, ffd.flex_field_key),E',') as DateFlexField,
				a.artifact_key
	
from 
				artifact_transaction_fact a, 
				date_dimension b, 
				artifact_flex_fields ff, 
				tracker_dimension c, 
				pf_dimension d, 
				project_dimension e ,
				flex_field_dimension ffd
where 
				a.trans_date_key=b.date_key 
				and ff.artifact_flex_fields_key = a.flex_fields_key 
				and a.tracker_key=c.tracker_key 
				and a.pf_key=d.pf_key 
				and a.project_key=e.project_key 
				and a.tracker_key=ffd.tracker_key
				and e.title='Project SCD-2 Test'
				and ffd.name in ('CreatedDate')
				and c.title in ('Tracker101') 
				and  get_artifact_date_value(ff.flex_fields, ffd.flex_field_key)='2015-09-02 00:00:00') as t
group by 1,2,3,4,5 
order by 1,2,3,4,5;
Use case 2: Filter Single-select flex fields
Suppose you want to retrieve data based on the following assumptions:
  • Flex field name: 'FSS1'
  • Value selected or stored: 'S1'
  • Tracker title: 'Tracker2'
Note:
  • You can change the field name, tracker title and values based on the data in your system
Sample query
select
		b.date_of_trans as Date,
		pd.title as Project,
		td.title as Tracker,
		d.title as Planning_folder,
		'P'||a.priority as Priority ,
		get_artifact_select_value(aff.flex_fields,ffd.flex_field_key) as SingleSelectFlexField,
		count(distinct a.artifact_key) TotalArtifacts 
from
		artifact_transaction_fact a,
		date_dimension b,
		artifact_flex_fields aff,
		project_dimension pd ,
		tracker_dimension td,
		pf_dimension d, 
		flex_field_dimension ffd 
where 
		a.trans_date_key=b.date_key 
		and a.flex_fields_key=aff.artifact_flex_fields_key
		and a.tracker_key=td.tracker_key
		and a.project_key=pd.project_key
		and td.tracker_key=ffd.tracker_key
		and a.pf_key=d.pf_key 
		and pd.title='ProjectTestFunctionSCD-2'
		and td.title in ('Tracker2')
		and ffd.name='FSS1'
		and get_artifact_select_value(aff.flex_fields,ffd.flex_field_key)='S1' 
		--and date(a.effective_from)<=date(now()) and date(a.effective_till)>'2012-04-01' 
group by 1,2,3,4,5,6
order by 1,2,3,4,5,6;
Use case 3: Filter Text flex fields
Suppose you want to retrieve data based on the following assumptions:
  • Flex field name: 'Text101'
  • Value contains: 'hello'
  • Tracker name: 'Tracker101'
Note:
  • You can change the field name, tracker title and values based on the data in your system
Sample query
select
   	 b.date_of_trans as Date,
		e.title as Project,
		c.title as Tracker, 
		d.title as Planing_folder,
		'P'||a.priority as Priority,
		get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) as TextFlexField,
		count(distinct a.artifact_key) Artifacts
from 
		artifact_transaction_fact a, 
		date_dimension b, 
		artifact_flex_fields ff, 
		tracker_dimension c, 
		pf_dimension d, 
		project_dimension e ,
		flex_field_dimension ffd
where 
		a.trans_date_key=b.date_key 
		and ff.artifact_flex_fields_key = a.flex_fields_key 
		and a.tracker_key=c.tracker_key 
		and a.pf_key=d.pf_key 
		and a.project_key=e.project_key 
		and a.tracker_key=ffd.tracker_key
		and e.title='Project SCD-2 Test'
		and ffd.name = 'Text101'
		and c.title in ('Tracker101') 
		and get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) like 'hello%' 
		--and date(a.effective_from)<=date(now()) and date(a.effective_till)>'2012-04-01' 
group by 1,2,3,4,5,6 
order by 1,2,3,4,5,6;
Use case 4: Filter Date flex fields
Suppose you want to retrieve data based on the following assumptions:
  • Flex field name: 'CreatedDate'
  • Value: '2015-09-02 00:00:00'
  • Tracker name: 'Tracker101'
Note:
  • You can change the field name, tracker title and values based on the data in your system
Sample query
select
		b.date_of_trans as Date,
		e.title as Project,
		c.title as Tracker, 
		d.title as Planing_folder,
		'P'||a.priority as Priority,
		regexp_split_to_table(get_artifact_date_value(ff.flex_fields, ffd.flex_field_key),E',') as DateFlexField,
		count(distinct a.artifact_key)
	
from 
		artifact_transaction_fact a, 
		date_dimension b, 
		artifact_flex_fields ff, 
		tracker_dimension c, 
		pf_dimension d, 
		project_dimension e ,
		flex_field_dimension ffd
where 
		    a.trans_date_key=b.date_key 
		    and ff.artifact_flex_fields_key = a.flex_fields_key 
		    and a.tracker_key=c.tracker_key 
		    and a.pf_key=d.pf_key 
		    and a.project_key=e.project_key 
		    and a.tracker_key=ffd.tracker_key
		    and e.title='Project SCD-2 Test'
		    and ffd.name in ('CreatedDate')
		    and c.title in ('Tracker101') 
		    and  get_artifact_date_value(ff.flex_fields, ffd.flex_field_key)='2015-09-02 00:00:00'

group by  1,2,3,4,5,6
order by 1,2,3,4,5,6;
Use case 5: Multi-select flex fields
Suppose you want to retrieve data based on the following assumptions:
  • Multi-select flex field name: 'Country'
  • Value: 'Russia,India'
  • Tracker name: 'TrackerN'
  • Conditional parameter: 'ALL'
Note:
  • You can change the field name, tracker title and values based on the data in your system
  • Flex field name, value and tracker title that are used in the SQL filter conditions are case sensitive
  • If you want to select all the values in User flex field, then pass 'ALL' as the conditional parameter
  • If you want to select any value, then pass 'ANY' as the conditional parameter
Sample query
select b.date_of_trans as Date, e.title as Project,c.title as Tracker, 
d.title as Planing_folder,'P'||a.priority as Priority,
 get_artifact_multiselect_value(ff.flex_fields,ffd.flex_field_key,'{Russia,India}','ALL') as MultiselectFlexField,
count(distinct a.artifact_key) Artifacts
--,a.artifact_key
from 
    artifact_transaction_fact a, 
    date_dimension b, 
    artifact_flex_fields ff, 
    tracker_dimension c, 
    pf_dimension d, 
    project_dimension e ,
    flex_field_dimension ffd
where 
    a.trans_date_key=b.date_key 
    and ff.artifact_flex_fields_key = a.flex_fields_key 
    and a.tracker_key=c.tracker_key 
    and a.pf_key=d.pf_key 
    and a.project_key=e.project_key 
    and a.tracker_key=ffd.tracker_key
    --and e.title='TestMultiFunction'
    and ffd.name = 'Country'
    and c.title in ('TrackerN') 
    and get_artifact_multiselect_value(ff.flex_fields,ffd.flex_field_key,'{Russia,India}','ALL')!='' 
    --and date(a.effective_from)<=date(now()) and date(a.effective_till)>'2012-04-01' 
group by 1,2,3,4,5,6 
order by 1,2,3,4,5,6;

Use case 6: Filter User flex fields
Suppose you want to retrieve data based on the following assumptions:
  • User flex field name: 'Select User'
  • Value: 'user1,user2'
  • Tracker name: 'TrackerN'
  • Conditional parameter: 'ALL'
Note:
  • You can change the field name, tracker title and values based on the data in your system
  • Flex field name, value and tracker title that are used in the SQL filter conditions are case sensitive
  • If you want to select all the values in User flex field, then pass 'ALL' as the conditional parameter
  • If you want to select any value, then pass 'ANY' as the conditional parameter
Sample query
select b.date_of_trans as Date, e.title as Project,c.title as Tracker, 
d.title as Planing_folder,'P'||a.priority as Priority,
 get_artifact_user_value(ff.flex_fields,ffd.flex_field_key,'{user1,user2}','ALL') as UserFlexField,
count(distinct a.artifact_key) Artifacts
--,a.artifact_key
from 
    artifact_transaction_fact a, 
    date_dimension b, 
    artifact_flex_fields ff, 
    tracker_dimension c, 
    pf_dimension d, 
    project_dimension e ,
    flex_field_dimension ffd
where 
    a.trans_date_key=b.date_key 
    and ff.artifact_flex_fields_key = a.flex_fields_key 
    and a.tracker_key=c.tracker_key 
    and a.pf_key=d.pf_key 
    and a.project_key=e.project_key 
    and a.tracker_key=ffd.tracker_key
    --and e.title='TestMultiFunction'
    and ffd.name = 'Select User'
    and c.title in ('TrackerN') 
    and get_artifact_user_value(ff.flex_fields,ffd.flex_field_key,'{user1,user2}','ALL')!='' 
    --and date(a.effective_from)<=date(now()) and date(a.effective_till)>'2012-04-01' 
group by 1,2,3,4,5,6 
order by 1,2,3,4,5,6;

Use case 7: Filter Text flex fields, case-insensitive search
Suppose you want to retrieve data based on the following assumptions:
  • Flex field name: 'Text102'
  • Value contains: 'soft'
  • Tracker name: 'Tracker101'
Note:
  • You can change the field name, tracker title and values based on the data in your system
  • The upper() function can be replaced with the lower() function appropriately for case-insensitive search
Sample query
select
        		b.date_of_trans as Date,
				e.title as Project,
				c.title as Tracker, 
				d.title as Planing_folder,
				'P'||a.priority as Priority,
				get_artifact_text_value(ff.flex_fields,ffd.flex_field_key) as TextFlexField,
				count(distinct a.artifact_key) as TotalCounts
from 
				artifact_transaction_fact a, 
				date_dimension b, 
				artifact_flex_fields ff, 
				tracker_dimension c, 
				pf_dimension d, 
				project_dimension e ,
				flex_field_dimension ffd
where 
				a.trans_date_key=b.date_key 
				and ff.artifact_flex_fields_key = a.flex_fields_key 
				and a.tracker_key=c.tracker_key 
				and a.pf_key=d.pf_key 
				and a.project_key=e.project_key 
				and a.tracker_key=ffd.tracker_key
				and UPPER(e.title)=UPPER('Project SCD-2 Test')
				and UPPER(ffd.name)=UPPER('Text102')
				and UPPER(c.title) = UPPER('Tracker101')
				and UPPER(get_artifact_text_value(ff.flex_fields,ffd.flex_field_key)) like UPPER('soft%')

group by 1,2,3,4,5,6
order by 1,2,3,4,5,6;