Calculation View, KEY check box metadata

[darkwingmcquack]

Hi there

Does anyone know where i will find the following information in the sys tables

in the graphic below the 'Key' check box ... defined on a CALCULATION VIEW

I am needing to query something like

SELECT * FROM SYS.CALC_VIEW_COLUMNS WHERE 'KEY??' = 'TRUE'

[darkwingmcquack]

Yes, all deployed an activated.

[KonradZaleski]

Try this query:

SELECT 
	"CATALOG_NAME",
	"SCHEMA_NAME",
	"CUBE_NAME",
	"PROPERTY_NAME",
	"PROPERTY_CAPTION"
FROM 
	"_SYS_BI"."BIMC_PROPERTIES" 
WHERE 
	"MODELED_KEY_COLUMN" = 1

[darkwingmcquack]

Hi Konrad,

Thanks, this looks promising. When i do the query, i get differences to what i see on the gui. i am not seeing the value of 1 on the table for the same key tick in the cal view gui ? am i missing something ?

and this the query

[KonradZaleski]

Hi Gary,

For each column of the table you will find multiple records in that table. See for my case column MANDT. It also contains multiple records and most of the values are null for MODELED_KEY_COLUMN.

However one of the record contains proper flag.

Can you try to add to your query, WHERE condition as follows:

WHERE "COLUMN_FLAG" = 'Dimension Attribute'

It should filter out other not relevant records.

[darkwingmcquack]

Hi Konrad

Yeah, i think the question is more that i find data for some of the calculation views but not ALL of them. I would have expected data for all calculation views that we have already created.

I run your code (with the new filter) and get a list of CV names, if i take for eg the first one, i open the same in the GUI, it shows exactly what i was expecting. The same columns are ticked as what i see in the table.

However, if i take another calculation view that exists in the repository, has key columns marked/ticked, it is not appearing in the query set. I don't know why some calculation views appear in the query set and others don't.

[KonradZaleski]

This is weird - I see all my views in BIMC_PROPERTIES systemtable.

Are those views deployed and activated? Maybe some of these views are created in local repository only?