Analysis Services Taking Forever and Over 800MB Memory....

Hi,

I recently started to use Analysis Services 2005 after i build my cube i initiated the adhoc analysis in Excel... what was my surprise when i saw the msmdsrv.exe process reaching over 800mb Memory occupation... and no data was return for hours...

Is this normal? When i design the aggregations in that cube i calculted the total items of about 338.323 or so... but then said 0 Agreggations made.. so i get a little bit confused here too...

Anyone experiencing this?

Best Regards,
Luis Sim?es

Well i have found that the problem is one of mine dimensions... It as 1031 members and about 200 are using rollup formulas...

Can this be the problem?

|||

Do you have any user hierarchies in the cube dimensions? I am talking about hierarchies in the old AS2000 style.

If the cube have dimensions with user hierarchies, have you designed attribute relations between the levels in each user hierarchy?

Kind Regards

Thomas Ivarsson

Analysis Services service hangs cube build / timeout on process cu

We have two apparent issues with Analysis Services (AS) as outlined below:
1.We have a rebuild of the nightly cube build and quite often the first
step would hang (just run without processing). We would have to stop the
job, restart Analysis Services and then start the job again, resulting in
loss of processing time and an impact to our Production Reporting. As a
workaround for this intermittent problem, we have adjusted the nightly job to
include a stop and start of AS before the cube build begins. An outline of
the current steps:
?net stop MSSQLServerOLAPService
?net start MSSQLServerOLAPService
?dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
?process cub files
?backup cube
We need to know why AS does not process cubes as it should from time to
time. Do we have a configuration problem? Is our Production Server not
sufficient enough for AS and the size of the cube? We have more than the
minimum requirements but possibly we need more due to our expectation of the
cubes and data.
Server Specifications -->
Production
CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
Memory = 20476 MB
OS – Windows 2003. SP1
SQL Server 2005
64 bit based on the x64 platform
2.We have been unable to deploy a copy of the Production cubes to a new
server. We even tried deploying a smaller cube, to include a large
dimension (ex: Account). We continually experience time outs with the larger
dimensions.
a.Tried deploying from Visual Studio 2005
b.Attempted to Process the cube through SQL Server Management Studio
c.Attempted to Process the larger dimension only from SQL Server Management
Studio
d.Attempted Parallel processing, Sequential – One Transaction, and
Sequential – Separate Transaction.
e.Also tried changing the default timeout for the AS server.
ForceCommitTimeout property was 30000, increased to 40000 and then again to
50000
f.Attempted to change the ExternalCommandTimeout property from 3600 to
7200. This was successful in the Deisaster Recovery server which has nothing
running (no users reporting). Although it did take over 5 hours to process.
At that rate, it might take 2 days to rebuild the large cube that we have,
with several partitions.
Disaster Recovery -->
CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
Memory = 9728 MB
OS – Windows 2003 SP 1
SQL Server 2005
32bit
Errors:
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.
do you have any proactive caching partitions?
I see this error when I'm using the proactive caching feature.
"Shari" <Shari@.discussions.microsoft.com> wrote in message
news:4D39C9AF-58CF-4639-BA8B-DF4E936D62F1@.microsoft.com...
> We have two apparent issues with Analysis Services (AS) as outlined below:
> 1. We have a rebuild of the nightly cube build and quite often the first
> step would hang (just run without processing). We would have to stop the
> job, restart Analysis Services and then start the job again, resulting in
> loss of processing time and an impact to our Production Reporting. As a
> workaround for this intermittent problem, we have adjusted the nightly job
> to
> include a stop and start of AS before the cube build begins. An outline
> of
> the current steps:
> ? net stop MSSQLServerOLAPService
> ? net start MSSQLServerOLAPService
> ? dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
> ? process cub files
> ? backup cube
> We need to know why AS does not process cubes as it should from time to
> time. Do we have a configuration problem? Is our Production Server not
> sufficient enough for AS and the size of the cube? We have more than the
> minimum requirements but possibly we need more due to our expectation of
> the
> cubes and data.
> Server Specifications -->
> Production
> CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
> Memory = 20476 MB
> OS – Windows 2003. SP1
> SQL Server 2005
> 64 bit based on the x64 platform
>
> 2. We have been unable to deploy a copy of the Production cubes to a new
> server. We even tried deploying a smaller cube, to include a large
> dimension (ex: Account). We continually experience time outs with the
> larger
> dimensions.
> a. Tried deploying from Visual Studio 2005
> b. Attempted to Process the cube through SQL Server Management Studio
> c. Attempted to Process the larger dimension only from SQL Server
> Management
> Studio
> d. Attempted Parallel processing, Sequential – One Transaction, and
> Sequential – Separate Transaction.
> e. Also tried changing the default timeout for the AS server.
> ForceCommitTimeout property was 30000, increased to 40000 and then again
> to
> 50000
> f. Attempted to change the ExternalCommandTimeout property from 3600 to
> 7200. This was successful in the Deisaster Recovery server which has
> nothing
> running (no users reporting). Although it did take over 5 hours to
> process.
> At that rate, it might take 2 days to rebuild the large cube that we
> have,
> with several partitions.
>
> Disaster Recovery -->
> CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
> Memory = 9728 MB
> OS – Windows 2003 SP 1
> SQL Server 2005
> 32bit
> Errors:
> OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
> OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.
>
|||Thanks Jeje for your quick response.
I browsed the setting of the partitions and proactive caching is not
enabled. Any other ideas?
Thanks,
Shari
"Jeje" wrote:
[vbcol=seagreen]
> do you have any proactive caching partitions?
> I see this error when I'm using the proactive caching feature.
>
> "Shari" <Shari@.discussions.microsoft.com> wrote in message
> news:4D39C9AF-58CF-4639-BA8B-DF4E936D62F1@.microsoft.com...

