Performance in Cube generation

during the cube generation, I saw the disk utilization in C drive is 100%.
How can I change the temporary drive for the cubes generation?
or
can I share the work load on others drives? nHow?There are lots of tips and tricks mentioned in the AS Operations and
Preformance Guides.
Pointers to them are at:
http://www.microsoft.com/sql/evalua.../bianalysis.asp
1) changing the temp folder -- yes. In Analysis Manager, right-click on the
server and select Properties...
2) different folders -- no. Everything in Analysis Services sits under the
same data folder.
You can modify where that is located, but everything sits under that single
folder.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kam" <Kam@.discussions.microsoft.com> wrote in message
news:613802AE-92ED-4B83-9C5D-0DAB5576B781@.microsoft.com...
> during the cube generation, I saw the disk utilization in C drive is 100%.
> How can I change the temporary drive for the cubes generation?
> or
> can I share the work load on others drives? nHow?

Performance degradation writing to a SAN / building a cube

Is anyone aware of performance degradation writing to a SAN over fibre optic network? We r using SQL Server 2000 (Enterprise version) with Compaq Proliant DL580 G2, writing to Compaq SAN. Is any network tuning needed? A gigabit ethernet card pushes the data over to the SAN.

Also, we r taking about 37 hours building a cube for a database barely 15GB. Database is shipped by Microsoft and is used by their product Microsoft Proect Server. So, database level tuning is out of question.

Any help would greatly be appreciated.

Thanx in advance,

BhushanYou should try the basic performance tuning practices to find out where your bottle neck is. Here are a few we use to help us troubleshoot our san...

*check CPU utilization on the sql node -- this could be caused by a lot of things
*Check disk idle time -- if your disk is below 20% idle, then you are spindle bound.
*check disk queue length. -- if the disk queue is higher than twice the number of disks in your raid array, then you have a problem. High disk queue could also be a problem with the NIC. it is possible it cant send data as fast as the cable and disks can handle it.
*check memory usage. -- if you are trying to cache everything and the pc starts paging, this will kill performance
*check your query plans (not sure if that is possible, but if it is running poorly it can make a huge difference.)
*check your network traffic between the pc and the san. are you seeing any collisions?

basically it comes down to finding the bottleneck and then fixing it.|||Originally posted by Pigeon
You should try the basic performance tuning practices to find out where your bottle neck is. Here are a few we use to help us troubleshoot our san...

*check CPU utilization on the sql node -- this could be caused by a lot of things
*Check disk idle time -- if your disk is below 20% idle, then you are spindle bound.
*check disk queue length. -- if the disk queue is higher than twice the number of disks in your raid array, then you have a problem. High disk queue could also be a problem with the NIC. it is possible it cant send data as fast as the cable and disks can handle it.
*check memory usage. -- if you are trying to cache everything and the pc starts paging, this will kill performance
*check your query plans (not sure if that is possible, but if it is running poorly it can make a huge difference.)
*check your network traffic between the pc and the san. are you seeing any collisions?

basically it comes down to finding the bottleneck and then fixing it.

Thanx a lot, Pigeon. I did check the Queue length originally; but was told by Compaq that the Queue length reported by Windows 2000 (for Compaq hardware) is not reliable for various reasons.

Anway! I personally think NIC card is slow and thats why I am seeing NETWORKIOWAIT within SQL Server. Just wanted to confirm, if I am not missing anything...

Thanx again.

- Bhushan|||The NIC can be a big bottleneck. WHat protocol does it use to send the data across the network? UDP, TCP, is it using a SCSI miniport driver (Encapsulating scsi calls into a udp or tcp packet) Many times, if this driver or protocol is not working, it will cause an increase in processor utilization, or the disk queue will be high. No matter what compaq tells you, if your queue is high, something is stopping the traffic from getting to the disk. Good Luck with it. I know these types of problems can sometimes be a real pain to find.

Performance - Best Practices

Hello,

I am having some pretty major performance issues with a cube I've built. My goal for this post is to give specifics related to the data and Analysis Services hardware to try and get some feedback from the community on whether or not I am having valid expectations performance-wise.

Hardware:

-Two dual-core Xeon 2.8GHz processors
-3GB RAM

Data:

The data is retail transaction data which is at the line item level. The most recent years have 50-60 million rows and I am partitioning by year. In addition, each transaction has related discount and tender (payment) data. The tender data has approximately 15-20 million rows per year while the discount data has approximately 10-15 million rows per year.

In my cube, each of these tables have their own separate fact group. I am pulling data from all three fact groups to create quite a few calculated members which build on each other to finally arrive at calculations such as GAAP Sales, sales for a particular product type, etc.

I believe I have my hierarchies and relationships set up correctly in my dimensions. In addition I have aggregations designed on each partition at about the 40% level.

Am I having reasonable expectations that data should be able to come back fairly quickly? I have some reports which take 10-15 minutes that bring back quite a lot of data for the whole company across the last two years. However, even some simple slicing and dicing in the cube browser can be slow. What is interesting to me is that even if I drop my time dimension on the rows axis before adding any measures or other dimensions, it can take 20 seconds or more to respond. All the while when reports are running or when I am browsing the cube myself, the CPU (all 8 in Task Manager [dual dual-core processors]) are spiking at 100%.

While I believe I can get some good responses from MS folks and MVPs for my own benefit, I think any thoughtful posts will benefit the community as a whole by setting performance expectations and pointing out potential pitfalls. I have found that there are not a ton of good resources out there on performance, so I appreciate any tips anyone can offer.

Thanks!
Todd

Dear Todd,

May be you can find useful information from this blog:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

For best practices in SSAS 2005, you can find it in presentation from TechEd 2006 (BIN 316):
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!906.entry

Best Regards,
|||

Hello. You have some good links to information posted here already regarding aggregations. I would only advice you to start with checking your attribute relations in your user hierarchies. This is mentioned in Chris Webbs blog.

Mosha has a good post here regarding MDX and performance: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx

You should also be aware of performance issues with using several measure groups in a cube. Here is a good blog post about this: http://prologika.com/CS/blogs/blog/archive/2006/06/28/1331.aspx

HTH

Thomas Ivarsson

Performance - Best Practices

Hello,

I am having some pretty major performance issues with a cube I've built. My goal for this post is to give specifics related to the data and Analysis Services hardware to try and get some feedback from the community on whether or not I am having valid expectations performance-wise.

Hardware:

-Two dual-core Xeon 2.8GHz processors
-3GB RAM

Data:

The data is retail transaction data which is at the line item level. The most recent years have 50-60 million rows and I am partitioning by year. In addition, each transaction has related discount and tender (payment) data. The tender data has approximately 15-20 million rows per year while the discount data has approximately 10-15 million rows per year.

In my cube, each of these tables have their own separate fact group. I am pulling data from all three fact groups to create quite a few calculated members which build on each other to finally arrive at calculations such as GAAP Sales, sales for a particular product type, etc.

I believe I have my hierarchies and relationships set up correctly in my dimensions. In addition I have aggregations designed on each partition at about the 40% level.

Am I having reasonable expectations that data should be able to come back fairly quickly? I have some reports which take 10-15 minutes that bring back quite a lot of data for the whole company across the last two years. However, even some simple slicing and dicing in the cube browser can be slow. What is interesting to me is that even if I drop my time dimension on the rows axis before adding any measures or other dimensions, it can take 20 seconds or more to respond. All the while when reports are running or when I am browsing the cube myself, the CPU (all 8 in Task Manager [dual dual-core processors]) are spiking at 100%.

While I believe I can get some good responses from MS folks and MVPs for my own benefit, I think any thoughtful posts will benefit the community as a whole by setting performance expectations and pointing out potential pitfalls. I have found that there are not a ton of good resources out there on performance, so I appreciate any tips anyone can offer.

Thanks!
Todd

Dear Todd,

May be you can find useful information from this blog:
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!907.entry

For best practices in SSAS 2005, you can find it in presentation from TechEd 2006 (BIN 316):
http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!906.entry

Best Regards,
|||

Hello. You have some good links to information posted here already regarding aggregations. I would only advice you to start with checking your attribute relations in your user hierarchies. This is mentioned in Chris Webbs blog.

Mosha has a good post here regarding MDX and performance: http://www.sqljunkies.com/WebLog/mosha/archive/2006/11/05/non_empty_behavior.aspx

