Created by James Hurrell on May 17, 2022

As outlined above, properties can be either alphanumeric or numeric. To modify the property of an object that already exists in the Analysis Service, two CAST entry tables are used, depending on the type of property:

  • Alphanumeric > CI_STR_PROPERTIES
  • Numeric > CI_INT_PROPERTIES

The examples in this section modify numeric properties, however it is fairly simple to modify the queries if you want to work with alphanumeric properties.

Based on object's ID

In this example, we are going to modify various numeric properties on a T-SQL object (a T-SQL Procedure called "DeleteAllOrders") that is synchronized with the Analysis schema.

The properties we are going to modify are

  • Number of inner comment lines, which is described in the Analysis Service as "BodyCommentLinesCount".
  • Number of code lines, which is described in the Analysis Service as "CodeLinesCount".
  • Number of heading comment lines, is described in the Analysis Service as "LeadingCommentLinesCount".

You can check the values for these properties by querying a CAST System View using the object's OBJECT_NAME:

select PROP_NAME, PROP_DESCRIPTION, VALUE
from CTV_OBJECT_PROPERTIES
where OBJECT_NAME = 'DeleteAllOrders'
and PROP_TYPE = 'NUMERIC'
go

This returns the following results:

PROP_NAME                PROP_DESCRIPTION                VALUE
[char]                   [char]                          [char]
BodyCommentLinesCount    Number of inner comment lines   1
CodeLinesCount           Number of code lines            22
LeadingCommentLinesCount Number of heading comment lines 14

We are going to modify these properties as follows:

  • Number of inner comment lines: 1 to 10
  • Number of code lines: 22 to 50
  • Number of heading comment lines: 14 to 200

The next step is to insert the data into the CAST entry tables that will cause the properties in question to be modified when the tool is run:

insert into <KB_name>.dbo.CI_INT_PROPERTIES (OBJECT_ID, PROP_NAME, VALUE, ERROR_ID)
select OBJECT_ID, 'BodyCommentLinesCount', 10, 0
from <KB_name>.dbo.CTV_GUID_OBJECTS
where OBJECT_NAME = 'DeleteAllOrders'
union
select OBJECT_ID, 'CodeLinesCount', 50, 0
from <KB_name>.dbo.CTV_GUID_OBJECTS
where OBJECT_NAME = 'DeleteAllOrders'
union
select OBJECT_ID, 'LeadingCommentLinesCount', 200, 0
from <KB_name>.dbo.CTV_GUID_OBJECTS
where OBJECT_NAME = 'DeleteAllOrders'
go

This is in effect three queries:

  • The first changes the "BodyCommentLinesCount" property to 10
  • The second changes the "CodeLinesCount" property to 50
  • The third changes the "LeadingCommentLinesCount" property to 200
  • The 0 parameter will enter 0 in the ERROR_ID column of the tables in question.
  • Note that you need to specify the Analysis Service database and user as above "<KB_name>.dbo" otherwise the query will fail when the job is run.

Then:

  • Complete the configuration of the job and run it.
  • Make sure you then update the CAST System Views.

If you want to check that the properties have indeed been modified successfully, you can use the following query in an SQL IDE against your Analysis Service (adapting it for your environment):

select PROP_NAME, PROP_DESCRIPTION, VALUE
from CTV_OBJECT_PROPERTIES
where OBJECT_NAME = 'DeleteAllOrders'
and PROP_TYPE = 'NUMERIC'
go

Based on object's GUID

For an object with an OBJECT_GUID

In this example, we are going to modify various numeric properties on a C++ object (a Global Function called "process_results ()") stored in the Analysis schema.

The properties we are going to modify are:

  • Number of inner comment lines, which is described in the Analysis Service as "BodyCommentLinesCount".
  • Number of code lines, which is described in the Analysis Service as "CodeLinesCount".
  • Number of heading comment lines, is described in the Analysis Service as "LeadingCommentLinesCount".

You can check the values for these properties by querying a CAST System View using the object's OBJECT_GUID:

select PROP_NAME, PROP_DESCRIPTION, VALUE
from CTV_OBJECT_PROPERTIES
where OBJECT_GUID = 'C_Fct.process_results.C_Fi."D:\TESTAPPLICATIONS\SQL SERVER\DEMO_C\EXAMPLE..C"'
and PROP_TYPE = 'NUMERIC'

This returns the following results:

PROP_NAME                 PROP_DESCRIPTION                VALUE
[char]                    [char]                          [char]
BodyCommentLinesCount     Number of inner comment lines   2
CodeLinesCount            Number of code lines            25
LeadingCommentLinesCount  Number of heading comment lines 7

We are going to modify these properties as follows:

  • Number of inner comment lines: 2 to 10
  • Number of code lines: 25 to 100
  • Number of heading comment lines: 7 to 200

The next step is to insert the data into the CAST entry tables that will cause the properties in question to be modified when the tool is run:

insert into <KB.name>.dbo.CI_INT_PROPERTIES (OBJECT_GUID, PROP_NAME, VALUE, ERROR_ID)
values ('C_Fct.process_results.C_Fi."D:\TESTAPPLICATIONS\SQL SERVER\DEMO_C\EXAMPLE..C"', 'BodyCommentLinesCount', 10, 0)
go
insert into <KB_name>.dbo.CI_INT_PROPERTIES (OBJECT_GUID, PROP_NAME, VALUE, ERROR_ID)
values ('C_Fct.process_results.C_Fi."D:\TESTAPPLICATIONS\SQL SERVER\DEMO_C\EXAMPLE..C"', 'CodeLinesCount', 100, 0)
go
insert into KB_name>.dbo.CI_INT_PROPERTIES (OBJECT_GUID, PROP_NAME, VALUE, ERROR_ID)
values ('C_Fct.process_results.C_Fi."D:\TESTAPPLICATIONS\SQL SERVER\DEMO_C\EXAMPLE..C"', 'LeadingCommentLinesCount', 200, 0)
go

This is in effect three queries:

  • The first changes the "BodyCommentLinesCount" property to 10
  • The second changes the "CodeLinesCount" property to 100
  • The third changes the "LeadingCommentLinesCount" property to 200
  • The 0 parameter will enter 0 in the ERROR_ID column of the tables in question.
  • Note that you need to specify the Analysis Service database and user as above "<KB_name>.dbo" otherwise the query will fail when the job is run.

Then:

  • Complete the configuration of the job and run it.
  • Make sure you then update the CAST System Views.

If you want to check that the properties have indeed been modified successfully, you can use the following query in an SQL IDE against your Analysis Service (adapting it for your environment):

select PROP_NAME, PROP_DESCRIPTION, VALUE
from CTV_OBJECT_PROPERTIES
where OBJECT_GUID = 'C_Fct.process_results.C_Fi."D:\TESTAPPLICATIONS\SQL SERVER\DEMO_C\EXAMPLE..C"'
and PROP_TYPE = 'NUMERIC'

This returns the following:

PROP_NAME                 PROP_DESCRIPTION                VALUE
[char]                    [char]                          [char]
BodyCommentLinesCount     Number of inner comment lines   10
CodeLinesCount            Number of code lines            100
LeadingCommentLinesCount  Number of heading comment lines 200

You can also check in CAST Enlighten.

For an object without an OBJECT_GUID

Note that in AIP Core ≥ 8.3.45, the table GUID_OBJECTS can no longer be used . Attempting to use it will generate and ERROR_ID = 5 on each returned row. If you have scripts using this table to generate custom OBJECT_GUIDs, for legacy technologies such as VisualBasic and PowerBuilder, you should update those scripts to use the OBJECT_ID (which is always available) instead of generating a custom OBJECT_GUID using this table. In this situation, the example below is obsolete and should not be used.

In this example, we are going to modify various numeric properties on a T-SQL server object (a Procedure called "byroyalty") stored in the Analysis schema.
The properties we are going to modify are

  • Number of inner comment lines, which is described in the Analysis Service as "BodyCommentLinesCount".
  • Number of code lines, which is described in the Analysis Service as "CodeLinesCount".
  • Number of heading comment lines, is described in the Analysis Service as "LeadingCommentLinesCount".

You can check the values for these properties by querying a CAST System View using the object's OBJECT_NAME (there is no OBJECT_GUID):

select OBJECT_NAME, PROP_NAME, VALUE
from CTV_OBJECT_PROPERTIES
where PROP_NAME  in ('BodyCommentLinesCount','CodeLinesCount', 'LeadingCommentLinesCount' )
and PROP_TYPE = 'NUMERIC'
AND OBJECT_NAME = 'byroyalty'

This returns the following results:

OBJECT_NAME  PROP_NAME                 VALUE
[char]       [char]                    [char]
byroyalty    BodyCommentLinesCount     0
byroyalty    CodeLinesCount            8
byroyalty    LeadingCommentLinesCount  6

 
We are going to modify these properties as follows:

  • Number of inner comment lines: 0 to 10
  • Number of code lines: 8 to 100
  • Number of heading comment lines: 6 to 200

The next step is to check that the OBJECT_GUID you will create for the object in question does not already exist in the Analysis schema (please see KB Update SQL Tool - Creating OBJECT_GUIDs for more information about creating OBJECT_GUIDs for objects that do not have them):

select count(1) Cnt
from CTV_GUID_OBJECTS
where OBJECT_GUID  = 'CASTPubs.PROC.byroyalty'

The result returns nothing:

Cnt
[int]
----
0

The next step is to insert the data into the CAST entry tables that will cause the properties in question to be modified and a new OBJECT_GUID to be created for the procedure "byroyalty" when the tool is run:

insert into <KB_name>.dbo.GUID_OBJECTS (OBJECT_ID, OBJECT_GUID, ERROR_ID)
select OBJECT_ID, 'CASTPubs.PROC.byroyalty', 0
from <KB_name>.dbo.CTV_GUID_OBJECTS
where OBJECT_NAME = 'byroyalty'
go
insert into <KB_name>.dbo.CI_INT_PROPERTIES (OBJECT_GUID, PROP_NAME, VALUE, ERROR_ID)
values ('CASTPubs.PROC.byroyalty', 'BodyCommentLinesCount', 10, 0)
go
insert into <KB_name>.dbo.CI_INT_PROPERTIES (OBJECT_GUID, PROP_NAME, VALUE, ERROR_ID)
values ('CASTPubs.PROC.byroyalty', 'CodeLinesCount', 100, 0)
go
insert into <KB_name>.dbo.CI_INT_PROPERTIES (OBJECT_GUID, PROP_NAME, VALUE, ERROR_ID)
values ('CASTPubs.PROC.byroyalty', 'LeadingCommentLinesCount', 200, 0)
go

This is in effect four queries:

  • The first creates a new OBJECT_GUID for the object "byroyalty"
  • The second changes the "BodyCommentLinesCount" property to 10
  • The third changes the "CodeLinesCount" property to 100
  • The fourth changes the "LeadingCommentLinesCount" property to 200
  • The 0 parameter will enter 0 in the ERROR_ID column of the tables in question.
  • Note that you need to specify the Analysis Service database and user as above "<KB_name>.dbo" otherwise the query will fail when the job is run.
  • Please see KB Update SQL Tool - Creating OBJECT_GUIDs for more information about creating OBJECT_GUIDs for objects that do not have them.

Then:

  • Complete the configuration of the job and run it.
  • Make sure you then update the CAST System Views.

If you want to check the OBJECT_GUID has been successfully created, use the following query in an SQL IDE against your Analysis Service (adapting it for your environment):

select OBJECT_ID, OBJECT_NAME, OBJECT_GUID
from CTV_GUID_OBJECTS
where OBJECT_NAME = 'byroyalty'

The result returns the following, indicating that the OBJECT_GUID has been created correctly:

OBJECT_ID OBJECT_NAME  OBJECT_GUID
[int]     [char]       [char]
5007      byroyalty    CASTPubs.PROC.byroyalty

If you want to check that the properties have indeed been modified successfully, you can use the following query in an SQL IDE against your Analysis Service (adapting it for your environment):

select PROP_NAME, PROP_DESCRIPTION, VALUE
from CTV_OBJECT_PROPERTIES
where OBJECT_GUID = ' CASTPubs.PROC.byroyalty '
and PROP_TYPE = 'NUMERIC'

This returns the following:

PROP_NAME                PROP_DESCRIPTION                VALUE
[char]                   [char]                          [char]
BodyCommentLinesCount    Number of inner comment lines   10
CodeLinesCount           Number of code lines            100
LeadingCommentLinesCount Number of heading comment lines 200

You can also check in CAST Enlighten.