More Modeling Questions

[UcfMike]

I have table fruits and table apples, and in the database they have a 1 to many relationship. I bring them into Framework manager and create a 1 to many relationship. As I continue to model the data I realize it would make more sense if these two tables were combined into one query subject.

My question is, do I need to keep the relationship that I've already created between the two tables when I make my new query subject, or could I delete the relationship between them?

 

[blom0344]

I think you're question is similar to the example in the fasttrack course on orderheader and orderdetails in the GO operational framework.
In that example orderheader and orderdetails are merged into a new model query subject in the model query subjects namespace. (transformation layer)

Ultimately objects for reporting will be taken from this model query subject (and not directly from the original 2 tables). However when creating the new model query subject the relation between the tables will be derived from the existing join in the datalayer.

So, best practice is to change as little as possible from the datalayer. When publishing a package you can hide the datalayer, but it needs to be there for th higher levels to work..

Ties Blom

 

[UcfMike]

There is a web seminar on the Cognos web site where they go over this that I just watched. In her example there were times when she didn't keep the ER relationships but adjusted them based on busienss rules. In another example she did do what you are mentioning.

I've read a lot of the Framework Manager manuals and best practices, but the examples are vague and there answers are not comprehensive.

 

[blom0344]

The fasttrack manual is pretty clear on this. By keeping the database layer as close as possible to the actual database it would be easier to propagate changes in the future.
In reality we seem to mix both possibilities (check with our modelling guru)..


Ties Blom

 

[UcfMike]

Ha, modeling guru, we ain't got no modeling guru round these parts.

So far the most success I've had is creating what I guess would be called the data layer, then creating a new layer and creating model query subjects.

Now I'm creating new joins in my new layer based on the model query subjects I create. Does this make sense, or are all the joins created in the data layer?

 

[blom0344]

Yes, that does make sense. In fact, if you follow the Cognos modelling doctrine you are forced to.

The second layer should only only have 2 type of model query subjects left:

1. True facts (= dimensional keys + facts)

2. True dimensions (= keys + attributes)

So, if you have a facttable with keys,attributes and facts, then - according to the true concept - you are supposed to

create 2 model query subjects out of the facttable

(see 1 and 2 above) and join them over the primary key

The dogma is that there should not be any attributes left in the factobject ANYWHERE.

The Cognos engine is smart enough to grasp that - if you select objects from both query subjects - it will only read the real table just once (and won't apply the join)

So far , so good.

What we just found out (not me, but our guru) is that this only works if you let the subject with the attributes be unjoined. If you join another query subject (fact), then the Cognos engine DOES apply a redundant join.

Are you still with me?

Probably not, I found thermodynamics at university to be a whole lot less demanding..


Ties Blom

 

[UcfMike]

In the current model I'm working on I've have my data layer. Then I've created another layer I call reporting where I've created certain query subjects. Now I haven't joined any of the query subjects together, yet the reports I'm producing off of them are producing the correct results.

I wish I knew why I didn't have to join those query subjects to each other.

 

[blom0344]

Try and publish a package without the data-layer. You should find that reporting is a mess.
If joins are not expicitly created in the transformation layer ,then Cognos will try to resolve with the datalayer relationships.

Ties Blom