Meta-Data Caching

[RobsWalker68]

Hi All,

A quick question on metadata caching in Framework Manager.  Quote from FM User Guide:

"Framework Manager stores the metadata that is imported from the data source. However depending on certain actions you take in the model, this metadata might not be used when preparing a query. In most cases Framework Manager accesses the metadata that has been stored in the model instead of querying the data source. The main exceptions are:

1.   The SQL in a data source query subject has been modified.
2.   A calculation or filter has been added to a data source query subject."

When talking about the SQL to what degree must it be modified to disable metadata caching.  Is it any deviation away from the standard Select * from table A.  Specifically, would the listing of columns in the select cause disable it or is it just the addition of filters, joins etc. 

I thought it was any change, however when running the Model Advisor on a table with a modified SQL of "Select column A, column B from Table C" it didn't pick that up as a metadata caching issue.  Any thoughts?

Cheers

Rob   

[MFGF]

I have seen this behaviour too. When adding derived columns/joins etc the metadata caching is definitely disabled and queries rely on metadata callbacks at runtime, but nothing seems to flag if you just limit the SQL to a subset of columns. Having said that, why would you want to do this? Query minimization will automatically limit retrieval from any query subject utilising a 'Select * from xxx' to just the columns required for the query, so it seems a little pointless to hard code the columns in the select in practice, and would also make updating the query subject more difficult where extra columns are added to the underlying table. Or is it just something you are wondering about and not really intending to implement?

Bottom line - you seem to be able to get away with it. I would always recommend not doing so though :)

MF.

[RobsWalker68]

I'm cleaning up a collection of frameworks at a client site that were built some time ago by various developers and its just one of the things I flagged up.  What has been lost in the mists of time is why they are hard coded in the first place.  There are some reports written directly against the database layer so perhaps it was a rudimentary form of object security to withhold certain columns, but who knows  ;D

I've moved the obvious i.e calculations, inline functions, filters to the business layer where they should be but I'm left with a large number of hand coded data source objects. Oh well know what I'm doing this afternoon !!!

Cheers

Rob

[cognostechie]

The newer versions of FM use the column names when you create a new Database query subject and it still works fine in terms of minimised SQL. Select * was in older versions. Even a customised SQL with joins could still generate a minimised SQL instead of using the entire code. However if there is a calculation which is a result of the entire query being executed first then it would definately use the entire code. 

[MFGF]

I think there are two separate issues here - minimised SQL and metadata callbacks. Adding a join into the SQL will still allow query minimisation, but will prevent metadata caching, resulting in callbacks to the db for the metadata any time the structure needs to be determined. It will also prevent full query minimisation once relationships are added to the query subject, so that the join is always honoured.

I am running 10.1.1, and all my imported query subjects are set up as 'select * from ...' I have never seen the import wizard do anything different unless you choose to import only a subset of the columns from a table. Am I missing a setting somewhere?

MF.

Sent from my iPad using Tapatalk HD

[cognostechie]

Most probably your model is upgraded from an older one that always had Select * ?

Try running the metadata wizard and select one table only with all columns. Even if you select all columns, it still writes the SQl with all column names. Better still, try creating a new model.