Available Objects and Elements
1. Homepage elements
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;
Click to copy
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;
Click to copy
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;
Click to copy
2. Favorites
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
Click to copy