Analysis Services service hangs cube build / timeout on process cu

We have two apparent issues with Analysis Services (AS) as outlined below:
1. We have a rebuild of the nightly cube build and quite often the first
step would hang (just run without processing). We would have to stop the
job, restart Analysis Services and then start the job again, resulting in
loss of processing time and an impact to our Production Reporting. As a
workaround for this intermittent problem, we have adjusted the nightly job t
o
include a stop and start of AS before the cube build begins. An outline of
the current steps:
? net stop MSSQLServerOLAPService
? net start MSSQLServerOLAPService
? dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
? process cub files
? backup cube
We need to know why AS does not process cubes as it should from time to
time. Do we have a configuration problem? Is our Production Server not
sufficient enough for AS and the size of the cube? We have more than the
minimum requirements but possibly we need more due to our expectation of the
cubes and data.
Server Specifications -->
Production
CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
Memory = 20476 MB
OS – Windows 2003. SP1
SQL Server 2005
64 bit based on the x64 platform
2. We have been unable to deploy a copy of the Production cubes to a new
server. We even tried deploying a smaller cube, to include a large
dimension (ex: Account). We continually experience time outs with the larger
dimensions.
a. Tried deploying from Visual Studio 2005
b. Attempted to Process the cube through SQL Server Management Studio
c. Attempted to Process the larger dimension only from SQL Server Management
Studio
d. Attempted Parallel processing, Sequential – One Transaction, and
Sequential – Separate Transaction.
e. Also tried changing the default timeout for the AS server.
ForceCommitTimeout property was 30000, increased to 40000 and then again to
50000
f. Attempted to change the ExternalCommandTimeout property from 3600 to
7200. This was successful in the Deisaster Recovery server which has nothin
g
running (no users reporting). Although it did take over 5 hours to process.
At that rate, it might take 2 days to rebuild the large cube that we have,
with several partitions.
Disaster Recovery -->
CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
Memory = 9728 MB
OS – Windows 2003 SP 1
SQL Server 2005
32bit
Errors:
OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.do you have any proactive caching partitions?
I see this error when I'm using the proactive caching feature.
"Shari" <Shari@.discussions.microsoft.com> wrote in message
news:4D39C9AF-58CF-4639-BA8B-DF4E936D62F1@.microsoft.com...
> We have two apparent issues with Analysis Services (AS) as outlined below:
> 1. We have a rebuild of the nightly cube build and quite often the first
> step would hang (just run without processing). We would have to stop the
> job, restart Analysis Services and then start the job again, resulting in
> loss of processing time and an impact to our Production Reporting. As a
> workaround for this intermittent problem, we have adjusted the nightly job
> to
> include a stop and start of AS before the cube build begins. An outline
> of
> the current steps:
> ? net stop MSSQLServerOLAPService
> ? net start MSSQLServerOLAPService
> ? dtexec /DTS "\MSDB\SSAS_Petra_Full_Dim_Load_Prod" /SERVER vbmsp19
> /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
> ? process cub files
> ? backup cube
> We need to know why AS does not process cubes as it should from time to
> time. Do we have a configuration problem? Is our Production Server not
> sufficient enough for AS and the size of the cube? We have more than the
> minimum requirements but possibly we need more due to our expectation of
> the
> cubes and data.
> Server Specifications -->
> Production
> CPU = Quad 2.8 GHz Intel Xeon (Hyper-Threaded)
> Memory = 20476 MB
> OS – Windows 2003. SP1
> SQL Server 2005
> 64 bit based on the x64 platform
>
> 2. We have been unable to deploy a copy of the Production cubes to a new
> server. We even tried deploying a smaller cube, to include a large
> dimension (ex: Account). We continually experience time outs with the
> larger
> dimensions.
> a. Tried deploying from Visual Studio 2005
> b. Attempted to Process the cube through SQL Server Management Studio
> c. Attempted to Process the larger dimension only from SQL Server
> Management
> Studio
> d. Attempted Parallel processing, Sequential – One Transaction, and
> Sequential – Separate Transaction.
> e. Also tried changing the default timeout for the AS server.
> ForceCommitTimeout property was 30000, increased to 40000 and then again
> to
> 50000
> f. Attempted to change the ExternalCommandTimeout property from 3600 to
> 7200. This was successful in the Deisaster Recovery server which has
> nothing
> running (no users reporting). Although it did take over 5 hours to
> process.
> At that rate, it might take 2 days to rebuild the large cube that we
> have,
> with several partitions.
>
> Disaster Recovery -->
> CPU = Dual 3.00 GHz Intel Xeon MP (Hyper-Threaded)
> Memory = 9728 MB
> OS – Windows 2003 SP 1
> SQL Server 2005
> 32bit
> Errors:
> OLE DB error: OLE DB or ODBC error: Operation canceled; HY008.
> OLE DB error: OLE DB or ODBC error: Query timeout expired; HYT00.
>

