Data Dictionary Inheritance?

[Bob Cergol]

Due largely to legacy reasons the field names in most of the tables in my database are all caps and the names are often cryptic. Therefore I'll want to substitute captions for the vast majority of them. There are also many fields in most tables that are for internal system use, or just not useful information to the intended users. Therefore I'll want to create "core" views of the tables and build reports on those views -- never the actual tables. I'll also want to create derivative views from those core views for specialized purposes and to streamline building reports and to make life easier for non-technical users.

My thought was to do this for each table so I started to do this on the main orders table I am using for testing and learning. But when I created a data dictionary for the core view of this table, it did not inherit any of the captions -- I typed 100 of them -- from the underlying table.

I can guess why this is so but would like to suggest a nice enhancement would be to automatically inherit any data dictionary attributes in a new DD from its underlying tables and/or views.

Bob

[Carsten Sørensen]

Good suggestion! We have considered it before however we have previously not found a way to be able to do it 100% correct for all potential database connection types (i.e. access to read underlying DB objects) + haven't necessary found the golden concept for keeping the inheritance intact during individual updates (like you point out!). We will review and see whether we can get some of the way for new dictionary entries.

You might consider using Roles as well. Roles allows you to hide columns for specific users - however the approach that you are taking is valid and solid.

Thanks for the input!

Best regards,
Carsten

[Bob Cergol]

Carsten,

Could you get around the difference in database connection types by only implementing this on dynamic views derived from dynamic views? That way I would only do all the data dictionary work on "core" views that are then the basis for all other views.

While talking about data dictionaries can you provide a good example of an "enforced filter". It sounds like something I want to define but I'm not sure how to go about it or what to enter in all the columns. I could not find too much detail on this in the documentation -- which by the way strikes me as pretty darn good documentation.

Bob

[Mikkel Jensen]

Hi Bob

In another topic you actually have an very good example of a case where Forcefilter could be useful:

Example: System Alias 1 = Account ID
User Spen D. Slotsofmoney from a big account has his alias1 value set to that accounts ID as it would occur in orders, invoices, etc. for that account.

After setting up users with Alias1 information, you can on each report define that AccountID should be equal to @Alias1 providing an automatic filter depending on the users Alias1 value.

However if you set that criteria in the dictionary it will affect all reports based on that data source. And it won't be possible to create a report on that specific data source which by a mistake isn't filtered by the Alias1 Value.

To set this up simply type Alias1 in the dictionary for the field AccountID in the column FORCEFILTER.

Mikkel

[Bob Cergol]

Mikkel,

I'm not sure I'm following you completely, but I think I need the benefit of having actually tried doing this. I want to remember reading of more flexibility in this regard, i.e. I could define a specific value for an alias for some users, but use a "wild card" alias for other users, so that all users could share the same data source for various reports.

My impression so far is that the repository is hiearchical and I want to avoid a situation where I can't make some change in a data source without adversely impacting a lot of reports, and at the same time I want to try and minimize the data source definitions I need to create.

My next task is to work towards a report I can demonstrate to management here of a customer logging in to the system and getting back several reports of just their orders. If I have questions as I work on it I will post them here.

Thanks,
Bob

[Carsten Sørensen]

Hi Bob,

You are correct that the repository is hierarchical and in nature - however it's relative "forgiving" - i.e. security related issues like the forced_filter is obviously not forgiving and shouldn't be - but you can always add columns to existing data-sources or overwrite captions from the dictionary at report and form level. It's our experience that developing reports and portals etc. is a iterative process - and we believe Dynamic AI supports that very well. Sometimes you might go back and extend or change a data-source however the reports and design features adapts as needed.

It's correct that the alias use as forced filter can be extended with alias-bundles, @all conditions through the user account.

Best regards,
Carsten

[Bob Cergol]

Hi Carsten,

Regarding DD inheritance, something as "simple" as cloning the data dictionary as part of a "save as" of an existing DAI view would likely serve most of my needs -- if that's a quicker, more flexible alternative than having true inheritance. I see that unlike SQL views, DAI views cannot be based on other DAI views. However you can copy existing views to new views using "save as" and then modify them as needed -- so effectively the same result.

Bob

[Carsten Sørensen]

Hi Bob,

Thanks for the feedback!

I will investigate smarter - snapshot - inheritance and get back to you.

Dynamic AI Joins allows that you use underlying Dynamic AI views - but you are right that we don't currently have a rule for how to incorporate Free Dynamic AI SQL Views into other Dynamic AI SQL Views.

You are correct with the copy - however it will not inherit changes - and vice versa - that may actually be a benefit... We'll see - we'll keep your suggestions in mind!

best regards,
Carsten

[Bob Cergol]

Hi Carsten,

You say: "Dynamic AI Joins allows that you use underlying Dynamic AI views".

That's got me thinking I should not do any joins in the DAI views. Just create one core view per table, and a data dictionary on that view, and then create joins using those core views, and then build the reports on the DAI joins. I'll have to think that through. I'll be studying the docs over the holiday vacation.

