BOE-XI WebIntelligence (800,000+ rows)

[MJRBIM]

We are a CRYSTAL 10 shop that is now upgrading our CR and CE software to BOE-XI (R2) including WebIntelligence. I'm not a WebI head, so I thought I would ask for some Tek-Tips advice...

One of our users would like to use WebIntelligence to interrorgate a Universe that pulls from one large history table (800,000+ rows) in their ORACLE db.

What the are basically asking for is to give them "AD-HOC" access to the whole-table (800,000+ rows) in WebIntelligence and they will drag and drop what they need out of it and then apply their own filters. They say they need to see what is in the data before they can pick the filters.

From a classic-crystal POV this sounds like a bad idea, not making your Filter parameter selections before you run the query. Also, I think that WebIntelligence has a Maximum rows returned setting of approx. 10,000 - so they will only get partial-data anyway.

Anyone had a request like this before...? If so, what is the best way to tackle this within WebIntelligence...?

Thanks In Advance!

 

[blom0344]

They say they need to see what is in the data before they can pick the filters.

I am sure they are not going to screen every record. There probably interested in a number of database columns and it is pretty easy to supply them with a distinct list of values from these columns.

What is their goal? Fetching individual records based on a set of criteria or are they going to aggregate the data as well?



Ties Blom

 

[MJRBIM]

The goal is both - to obtain individual records and also to be able to aggregate data on a region/branch level.

I think some of their aggregate reports would be better served by Crystal Reports (but that's my personal skill-set).

There are 85 colums in the table and they want to be able to select and search any of that data in an "AD-HOC" (oh, how I hate that phrase...) format using WebIntelligence.

 

[blom0344]

I would really like to know what that table contains.
If you can perform an aggregate against the table, then it contains measures (and no sane person would like to search for individual measures)

BO is perfectly suited for aggregate reports , but then again an aggregate over 1/2 dimensions will probably yield a very compact dataset

Either you have a most exceptional facttable or your user is putting up a very silly requirement.

I presume you have limited set of indices on this table?
Doing full-table scans against 800000 rows / 85 columns is not going to be a very sweet query.

I can think of nice solutions but these would require additional database objects and universe modifications

Ties Blom

 

[MJRBIM]

It's a master-table on school enrollment for individuals from different groups.

Every individual has a record for each School Year including their AGE, GENDER, GRADE, SPECIAL NEEDS, LANGUAGE, etc, etc, etc. (85 Colums per row)

The only real measure is a Distinct Count of their unique student ID for Summary Purposes...

Sample Queries could include -

1.) List individual records for all Special Needs students in the last five years.

2.) Summary Counts of all students with summary on all Grades, Genders, Languages, and Special Needs classifictaions.

 

[skrandel]

This is a horrible idea. Give them filters with LOVs in the query panel. No way you're going to do this well at the report-level.

Wrong tool for the job. You have a screwdriver, they need a hammer. It will work, but it will be very painful.

Steve Krandel
Symantec