Analysis Services error

I was able to build SQL 2005 cubes from my project. However, for some reasons, it doesn't work any more, and I have the error below. Anyone can shed some light would be greatly appreciated.

Error 1 File system error: The logical file cannot be found inside the physical file. Physical file: \\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Beacon DW.0.db\Tbl Policy Coverage Data.6.dim\14.ERP Effective Month.Tbl Policy Coverage Data.dstore. Logical file: . Errors in the metadata manager. An error occurred when loading the Coverage dimension, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Beacon DW.0.db\Tbl Policy Coverage Data.16.dim.xml'. Errors in the metadata manager. An error occurred when loading the Production cube, from the file, '\\?\C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Data\Beacon DW.0.db\Beacon DW.350.cub.xml'. 0 0

Hi,

It sounds like the file is corrupted. It looks like its only the Coverage dimension, so you may be lucky.

Can the project be built or is the error when you try to deploy the project to the server?

If the error is in the deployment try to deploy to a new database to see if the corruption is on the server itself.

If the error is on the build which tells me something is corrupted in the project files itself do the following.

1. Go to a back up of the project if you have one.

2. If not make a backup of the project now and delete the Coverage dimension from the project to see if you can build it without that dimension.

I hope that helps,

David Botzenhart

|||

It appears that it is one of the xml files for the Coverage dimension was corrupted on the SQL server. I have deleted the Coverage dimension on my project, and it failed with the same error when I rebuilt the cube. Do you know of how to fix the corrupted file on the SQL server?

Mitch Cheung

Analysis Services Cube using SQL2000 database

Hi All
I'm trying to build my first Analysis Services (2005) cube based on fact and
dimension tables on a SQL200 database on a different server.
I'm having problems setting up the data sources - I'm assuming I need two:
one for the source SQL2000 tables and one for the target SQL2005 cube. I've
done this but when it comes to deploying the cube it looks like it's
expecting the source tables on my SQL2005 data source
I think I'm missing something fundamental. Any help or suggestions most
welcome.
Thanks
PeterHello Peter,
If I understand you correctly, you have two data sources in the data
source folder in your Analysis Services project. Can you confirm that
the data for your cube is only based on the SQL2000 data source?
If this is the case, then you should delete SQL2005 data source, as you
do not need a connection to your SQL server 2005. Make sure the data
source view is only base on the SQL2000 connection.
To deploy your cube to Analysis services you only need to set up the
deployment location in the project. This most like will be already
setup for you if you are working with everything on one PC.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/