Query to Fetch Inactive Columns from a Workspace

Query to Fetch Inactive Columns from a Workspace

Requirement:

Customer wanted to determine the number of used or unused columns in a workspace.

Solution:

To achieve this, we need to connect to the Analytics Plus database by using local database import method (PostgrSQL) and then use the custom query option to fetch the data into a table format using the queries attached below. Once we imported the used_columns and All_columns tables, we need to prepare a Query table that shows the UnUsed_Columns column details. 

PFA Queries

This query can be used to fetch unused column details from any workspace. Simply replace the workspace ID (e.g., zd.dbid = 1234) with the appropriate workspace ID to get the required data.

Steps to import a table from our "zreportsdb" database:

• Hostname = localhost
  
• The port number refers to the "database_params.conf" under "AnalyticsPlus/conf"
  
• User name = opuser
  
• Password, take the value for the "password" property in the database_params.conf" under "AnalyticsPlus/conf"
  
• Then go to the browser and then go to https://<Analytics URL>/zrop/jsp/Encrypt.jsp URL and then enter the above value in the "Enter Value to Crypto Decrypt:", then submit and use that value as a password
  
• Database=zreportsdb

Note:

This will help you identify columns that are not used in any reports, dashboards, or formula columns.