January 6, 2008 at 12:33 pm
Looks like Cubes are not meant to be queried like relational data sources to return a large flat resultset.
I have a requirement to display some 13 columns in a SSRS report that uses MDX to retrieve data from Cube. The report has got 7 parameters that can filter data based on different dimensions.The parameters are designed in a such a way that the resultset will not contain > 50000 records.
All these column values come from attributes of different dimension. I also use 2 dimensions in slicer axis. The performance is fine till 12 dimensions(each of the dimension contain data from 2 members to 1000 members). When I add the last one (which is a large dimension of about 0.7 million members), the performance is arbitrary. Sometimes it is quite fast (1 to 5 sec) but some times it just hangs (seems to do cell-by-cell computation instead of bulk computation) (i.e. In a scenario where it has to return 50 K records, it works fine but when it has to return 20 K records it errors out and throws mini-dumps)
The query is very simple... (as below)
WITH
SET [BookList] AS
IIF(@SourceBook='', [Book].[Book].[Book].Members, STRTOSET(@SourceBook) )
.
.
.
SELECT NON EMPTY {
Measures.Value
} ON 0,
NON EMPTY (
Currency *
BookList *
.
.
.
[Trade].[Trade].[Trade]
) ON 1
FROM MyCube
WHERE StrToSet(@Calendar)
Each of the set is defined based on SSRS parameters.
Any Pointers to improve performance?
Cheers,
Arun
January 7, 2008 at 11:32 am
Understanding performance in MSAS is quite a detailed topic. However, some things to get your started:
- How big is your cube and how do you have it partitioned? Partitioning the cube is fundamental to maintaining good performance on non-trivial cubes.
- Your MDX isn't really well formed, so it's a little hard to tell what you're attempting to do. For example, I only see two parms (@SourceBook and @Celendar). Can you include the entrie MDX?
- Only pull / reference the information you actually need. As wellyou're referencing "sets", you can see dramatically different performance characteritics, depending on what you've referenced.
Download the BIDS project and add it to your VS environment. It'll provide some handy add-ins to hep you understand cube performance and tuning your environment.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply