@Brett Saviano Is there something needed to enable Show Plan.  I checked my extensions and none of them say I need updating.  Additionally while it is great to have the action to Show Plan for a class query, InterSystems Studio allowed us to select any text-Right Click-Show Plan.  This is useful when either you have a Dynamic SQL statement inside of a method or you are using embedded SQL with or without a cursor such as

	&SQL(DECLARE Loop CURSOR FOR
			SELECT  %Id,Super
			INTO    :tClass,:tSuper
			FROM	  %Dictionary.CompiledClass
			WHERE	  %Id %STARTSWITH :Prefix AND GeneratedBy IS NULL AND COALESCE(Deployed,0)=0
              AND (Super not like '%DeepSee.KPIPlugIn')
			FOR READ ONLY
			)
		&SQL(OPEN Loop)
		For  {
			&SQL(FETCH Loop)

Hooray for 

1. Show Plan
2. Open Error Location

Great work and many thanks

Excellent work.  I was just search for this yesterday and this morning saw this.  I gave it a spin and it works perfectly.

One aspect that I think is not always appreciated is how locking is happening automatically.  For example, 

1. the %OpenId method when used in Objects has a second parameter that is not always utilized or thought about.  The second parameter is the concurrency parameter.  I think of applications as plenty of locations where reads are done and a single(small number of places) where %Save is done.  With this in mind do you really need a lock of some kind in the place where reads are done.  You may feel like they do need locks which is fine, just understand that calling %OpenId will use the default concurency option and this may acquire some form of a lock.  Now the other aspect of this is %OpenId returns tSC by reference.  How many people call %OpenId and never check the value of tSC.  This could come back with an error %Status if for example you didnt specifiy the concurrency option and some other process had an Exclusive Lock on the record you are attempting to Open
2. SQL statements like INSERT, UPDATE, DELETE will by default acquire locks on the rows that it is impacting.  You can use the %NOLOCK keyword to tell the SQL engine to not lock but that is on you to add.  If you have a single SQL statement like DELETE FROM Table1 WHERE NOT EXISTS (SELECT * FROM Table2) this statement is atomic, which means among other things
   1. all of the deletes on the rows are journalled
   2. all of the rows that are being deleted will be locked.  At some point lock escalation will take place and the table will be locked.

TLDR .. know that locking is happening when using Objects and SQL and use accordingly.

This all starts by examinging the query plans and undestanding what the Query plan is telling us.  With large data sets a bitmap index is generally much better for performance than a traditional index for columns that 

1. have a small number of distinct values, the general recommendation is when the column has less than 10,000 distinct values.  For example MRN which should generally be thought of as unique, or StreetAddress are not good candidates for bitmap indices as they have thousands of distinct values.  On the other hand columns like Status, InsertDate, Code etc are often times good candidates for bitmap indices.  Query plans are available from the Show Plan button when writing a SQL Statement and also from the SQL Statements tab from System Explorer->SQL.  This tab allow you to sort the columns and I find it useful to look at 
   1. statements that are run frequently
   2. statement that have a lengthy Average Time

In System Admin->Configuration->SQL and Object Settings->SQL do you have "Execute Queries in a single process" checked.  If so, you may want to consider unchecking it.

When defining bitmap indices it is better to create a bitmap index on a single column and have multiple indices over creating a bitmap index that is based on several columns.  The optimizer, query engine will combine individual indexes when needed whereas if you have a bitmap index on A,B and you query only has a condition on B the index may not be selected.  Having Index A on A and Index B on B will combine indices when you have conditions on A and B but will also be able to use the B index if you only have a condition on B.

Relative Cost is just that..its best thought of the number for your SQL statement.  If you adjust your statement and the cost increases and you cannot explain why you might want to retrace your steps.  But the cost is just for your single statement.. you cant compare the cost between a Statement that query Table A against a SQL Statement for Table B.

Have you tuned your tabled and gathered statistics so the query engine has information about your tables?

What kind of large volume do you have?

and again, everything is revealed in the Show Plan.

In the rule you should be able to have an expression with 

Stream.Read(some number of character.. you might consider 3200000)  

to get the first X characters of the stream content... of course assuming the thing you are searching for can be found in that string.

My experience is the developer room offers the best opportunity to interact with the InterSystems developers, this is the most rewarding part.

Not entirely dogmatic but I've always found https://github.com/jnguyen095/clean-code/blob/master/Clean.Code.A.Handb… to be a good guide.

this has sections on veriticle and horizontal formatting/density/distance.  Again, not a specific answer but I use it as a guide.

I'm struggling to understand your original question "We are attempting to "Repoint" old class data to new class data to save disk space and data redundancy across multiple tables.  "

Will "Repoint"ing acutall save disk space?  At first glance it would be hard to see how that would make a significant difference.  It might be true that the old class has a number of properties that are no longer used so in the physical storage you have a number of unused storage element but I wouldnt think that would make a significant difference.  

One area that can make a difference is if your classes utilize bitmap indices and if you have rapidly changing data with fields are indexed.  IRIS delivers %SYS.Maint.Bitmap which is described as 

This utility is used to compact bitmap/bitslice indices. Over time in a volatile table (think lots of INSERTs and DELETEs) the storage for a bitmap index may become less efficient. To a lesser extent index value changes, i.e. UPDATES, can also degrade bitmap performance.

This utility can by run on a live system. It will loop over all bitmap/bitslice indices in a class or namespace and compact them by either removing a chunk that is all zeros or by using $BITLOGIC() to compact the bits.

Consider Ens.MessageHeader which has a bitmap index on Status where the Status values are 

1=Crerated

2=Queued

3=Delivered

4=Discarded

5=Suspended

6=Deferred

7=Aborted

8=Error

9=Completed

You can imagine that a Message Header is save with 

Event 1 Status=Created

Event 2  Status=Queued

Event 3  Status=Delivered

Event 4 Status = Completed

in the normal course of processing

This would have the side effect of 

1. Event 1 setting the Status =Created bit on

2. Event 2 Setting the Status=Created bit Off, Setting the Status=Queued On

3. Event 3 Setting the Status=Queued bit Off, Setting the Status=Delivered On

4. Event 4 Setting the Status=Delivered bit Off, Setting the Status=Completed On

all of this means that the bitmap index for 

Status=Created, Queued, Delivered over time is just a series of off bits and in many cases the entire bitchunk is just a bunch of off bits(although there is some compression here).

I have utilized  %SYS.Maint.Bitmap on systems that have existed for many years and saw some noticable space savings.  However, once run it doesnt seem to make a lot of sense to run very often.

https://github.com/synthetichealth/synthea can be used to create synthetic data, FHIR being one of the formats

Regarding 

• <Invoke> in module.xml behaves more intuitively by always checking the %Status return value if and only if the method signature declares %Status is returned. This means an error will be thrown if nothing is returned, the return value is not a %Status, or it is not $$$OK.

If we have 

specifically

        <Invoke Class="RCIPM.Utils" Method="MakeModuleDeployed" Phase="Compile" When="After" CheckStatus="false">

            <Arg>{$root}</Arg>

            <Arg>{$verbose}</Arg>

        </Invoke>

CheckStatus="false", is it still going to check the status.  sorry if I'm reading this incorrectly.

I guess then reading further I see

• #828: The CheckStatus flag for <Invoke> action has been deprecated. Default behavior is now to always check the status of the method if and only if the method signature returns %Library.Status

so it would appear, yes, the CheckStatus attribute is now going to be ignored.

I have 109 examples across 31 modules that use CheckStatus="false"

some of these are during the Phase="Clean" where I dont care if the Invoke succeeds completely or not during a Clean.  

With the behavior of modules being all part of a TSTART if any of the Invokes return an error %Status then the module is not installed and everything is TROLLBACK'd. in the case of Invoke I dont have a super string feeling about some of the <Invoke>s, if they fail I still want the module installed.

Ben,

  Can you provide some clarity around why some of your SQL statements which are SELECT statements do not use 

%NOLOCK

while one of them does use %NOLOCK.

I always thought a by default a SELECT statement does not attempt to lock any of the records of any type of lock.

If I study the ObjectScript code generated to satisfy a SELECT statement like

select id from ens.messageHeader

it's not clear that any of this does any form of a lock(whether exclusive, shared etc)

%0Afirst 
 ; asl MOD# 2
 set i%Vsb23=""
%0AmBk1 s i%Vsb23=$o(^Ens.MessageHeaderI("Extent",i%Vsb23),1,i%Vch24) i i%Vsb23="" { g %0AmBdun }
 s i%Vhi25=i%Vsb23-1*64000
 s i%Vvecvar17=$vop("+",$vop("positions",i%Vch24),i%Vhi25-1)
 ; asl MOD# 3
 set i%Vlo26=""
%0AmCk1 s i%Vlo26=$bitfind(i%Vch24,1,i%Vlo26+1) i 'i%Vlo26 g %0AmCdun
 s i%IDO1=i%Vhi25+i%Vlo26-1
 s i%IDO1=$ve(i%Vvecvar17,i%Vlo26)
 s:$g(SQLCODE)'<0 SQLCODE=0 s %ROWCOUNT=$i(i%rowcnt),%ROWID=i%IDO1,i%CursorState=10
 //-- FETCH Output
 set:$l($g(i%querystats)) i%time=$g(i%time)+$zh-$lg(i%querystats),i%commands=$g(i%commands)+$zu(61,8,$zu(61))-$lg(i%querystats,2),i%querystats=""
 quit
%QRS0f //-- FETCH tag
 i '$g(i%CursorState) { s SQLCODE=-102 q  } i i%CursorState=100 { s SQLCODE=100 q  } s SQLCODE=0
 i i%rowlimit,i%rowcnt'<i%rowlimit { s SQLCODE=100,%ROWCOUNT=i%rowcnt,i%CursorState=100 q }
 Do $system.Process.AppFrameInfo(i%StackInfo) 
 set i%querystats=$lb($zh,$zu(61,8,$zu(61)))
 g %0Afirst:i%CursorState=1
 g %0AmCk1
%0AmCdun g %0AmBk1
%0AmBdun 
%0AmAdun 
 s %ROWCOUNT=i%rowcnt,SQLCODE=100,i%CursorState=100
 set:$l($g(i%querystats)) i%time=$g(i%time)+$zh-$lg(i%querystats),i%commands=$g(i%commands)+$zu(61,8,$zu(61))-$lg(i%querystats,2),i%querystats=""
 //-- no [more] data
 quit

While the above is for a simple query and is completely index satisfiable, i.e. it doesnt have to read from the mastermap I dont think the question of lock/nolock changes for a SELECT statement.

Question:

Why would the usage of %NOLOCK ever come into play?

You might find some success looking at the tables/sqlprocs found in the %SQL* schemas

for the example you provided given that the methods invoked by the <routes> are classmethod I commonly do something along the lines of 

    <Route Url="/error/list" Method="GET" Call="GetErrors" />

ClassMethod GetErrors(pStartTime As %UTC = "", pEndTime As %UTC = "") As %Status

{

    #DIM tSC                    As %Status = $$$OK

    #DIM eException             As %Exception.AbstractException

    #DIM %request               As %CSP.Request

    Try {

            Set:$Get(%response)'="" %response.ContentType="application/json"

            If $Get(%request) {

                Set tStartDate = $Get(%request.Data("StartTime",1))

                Set tStopDate = $Get(%request.Data("EndTime",1))

            }

            Else {

                #;If they are defined in the parameter list

                If pStartTime'="" Set tStartDate=pStartTime

                If pEndTime'="" Set tStopDate=pEndTime

            }

removeEmpty is defined as 

The <table> removeEmpty attribute controls whether or not the empty nodes that Zen encounters in the XML data for this report display in the XHTML or PDF output generated by this <table> in the report. If removeEmpty is:

• Not specified, the <table> inherits the removeEmpty value of its parent. If no element in the ancestry of this <table> specifies a removeEmpty value, then the default value, 0, applies to this <table>.
• 0, empty element and attribute values are output to the XHTML or PDF generated for this <table> in the report.
• 1, empty element and attribute values are not output to the XHTML or PDF generated for this <table> in the report. If orient is "row," any rows with all empty data values are omitted from the output. If orient is "col," any columns with all empty data values are omitted from the output. If there are some empty cells, but the entire row (or column) is not empty, then the row (or column) is displayed with the empty cells blank.

The group attribute must be set for removeEmpty to work.

This attribute has the underlying data type %ZEN.Datatype.booleanOpens in a new tab. See “Zen Reports Attribute Data Types.”

so I dont think removeEmpty addresses what you have asked.  I would consider using an 

ifxpath expression that would control whether or not to display the table

Interesting.. but if the user understands SQL and understands the db schema why dont they connect via ODBC/JDBC.  If they dont understand SQL and the database schema why not make a specific REST class that has endpoints for each type of query they might want to run.  Allow for arbitrary SQL statement here including INSERT, UPDATE, DELETE is probably not a good idea.. although it looks like /query may only allow Statement Type = 1 SELECT and Type=45 CALL.

I take the approach of adding my .DFI items to a class that extends %DeepSee.UserLibrary.Container following

Creating a Business Intelligence Container Class

as this allows me to treat like any other class.

This means I can

• commit to the source control repository just like any other class
• deploy like any other class
• include the Container class in my module definition to be delivered via ZPM/IPM

This capability has been around since 2018 +/-.

and while not documented fully in DocBook the usage of %NOPARALLEL can be used to tell the optimizer not to use parallel processing.

If we look to what InterSystems provides as an application like Ensemble(Interoperability) or HealthShare we will find the class defintions exist in the ENSLIB or HSLIB namespace.  Then via Package mapping you will find that Interoperability enabled namespaces or HS enabled namespaces map the package definitions.  For example in the namespace HSEDGE1 we see

which says the classes from these packages are visible to the HSEDGE1 namespace.  

The data for the extent of these classes by default would live in the associated db for the namespace HSEDGE1.

In most cases the data for the extent is not mapped so as to live in a single univeral database but generally speaking it could be, although in the case iof the Ensemble/Interoperability and HealthShare data you shoud not do this.

The error you are seeing is a compile error, its not a run time error so the DC AI Bot isnt telling the correct answer.  The field in the RecordMap UI is a bit wonky.  If you hover over the label you get a tooltip which indicates you need to separate the parameter values by ;

So something like this works

MAXLEN=80;PATTERN=14N

which gets stored in the RecordMap definition class as

  <Field name="NPI" required="0" ignored="0" datatype="%String" index="1" params="MAXLEN=80;PATTERN=14N" sqlColumn="2" repeating="0">
  </Field>
and the generated RecordMap class now has

Property NPI As %String(MAXLEN = 80, PATTERN = "14N") [ SqlColumnNumber = 2 ];

so 

1. parameters should be separated by ;

2. for pattern you do not need to surround the pattern value by "s

3. If something like ResearchId is actually a pure numeric and number it might be better defined as %Integer

4. For something like FirstName you might want PATTERN=1.A  which means 1 to N alpha characters

You may have an issue with the format of the date.

What is the datatype of 

• somedate
• anotherdatefield

Are they %Date, %TimeStamp, %UTC, or PosixTime

I'm not certain but does MAX(somedate) cause the value to no longer be in a format that would support

WHERE dateField >= anotherDateField

When in Analyzer you can see the actual query being run aftet the query runs by clicking on the Show Query button in the toolbar

which it seems like you have done so as you have 

WHERE source.%ID IN (SELECT _DSsourceId FROM MyTable.Listing WHERE _DSqueryKey = 'en3460403753')

you can copy the query SQL Query Listing and paste into (albeit remove the portion that has

WHERE source.%ID IN (SELECT _DSsourceId FROM MyTable.Listing WHERE _DSqueryKey = 'en3460403753')

)

System Explorer->SQL and take special note of the Runtime mode of the query.

The Runtime mode has the greatest impact on columns that are 

%Date 

%UTC

as these columns will have different values based on the runtime mode(Logical/Display/ODBC)

Given that the routes in your enabled %CSP.REST class point to class methods you should be able to debug/step thru line by line by calling the class method.  In the case where you might have a POST/PUT for your route that calls the class method you can always define the formal specificaiton of the class method to accept parameters and then have the code in your class method look at either your parameter values or %request.Data to determine if the class method is being called by a Http call or your simple debugging.

When building data for a cube, either a full build or a synchronization, think about it this way.  The code generated to support this essentially does a SELECT AllOfYourDimensionsMeasuresRelationships FROM SourceTable.

when you have non primary field references these expressions are evauluated as ObjectScript expressions.

These are documented but not on a single page

%expression 

%cube

%source

%sourceID although this is typically thought of being used in a detail listing specifically if you chose the option for whereas the other's are when defining the cube dimension, measures etc

There were several questions asked during the pressentation.  One of them was 

 How can we restrict usage of the cubes so that we only allow users to specific data?

If this were SQL one way of approaching this is to define a View and then grant access to the View but not the table to users.  Fortunately Analytics has the same concept. Just as SQL tables are the resource that has the data and a view is defined representation of the data, Analytics has Cubes which has the data and Subject Areas.  

A subject is a virtual view of the cube but does not require the additional storage/build/sync time.  Within the Subject Area you can

1. Define a filter, consider we want to define a subject on Senior Citizens, we would define a filter on Age > X.  Then when we make the Senior Citizen Subject area to a user they will only ever see patients > X,

2. Define what dimensions are available 

3. Define listings

One important note about QuickStream.  If you decide to go down this path you must make certain to Clear the QuickStream as part of your pipeline.  The Message Purge that is set up as a task will purge the message header and the associated message body but when it looks at the message body all it sees is a property called QuickStreamId and it doesnt know it should also Clear the associated QuickStream object.

A while back I read Clean Architecture: A Craftsman's Guide to Software Structure and Design (Robert C. Martin Series) which I thought had a ton of useful recommendarions....not all were entirely applicable to ObjectScript but still very useful.

https://blog.cleancoder.com/uncle-bob/2012/08/13/the-clean-architecture…