Available Objects and Elements

1. Homepage elements

1.1. Available elements on the Homepage (per User)

USE CASE:

  • Retrieve available elements on the Homepage per User
select d.user_id as user_id, u.username as username, count(element_id) as number_of_available_elements_on_HP
from user_dashboard_element_instance d
join user u on u.user_id=d.user_id
where in_dashboard_ind_flag = 'Y'
group by 1;
1.2. Available elements on the Homepage (per User by User types)

USE CASE:

  • Retrieve available elements on the Homepage per User (by User type)
select case is_administrator_ind and is_power_user_ind
when is_administrator_ind = 'Y' then 'Administrator'
when is_power_user_ind ='Y' then 'Power User'
else 'Regular User'
end as usertype, d.user_id as user_id,
u.username,
count(element_id) as number_of_available_elements_on_HP
from user_dashboard_element_instance d
join user u on u.user_id=d.user_id
where in_dashboard_ind_flag = 'Y'
group by 2;
1.3. All available elements on the Homepage (by count of all Users)

USE CASE:

  • Retrieve the count of all available elements on the Homepage (by count of all Users)
select (select count(element_id)from user_dashboard_element_instance where in_dashboard_ind_flag = 'Y')/count(user_id)
from user_dashboard_element_instance
1.4. Homepage search and the number of returned Tiles (per query)

USE CASE:

  • Retrieve data on Homepage searches and the count of Tiles that were returned by search queries
  • Table homepage_search is used to track searches performed by Users on the Homepage
  • Table homepage_search contains the following: homepage search id, user id, search text, the time of search and the number of Tiles returned
SELECT * 
FROM homepage_search 
ORDER by homepage_search_id DESC 
limit 100;

2. Favorites

2.1. List of Favorite elements

USE CASE:

  • Retrieve a list of Favorite elements
  • Fetch a list of Favorites for each user
  • Recommendation: build a Report
SELECT u.user_id AS User_ID, u.username AS User_Name,  fdei.element_id AS Element_ID, de.name AS Element_Name,  f.display_name AS Fovirites_Name
FROM favorite_dashboard_element_info AS fdei
JOIN dashboard_element AS de ON(de.element_id=fdei.element_id)
JOIN user AS u ON(u.user_id=fdei.user_id)
JOIN favorite AS f ON(f.favorite_id=fdei.favorite_id)
GROUP BY fdei. favorite_dashboard_element_id
ORDER BY u.username