October 30, 2003 at 7:54 am
Hi all,
My cube is based on a fact table with aproc. 130.000 records. The number of dimensions is 16. 3 Dimensions consists out of more than one level (avg 3 levels) and I have 10 measures. The server machine has 265 MB RAM (test). Can I do something to speed up the query time.
I tried to add an extra partition (one for each year) but then get the error "User-defined partitions are available only if you install Analysis Services for SQL Server 2000 Enterprise Edition". How can I see if this is the case ?
Thanks in advance.
Kind Regards,
Bart
October 30, 2003 at 8:09 am
quote:
I tried to add an extra partition (one for each year) but then get the error "User-defined partitions are available only if you install Analysis Services for SQL Server 2000 Enterprise Edition". How can I see if this is the case ?
The fact that you get the error means that you don't have the Enterprise edition...
Execute
SELECT @@VERSION
in Query Analyzer to get the installed edition and version.
Increasing hardware specs will certainly speed things up. Think about RAM, CPU's, disks, ...
Also look at optimising your cube for performance when 'Designing Storage'. Set the performance gain in the wizard to a high enough value.
There is also an option to perform 'Usage Based Optimizations'. Haven't tried that though.
October 30, 2003 at 9:42 pm
Bart,
What sort of query performance are you getting (or not getting)?
Do you have distinct count measure in the cube?
130k records is not very large, do your dimensions have very large member counts? If so, what would be the percentage of used dimension members (ie are there a lot of nulls)?
lastly, are you using a tool to generate the queries that are performing poorly or are they hand-written mdx? If a tool, have you traced the mdx to find out what the query actually looks like?
Steve
Steve.
October 31, 2003 at 12:32 am
Thanks Noel and Steve,
Noel:
When I execute the "select @@version" the result is "Microsoft SQL Server 2000 - 8.00.760 ..." so it's not the Enterprise version. Only thing to do is install in Enterprise version ... For the moment we are doing some tests but in production I'll make sure we have enough RAM and Disks. I'll let you know the result.
Steve:
It takes a while to get the result from the query (query is based up on a Pivot Table in MS Excel 2000 and not a hand written MDX).
Ditinct count measure in cube... I think I can find this in the Aggregation function ?... it's Sum.
The member counts varies between 2 and 4300. There are a lot of fields that are empty but not contain NULL.
Is it possible to see which MDX queries Excel (PTS) sends to the cube in Analysis services ?
Again, thanks for the replies from the both of you.
Kind regards,
Bart
October 31, 2003 at 8:24 am
When you process the cubes, what % aggregations are you setting the system to process? The more aggregations you let it build to begin with, the faster your queries as it won't need to go back to the db to calculate on the fly.
Also look at your tables/views you are using to build the cube and look at you indexes. Indexing your most used dimension tables and associated codes in the fact table will help response times if it needs to look back at the database to calculate something.
Also GET MORE RAM!
November 2, 2003 at 3:53 am
Bart,
How many 'nests' are you doing in your report/query? i.e. Do you have several dimensions on rows and columns? And are you trying to suppress the zero valued cells? (you say a lot of the values are zero but not empty). If yes, then this could be a problem with AS and PTS - in short, if you can't make the queries use NON EMPTY or nonemptycrossjoin, then performance will degrade. Some things you can try is to ensure you have SP3 for AS on the server, and the SP3 version of PTSfull/PTSLite on the client. There's also some connection string settings you can change - i'll shoot them thru tomorrow.
HTH,
Steve.
Steve.
November 3, 2003 at 4:46 am
Hi Steve,
I have several dimensions in row and columns. The zero values are suppressed. How can I check which version (SP3...) I have for PTSfull/lite.
This connection string you mentioned where can I adjust this ?
Kind regards,
Bart
November 3, 2003 at 5:21 pm
Hi Bart,
Couple of things, I remembered that when trying to speed the query performance up, we did this by trying to make the server perform the majority/all of the work. With your lower spec machine, this may not be the best idea/may not make any difference, but you can try.
To see if the zero suppression is really affecting you, you could try using OWC to open the cube, create the same report but change the options so that you are returning empty rows. This *should* return faster as there is now filtering required. I thought you used to be able to do this in Excel, but I tested 2k and xp, and in both it's disabled for olap datasources.
The connection string/s can be found within the *.oqy file/s on your machine. For me (win xp), these are located at C:\Documents and Settings\steven\Application Data\Microsoft\Queries. I'm still chasing down the properties to add (i've posted them here before) hope to find them before the day is out.
Not sure on how to find whether you're on SP3 of PTS, will take alook around.
HTH,
Steve.
Steve.
November 4, 2003 at 12:20 am
Hi Steve,
Can you tell me what OWC is ? I don't know it and I want to see if the zero supression is affecting me.
I've found the connection string in the *.ocq file.
Thanks in advance.
Kind regards,
Bart
November 4, 2003 at 12:27 am
OWC = Office Web Components. If you can't make use of these, try to log the MDX query being sent and try running it in the MDX Sample Application. This should take the same amount of time to run. To then determine if the zero suppression is hurting performance, you'll need to modify the query (in MDX Sample App) to remove the zero suppression and see how long it takes to resolve then.
To log -> In the connection string, add an entry of Log File=C:\barts_Log.log;
Just remember to remove this later on!
Alternatively, you could attempt to recreate the query using the "Cube Viewer" built into AS Manager. This allows suppression and non-suppression of empty values.
HTH,
Steve.
Steve.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply