Note about DAI Views and Dictionaries

[Bob Cergol]

Hi,

This is just feedback for other, future new users of Dynamic AI.

I find myself favoring the use of Dynamic SQL views where I feel I can better control the definition of my tables and the joins from DAI's perspective. Much of this could stem from legacy issues from a non-SQL database, specifically field naming conventions, and perhaps data normalisation shortcomings. (I found a little redundancy in tables very useful and convenient in my past development work.)

Anyway, when I first started with DAI I got into some trouble a few times and had to delete report designs and recreate them when results seemed weird, i.e. out of "alignment" with actual data. I now understand what I did wrong.

In my DAI SQL views, I changed column names, i.e. captioned them in the SQL code itself, or changed the previous caption. I may even have changed the sequence of some columns. If you are creating and maintaining dictionaries on these views, DAI will see the changed caption as a "new" column and add it to the dictionary, leaving the previous entry -- now non-existant in the actual underlying view. Also I think DAI is using ordinal referencing of the columns and so if column #59 in a view becomes column 62, this will confuse the reports designs.

I find when I do my use joins in my view, it is very useful to caption identically names fields in the view -- not in DAI's dictionary, in order to make life easier, i.e. "Jobs.Title Job_Title" and "Parts.Title Part_Title". That way it is clear what the source fields are in DAI's dictionary, which of course sees all the fields in the view as a single data source and so is not prefixing them with the actual table names.

Regards,
Bob

[Carsten Sørensen]

Just to add to Bob's recommendations:

1. The ordinal position is used only for presenting the available columns in Design view - it is not a material part of the report design - or in any way used as a reference to the columns in an underlying data-source.

2. Changing captions in a view might be useful - however the captions will become the data-source-field that IS the reference for Dynamic AI into the underlying data as well as when looking up central functionality in the dictionary. I.e. changing a caption equals removing a column and adding a new. If columns are disappearing from a report definition it's most likely because the column name was changed in the view or the column has been changed in the database behind. All changes are documented in the Report Documentation.

3. The prefixing of underlying data-sources is intended to help end-users group similar columns of a joined table together - as e.g. used by Join-builder floating forms.

4. Caption maintenance in the dictionary is solely changing the default caption of a column and is not interfering with the SQL view definition. In some scenarios a stabile data-source without to many changes is preferred.

5. The caption/help-text attributes of the dictionary is first and foremost a feature to ensure that captions are global - and will follow the user-preferred-language where that is relevant. In some cases it might make sense to create report-friendly data-sources - however this isn't necessary - and will require separate maintenance when new columns are added to the underlying tables in the database - where Join-builder or connections directly to the datasources will handle such changes automatically and make them available to end-users.

Best regards,
Carsten

[Bob Cergol]

Thanks for the added comments Carsten.

These phrases really stood out for me: "stabile data sources" and "report-friendly data-sources".

I can see where the SQL views might be more subject to change and thus make me more vulnerable to tripping myself up. However, I have found, it is safe to append new columns to my views and then execute an update on the dictionary. Beyond that it is possible to manually edit dictionary entries, including removing them. I can see how both dynamic joins and dynamic views can be used in different circumstances to "keep it simple".

As a general rule in the actual back-end tables, I have almost always appened new columns and probably have never deleted any.

I was working on a new schedule report and when I used join builder to join all the tables I knew I would need some fields from, I ended up with more than 700 fields in my dictionary and in the report design! With my legacy naming conventions the "J#" prefixing of the names made it hard for me to keep track of which source table the field came from. By defining my own view I selected only the 54 I will actually use frequently in these types of reports.

I can see the advantage of doing all captioning in the dictionary and not the view however, because in the report design, you then see the original table.column designation and the caption so you always know what the field is. But in a several cases, I wanted to "correct" the underlying field name in the view itself.

Bob

[Carsten Sørensen]

I agree that 700 fields is a challenge to work with in available fields and I understand your decision to use "prepared" subset of fields in views. I primarily wants to justify the use of the dictionary to centrally maintain captions.

The Jx prefix has been discussed on several occasions - it could be the source table name instead or A B C.. however we found that the use of full table names could be a bit overwhelming and it is making the column names very long. As I recall one benefit of the Jx is that you can change a table sub-object of a join-builder view with a Free SQL view in case you find out later that you need to set special conditions or make special operations on the subset of records. Replacing an object should keep the same prefix - everything else being equal.

In general you should only have to type Jx column names when creating more advanced formula's - IMO making the prefix more or less irrelevant apart from when reference is needed to the source object.

Best regards,
Carsten

[Bob Cergol]

I think the "J#" designation is perfectly logical and I wouldn't want to see it changed.

Also, in my 700+ fields examle, I did notice each group of fields was titled in bold type showing the source table or view name alongside the "J#" designation.

Correction to my earlier statement about appneding new fields to a DAI SQL view: After "updating" the dictionary, the new fields show up there and in report design, but not the captions entered in the dictionary. This is odd because I can change a caption on previously existing fields, and the new caption shows up in the design for existing reports, and is used by those reports. But on these two new fields I entered the caption does not.

Bob