Practical SQL: Common time saving queries to simplify your HANA workflow

[vivekbhoj]

Hi Everyone!

As a data professional, we often perform tasks manually or try to search for right SQL to pull data quickly to solve our immediate requirement. Whether we are building HANA models, making enhancements to the existing models or troubleshooting authorization issues, SQL is the backbone for efficient workflows. But knowing the right query at the right time can save us a lot of effort.

In this blog, we will explore a collection of SQL queries that are not only practical but also designed to help us tackle day-to-day tasks with ease. From troubleshooting authorization issues to looking for dependent objects based on different conditions, these queries will make our work faster and more efficient.

Let's dive into these SQL queries and see how they can help us to simplify our workflow.

1. Get a list of Broken Objects 

• We have often observed that our HANA objects become broken during work and they impact us in various ways
• For example, if a HANA Calculation view gets broken, this impacts our report resulting in no data and also affects other objects dependent on this view. 
• Similarly, if a HANA design-time role is broken, the authorization is lost and users get "Not authorized" or "Insufficient Privilege" error in reports
• If any HANA object is broken, it is also not available in "OBJECT_DEPENDENCIES" view and this can cause issues later if this object is missed while updating other objects

ACTIVE_OBJECT table stores the status, suffix and other details of each object

-- Query to get list of Broken/Inactive Objects
SELECT "PACKAGE_ID", "OBJECT_NAME", "OBJECT_SUFFIX", "ACTIVATED_AT",
"ACTIVATED_BY"
FROM "_SYS_REPO"."ACTIVE_OBJECT"
WHERE "OBJECT_STATUS" != 0 AND "PACKAGE_ID" = '<package name>'

This query helps a lot especially in the cases with Broken role where users have reported that they are not able to access our application. Simple redeployment of the broken role fixes this issue.
Sometimes the reporting view added to the role gets broken and then this view gets missing in the role. This also causes authorization error and we need to redeploy the view to fix this error.

2. Troubleshoot user authorization issues

• To resolve authorization issues, we need to verify that users have been assigned the appropriate roles and ensure that the roles include all the necessary objects.

-- Query to get list of roles assigned to the user
SELECT DISTINCT "USER_NAME", "ROLE_NAME"
FROM "PUBLIC"."EFFECTIVE_ROLES"
WHERE "USER_NAME" = '<user_name>'

-- Query to check whether user has access to a particular object
SELECT DISTINCT "USER_NAME", "OBJECT_NAME", "PRIVILEGE"
FROM "PUBLIC"."EFFECTIVE_PRIVILEGES"
WHERE "USER_NAME" = '<user_name>' AND "OBJECT_NAME" = '<object_name>'

-- Query to check if a particular object is present in the role or not
SELECT "GRANTEE" AS "ROLE_NAME", "OBJECT_NAME"
FROM "PUBLIC"."GRANTED_PRIVILEGES"
WHERE "OBJECT_NAME" = '<object_name>'

• Sometimes user has proper authorization and roles have also been maintained properly but still user is not able to access the report. In such cases, we need to check whether the user has been deactivated, password is locked due to invalid attempts, SSO is enabled or not or whether a user is a normal user or a restricted user.

-- Query to check if user is active and if SSO is enabled for the user or not
SELECT "USER_NAME", "VALID_UNTIL", "LAST_INVALID_CONNECT_ATTEMPT",
"PASSWORD_CHANGE_NEEDED", "USER_DEACTIVATED", "DEACTIVATION_TIME",
"IS_PASSWORD_ENABLED", "IS_KERBEROS_ENABLED", "IS_SAML_ENABLED", "IS_RESTRICTED"
FROM "PUBLIC"."USERS"
WHERE "USER_NAME" = '<user_name>'

These queries help in resolving authorization related issues as we can easily identify if a  user is active or not, required role has been assigned to the user or not, whether the user has access to the required object  or if the required object is added to the role or not

3. Get a list of  views that use a particular Input Parameter or list all the Input Parameters present in a View

• We frequently use input parameters in our views and often update their default values manually
• For example, default hierarchy changes every year in one of our reports so we need to manually update the Hierarchy Input parameter every year

