Big performance problem with reports

Hello everyone!
I have major performance problems with reports that I have deployed in
Reporting Services.
I have an Sql database with millions of rows, and I have created a cube in
Analysis Services that is run against a view in the database.
My reports get data from the cube and the dataset is created with Sql syntax.
I need to filter the report and needed to create parameters, but only
prameters that give the option of choosing "all", i.e. being optional
parameters. The only way I can do that, as far as I know, is by creating the
filter parameter datasets in Sql Syntax. That is to be able to use union as
in
"select 'All'
union
select the real dataset
group by value" syntax. And it seems that I can't use union when running the
sql statement on a cube, so I have to run the statement on the original
database view.
Now the problem I have is that it takes about half an hour or more to view
the report, after choosing values in some filter parameters!
As far as I can see, the group by, is the statement that takes such a long
time. But that can't be the only problem.
So... does anyone know what I can do to reduce the time it takes to process
a report and what performance enhancements I can do?
I also have a problem with a specific report that gets a
System.OutOfMemoryException when I try to view it in the preview pane. But
when I run the dataset generating the report data, there are no problems.
Does anybody know what the problem might be?
I'm in a tight place and would appreciate any fast responses.
Thanks a lot,
TaraDo you have a stored procedure or is the sql code inside the RDL?
If you put as much as possible on the SQL Server, you can run it
through Query Analyzer and look at query plans etc. It does sound like
you are passing a lot more data around than you need, so get all the
SQL code out of the RDL and see where that gets you.

BI Development Studio: Crashes when viewing a Cube in the Browser

Hello, I created an Analysis Services project, defined the views and the cube itself. After I deployed the cube, I wanted to compare the results with the browser.

Everytime I try to open the browser, BIDS starts loading, but after a while it crashes.

Is this a known error?

Thanks in advance!

Are you running Office 2007 on the same machine as BIDS? Is it a beta version of Office 2007?

You have not told us about the sp for SQL Server 2005 or if you run a version of Office 2003 or later on the same machine.

Try downloading SP2 CTP3 (December 2006) from www.microsoft.com/sql and see if it helps.

Regards

Thomas Ivarsson

|||

Yes, I do run Office2003 on my machine, btw it is an Acer5102-notebook. I installed the SP2 CTP3, but the error still occurs.

Any other suggestions, it is really urgent?

Thanks in advance!

|||

I hope by the word "crash" you do not mean that you get a hour glass.

1. Do you run any Firewall? I have seen some issues with Integrity where one would not be able to browse the cube.

2. Did you try browsing the cube from any other application instead of VS? like Excel

3. Also are you trying to go to the Browse tab after the cube is completely deployed. I have some instances where if you try to shift tabs while the cube is being deployed the VS crashes (shuts down completely and launches again)

See, if it is related to any of the aboce behaviors?

|||

I have never had any problem with BIDS and Office2003 since SQL Server 2005 RTM.

Try to install this add in for Excel 2003 and connect to the cube: http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

Have you runned windows update and especially office update lately?

Regards

Thomas Ivarsson

BI Development Studio: Crashes when viewing a Cube in the Browser

Hello, I created an Analysis Services project, defined the views and the cube itself. After I deployed the cube, I wanted to compare the results with the browser.

Everytime I try to open the browser, BIDS starts loading, but after a while it crashes.

Is this a known error?

Thanks in advance!

Are you running Office 2007 on the same machine as BIDS? Is it a beta version of Office 2007?

You have not told us about the sp for SQL Server 2005 or if you run a version of Office 2003 or later on the same machine.

Try downloading SP2 CTP3 (December 2006) from www.microsoft.com/sql and see if it helps.

Regards

Thomas Ivarsson

|||

Yes, I do run Office2003 on my machine, btw it is an Acer5102-notebook. I installed the SP2 CTP3, but the error still occurs.

Any other suggestions, it is really urgent?

Thanks in advance!

|||

I hope by the word "crash" you do not mean that you get a hour glass.

1. Do you run any Firewall? I have seen some issues with Integrity where one would not be able to browse the cube.

2. Did you try browsing the cube from any other application instead of VS? like Excel

3. Also are you trying to go to the Browse tab after the cube is completely deployed. I have some instances where if you try to shift tabs while the cube is being deployed the VS crashes (shuts down completely and launches again)

See, if it is related to any of the aboce behaviors?

|||

I have never had any problem with BIDS and Office2003 since SQL Server 2005 RTM.

Try to install this add in for Excel 2003 and connect to the cube: http://www.microsoft.com/downloads/details.aspx?FamilyId=DAE82128-9F21-475D-88A4-4B6E6C069FF0&displaylang=en

Have you runned windows update and especially office update lately?

Regards

Thomas Ivarsson