Yes, I think it well could be an advantage not to inherit changes in view-specific DD's cloned from another DD.

Bob

[Carsten Sørensen]

..."to be or not to be a Dynamic AI Join"...

It depends on the sophistication needed in the join. Sometimes you might have to use a Dynamic AI Free SQL View for joins - I use Dynamic AI Free SQL Views for joining only when I need to do conditional joining or combine a join with union, have many objects to join etc. OR when I need to incorporate runtime @PAR input from the user.

But it is a good "best-practice" Dynamic AI design rule to use Join-builder as much as possible over Dynamic AI SQL views + use +Columns / SQLVARS in the dictionary to add additional computed columns to reports instead of making them part of a view definition.

I understand that you are concerned about getting the fundamental data-sources right - and I'm sure that will be a huge benefit for you going further - however it's not our experience that adding/changing database schema's and data-sources after implementation is causing a lot of frustration. E.g.: you can always modify captions in the dictionary and have the changes flow though on the reports immediately, you can always add an additional SQLVAR column to a dictionary giving all underlying reports a new available column etc.

Best regards
Carsten

[Bob Cergol]

Ah, but since DAI joins can be built on DAI views, it seems I can have the best of both worlds, i.e. I can use my params, alias values and other where conditions in core views, define my global SQL variables, captions, etc. and if a report didn't use columns from one of the tables DAI would optimize the join by leaving it out.
Bob

[Carsten Sørensen]

Hi Carsten,

Regarding DD inheritance, something as "simple" as cloning the data dictionary as part of a "save as" of an existing DAI view would likely serve most of my needs -- if that's a quicker, more flexible alternative than having true inheritance. I see that unlike SQL views, DAI views cannot be based on other DAI views. However you can copy existing views to new views using "save as" and then modify them as needed -- so effectively the same result.

Bob

Hi Bob,

We've added a new feature to allow that Dynamic AI SQL views can be included into other Dynamic AI SQL views. Using the syntax: @DYNAMIC_AI_SQL_VIEW:1717@ you can get the source SQL from the view ID 1717 inserted at runtime.

Like:

SELECT O.[OrderID], O.[OrderDate], O.[Customer], O.[Product], O.[SalesPerson], O.[SalesPrice], O.[Quantity], O.[CostPrice], O.[VAT]
FROM Orders O
WHERE O.[OrderID] in (SELECT orderID from (
@DYNAMIC_AI_SQL_VIEW:1717@
))

becomes:

SELECT O.[OrderID], O.[OrderDate], O.[Customer], O.[Product], O.[SalesPerson], O.[SalesPrice], O.[Quantity], O.[CostPrice], O.[VAT]
FROM Orders O
WHERE O.[OrderID] in (SELECT orderID from (
SELECT O.[OrderID], O.[OrderDate], O.[Customer], O.[Product], O.[SalesPerson], O.[SalesPrice], O.[Quantity], O.[CostPrice], O.[VAT], O.[Invoicedate]
FROM Orders O
))

The RightID's are visible on mouse-over on the views in the explore menu. Dynamic AI Join-builder joins can be inserted this way as well.

Will be available with next build.

Best regards,
Carsten

[Carsten Sørensen]

Hi Bob,

For the new version we have completed the following new features for Dictionary inheritance:

1. When creating a dictionary for a Join-builder view - already entered captions on the underlying data-sources will be automatically cloned/copied to the join-builder dictionary entries.

2. When updating any dictionaries - multi-select checkboxes will be available for the DB explorer allowing to check which dictionaries should be inherited to the current / actual dictionary being updated.

Best regards,
Carsten

[Bob Cergol]

Hi Carsten,

Just to be clear, this is for Dynamic Joins only, not for Dynamic Views -- right?

I was working primarily with Dynamic Views since I'm not as worried about the overhead of unused columns in reports as I am desirous of the features unique to Dynamic views. But maybe I should rethink that and go with joins and "plus columns" in the reports. At the moment I can't quite remember why I decided dynamic views were better for me to use than dynamic joins. (I can easily do my joins in the views and there whatever columns I picked in the joined tables would frequently be used in reports anyway.)

Bob

[Carsten Sørensen]

Hi Bob,

Not exactly!

For Dynamic AI Join-builder joins we know the underlying sources (for sure) and we can't really harm anyone by automatically inherit from the sources during CREATION of the dictionary. However in order for the sources to inherit UPDATES into the Join-builder dictionary - you would have to mark which of the sources that you want to have impact. That way you can do partial update / overwrite on existing Join-builder views.

For everything else: Database tables, views, synonyms, Dynamic AI SQL views you can inherit as well - but you will for these type of objects manually have to select from which other objects you want to inherit.

It sounds a bit weird - but it should solve the issue of having to manually maintain captions within multiple objects and instead be a function to easily update entire or part of existing dictionary objects.

Best regards,
Carsten