Adjust Query Item based on Page

[UcfMike]

I have a report with nested page sets. I have to change the grouping level of the lists in the report depending on the page.

My initial thought was to try this in a query item:

Case PageName()

When 'Project' Then [Project]
When 'Region' Then [Region]
Else 'Unknown'

End

This doesn't work because PageName isn't recognized. Any idea on how I could make this works.

 

[ghostraider]

Hi UcfMike, Sorry i don't have an answer to your question instead i have a question for you. I have seen your posts about Framework Modeling on this site and other sites as well and was wondering if you got the OLTP database modeled in Framework without a data warehouse or data mart. If yes, could you please let me know if it is giving you consistent results and also the performance issues you are facing if any? We have a 300 table OLTP database and we are trying to model it in FM. It just has relations as they are in the ER diagram and not modeled dimensionally. Now, we are planning to build a data mart for it and i was wondering if that step is really needed or if we could just model dimensionally in FM. Our model has a lot of performance issues and some inconsistent results as well.

And finally, were you able to find a good trainer? Please let me know if you got trained and if it was good.

Thanks much.

 

[UcfMike]

We did get our relational database modeled in FM, and we did it without a datamart or data warehouse. Until our upgrade to Cognos 8, we had been using Impromptu for our reporting needs. The results between our Impromptu reports and Cognos 8 reports are accurate.

As far as performance, I'm sure we could have built a faster reporting system if we had built a datamart or data warehouse, but that wasn't an option to us.

I attended the first part of the Cognos FM class, but that was it as far as any training or trainers. It taught me somethings, but Cognos wants everything developed as a star schema, and that didn't work for us.

We built our system by first building a database layer, which mimicked the existing relational database, and then by building what we called business views. This is where we got rid of loops and joined the table according to the reporting groups needs.

Our FM isn't dimensionally modeled. One of the problems we ran into early was determinants. We didn't start to get consistent results until we removed all determinants in our database layer.

Hope this helps some. Happy to answer more quesitons if you have them.

 

[ghostraider]

UcfMike, many thanks for the reply. I do have some more questions. When you imported the metadata into your database layer based on your OLTP system, did you join tables based on your ER diagrams in this layer? How many tables did you import from your relational database? And when you say Business Views did you have several namespaces for each business idea (Ex:Sales, Retail)? Were the same tables (Ex:Customer) used in multiple namespaces as and when they were needed for a certain business idea? How about 1..n and 0..n cardinalities? Since cognos treats the n side as fact and there will be lot of 1..n's in the OLTP system. Do your users think Impromptu was faster than Cognos 8 especially because of the model without a data mart in COgnos 8? I would really appreciate if you can put in a little sample in your reply.

And this is how our current model is, i have been working on this model for a long time. All the tables imported into the Database Layer and joins are placed based on the ER diagram. So we have lot of 1..n's and some 0..n's in cases where users wanted to see missing data as well. I have picked this model up from someone else and there are lot of loops which have to be resolved for sure. And there is no middle layer, it is directly the presentation layer. I made my users aware that a new model needs to be built but i am trying to figure out how to actually build a model to achieve consistent results. My manager has plans of doing a data mart but users would like to report on live data.

So please let me know anything i am supposed to know. Many Many thanks for your time.

 

[UcfMike]

In the database layer we did join the tables based on the ER diagrams. This is where I also removed all determinants, and set all of my query item attributes to unknown or unsupported.

I created a namespace called business views and then created namespaces inside that called sales retail, etc. In the namespaces I created model query subjects based off the database tables. This is where I would resolve loops, combine tables, etc. You would see the same tables referenced in different namespaces, but the joins to other model query subjects might be different depending on the reporting need. Our cardinality would be determined by the reporting and user needs.

Things aren't always smooth, and we spend a lot of time hacking together solutions. You'll find you do what works, but the reason you do it is not always clear.

We didn't do a middle layer per say. Just the database view and business views.

The main thing you need to know, the right way is the way you get it to work. Your going to spend a lot of time building model query subjects, joining them, testing them, and then looking at the data to see if your results make sense.

Sometimes you'll create tables in your namespace that are identical, excpet their joins to a particular table are different.

Be consistent with your attributes throughout all business views, and decide how you want dates displayed and set that consistenly as well. Also, use descriptive names, they help everyone.

If you have more specific questions let me know, it's a lot.

 

[UcfMike]

One thing we did do with our joins is this.

this Diagram is rough but here it goes.

if this was our database view

[a]>----<[c]--<[d]

We would roll that up, which is what you'll learn in the training classes to this

[a]>--[b+c+d]

but we would do it in our business view.

You spend a lot of time doing that, which i'm sure you're already aware of.

You have to get the determinants out of the database view though, they always caused problems and cognos adds them by default.

 

[blom0344]

To add to Mike's experience:

Cognos 8 modelling is centered around the starschema. Even if you are reporting against an OLTP system, make sure you remodel to end up with just 2 type of model query subjects:

1. Facts (just keys and measures)
2. Dimensions (keys and attributes)

Only specify determinants if you have the need for multifact/multigrain queries and then only specify them in the modelling layer (NEVER in the database layer)




Ties Blom

 

[UcfMike]

We never got our views into true star schemas, but we got as close as possible.

If you can though, that makes life nicer on the reporting side.

 

[ghostraider]

Ucfmike and Blom0344, appreciate your inputs. So, when we consolidate tables using model query subjects based on requirement do you have coalesce and full outer joins in your model query subject queries itself? Are there any cases like that? And doesn't the SQL be very very difficult to comprehend even though we are doing a simple report? Please let me know. Thanks.

 

[blom0344]

Normally you would not have the need to explicitly write full-outerjoins. Cognos has a mechanism (stitch-query) to build one herself if you are going multi-fact.

As a former BusObj designer I was used to checking SQL generated. With Cognos it is nigh impossible since ALL definitions need to be worked into the SQL. If the model is sound, then checking the SQL would not be necessary..

Ties Blom

 

[UcfMike]

We never had full outer joins in our database view, though when joining up query subjects in our business views, we sometimes did.

The only time I would check the sql in FM was when the reports didn't work properly. I found that I would have problems when Cognos was producing Coaelsce statements. I would usually rework the model at that point.

but not always.