February 3, 2012 at 7:16 am
I have a simple cube with two calculation to calculate average and standard deviation. I have to create a simple SSRS report based on this cube, showing these two calculated member of the cube. These facts have to be aggregated in the AS database so I tried to use the SSRS aggregate function. (More on this question here: http://blog.datainspirations.com/2011/03/08/retrieving-aggregate-data/).
I found that changing the report level aggregation (SUM or AVG) to AGGREGATE the automatically generated MDX of the dataset become very slow.
I haven't found any solution on the Net, so I began to try things. Finally I got a very fast solution simply using union operator on the row sets. The duration time decreased from 37 minutes (!) to 3 seconds (!!!)
I'm a newbie on MDX so I am not quite sure if it is a "legal" solution or it has got some drawbacks. The union function filters out the duplicated rows but it's no problem.
If the solution is OK, I do not know why I can't find any information about this (appearantly) very simple trick.
The origiinal MDX script with 37 minutes running time:
SELECT
NON EMPTY
{
[Measures].[Ea STD_DEV]
,[Measures].[Ea AVG]
} ON COLUMNS
,NON EMPTY
{
{[DIM Ea].[Eacim].[All]}*
{[DIM Ea].[Kurzuscim].[All]}*
{[DIM Ea].[Ea Name].[All]}*
{[DIM Kerdes].[Kerdes Sorszam].[All]}
*[DIM Ea].[SZEMESZTER NEV].[SZEMESZTER NEV].ALLMEMBERS
,
[DIM Ea].[Eacim].[Eacim].ALLMEMBERS*
[DIM Ea].[Kurzuscim].[Kurzuscim].ALLMEMBERS*
[DIM Ea].[Ea Name].[Eloado Neve].ALLMEMBERS*
[DIM Kerdes].[Kerdes Sorszam].[Kerdes Sorszam].ALLMEMBERS
*[DIM Ea].[SZEMESZTER NEV].[SZEMESZTER NEV].ALLMEMBERS
}
DIMENSION PROPERTIES
MEMBER_CAPTION
,MEMBER_UNIQUE_NAME
ON ROWS
FROM [SAMPLE_DW]
The second and fast one (the difference only the union function):
SELECT
NON EMPTY
{
[Measures].[Ea STD_DEV]
,[Measures].[Ea AVG]
} ON COLUMNS
,NON EMPTY
{union(
{[DIM Ea].[Eacim].[All]}*
{[DIM Ea].[Kurzuscim].[All]}*
{[DIM Ea].[Ea Name].[All]}*
{[DIM Kerdes].[Kerdes Sorszam].[All]}
*[DIM Ea].[SZEMESZTER NEV].[SZEMESZTER NEV].ALLMEMBERS
,
[DIM Ea].[Eacim].[Eacim].ALLMEMBERS*
[DIM Ea].[Kurzuscim].[Kurzuscim].ALLMEMBERS*
[DIM Ea].[Ea Name].[Eloado Neve].ALLMEMBERS*
[DIM Kerdes].[Kerdes Sorszam].[Kerdes Sorszam].ALLMEMBERS
*[DIM Ea].[SZEMESZTER NEV].[SZEMESZTER NEV].ALLMEMBERS
)
}
DIMENSION PROPERTIES
MEMBER_CAPTION
,MEMBER_UNIQUE_NAME
ON ROWS
FROM [SAMPLE_DW]
Thanks,
Laszlo
February 3, 2012 at 8:33 am
I am not very sure about it but the first MDX query is trying do something like cross join whereas the second one is doing union that is why the second one is much faster and will have much lesser number of records as compared to first one
February 6, 2012 at 12:32 am
Thank you for replying.
Actually the two scripts generate the same result. UNION only filter out the same rows but in this case there aren't any. And if I try to run the two comma separated parts one by one in the slow MDX, the running times are below 10 seconds in both cases and of course I get the the two parts of the result of the original scipt. That is why I tried union at last. And it was a great success, but I do not really understand the reason.
February 6, 2012 at 1:05 pm
I will keep it in mind and will update you if I ever find the reason....:-)
February 9, 2012 at 3:40 am
Thanks! 🙂
I created a similar MDX script with SSRS on the Adventure Works DW 2008R2 sample database for testing purposes. The result is the same.
The difference between the two scripts is only the usage of UNION function ("+" operator) instead of enumeration of the two sets (by ",").
The corresponding scripts:
1. the slow one with enumeration (running time on my machine more than twenty minutes):
SELECT
NON EMPTY
{
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Average Sales Amount]
} ON COLUMNS
,NON EMPTY
{
[Reseller].[Reseller Type].[Reseller].ALLMEMBERS*
{[Reseller].[Reseller].[All Resellers]}*
[Geography].[Country].[Country].ALLMEMBERS*
[Date].[Fiscal Year].[Fiscal Year].ALLMEMBERS*
[Date].[Fiscal Weeks].[Fiscal Week].ALLMEMBERS
,
[Reseller].[Reseller Type].[Reseller].ALLMEMBERS*
[Reseller].[Reseller].[Reseller].ALLMEMBERS*
[Geography].[Country].[Country].ALLMEMBERS*
[Date].[Fiscal Year].[Fiscal Year].ALLMEMBERS*
[Date].[Fiscal Weeks].[Fiscal Week].ALLMEMBERS
}
DIMENSION PROPERTIES
MEMBER_CAPTION
,MEMBER_UNIQUE_NAME
ON ROWS
FROM [Adventure Works]
CELL PROPERTIES
VALUE
,BACK_COLOR
,FORE_COLOR
,FORMATTED_VALUE
,FORMAT_STRING
,FONT_NAME
,FONT_SIZE
,FONT_FLAGS;
2. The fast one with + operator (union) (running time: 9 sec):
SELECT
NON EMPTY
{
[Measures].[Reseller Sales Amount]
,[Measures].[Reseller Average Sales Amount]
} ON COLUMNS
,NON EMPTY
{
[Reseller].[Reseller Type].[Reseller].ALLMEMBERS*
{[Reseller].[Reseller].[All Resellers]}*
[Geography].[Country].[Country].ALLMEMBERS*
[Date].[Fiscal Year].[Fiscal Year].ALLMEMBERS*
[Date].[Fiscal Weeks].[Fiscal Week].ALLMEMBERS
+
[Reseller].[Reseller Type].[Reseller].ALLMEMBERS*
[Reseller].[Reseller].[Reseller].ALLMEMBERS*
[Geography].[Country].[Country].ALLMEMBERS*
[Date].[Fiscal Year].[Fiscal Year].ALLMEMBERS*
[Date].[Fiscal Weeks].[Fiscal Week].ALLMEMBERS
}
DIMENSION PROPERTIES
MEMBER_CAPTION
,MEMBER_UNIQUE_NAME
ON ROWS
FROM [Adventure Works]
CELL PROPERTIES
VALUE
,BACK_COLOR
,FORE_COLOR
,FORMATTED_VALUE
,FORMAT_STRING
,FONT_NAME
,FONT_SIZE
,FONT_FLAGS;
Any explanation is appreciated. Or use it as a tuning tip.
BR
Laszlo
March 22, 2017 at 5:24 pm
I have a similar issue wherein SSRS is needlessly duplicating all crossjoins in the generated query, causing an exponential increase in execution time.
SELECT
NON EMPTY {
[Measures].[Percent Complete]
,[Measures].[Planned Percent Complete]
} ON COLUMNS,
NON EMPTY {
{[Class].[Class Name].[All Classes]} *
[Student].[Last Name - First Name].[Last Name - First Name].ALLMEMBERS *
[Snapshot Date].[Calendar Weeks].[Calendar Year].ALLMEMBERS,
(
[Class].[Class Name].[Class Name].ALLMEMBERS *
[Student].[Last Name - First Name].[Last Name - First Name].ALLMEMBERS *
[Snapshot Date].[Calendar Weeks].[Calendar Week].ALLMEMBERS
)
} ON ROWS
FROM [Training]
The only work-around for this seems to be to write the query manually.
May 9, 2017 at 2:08 pm
I just posted here.
Im having the exact same problem in SSDT
https://www.sqlservercentral.com/Forums/1872860/AGGREGATE-FUNCTION-hanging-against-SSAS
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply