Performance issue - large dimensions

MS SQL SERVER

    Sponsored Links

    Next

  • 1. display seven days data
    Hi, I got the answer for Yesterdays query thanks Deepak Can you please help me in getting seven days of data for the current week Please find the query Please note [Time].[JC] is the time hierarchy Select member measures.[Production In Tons] AS '[Measures].[Production Qty In Tons]' on columns, {([Time].[JC].Currentmember.children)} on rows from [Product Dispatch Compliance] WHERE [Time].[JC].[JC Week].[wk46.2003] i want this query to display ,their is syntax error in this query.I could not understand. Seven days of this week with measure value Regards Prasanna Please reply
  • 2. Date Dimension for just hours ?
    I have 2 dimensions for time... 1) date dimension for days of year (ie 365/366 entries per year) 2) time dimension with minute granularity (ie 1440 entries) When I define the dimensions using the analysis manager, what is the right way to deal time dimension ? I the date dimension seems to get special treatment since the wizard asks me if this is a date dimension. -- Thanks, is advance :) Andrew
  • 3. Help: how to get support of Microsoft for SQL2K Analysis Services OLAP
    Hi, What is the procedure to obtain formal support from microsoft for SQL2K Analytical Services OLAP? do you know a web site for this? The standard microsoft support webpage asks for a product-id of sqlserver, which we don't have - we have an MSDN subscription? Please advise. Thanks, ralph
  • 4. Problem access OLAP in client
    Hi, all. We have setup new OLAP server in a server. Problem is when view the report no data appear on screen. We even tried to use administrator right to logon Windows also cannot. No error display in the web page. Some computer can show without problem. What is the actual system requirement? Any idea why? Thanks a lot. regards, LC
  • 5. Descendants Problem
    Hi, I have a problem with peforming a topcount on a dimention with following Level 1 Level 2 Level 3 Level 4 Level 5 A A A A B A A A B B A A A B C X C A E B X A D A B What I want to do is take any level and perform a topcount of the lowest level for example if I was at LEVEL 3 and "A" was selected. I should get a count of B=3 , C=1 however becouse level 5 "B" is under difrent parents I get "B" repeated. Can anyone help with is one I have tried everything. I have included the MDX code that I am using, SELECT Non Empty { [Measures].[Total Visits] } ON COLUMNS, Non Empty { TopCount ( { Generate ( { [dimReferrerSearch].[MediaGroup].&[G-1].&[G-2] }, { Order ( { Descendants ( [dimReferrerSearch].CurrentMember, [dimReferrerSearch].[SearchPhrase], SELF ) }, [dimReferrerSearch].CurrentMember, BDESC ) } ) }, 50, [Measures].[Total Visits] ) } ON ROWS FROM [cubVisit] WHERE [dimCampaignID].[All dimCampaignID].[200047]

Performance issue - large dimensions

Postby S29lbg » Thu, 12 Jan 2006 18:50:02 GMT

Hi all,

I have 2 dimensions, one called "Product", the other one is "Advertiser". 
They each contain resp. 13.000+ and 3.000+ elements.

I'm using the pivottable in Excel. When i put one of the dimensions in the 
grid, it takes 2 or 3 seconds, which is good. But when i place the 
Advertisers, and next to that the products in the grid, it takes mor than 3 
minutes to load. 

I'm using a MOLAP storage, at 25% (+/- 230 aggregations), and i've also trie 
usage-based optimisation, but none of that made any great difference.

Is there a way to enhance this, or do i have to reduce my dimension sizes ?

Thanks in advance!


Re: Performance issue - large dimensions

Postby » Thu, 12 Jan 2006 22:51:02 GMT

there is many point to verify.

how many cells are retrieved in Excel?
does your AS server works (CPU usage) during this time?
do you display a measure or calculated measure?
Do you really need to display a so huge amount of data?
Do you use the pivottable or the Microsoft Excel add-in (separate download 
on the MS site)?
its AS2000 or 2005?








Re: Performance issue - large dimensions

Postby S29lbg » Thu, 12 Jan 2006 23:04:03 GMT

Hi,

Thanks for your reply. To answer your questions :
- In excel, i only have 1 columns, and 175 000 records
- The AS server works a, but it's CPU isn't very high, and it doens't last 
to long (in contrast : the cpu of the client is working very hard)
- i display a measure
- yes, i really do need to display so much, it depends on the combination 
Advertiser-Product
- i use the standard excel pivottable. i also have the tool installed, but i 
only use that one for layoing the base of an mdx)
- It is AS2000

A detail: when i query the facttable, i get the same resultset in only 
seconds! 

Kind regards, Koen








Re: Performance issue - large dimensions

Postby » Fri, 13 Jan 2006 03:04:00 GMT

the pivottable in excel is not efficient.
if you use the pivottable outside excel (using the OWC (office web 
component)) the performance will be better.
there is nothing to do.
maybe you have to change autofit options or formatting options..

using the new Excel add-in the performance will be better. but 175K is big, 
its greater then the supported number of rows!!!






>>
>>> there is many point to verify.
>>>
>>> how many cells are retrieved in Excel?
>>> does your AS server works (CPU usage) during this time?
>>> do you display a measure or calculated measure?
>>> Do you really need to display a so huge amount of data?
>>> Do you use the pivottable or the Microsoft Excel add-in (separate 
>>> download
>>> on the MS site)?
>>> its AS2000 or 2005?
>>>




