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.
New to ADSelfService Plus?
Related Articles
Workspace Audit information collection
The below query could be used to collect the audit information requested by connecting to the Analytics Plus database using local DB import. select dbobj.displayname AS "View Name", createdusermail.email_id as "Created By", ...Procedure to upload local DB to Analytics Plus
Steps to use Upload Tool for uploading Data from any DB: 1. Download and extract the uploadtool.zip from this link 2. Configure the Database connection details: Open the file 'database_connection_params.conf' located under <Uploadtool>\conf\ folder, ...Query Reports not refreshing on Build 3600
Issue: Once migrated to 3600, the existing query reports do not get refreshed with latest data. Needs manual update. Solution: This seems to be breakage in 3600 builds. Open the <AnalyticsPlus\conf\ app.properties> file and then change the value ...Query table throws error upon execute query - Linux - 4400 and above
Issue: Query table's 'execute query' will throw an error as shown below. Even when executing 'select 1', we might get this error. Build affected: 4400, 4410 and 4420 Only on Linux installations Cause: It is due to internal code changes and will be ...Query to Pull Asset Module data from ServiceDesk Plus
Please use the following query for Asset Module(General Details) PGSQL: select res.RESOURCEID AS "ASSET ID", res.RESOURCENAME AS "NAME", to_timestamp(res.ACQUISITIONDATE/1000) AS "ACQUISITION DATE", to_timestamp(res.WARRANTYEXPIRY/1000) AS "WARRANTY ...