-- Query to get a list of views that use a particular input parameter
SELECT "QUALIFIED_NAME" AS "VIEW_NAME", "VARIABLE_NAME" AS "PARAMETER_NAME",
"VARIABLE_DESC" AS "PARAMETER_DESC", "SELECTION_TYPE", "DEFAULT_VALUE",
"IS_MANDATORY"
FROM "_SYS_BI"."BIMC_ALL_VARIABLES"
WHERE "VARIABLE_NAME" = <'input_parameter'> AND "CATALOG_NAME" = <'pacakge'> 

-- Query to list all the input parameters used in a calculation view
SELECT "QUALIFIED_NAME" AS "VIEW_NAME", "VARIABLE_NAME" AS "PARAMETER_NAME",
"VARIABLE_DESC" AS "PARAMETER_DESC", "SELECTION_TYPE", "DEFAULT_VALUE",
"IS_MANDATORY"
FROM "_SYS_BI"."BIMC_ALL_VARIABLES"
WHERE "CUBE_NAME" = '<view name'>

This query helps a lot in getting the list of calculation views that need to be updated with the new value. Otherwise we would have to manually open each calculation view to check if this input parameter is being used or not.

4. Get a list of views/tables that use a particular column

• We enhance views/tables multiple times by removing the columns that are not longer required
• This requires proper planning as our views/tables are also used by other teams and we need to intimate them before performing deletion 

-- Query to get list of views that use a particular column
SELECT "SCHEMA_NAME". "CATALOG_NAME" AS "PACKAGE", "CUBE_NAME" AS "VIEW_NAME",
"COLUMN_NAME", "DESC_ATTRIBUTE_COLUMN_SQL_TYPE" AS "DATA_TYPE"
FROM "_SYS_BI"."BIMC_PROPERTIES_VIEW"
WHERE "CATALOG_NAME" = '<package>' AND "COLUMN_NAME" = '<column name>'
AND "COLUMN_FLAG" = 'Dimension Attribute'

-- Query to get list of tables that use a particular column 
SELECT "SCHEMA_NAME", "TABLE_NAME", "COLUMN_NAME", 
"DATA_TYPE_NAME" AS "DATA_TYPE", "LENGTH" AS "COLUMN_LENGTH"
FROM "PUBLIC"."TABLE_COLUMNS"
WHERE "COLUMN_NAME" = '<column_name>'

This query helps a lot when we need to remove columns from the views or tables as we get an overview of all the impacted objects. 

5. Get a list of dependent/base objects

• We perform regular cleanup activities to delete obsolete objects and need to perform impact analysis before deletion
• We add new columns to the reporting view or table and need to identify all the objects where new column need to be added 

-- Query to get list of dependent objects
SELECT "DEPENDENT_OBJECT_NAME", "DEPENDENT_OBJECT_TYPE",
"DEPENDENT_SCHEMA_NAME"
FROM "SYS"."OBJECT_DEPENDENCIES"
WHERE "BASE_OBJECT_NAME" = '<object_name>'
AND "DEPENDENT_OBJECT_NAME" NOT LIKE '%/hier%'
AND "DEPENDENT_OBJECT_TYPE" IN ('VIEW','TABLE','PROCEDURE', 'FUNCTION')
AND "DEPENDENCY_TYPE" = 1

-- Query to get list of base objects that are used in a particular object
SELECT "BASE_OBJECT_NAME", "BASE_OBJECT_TYPE", "BASE_SCHEMA_NAME"
FROM "SYS"."OBJECT_DEPENDENCIES"
WHERE "DEPENDENT_OBJECT_NAME" = '<object_name>'
AND "BASE_OBJECT_NAME" NOT LIKE '%/hier%'
AND "BASE_OBJECT_TYPE" IN ('VIEW','TABLE','PROCEDURE', 'FUNCTION')
AND "DEPENDENCY_TYPE" = 1

This query is used a lot when we need to delete objects, add new columns or need to understand the flow of data. 

6. Get list of objects where a particular filter is applied

• We often use filter conditions in our daily work, and sometimes we need to identify all the objects where a specific filter has been applied
• For example, suppose we have applied a Year filter in multiple views, functions and procedures and now we need to update the filter. For performing this task, we need to check all the objects

CDATA column present in ACTIVE_OBJECT table stores the object informtaion in
XML format and can be used to check for filter conditions

-- Query to get list of objects where particular filter is being applied
SELECT "PACKAGE_ID", "OBJECT_NAME", "ACTIAVTED_AT", "ACTIVATED_BY"
FROM "_SYS_REPO"."ACTIVE_OBJECT"
WHERE "CDATA" LIKE '%"YEAR" =%'

