August 31, 2011 at 4:52 pm
Greetings all 😀
I swear I am completely engrossed in cubes lately!
So one issue my users have been noticing is filtering on dimensions with many members. For instance Item or account name. When originally filtering the list it works fine and some users will filter out just a few items. However some time passes and they want to filter out a few more accounts or items, when they click the filter list (whether it is in the report filter, column, or row label) the window pops up but the list never populates for them to select more. Sometimes it will even cause excel to close out and need to be restarted. Note that everyone here uses excel 2007. This is happening on all my cubes, which range from 4MB all the way up to 1GB. I have fairly well defined partition structures and aggregations.
Any help would be appreciated 🙂
Link to my blog http://notyelf.com/
September 3, 2011 at 2:23 pm
does it only happen when the users filter on columns with many members?
are these members at the same granularity as the key?
are you sure your attribute relationships are created correctly?
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
September 6, 2011 at 12:20 pm
Yes this seems to only happen on fields with more than 1000 members.
Often these have the same granularity as the key. The attribute relationships are the default relationships.
Link to my blog http://notyelf.com/
September 7, 2011 at 6:29 am
You likely need discretion buckets to group the attributes into groups. You can create your own or let SSAS do it.
September 7, 2011 at 7:22 am
shannonjk (9/6/2011)
Yes this seems to only happen on fields with more than 1000 members.Often these have the same granularity as the key. The attribute relationships are the default relationships.
Is it happening to all users or just some?
Do the users have a decent connection speed to the Analysis Services Server?
Does the Analysis Services Server have enough resources?
Have you been able to reproduce the issue via SSMS by browsing the same cube and recreating the layout?
EDIT: you're not using ROLAP storage for dimensions or measure groups are you??
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
September 7, 2011 at 2:18 pm
This is happening to all users. I believe the users have a fairly decent connection to the data cubes. The overall speed of the cubes seems to be on par with using Visual studio to browse the cube.
How would I go about determining if Analysis services has enough resources? Currently the server it is on has 50GB of memory and I believe I limited to a max of 25% (12.5GB of memory).
I have never used SSMS to browse the cubes but I will attempt to do that now 😀
And no I am not using ROLAP as this is a DW that is only updated once a night, and processed once when that update has been ran. I just use the default MOLAP.
I do have the larger cube split into 6 partitions (by year) and aggregations built on top of that, but that was a recent addition, and did not resolve any of the problems.
Link to my blog http://notyelf.com/
September 7, 2011 at 3:30 pm
shannonjk (9/7/2011)
This is happening to all users. I believe the users have a fairly decent connection to the data cubes. The overall speed of the cubes seems to be on par with using Visual studio to browse the cube.How would I go about determining if Analysis services has enough resources? Currently the server it is on has 50GB of memory and I believe I limited to a max of 25% (12.5GB of memory).
I have never used SSMS to browse the cubes but I will attempt to do that now 😀
And no I am not using ROLAP as this is a DW that is only updated once a night, and processed once when that update has been ran. I just use the default MOLAP.
I do have the larger cube split into 6 partitions (by year) and aggregations built on top of that, but that was a recent addition, and did not resolve any of the problems.
definitely sounds like your server has plenty of juice.
try reproducing in SSMS (to eliminate Excel from the equation). On the login screen that pops up by default when you launch SSMS, select "Analysis Services" in the server type box (instead of "Database Engine"). Locate your Analysis Services database in the object explorer and expand the folders until you see the cube in question. Right click the cube and select "Browse"...this will bring up a pane in the right side of the screen that resembles an Excel pivot table layout. Just drag/drop as you would in Excel and see if you are able to reproduce the issue.
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
September 7, 2011 at 3:39 pm
I was able to figure it out right before you posted :-D. I did this in SSMS and the problem did not replicate. I did a ping from my computer to the server and the ping is on average of 8ms. I did some major processing and watched the network usage on both my computer and the network computer and neither pushed over 1% of a 1Gbps connection. Not sure if that means it is not using much network connectivity, or if the connection is not strong enough.
Link to my blog http://notyelf.com/
September 7, 2011 at 3:59 pm
This may sound like a silly question, but is there a way to limit what filters are in the list? For example, if I just filtered the dataset to this month, there would be significantly less members to choose from, however, in doing this, when I select the mebers to filter, ones that do not exist in the current data filter still appear. Is there a way to adjust that?
Link to my blog http://notyelf.com/
September 9, 2011 at 12:31 pm
shannonjk (9/7/2011)
I was able to figure it out right before you posted :-D. I did this in SSMS and the problem did not replicate. I did a ping from my computer to the server and the ping is on average of 8ms. I did some major processing and watched the network usage on both my computer and the network computer and neither pushed over 1% of a 1Gbps connection. Not sure if that means it is not using much network connectivity, or if the connection is not strong enough.
doesn't sound like the network...and the fact that you weren't able to reproduce via SSMS makes me think it could be an issue with the workstation. Do you know the specs (OS / amount of memory)?
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
September 9, 2011 at 12:36 pm
shannonjk (9/7/2011)
This may sound like a silly question, but is there a way to limit what filters are in the list? For example, if I just filtered the dataset to this month, there would be significantly less members to choose from, however, in doing this, when I select the mebers to filter, ones that do not exist in the current data filter still appear. Is there a way to adjust that?
you could create a dynamic named set.
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
September 19, 2011 at 2:57 pm
So after some more research and monitoring of both sides of the equation, I notice there are times where Excel takes literally 2GB of memory while doing this calculation. There is very little memory being taken on the server ( I have even raised the limit of the memory) but I do notice only 1 processor is running at 100% on the server when this happens despite there being 16 cores. Is there any way to fix this?
Link to my blog http://notyelf.com/
September 19, 2011 at 7:37 pm
shannonjk (9/19/2011)
So after some more research and monitoring of both sides of the equation, I notice there are times where Excel takes literally 2GB of memory while doing this calculation.
yikes...any chance you can capture the query being generated by excel using profiler?
There is very little memory being taken on the server ( I have even raised the limit of the memory) but I do notice only 1 processor is running at 100% on the server when this happens despite there being 16 cores. Is there any way to fix this?
the single processor spike on the server is likely due to some computations begin carried out in the formula engine.
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
September 28, 2011 at 11:48 pm
Can you build a hierarchy on above the attributes that are causing this issue?
I've noticed this issue occurs when you have an extremely large dimension and the user tries to filter on it in Excel.
What I think is happening is that when a user tries to filter on a Dimension, Excel does a full retrieve of all members in the dimension attribute the user has selected. To allow the user to filter and choose, Excel has to cache all the members and this would explain why the user's computer would have high RAM usage.
The reason I bring up hierarchies is because I believe when you build a hierarchy for an attribute with a lot of members, and only allow the users to get to the lower attribute through the hierarchy, Excel never has to have a full cache of all the members. When a user filters using the hierarchy, the first set Excel needs to cache is at the parent attribute, then when the user expands a parent member, the child attribute members will be cached (a smaller subset of the original members).
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply