Import Layer, managing large number of query subjects ...

[Alp]

In the light of many discussion on the 3 layers design, is there any reservation for having sub-folders in the Import Layer?

In fact, we may have hundreds of query subjects both representing physical data model and model query subject. It is real hard to scroll such a big flat list.

Assuming that the intent is to create all the links between query subjects in the Import Layer, would it be any "violation of best practices" to have sub folders in the Import Layer?

Example:

Import Layer
    SubFolder1
    Subfolder2
    ...
Model Layer
Presentation Layer

Thanks,
- Alp

[IceTea]

No, subfolders are not a violation of the best practices.

We use them also for organizing our objects in all Layers. Contrary to a real namespace, subfolders doesn't affect any other things like model object ids and so on. Of course you can also join and link objects / query subjects in different subfolders, there is no limitation while using subfolders.

[cognostechie]

It is perfectly fine to have folders in the Import Layer as well as Business layer. Folders are merely for organising and in some cases act better than Namespaces because moving things around with folders do NOT break any reports since there is no reference to folder name in any of the query items at the report level.

It is also fine to have folders inside one MQS to organise Query Items so that the user does not have a big list of items to scroll thru to find one item.

I always use folders in my Import Layer as well as Business Layer if required.

[Alp]

May be I have a stupid question, but what is the basis for the best practices recommendation of having relationships defined in the Import Layer only? I thoughts that this is somehow related to where Cognos store the relationships and having the relationships defined in different folders was not a good idea at the first place. Am I missing something?

- Alp

[IceTea]

As i got it, the main thing is this, that the relationships normally apply to tables in data bases, so the import or databse layer is logically the right place to define them in your model. The benefit of doing this here is, that in your model layer (or however you call the thing between import and business layer) you don't have to care about relationships anymore. You don't have to care and define relationships for every single query object you create in this layer. All relationships automatically depend on the import layer objects, so you're done here. This makes work for me transparent, fast and effective.

The other thing is to define the relationships at ONE place and not over all three layers. This is not a technical reason (defining relationships in more layers works), but more a helpful strategy for the modellere not to get lost in a jungle of layers and relationships.

Conclusion:
Storing and defining relationships in different folders in one layer is GOOD.
Storing and defining relationships in different layers is NOT GOOD.

[MFGF]

May be I have a stupid question, but what is the basis for the best practices recommendation of having relationships defined in the Import Layer only? I thoughts that this is somehow related to where Cognos store the relationships and having the relationships defined in different folders was not a good idea at the first place. Am I missing something?

- Alp

It's all to do with query minimization at runtime.  Generally the abstract layers in an FM model are made up of "model query subjects", each of which may span several "data source query subjects".  If these model query subjects have no relationships, queries based on them can be fully minimized to retrieve only the selected items - if these came from only one of the underlying data source query subjects, then this is the only table that would be queried.  As soon as you add relationships to a model query subject, queries based on this are handled "as view" - ie the query will span *all* the underlying data query subjects used to define the model query subject, even if the items being retrieved come from only one of them.

You can see this in action if you set up a simple test:
1. Define a new model query subject that brings in items from two data source query subjects which have a relationship between them.
2. Select a single item from the model query subject and use the 'test' feature to generate a query - look at the SQL of the generated query, and it should retrieve from only one table.
3. Add a relationship from the model query subject to something else, and repeat step 2 above - this time the query should span both tables from the underlying data source query subjects.

Regards,

MF.

[Alp]

It's all to do with query minimization at runtime.  Generally the abstract layers in an FM model are made up of "model query subjects", each of which may span several "data source query subjects". 
...

Good and simple explanation! One thought crossed my mind while reading your explanation though. We may have a number of aliases created as model query subjects (not from multiple data source qs). These aliases may be for dimensions as well as for fact tables. In this case regardless of the layer where one create relationships between such aliases, query minimization will be achieved. Am I right?

Probably IceTea has a point here that having all relationships in one layer has some logical rather than technical basis?

- Alp

[cognostechie]

I would like to add a technical fact here.

If you create joins in the Import Layer as well as Business Layer, the joins of the Business Layer (assuming joins in the Model Query subjects) will get a higher priority as opposed to the joins in the
Import Layer. This is sometimes required to override the join condition of the Database Query subject in the Import Layer (for resolving what Cognos refers as 'Blind Spot' and things like that).

That being said, it is better to create joins only in the Import Layer and create joins in the Business Layer only when no other option works.

It is perfectly fine to have folders in the Import Layer and join the DQS across different folders.  Unlike namespaces, folders are not physical. Creating joins across different namespaces may cause problems, though technically you are permitted to do so. However, I had a problem when using 8.2. Two Query subjects were joined, both were in the Import Layer but in different namespaces since we were dealing with 2 schemas so we had 2 namespaces for those schemas. The join failed in Report Studio. After a lot of brain wrecking, I could not find any problem. Then I replaced the namespaces with folders and the join worked !! There used to be bug there, not sure if it is fixed in newer versions. 

[Alp]

I would like to add a technical fact here.

If you create joins in the Import Layer as well as Business Layer, the joins of the Business Layer (assuming joins in the Model Query subjects) will get a higher priority as opposed to the joins in the
Import Layer. This is sometimes required to override the join condition of the Database Query subject in the Import Layer (for resolving what Cognos refers as 'Blind Spot' and things like that).
.... 

Can not you have the same issue in the Import layer?
I assume you may create model query subjects in the Import layer or you imply that under no circumstances should one have model query subjects in the import layer?

- Alp

[cognostechie]

Model Query subjects should be created in the Business Layer, not the Import Layer. What I meant was that you can have joins in the Business Layer too in the MQS, though not a preffered practise.

[Alp]

Model Query subjects should be created in the Business Layer, not the Import Layer. What I meant was that you can have joins in the Business Layer too in the MQS, though not a preffered practise.

Thanks for quick reply. I appreciate it.
When you create an alias for a shared table to resolve a relationship loop, where do you put the "aliased" query subject In the import or business layer?
Let us assume that you decided to create an alias as a model query subject rather than an alias shortcut.

- Alp

[IceTea]

When you create an alias for a shared table to resolve a relationship loop, where do you put the "aliased" query subject In the import or business layer?

We do this always in the import layer.

[cognostechie]

When you create an alias, you create the alias of the Database Query subject so there is no need to create it as a Model Query subject. Like Icetea said, we create the alias always in the Import Layer if it is for joins.

[Alp]

When you create an alias, you create the alias of the Database Query subject so there is no need to create it as a Model Query subject. Like Icetea said, we create the alias always in the Import Layer if it is for joins.

You mean you use "Alias Shortcut" for that, right?

[cognostechie]

Yup !

[Alp]

Yup !

Last question, I promise :-)

What do you do exactly to move/copy the "Alias Shortcut" so it is available in the Presentation Layer?
Do you push it from the Import Layer to the Model Layer and then to Presentation Layer or you bypass Model Layer?

There is no catch in my question, really, just wondering how you do it.

Thanks in advance,
- Alp

[cognostechie]

Model Layer is usually different than Business Layer unless you are referring to Business Layer as Model Layer.

Once you have the alias shortcut ready with the joins in place, you create a Model Query Subject in the Business Layer. You include items from that alias shortcut in this Model Query Subject, re-name the columns to give meaningful/user-friendly names. For this purpose, the alias shortcut behaves the same as a Database Query Subject. Re-naming is to be done in MQS, not the original Database Query Subject.  Then you create a 'shortcut' (not 'alias shortcut') for this Model Query Subject and move the shortcut to Presentation Layer which you publish as a package.

You might notice that instead of creating an 'alias shortcut', you could have created another Database Query subject too. Well, the problem is if another column gets added to the table in the DB, you would have to update BOTH the Database Query Subjects. With the alias shortcut, you have to update only one Database Query Subject and the shortcut gets updated automatically. So it is useful when you have to create multiple alias shortcuts for join strategies.

[Alp]

Thanks!