FM Prompt Modeling

[UcfMike]

This question may point out that I lack some formal understanding of how FM works, but I'll ask anyway.

While building a report with a prompt page, I noticed that when running the report the prompt was taking a long time to load. This actually made sense because the query item the prompt was pulling from is a large table that takes FM a minute to get together. So I figured I could do two things. One, remove that query item from the query subject and place it into its own query subject. That would be simple. My other thought was to create a new query subject, and to store all of my prompt parameters in it. Is this second way possible if you're working with a relational database system.

 

[blom0344]

The time it takes to load all the prompt values seems to depend also on the number of distinct values to display.
Cognos needs to concatenate these values into one long 'array' to present them.
So, the size of the table queried may not be the bottleneck, though that should be fairly easy to test.

A good strategy is to make sure that Cognos only needs to display those values for the prompt that have factual data associated with it. This also prevents choosing a prompt value and not having data returned.

(Obviously the last remark does not apply when you already base the prompt on a fact object)

Ties Blom

 

[almeids]

No need to be shy, the number of things of which I lack an understanding continually astounds me...
I'm not sure what you hope to gain by rearranging your prompt items in FM - you're still going to have to scan that large table. Fact-based prompting is good from a functionality perspective - the empty report runs Ties mentions can be frustrating for users - but the downside is that by considering the facts you give up performance. Specific circumstances may warrant a pure dimensional prompt: if your users "know what they're doing" in terms of making their selections, or if the universe of values is reasonably limited such that null report runs are relatively unlikely, or if the report itself runs quickly and is run frequently, you may want to opt for the better performance in the prompt. For the best of both worlds, you can generate a fact-based dimensional subset table ahead of time (assuming you are working from an ODS or DW and not reporting "live") and use that for prompting.

 

[blom0344]

but the downside is that by considering the facts you give up performance.

I wonder if this is always the case. If your dimension table stores 10000 items and only 500 exists in the fact, than scanning the fact would result in a much shorter list of values to be displayed.
From what I have been told (from someone with much more experience) returning a long list from the server can be quite a performance drag itself due to the nature in which the prompt-values are stored..

Ties Blom

 

[almeids]

Good point. But if those 500 repesented in the facts are represented in a million rows, then...

Of course it also depends on your indexing, degree of denormalization, and other environment specifics. I've had cases where a (multiple) fact-based prompt page that took a minute or more to load came down to several seconds by switching to the hybrid "best of both worlds" approach.

For us a complicating factor is data-level security, which filters the fact query subject based on 3 different dimensional tables; this slows down fact scans and makes indexing solutions iffy at best. Here we've opted to apply security filters to the prompting dimensional tables as well, so prompts don't include what the users aren't allowed to see but do potentially include factless items.

Best I can offer is "your mileage may vary" and of course, "experiment"!

 

[UcfMike]

Thanks for the replies to this question. I remodeled the business view in question, so things are now running faster.

For my purposes, I'm working in a transactional database, so there isn't a huge concern for the fact vs dimension argument.

With our database, the sales office are all store in one table, so the data is limited to that. It's a fairly small table. The problem would be combining other prompt fields into one query subject, it just doesn't work out cleanly.