Modifying an object property on an existing object using CI_INT_PROPERTIES

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 CAST Analysis Service.

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. Add a new Update CAST Knowledge Base Tool and enter the following query:

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

Then:

• Complete the configuration of the job and run it as outlined in Using the Update CAST Knowledge Base Tool.
• 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 CAST Analysis Service.

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. Add a new Update CAST Knowledge Base Tool and enter the following query:

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

Then:

• Complete the configuration of the job and run it as outlined in Using the Update CAST Knowledge Base Tool.
• 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

In this example, we are going to modify various numeric properties on a T-SQL server object (a Procedure called "byroyalty") stored in the CAST Analysis Service.
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 CAST Analysis Service (Please see 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. Add a new Update CAST Knowledge Base Tool and enter the following query:

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

Then:

• Complete the configuration of the job and run it as outlined in Using the Update CAST Knowledge Base Tool.
• 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.