Wide Fact Tables

[ComplexQry]

I have a business partner that has a fact table that is huge...  It has probably over 300 columns.  So many the table won't validate in Framework Manager.  So I have a few questions...

1. Is the table not usable since it won't validate?

2. If it isn't usable, do you have an suggestions on how to get around this (Besides restruction the table)?

3. (This one may just be dumb) But what is the best way to break this table up into a business view without making it a maintenance nightmare?

Thanks in advance.

[cognostechie]

Yes, the Query Subject would not be able to read the data unless it validates. You can open the Query Subject and write your own SQL like 'Select field1, field2....from ...'.

I don't know if having 300 fields in the table is causing the problem. By default, FM brings in all fields but you can write your own SQL to bring in only those columns that are required. That would not be the best practise as everytime a field is added to the table, it would have to be manually added in FM.

To answer your question about Business View, the only way to avoid maintenance is to use the SQL that is not specific to the fields. This would be the SQL that is generated by FM which is 'Select * from <data source name>.<table name>' but this would again go back to your problem of 300+ fields.

[blom0344]

I have a business partner that has a fact table that is huge...  It has probably over 300 columns.  So many the table won't validate in Framework Manager.  So I have a few questions...

1. Is the table not usable since it won't validate?

2. If it isn't usable, do you have an suggestions on how to get around this (Besides restruction the table)?

3. (This one may just be dumb) But what is the best way to break this table up into a business view without making it a maintenance nightmare?

Thanks in advance.

Why not update its definition? Hardcoding it is not best practice, as it will not allow auto-updating in the future..

[ComplexQry]

Yeah, sounds like that may be the only option...

So this leads me to the next question.  How do I organize this huge fact into a Business View?  It needs to be organized well for ease of use for the end user, so I would want to logically group fields.  In the BO Space you can move fields into folders however you want.  But within the Framework Manager Realm, I haven't been able to figure that out.  So as an example, there are measures, Time, Location, Product all within this fact.  I want to take these fields and put them into separate folders.  How can this be done in Framework Manager?

[blom0344]

Cognos8 offers the Query Item Folder as such. It is less intuitive that BO's option, but it works much the same..

[ComplexQry]

Hrm, I must be doing something wrong then...  Assuming I am talking the same as you (Create -> Folder), I can't add individual fields, only Query Subjects (Tables).

[ComplexQry]

Ok, so I found the actual "Query Item Folder".  That is better, and it may be all I need.  But if I truly wanted free reign to put whatever I want in a folder, I am still constrained to a Folder to Query Subject Relationship.  I can't put fields from two different Query Subjects into the same folder easily.

[blom0344]

Perhaps not as easily as with BO, but a universe does not allow you to remodel the way a framework does. I started my BI career with BO, but the FM concept is overall more sophisticated. Not surprisingly as it is at least 10 years later conceived. But in some parts BO is just a tad more flexible..

[MFGF]

It sounds like you just need to create a new model query subject and drag the relevant items into it, rather than a folder or query item folder.

MF.

[ComplexQry]

It sounds like you just need to create a new model query subject and drag the relevant items into it, rather than a folder or query item folder.

MF.

I guess my only question is do these Model Query Subjects have to have relationships with them?  Or do they just inherit the relationships from where the Query Items are coming from?

[MFGF]

I guess my only question is do these Model Query Subjects have to have relationships with them?  Or do they just inherit the relationships from where the Query Items are coming from?

No they do not need to have relationships, provided the data being brought in is linked elsewhere in the model and returns correct results.

MF.

[ComplexQry]

Beautiful, it worked perfectly.   :o