-- For checking the filter in views, update the filter condition as below
SELECT "PACKAGE_ID", "OBJECT_NAME", "ACTIAVTED_AT", "ACTIVATED_BY"
FROM "_SYS_REPO"."ACTIVE_OBJECT"
WHERE "CDATA" LIKE '%"YEAR&quot%'

This query helps a lot whenever we need to change filter values at multiple places. 

• Let's extend this query and make it even better. We saw in the query above that we searched objects where YEAR filter was being applied. 
• Now lets say that we need to look for objects where YEAR column from Table SALES is used for filtering

 Query to get list of objects where particular filter from a 
particular table is being applied

-- Query to get list of dependent objects based on the table SALES
WITH objectdependency AS (
SELECT "BASE_OBJECT_NAME", "DEPENDENT_OBJECT_NAME",
"DEPENDENT_OBJECT_TYPE"
FROM "SYS"."OBJECT_DEPENDENCIES"
WHERE "BASE_OBJECT_NAME" = 'SALES'
AND "DEPENDENT_OBJECT_NAME" NOT LIKE '%/hier%'
AND "DEPENDENT_OBJECT_TYPE" IN ('VIEW', 'PROCEDURE', 'FUNCTION')
AND "DEPENDENCY_TYPE" = 1
),
-- Query to get list of objects where YEAR filter is applied
filterdobjects AS (
SELECT 
CASE 
WHEN "OBJECT_SUFFIX" IN ('attributeview','analyticview',
'calculationview') THEN "PACKAGE_ID" || '/' || "OBJECT_NAME"
WHEN "OBJECT_SUFFIX" IN ('hdbtablefunction','hdbprocedure') 
THEN "PACKAGE_ID" || '::' || "OBJECT_NAME"
ELSE "OBJECT_NAME" END AS "FULL_OBJECT_NAME",
"ACTIVATED_AT", "ACTIVATED_BY"
FROM "_SYS_REPO"."ACTIVE_OBJECT"
WHERE ("CDATA" LIKE '%"YEAR" =%') OR ("CDATA" LIKE '%"YEAR&quot%')
)
SELECT a."BASE_OBJECT_NAME", a."DEPENDENT_OBJECT_NAME",
a."DEPNDENT_OBJECT_TYPE", b."ACTIVATED_AT", b."ACTIVATED_BY"
FROM objectdependency a
INNER JOIN filterdobjects b
ON a."DEPENDENT_OBJECT_NAME" = b."FULL_OBJECT_NAME"

This query lists all the objects that use table SALES and have filter applied on "YEAR" column. This is also helpful in scenario where we are removing some obsolete value from a column of master data table and need to find if we are using that value for filtering in any objects or not.

7, Create a parameter table

• As seen earlier, maintaining a filter in multiple places requires manual updates whenever it needs to be changed, making the process very time consuming.
• To make this process easier, we can create a parameter table with ID and Value columns and insert the filter values in this table.
• Later we can create a scalar function based on the values coming from this table and use it as  Input Parameter in the calculation view to filter the data
• For example, we need to apply filter on "YEAR" as 2025 at multiple places

-- Create parameter table
CREATE COLUMN TABLE "PARAM_SCHEMA"."PARAM_TABLE" (
   FILTER_ID NVARCHAR(20),
   FILTER_VALUE NVARCHAR(100)
);

-- Insert Year filter in the parameter table
INSERT INTO PARAM_TABLE VALUES ('YEAR', '2025');

-- Query to create a scalar function for Year based on the parameter table
FUNCTION "PARAM_SCHEMA"."param.functions::filter_year () 
	RETURNS "YEAR" NVARCHAR(4)
	LANGUAGE SQLSCRIPT
	SQL SECURITY DEFINER AS
BEGIN
  SELECT "FILTER_VALUE" INTO "YEAR"
  FROM "PARAM_SCHEMA"."PARAM_TABLE"
  WHERE "FILTER_ID" = 'YEAR';
END;

We can use the scalar function created above in our views in the input parameter and filter the data.
This helps a lot as we update the filter value only once and it automatically gets updated in all the required objects. 

In this blog, we have explored few SQL queries designed to tackle common day-to-day challenges - whether it is troubleshooting authorization issues or performing impact analysis. These queries are tools to make your work faster and efficient.

If you have favorite queries or unique tips, I would love to hear them and add to the blog.
Please share your thoughts or questions in the comments below.