Question Re: Web intelligence not retrieving all the data in Technology Q&A

Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 14:49:33 GMT

We created a new Web intelligence document base on a universe we created using designer and the SQL generated from the web intelligence document retrieves much more data than the report. Are there limitations on how many rows are retrieved? I have the "limit size for result set" unchecked in designer.

Re: Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 15:08:31 GMT

Is there a limit on the number of rows returned on the query level?

Re: Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 15:11:02 GMT

Max rows and max time are both enchecked at the query level

Re: Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 15:29:05 GMT

How many rows are retuned via Webi?


=NumberOfRows([Query 1])

Re: Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 15:48:58 GMT

Where do I see the number of rows?

Re: Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 16:06:28 GMT

Create a variable in the Report.

For example,


x=NumberOfRows([Query 1])

Where

[Query 1]

is the name of the data provider. (The square brackets are required.)

x contains the number of rows.

Re: Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 16:37:14 GMT

Is the data provider the Universe name that we are using to build the Webi doc?

Re: Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 16:39:04 GMT

No. It's the query name.

Re: Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 16:47:31 GMT

I get a WIS 10006 error when I try and insert a free standing formula cell with this:

=NumberOfRows([Drug Utilization-Test])

Re: Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 17:38:12 GMT

You can't use more than one query (name) with this function. Try creating one variable for each query.

Re: Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 17:42:50 GMT

create the variable in designer?

Re: Web intelligence not retrieving all the data

Former Member — Wed, 15 Oct 2008 17:50:15 GMT

The NumberOfRows() is a report level function.

If you have 2 queries called Drug Utilization and Test, create 2 variables which return the number of rows in each query.

i.e.


     x=NumberOfRows([Drug Utilization]) and
     y=NumberOfRows([Test])

That said, I believe Webi (appears to be) returning fewer rows because because of the way Webi aggregates data.

How many rows does an export to Excel give?

Re: Web intelligence not retrieving all the data

Former Member — Thu, 16 Oct 2008 04:15:19 GMT

Hi Graham ,

Drag and Drop a Column in the Report which is not a Measure should be a dimension and Hide that perticular Object from the report

This should repeat the number of Rows.

Regards

Prashant

Re: Web intelligence not retrieving all the data

Former Member — Thu, 16 Oct 2008 14:40:21 GMT

That didn't seem to make a difference

Re: Web intelligence not retrieving all the data

Former Member — Thu, 16 Oct 2008 18:24:10 GMT

It looks like we are only getting around 1000 lines, the SQL returns over 200,000 records.

Re: Web intelligence not retrieving all the data

Former Member — Thu, 16 Oct 2008 19:42:13 GMT

Graham,

Could it be that the query is returning 200,000 rows and your Webi report is aggregating or grouping the measure by the dimensional objects in the report? Making the number of lines in the report 1000.

Mike

Re: Web intelligence not retrieving all the data

amrsalem1983 — Thu, 16 Oct 2008 20:47:14 GMT

i've a small idea after all those posts,

if you have a measure like sales, or budget or watever,

get the total, using SUM in the webi report

and get the total using some aggregate function in the SQL, and see if there is any difference.

good luck

Amr