Database layer - not enforcing joins

[ry1633]

Is there a way to check if my joins in my database layer are really being enforced or not?      I'm working on a big project that has 200+ views in my database layer, and I set up all my joins just like they are in the actual database and double-checked.   But the query subjects and shortcuts in my business layer and presentation are sometimes acting like the joins aren't being enforced,  and yet the model validates fine and doesn't throw any errors in Model Advisor.

[Ricardo Julio "Ricky" Villa]

Do you really need 200 views imported into FM Model and joins created between them?

Will it be a meaningful presentation layer to the end users / report authors?

FM Manager is not a black box that you can plug onto a database and out pops an intuitive user interface / metadata layer - you need to think about modelling on the database side or in the FM Model.

Not sure how you are testing the joins and what is suggesting the joins are not working?  What SQL is being produced when you test the query subjects?

[Francis aka khayman]

sometimes? when are those times?

anyway you can create a query and select items from query subjects which are supposed to be joined. then you can examine the sql generated.

But the query subjects and shortcuts in my business layer and presentation are sometimes acting like the joins aren't being enforced

[cognostechie]

By default, joins are not enforced in FM because the SQL type will be set to 'Minimized'. It will join only to those tables/views from which a column is used in the report. If the SQL type is set to 'As View', then the joins will be enforced but that comes with heavy performance degradation. I, personally, don't like the idea of using views in FM as that just bypasses the ability of the tool and creates
a cumbersome model which would result into problems sooner or later. The views themselves will have joins inside them otherwise there would have been no reason to use those views in the first place.

[ry1633]

As a general rule of thumb, in my Database layer, should I be using all the tables of a particular system and making the joins myself, or should I be using particular views made by my DBA that are sets of data from the tables, and just joining those views together?

[cognostechie]

Using the tables is a better idea but then it also depends on what kind of schema you have in the data warehouse. Why did the DBA create the views in the first place?

[Invisi]

In general you will have a dimensionally modelled data mart structure that you connect your framework to. This gives you a certain flexibility that is lost with views. When performance gets an issue you may create aggregates in your data warehouse and this may be accomplished with views. You can look at the quality of your dimensional model. When there is snowflaking, then the amount of joins may increase and this can influence query performance.