Hello
I create a cube using SSAS 2005. The cube has 2 years' data. There are 4 dimensions (including date) . Thee are not large dimensions. THe largest dimension has 300 members. (other than the date ofcourse).
I am facing serious performance issues while querying the cube. each query (click on a dimension member) takes about 15 seconds. I noticed that SSAS was using only 55 MB of memory - eventhough we have 2 GB on the machine. There was no meaningful information in Books online or technet on how to increase this parameter. All of the memory related items have the description "This is an advanced oiption that should only be changed with the advise of Microsoft tech support". Anybody else has run into this issue . If so can you share how you resolved this . Can you tell me what parameters I should change to get SSAS to use more memory
Thanks
Performance Issues with Analysis Services 2005
Ori Lahav
I'm having a problem with performance in SSAS 2005. This is the server specification:
OLAP Server and ORACLE Data Warehouse Server (64 bit) : IBM xSeries 460, windows 2003 EE x64 SP1, SQL Server Analysis Services 2005 x64 SP1 + Hotfix, , ORACLE 10g R2, 8x Xeon 3 GHz, memory 8 GB. Data Warehouse is located on drive D 400GB and OLAP is in drive E 300GB and both of them are HDD External using SAN (Storage Area Network) RAID 5.
Application Server (32 bit) : 52 IBM xSeries 366 Windows 2003 R2 SP1 BI Serv Dev ,sqlserv 2005 8x Xeon 3GH, mem 8 GB
For the Data warehouse I'm using ORACLE 10g R2 64 bit and for the OLAP I'm using SSAS 2005 64 bit. I'm already installed the oracle client. For the front-end, I'm using Proclarity.
I have problems with the performance when querying the OLAP by using ProClarity or SSMS (Management Studio). The response time is very low, but the CPU Utilization is below 10% and Memory Usage not more than 50%. When I check in the performance monitor, the I/O access is very high continuously 100% utilization when I are running query from ProClarity Analytics Server. For improving the performance I have applied design aggregation for each cube. However, design aggregation cannot be made on several cubes that have a lot of data, and have a lot of measures and dimension (the number of fields in the table more or less 150 fields). They return 0% optimization level when I run design aggregation.
FYI, I have done several ways for optimizing the performance such as:
1. Using MOLAP storage mode with 100% design aggregation, except several cubes that can not be made.
2. Cube design follows the best practice, e.g. using key member.
3. Separation of physical storage for data warehouse and OLAP files in different disk.
My key points are how to improve the performance and fix the aggregation problem that cannot be made on several cubes. I am looking forward to hear from you soon.
Thanks in advance.
Kervin
Hello,
I have the same performance problem. Each time I drag and drop a dimension, it seems the system query the cube to retrieve data. So its not really effective... Is there any options i can configure, for the system query the cube only at the end of changes, where all selections are made
Thanks
sagesmith7
It is probably not related to the dimension sizes.
How much data you have in the fact tables ( num of rows )
Try to monitor disc usage using perfmon. See if problem is that Analysis Server needs to do a lot of I/O.
What about if you try to navigate to the same member for the second time Do you see the same performance
What to you use to browse the cube
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.