Hi,
We are currently looking into AS2005 performances for one of our client. For some reason the 'cubes' are not giving us the performance we were expecting. Her is some info about our environment.
Server: 2 * Dual 2.8 GHz with 4 GB of memory
The fact table has around 30 millions of records for three years worth of sales history, and we have created 1 partition per year. We are using MOLAP with aggregation level at around 40%.
The biggest dimension (Product) has 45 000 members, with 2 manual hierarchy and many attributes hierarchy. Attribute relationships are setup properly in every dimension.
They are using OWC11 to access the AS2005 server.
Everything is running pretty smouth but as soon as we try to hit the lowest level of the product dimension or almost any dimension for that matter the performances decrease to a point where the server is not usable anymore, processors are running at 100%.
Is there any server side configuration available to enhance performance For some reason AS is only using a small amount of memory on the server (never used more than 650 Mb)
Any tought on the 64 bit version of AS2005
Thanks,
AS2005 Performance issues
audigger
We did try to design higher percentage of aggregations, even if we put 80% of aggregations, the build will stop at around 55%.
John W Powell
A couple of thoughts:
- Did you try usage-based optimization of aggregation design
- You can use SQL Profiler to track whether and when aggregations are used in evaluating various MDX queries. This might help you to figure out whether there are aggregations that should be added. There was a presentation at the SQL PASS 2005 Conference which described this in detail: "108S: Understanding Analysis Services 2005 Aggregations from Every Angle"
MadKad
Just based on your description, looks like you need to design higher percentage of aggregations. The minute query misses aggregation, it is going to scan all partitions. Get more aggregations for your cube, you should see performance improvement.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
oivindroed
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 6.2.
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:
- Cube design follows the best practice, e.g. using key member, natural hierarchies, etc.
- Separation of physical storage for data warehouse and OLAP files in different disk.
- Merge
many dimensions with single attribute into one dimension.
- Modify
key attribute dimensions with non numeric data type to integer data type
in data source view. Note: we do not modify relational data warehouse
because of limited time.
- Make sure
aggregation size less than or equal than 30% of fact table size.
- Revise
aggregation usage properties in cube dimension usage with Unrestricted for
the commonly accessed attributes.
- Create
non-empty behavior for calculated measures with related measures.
- Move many
calculated measures from cube calculation to data source view.
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.
MoonRiver000
For you to be able to take advantage of aggregations, you need to work a little more on re-designing your dimensions. You need to make sure you define relationships between attributes correctly.
Take a look at the description of the project REAL http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx
Read there about natural hierarchies and attribute relationships. After you design them correctly, you should see aggregation design algorithm working better and your queries running faster.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
TBurrowsNZ