You should also be aware of performance issues with using several measure groups in a cube. Here is a good blog post about this: http://prologika.com/CS/blogs/blog/archive/2006/06/28/1331.aspx

HTH

Thomas Ivarsson

Percentage of Total

Hi Friends,

I have a Cube wherein i have dimensions with following Heirachy:-

Activity

PartNumber

Now if the person selects Activity and Quantity, like as below i want output as

Activity Part Quantity %of Quantity

Activity1 Part1 75 37.5%

Part2 125 62.5%

Activity1 Total 200 40%

Activity2 Part3 90 90%

Part4 10 10%

Activity2 Total 100 20%

Activity3 Part5 100 50%

Part6 100 50%

Activity3 Total 200 40%

This is really urgent...

Regards,

Kaushal

Here's a sample Adventure Works query which may help:

With

Member [Measures].[OrderPercentOfParent] as

[Measures].[Order Quantity] /

([Measures].[Order Quantity],

[Promotion].[Promotions].Parent),

FORMAT_STRING = 'Percent'

select

{[Measures].[Order Quantity],

[Measures].[OrderPercentOfParent]} on 0,

NON EMPTY DrillDownLevel([Promotion].[Promotions].[All Promotions].Children) on 1

from [Adventure Works]

Order Quantity OrderPercentOfParent
No Discount 238,806 86.91%
Reseller 35,970 13.09%
Discontinued Product 838 2.33%
Excess Inventory 304 0.85%
New Product 2,356 6.55%
Seasonal Discount 1,172 3.26%
Volume Discount 31,300 87.02%

|||

Deepak thanks for the help, but i have used following formula...

IIF([Activity].CurrentMember.Parent is null, 100,(Measures.[Net OI EUR - 2006] / (Measures.[Net OI EUR - 2006],[Activity].CurrentMember.Parent))*100)

Activity Value - 2006 Value - 2007 Var% Delta Mix(Value2007 - Value2006) * Var%

Act1 100 75 5% -0.125

Act2 50 80 10% 3

Act3 50 80 3% 0.9

Grand Total 200 235 4% 1.4(But i want 3.775 Sum of Act1+Act2+Act3)

How can i achieve this, really urgent...

Regards,

Kaushal

Percentage of Total

Hi Friends,

I have a Cube wherein i have dimensions with following Heirachy:-

Activity

PartNumber

Now if the person selects Activity and Quantity, like as below i want output as

Activity Part Quantity %of Quantity

Activity1 Part1 75 37.5%

Part2 125 62.5%

Activity1 Total 200 40%

Activity2 Part3 90 90%

Part4 10 10%

Activity2 Total 100 20%

Activity3 Part5 100 50%

Part6 100 50%

Activity3 Total 200 40%

This is really urgent...

Regards,

Kaushal

Here's a sample Adventure Works query which may help:

With

Member [Measures].[OrderPercentOfParent] as

[Measures].[Order Quantity] /

([Measures].[Order Quantity],

[Promotion].[Promotions].Parent),

FORMAT_STRING = 'Percent'

select

{[Measures].[Order Quantity],

[Measures].[OrderPercentOfParent]} on 0,

NONEMPTYDrillDownLevel([Promotion].[Promotions].[All Promotions].Children) on 1

from [Adventure Works]

Order Quantity OrderPercentOfParent
No Discount 238,806 86.91%
Reseller 35,970 13.09%
Discontinued Product 838 2.33%
Excess Inventory 304 0.85%
New Product 2,356 6.55%
Seasonal Discount 1,172 3.26%
Volume Discount 31,300 87.02%

|||

Deepak thanks for the help, but i have used following formula...

IIF([Activity].CurrentMember.Parentisnull, 100,(Measures.[Net OI EUR - 2006] / (Measures.[Net OI EUR - 2006],[Activity].CurrentMember.Parent))*100)

Activity Value - 2006 Value - 2007 Var% Delta Mix(Value2007 - Value2006) * Var%

Act1 100 75 5% -0.125

Act2 50 80 10% 3

Act3 50 80 3% 0.9

Grand Total 200 235 4% 1.4(But i want 3.775 Sum of Act1+Act2+Act3)

How can i achieve this, really urgent...

Regards,

Kaushal