| Sebastian Crewe 2006-03-28, 11:30 am |
| Greetings,
I am trying to troubleshoot a problem in a Report Server project, which
draws its data from an Analysis Services cube. RS and AS are v2005
(Developer's Edition), with a hotfix applied (AS2005-KB913363); Visual Studio
is v2005; machine is HP DC7100, dual 3.2GHz processors, 1.5GB RAM, XP SP2 and
subsequent patches.
I made my cube in an Analysis Services project, and it works a treat. I
created a couple of calculated measures as well as defining the dimensions et
al. Browsing the cube from within the AS project, and from within SQL Server
Management Studio, is absolutely fine. I can import the cube into Excel 2003
(SP2) just fine, and it displays also within an Office Web Component on a
Windows Form project (VB.NET 2005).
Indeed, the only problem comes when I try to make a report from the cube
and, in particular, when I try to use my calculated measures. When any one
of the calculated measures is added to the project (either via the Query
Designer wizard or on the Data tab of the Report Designer), there is a notice
saying that the query is executing, and then things start to slow down.
The first time this happened, I noticed that msmdsrv.exe was consuming high
amounts of memory (1.8GB and rising). I waited half an hour, but no joy, so
had to close Visual Studio via Task Manager. That's when I researched a bit,
saw that a hotfix was available, got and applied it, as per the first
paragraph above.
Still, after the hotfix, I am getting the same problem, though the memory
usage hasn't yet got above 750,000K. No results, even after 1/2 hour.
Sometimes I can cancel the query from within the Report Designer, yet the
memory used by msmdsrv.exe continues to rise, even after its CPU usage has
dropped to 0.
I feel that the calculated measures are OK, given that they work fine and
lickety-split from within SQL Management Studio, Excel and in a Windows Form.
Perhaps I am missing something in my configuration of Reporting Services? I
tried adding a calculated measure within the Report Designer (using a
different name), deployed the report and then tried running it from the IE
interface: msmdsrv.exe was more under control this time (max of 68,000K
memory usage), but no results even after 45 minutes. I figure this was
because the Report Manager Service was having to do the calculation, rather
than AS.
Any guidance most appreciated. Reporting Services will be much less useful
if it can't work with calculated measures in a cube.
With thanks and regards
Sebastian Crewe
|