a SQLScript stored procedure to get the list of measures and dimension for a given OLAP cube (Analytic view) in SAP HANA

getListOfMeasuresAndDimensions.sql

drop type measuresAndDimensionsType; 
create type measuresAndDimensionsType as table("ATTRIBUTE_NAME" varchar(100),"DIMENSION_NAME" varchar(100), "ATTRIBUTE_DESCRIPTION" varchar(100), "ATTRIBUTE_TYPE" integer);


drop procedure getListOfMeasuresAndDimensions; 
create procedure getListOfMeasuresAndDimensions(in currentCubeName varchar(100), in currentPackageName varchar(100), out outputResults measuresAndDimensionsType)
language sqlscript as 
cursor listOfDimensions for select distinct "DIMENSION_NAME"
from  "_SYS_BI"."BIMC_DIMENSIONS" where "CUBE_NAME"='KDD' and 
"CATALOG_NAME"='kddSetupPackage.kddSetup.views';
begin 

	declare isMeasure int default 1;
	create local temporary table #tempTable("ATTRIBUTE_NAME" varchar(100), "DIMENSION_NAME" varchar(100), "ATTRIBUTE_DESCRIPTION" varchar(100), )
	
	
	for cur as listOfDimensions do 
		
		if cur.DIMENSION_NAME != 'Measures' then 
			isMeasure := 0; 
		end if; 
		
		insert into #tempTable(ATTRIBUTE_NAME, DIMENSION_NAME, ATTRIBUTE_DESCRIPTION, ATTRIBUTE_TYPE) select distinct "ALTERNATIVE_PROPERTY_NAME" as 
		"ATTRIBUTE_NAME", cur.DIMENSION_NAME as "DIMENSION_NAME",
		"DESCRIPTION" as "ATTRIBUTE_DESCRIPTION", isMeasure as "ATTRIBUTE_TYPE"
		from "_SYS_BI"."BIMC_PROPERTIES"  where "CUBE_NAME" = :currentCubeName and 
		"CATALOG_NAME" = :currentPackageName and ALTERNATIVE_PROPERTY_NAME is not null 
		and DIMENSION_UNIQUE_NAME = '['||cur.DIMENSION_NAME||']';
		
	end for; 
	
	--assign the content of the temporary table to the output variable 
	outputResults = select * from #tempTable; 
	
	-- free the memory used by the temporary table 
	truncate table #tempTable; 
	
	-- drop the temporary table 
	drop table #tempTable; 
	
end; 

-- call procedure for the OLAP cube 'KDD'
call getListOfMeasuresAndDimensions('KDD', 'kddSetupPackage.kddSetup.views', ?)