>>>>> Hi all,
>>>>>
>>>>> I have 2 dimensions, one called "Product", the other one is 
>>>>> "Advertiser".
>>>>> They each contain resp. 13.000+ and 3.000+ elements.
>>>>>
>>>>> I'm using the pivottable in Excel. When i put one of the dimensions in 
>>>>> the
>>>>> grid, it takes 2 or 3 seconds, which is good. But when i place the
>>>>> Advertisers, and next to that the products in the grid, it takes mor 
>>>>> than
>>>>> 3
>>>>> minutes to load.
>>>>>
>>>>> I'm using a MOLAP storage, at 25% (+/- 230 aggregations), and i've also
>>>>> trie
>>>>> usage-based optimisation, but none of that made any great difference.
>>>>>
>>>>> Is there a way to enhance this, or do i have to reduce my dimension 
>>>>> sizes
>>>>> ?
>>>>>
>>>>> Thanks in advance!
>>>>>
>>>
>>>
>>> 



Similar Threads:

1.SSAS2005: performance issue with my largest dimension

Hi,

in my cubes, my largest dimension (customers) is also the only without any 
user hierarchy.
I have 150 000 members in this one.
My users use Excel to access the cube

my users want to list the customer name in their analysis
this result in a slow response time (5 minutes to return 900 customers)
my users do multiple selection in filters, ask for real measure (no 
calculation) and for many columns (between 1 and 10; not so much)
also if the user ask to sort the results the response time is horrible.

after analysis I have found that the way Excel send the query is not 
optimum, and I can't change this.
If I deactivate the sub totals the response time is greatly improved, but 
still slow.
and the first time the user put the customer name in row, the subtotal is 
check by default and this cause the problem.

the SP2 appear to greatly improve the peformance. good.

but the response time still too slow for my real world.

the system always scan the 150 000 members in the queries

what can I do to improve this?
there is any option on the server side or in the connectionstring?
there is anything special to do at my dimension level?
Does a user hierarchy based on the customer name only improve the 
performance?

thanks for your ideas.

Jerome.
 

2.Performance issues with large fact tables(1.5 mill rows) and large dimensions (one dimension 20k another 50k)

I am using excel as a presentation layer for the processed data.

I have 10 dimensions including the fact table dimension.

There are 2 larger dimensions one with 20k members and another 50k
members.

Three dimensions have 2-6 members, others have between 20 - 300
members.

The fact table has 1.5 million rows.



All the tables being used by the dimensions have proper indices.

I tried aggregation but the max optimized level is 6% with 20
aggregations.



Is there anything else I can do to optimize this cube?

Would ROLAP/MOLAP make a difference?

Would it be faster to make smaller dimensions attributes of larger
dimensions(clutching at straws here) ?

All the dimensions are necessary.



This isn't a disk bottleneck issue as I am using a SAN for storing
cube files.

3.Large Dimension Performance Improvement Tips in SSAS 2005

hi Every1,
             Please help me out with this problem.... The scenario is
i have a cube with close to 1.7 million rows in fact table i have ....
about 14 dimensions .. of which two of them have 170,000 and 85,000
members.. The queries that are fired upon are kind of top in top . If
you dont get what i am talking abt heres the MDX construct:
                       generate( { topcount([dim1], measure,10)},
{nonemptycrossjoin([dim1].currentMember ,{topcount([dim2],measure,
10)} )}.. where dim2 is the large dimension

The above query takes typically 1min 30 secs..
Would love to drop the minute and make it 30 secs.
 My hardware for SSAS-2005  machine is 2 - Dual core Cpus of Intel
Xeon , 8 gb Ram , 10k RPM SAS disks.

I tried creating aggregations based on usage but still the performance
hasnt been boosted all i got was a 1 sec difference . Also tried
creating partitions but even that didnt help....
 I remember in AS2000 there was a setting for Very Large Dimensions
which could be set upto 10000.. I had tried that in AS2K and had
gotten good results... Any such things in SSAS-2005


Please help me out over here

4.Performance problem with large dimension and OWC

Hi there,

We have built a cube using SSAS 2005 and have a customer natural dimension 
that has eight attribute dimesion levels. The seventh level has about 200k 
members, and the eighth level has 1.1M members

We have a time dimension with two levels (year and month), and we have a 
partition for each month of the year - currently 24 months.

We have built a simple OWC interface for our users to browse the cube, but 
have noticed that there are some severe performance problems when our users 
drill down from the seventh level to the eighth level of the customer 
hierarchy.

The basic grid is the customers on the rows and time on the columns. When 
there is more than one month in the columns the simple drill through from 
the seventh to the eighth level the response time takes around 1-2 minutes. 
We are running on a quard core 4CPU 64bit server with 16GB RAM, so I don;t 
think it is hardware related.

When I do a performance trace I noticed the OWC control is constantly going 
backwards and forwards to the cube server.

Has anyone seen anything like this before?


5.security on a large dimension and performance impact (AS2005)

Hi,

I have to plan to apply some security in my cubes, and I'm looking for 
somebody with similar setup and if you have encounter performance issues... 
(or any issue)

My largest dimension (180 000 customers members) will be secured.
I'll create 200 roles, each role give access to a particular list of member 
in this dimension. (its NOT a dynamic security; the leaf level will be 
secured)
the same member is allowed in more then 1 role (I can't setup an hierarchy 
in this dimension)
Visual total is NOT enabled
Today I have 3 users by role, but this will change to hundred of users by 
roles if there is no problems.

So the user can see the total of all the customers, but when the user want 
to see the customer name, only the name of allowed customers are displayed.

how this impact the performance?

Before I'll apply this security, I want to know what I have to check and 
validate.

Can you share your experience?

thanks.

Jerome.


6. order and rank large dimension performance

7. performance with owc and large dimensions

8. Drill down issue with large dimension



Return to MS SQL SERVER

 

Who is online

Users browsing this forum: No registered users and 43 guest