August 11, 2015 at 2:27 am
Hello everybody,
This is my issue:
I have an mdx dataset for a SSRS report which is running in Management Studio in 1 sec, but in report it takes 1 minute. This is a critical report and the user want it as fast as possible. I have noticed that in the view ExecutionLog3 of the ReportingServices database, the dataretrieval causes the issue.
The MDX uses calculated members which use DESCENDANTS function which I putted in a declared set because of performance.
Please could someone advise what to do to lower the report execution time?
Thank you in advance!
August 11, 2015 at 3:32 am
are you sure its the mDX thats causing the issue, why are you not using the same MDX query in both cases? SSRS performs rendering of the report in addition to fetching the data so there is a possiblity that the MDX completed quickly and its just SSRS thats taking time to render the report, try running a trace on SSAS to see how long the query takes when running from SSRS
August 11, 2015 at 4:18 am
I already did this. Profiler shows that the MDX is running slow. Also I've checked the view ExecutionLog3 of the ReportingServices database, and the column TimeDataRetrieval have the much higher value than the TimeProcessing or TimeRendering columns.
The MDX returns 5 rows and 54 measures.
August 12, 2015 at 2:28 am
August 12, 2015 at 2:52 am
This is the MDX I run:
with
member [Articlegroup].[Umbrella Art].[Other Branded] AS NULL
member [Articlegroup].[Umbrella Art].[Total Branded] AS NULL
member [Articlegroup].[Umbrella Art].[Total Own Branded] AS NULL
member [Articlegroup].[Umbrella Art].[Total] AS NULL
set DescUmbArtCat as
DESCENDANTS(StrToset("{[Articlegroup].[Umbrella Art].&[118],[Articlegroup].[Umbrella Art].&[602]}"), 2)
Set DescUmbArt as
DESCENDANTS(Strtoset("{[Articlegroup].[Umbrella Art].&[118]}"), 2)
member SalesCY as
[Measures].[Sales]
member TotalMktGrossSalesCY AS
SUM(DESCENDANTS({[Articlegroup].[Umbrella Art].CURRENTMEMBER}, 2)
, [Measures].[Total Market Gross Sales])
member TotalMktGrossSalesCYTotalBranded as
SUM(([Article].[Branded Label].&[0], DescUmbArtCat)
, [Measures].[Total Market Gross Sales])
member TotalMktGrossSalesCYTotalBrandedSelected as
SUM(([Article].[Branded Label].&[0], DescUmbArt)
, [Measures].[Total Market Gross Sales])
member TotalMktGrossSalesCYTotalOwnBranded as
SUM(([Article].[Branded Label].&[1], DescUmbArt)
, [Measures].[Total Market Gross Sales])
member TotalMktGrossSalesCYTotalCategory as
SUM(DescUmbArtCat
, [Measures].[Total Market Gross Sales])
member TotalMktGrossSalesCYOtherBranded as
iif(TotalMktGrossSalesCYTotalBranded -TotalMktGrossSalesCYTotalBrandedSelected=0, null,TotalMktGrossSalesCYTotalBranded -TotalMktGrossSalesCYTotalBrandedSelected)
member SmallFormatGrossSalesCY AS
SUM(DESCENDANTS({[Articlegroup].[Umbrella Art].CURRENTMEMBER}, 2)
, [Measures].[Small Format Market Gross Sales])
member SmallFormatGrossSalesCYTotalBranded as
SUM(([Article].[Branded Label].&[0], DescUmbArtCat)
, [Measures].[Small Format Market Gross Sales])
member SmallFormatGrossSalesCYTotalBrandedSelected as
SUM(([Article].[Branded Label].&[0], DescUmbArt)
, [Measures].[Small Format Market Gross Sales])
member SmallFormatGrossSalesCYTotalOwnBranded as
SUM(([Article].[Branded Label].&[1], DescUmbArt)
, [Measures].[Small Format Market Gross Sales])
member SmallFormatGrossSalesCYTotalCategory as
SUM(DescUmbArtCat
, [Measures].[Small Format Market Gross Sales])
member SmallFormatGrossSalesCYOtherBranded as
iif(SmallFormatGrossSalesCYTotalBranded -SmallFormatGrossSalesCYTotalBrandedSelected=0, null,SmallFormatGrossSalesCYTotalBranded -SmallFormatGrossSalesCYTotalBrandedSelected)
member LargeFormatGrossSalesCY AS
SUM(DESCENDANTS({[Articlegroup].[Umbrella Art].CURRENTMEMBER}, 2)
,[Measures].[Large Format Market Gross Sales])
member LargeFormatGrossSalesCYTotalBranded as
SUM(([Article].[Branded Label].&[0], DescUmbArtCat)
,[Measures].[Large Format Market Gross Sales])
member LargeFormatGrossSalesCYTotalBrandedSelected as
SUM(([Article].[Branded Label].&[0], DescUmbArt)
, [Measures].[Large Format Market Gross Sales])
member LargeFormatGrossSalesCYTotalOwnBranded as
SUM(([Article].[Branded Label].&[1], DescUmbArt)
, [Measures].[Large Format Market Gross Sales])
member LargeFormatGrossSalesCYTotalCategory as
SUM(DescUmbArtCat
,[Measures].[Large Format Market Gross Sales])
member LargeFormatGrossSalesCYOtherBranded as
iif(LargeFormatGrossSalesCYTotalBranded -LargeFormatGrossSalesCYTotalBrandedSelected=0, null,LargeFormatGrossSalesCYTotalBranded -LargeFormatGrossSalesCYTotalBrandedSelected)
member TotalMCCGrossSalesCY AS
SUM(DESCENDANTS({[Articlegroup].[Umbrella Art].CURRENTMEMBER}, 2)
,[Measures].[Total MCC Gross Sales])
member TotalMCCGrossSalesCYTotalBranded as
SUM(([Article].[Branded Label].&[0], DescUmbArtCat)
,[Measures].[Total MCC Gross Sales])
member TotalMCCGrossSalesCYTotalBrandedSelected as
SUM(([Article].[Branded Label].&[0], DescUmbArt)
, [Measures].[Total MCC Gross Sales])
member TotalMCCGrossSalesCYTotalOwnBranded as
SUM(([Article].[Branded Label].&[1], DescUmbArt)
, [Measures].[Total MCC Gross Sales])
member TotalMCCGrossSalesCYTotalCategory as
SUM(DescUmbArtCat
,[Measures].[Total MCC Gross Sales])
member TotalMCCGrossSalesCYOtherBranded as
iif(TotalMCCGrossSalesCYTotalBranded -TotalMCCGrossSalesCYTotalBrandedSelected=0, null,TotalMCCGrossSalesCYTotalBranded -TotalMCCGrossSalesCYTotalBrandedSelected)
member TraderMCCGrossSalesCY AS
SUM(DESCENDANTS({[Articlegroup].[Umbrella Art].CURRENTMEMBER}, 2)
,[Measures].[Trader MCC Gross Sales])
member TraderMCCGrossSalesCYTotalBranded as
SUM(([Article].[Branded Label].&[0], DescUmbArtCat)
,[Measures].[Trader MCC Gross Sales])
member TraderMCCGrossSalesCYTotalBrandedSelected as
SUM(([Article].[Branded Label].&[0], DescUmbArt)
, [Measures].[Trader MCC Gross Sales])
member TraderMCCGrossSalesCYTotalOwnBranded as
SUM(([Article].[Branded Label].&[1], DescUmbArt)
, [Measures].[Trader MCC Gross Sales])
member TraderMCCGrossSalesCYTotalCategory as
SUM(DescUmbArtCat
,[Measures].[Trader MCC Gross Sales])
member TraderMCCGrossSalesCYOtherBranded as
iif(TraderMCCGrossSalesCYTotalBranded -TraderMCCGrossSalesCYTotalBrandedSelected=0, null,TraderMCCGrossSalesCYTotalBranded -TraderMCCGrossSalesCYTotalBrandedSelected)
member SCOMCCGrossSalesCY AS
SUM(DESCENDANTS({[Articlegroup].[Umbrella Art].CURRENTMEMBER}, 2)
,[Measures].[SCO MCC Gross Sales])
member SCOMCCGrossSalesCYTotalBranded as
SUM(([Article].[Branded Label].&[0], DescUmbArtCat)
,[Measures].[SCO MCC Gross Sales])
member SCOMCCGrossSalesCYTotalBrandedSelected as
SUM(([Article].[Branded Label].&[0], DescUmbArt)
,[Measures].[SCO MCC Gross Sales])
member SCOMCCGrossSalesCYTotalOwnBranded as
SUM(([Article].[Branded Label].&[1], DescUmbArt)
, [Measures].[SCO MCC Gross Sales])
member SCOMCCGrossSalesCYTotalCategory as
SUM(DescUmbArtCat
,[Measures].[SCO MCC Gross Sales])
member SCOMCCGrossSalesCYOtherBranded as
iif(SCOMCCGrossSalesCYTotalBranded -SCOMCCGrossSalesCYTotalBrandedSelected=0, null,SCOMCCGrossSalesCYTotalBranded -SCOMCCGrossSalesCYTotalBrandedSelected)
member MaxDate as
max(filter(StrToMember("([Time].[YM].[Month Name].&[201501])"):StrToMember("([Time].[YM].[Month Name].&[201505])"),TotalMktGrossSalesCYTotalBrandedSelected <> NULL),[Time].[YM].currentmember.MEMBER_KEY)
member MarketPriceCY AS
SUM(DESCENDANTS({[Articlegroup].[Umbrella Art].CURRENTMEMBER}, 2)
, [Measures].[Market Price])
member MarketPriceCYTotalBranded as
SUM(([Article].[Branded Label].&[0], DescUmbArtCat)
,[Measures].[Market Price])
member MarketPriceCYTotalBrandedSelected as
SUM(([Article].[Branded Label].&[0], DescUmbArt)
,[Measures].[Market Price])
member MarketPriceCYTotalOwnBranded as
SUM(([Article].[Branded Label].&[1], DescUmbArt)
, [Measures].[Market Price])
member MarketPriceCYTotalCategory as
SUM(DescUmbArtCat
, [Measures].[Market Price])
member MarketPriceCYOtherBranded as
iif(MarketPriceCYTotalBranded -MarketPriceCYTotalBrandedSelected=0, null,MarketPriceCYTotalBranded -MarketPriceCYTotalBrandedSelected)
member MCCPriceCY AS
SUM(DESCENDANTS({[Articlegroup].[Umbrella Art].CURRENTMEMBER}, 2)
, [Measures].[MCC Price])
member MCCPriceCYTotalBranded as
SUM(([Article].[Branded Label].&[0], DescUmbArtCat)
,[Measures].[MCC Price])
member MCCPriceCYTotalBrandedSelected as
SUM(([Article].[Branded Label].&[0], DescUmbArt)
,[Measures].[MCC Price])
member MCCPriceCYTotalOwnBranded as
SUM(([Article].[Branded Label].&[1], DescUmbArt)
, [Measures].[MCC Price])
member MCCPriceCYTotalCategory as
SUM(DescUmbArtCat
, [Measures].[MCC Price])
member MCCPriceCYOtherBranded as
iif(MCCPriceCYTotalBranded -MCCPriceCYTotalBrandedSelected=0, null,MCCPriceCYTotalBranded -MCCPriceCYTotalBrandedSelected)
MEMBER SalesCov AS
sum((FILTER( DESCENDANTS([Articlegroup].[Umbrella Art].CURRENTMEMBER, 2), [Measures].[Market Price] <> NULL)), [Measures].[Total Market Gross Sales])
/ TotalMktGrossSalesCY
MEMBER SalesCovTotalBranded AS
SUM((FILTER( ([Article].[Branded Label].&[0], DescUmbArtCat), [Measures].[Market Price] <> NULL)), [Measures].[Total Market Gross Sales])
/ TotalMktGrossSalesCYTotalBranded
MEMBER SalesCovTotalOwnBranded AS
SUM((FILTER( ([Article].[Branded Label].&[1], DescUmbArtCat), [Measures].[Market Price] <> NULL)), [Measures].[Total Market Gross Sales])
/ TotalMktGrossSalesCYTotalOwnBranded
MEMBER SalesCovTotalCategory AS
SUM((FILTER(DescUmbArtCat, [Measures].[Market Price] <> NULL)), [Measures].[Total Market Gross Sales])
/ TotalMktGrossSalesCYTotalCategory
MEMBER SalesCovOtherBranded AS
iif( SUM(([Article].[Branded Label].&[0], DescUmbArtCat), [Measures].[Total Market Gross Sales]) -SUM(([Article].[Branded Label].&[0], DescUmbArt), [Measures].[Total Market Gross Sales])=0, null,
(SUM((FILTER( ([Article].[Branded Label].&[0], DescUmbArtCat), [Measures].[Market Price] <> NULL)), [Measures].[Total Market Gross Sales]) -SUM((FILTER( ([Article].[Branded Label].&[0], DescUmbArt), [Measures].[Market Price] <> NULL)), [Measures].[Total Market Gross Sales]))
/ TotalMktGrossSalesCYOtherBranded
)
member MaxDatePrice as
max(filter(StrToMember("([Time].[YM].[Month Name].&[201501])"):StrToMember("([Time].[YM].[Month Name].&[201505])"),MarketPriceCYTotalBrandedSelected<> NULL),[Time].[YM].currentmember.MEMBER_KEY)
member LinearMetersCY AS
SUM(DESCENDANTS({[Articlegroup].[Umbrella Art].CURRENTMEMBER}, 2)
, [Measures].[Linear Meters])
member LinearMetersCYTotalBranded as
SUM(([Article].[Branded Label].&[0], DescUmbArtCat)
, [Measures].[Linear Meters])
member LinearMetersCYTotalBrandedSelected as
SUM(([Article].[Branded Label].&[0], DescUmbArt)
, [Measures].[Linear Meters])
member LinearMetersCYTotalOwnBranded as
SUM(([Article].[Branded Label].&[1], DescUmbArt)
, [Measures].[Linear Meters])
member LinearMetersCYTotalCategory as
SUM(DescUmbArtCat
, [Measures].[Linear Meters])
member LinearMetersCYOtherBranded as
iif(LinearMetersCYTotalBranded -LinearMetersCYTotalBrandedSelected=0, null,LinearMetersCYTotalBranded -LinearMetersCYTotalBrandedSelected)
member MaxDateSpace as
max(filter(StrToMember("([Time].[YM].[Month Name].&[201501])"):StrToMember("([Time].[YM].[Month Name].&[201505])"),LinearMetersCYTotalBrandedSelected<> NULL),[Time].[YM].currentmember.MEMBER_KEY)
member [Article].[Branded Label].[1] as
[Article].[Branded Label].&[1]
SELECT {TotalMktGrossSalesCY , TotalMktGrossSalesCYOtherBranded, TotalMktGrossSalesCYTotalBranded, TotalMktGrossSalesCYTotalOwnBranded, TotalMktGrossSalesCYTotalCategory,
SmallFormatGrossSalesCY , SmallFormatGrossSalesCYOtherBranded, SmallFormatGrossSalesCYTotalBranded, SmallFormatGrossSalesCYTotalOwnBranded, SmallFormatGrossSalesCYTotalCategory,
LargeFormatGrossSalesCY , LargeFormatGrossSalesCYOtherBranded, LargeFormatGrossSalesCYTotalBranded, LargeFormatGrossSalesCYTotalOwnBranded, LargeFormatGrossSalesCYTotalCategory,
TotalMCCGrossSalesCY , TotalMCCGrossSalesCYOtherBranded, TotalMCCGrossSalesCYTotalBranded, TotalMCCGrossSalesCYTotalOwnBranded, TotalMCCGrossSalesCYTotalCategory,
TraderMCCGrossSalesCY , TraderMCCGrossSalesCYOtherBranded, TraderMCCGrossSalesCYTotalBranded, TraderMCCGrossSalesCYTotalOwnBranded, TraderMCCGrossSalesCYTotalCategory,
SCOMCCGrossSalesCY , SCOMCCGrossSalesCYOtherBranded, SCOMCCGrossSalesCYTotalBranded, SCOMCCGrossSalesCYTotalOwnBranded, SCOMCCGrossSalesCYTotalCategory,
MarketPriceCY , MarketPriceCYOtherBranded, MarketPriceCYTotalBranded, MarketPriceCYTotalOwnBranded, MarketPriceCYTotalCategory,
MCCPriceCY , MCCPriceCYOtherBranded, MCCPriceCYTotalBranded, MCCPriceCYTotalOwnBranded, MCCPriceCYTotalCategory,
SalesCov, SalesCovOtherBranded, SalesCovTotalBranded, SalesCovTotalOwnBranded, SalesCovTotalCategory,
LinearMetersCY , LinearMetersCYOtherBranded, LinearMetersCYTotalBranded, LinearMetersCYTotalOwnBranded, LinearMetersCYTotalCategory,
MaxDate, MaxDatePrice, MaxDateSpace
} on columns
,{
iif(strtoset("{[Articlegroup].[Umbrella Art].&[118]}").count>16 or strtoset("{[Article].[Buying Domain].[PCG Main Cat].&[412],
[Article].[Buying Domain].[PCG Main Cat].&[853],
[Article].[Buying Domain].[PCG Main Cat].&[856],
[Article].[Buying Domain].[PCG Main Cat].&[858],
[Article].[Buying Domain].[PCG Main Cat].&[877]}")
.count>20,null,
{(order(([Article].[Branded Label].&[0], topcount(Strtoset
("{[Articlegroup].[Umbrella Art].&[118]}")
, 16, (SalesCY ))), SalesCY , desc))
,([Article].[Branded Label].&[0],[Articlegroup].[Umbrella Art].[Other Branded])
,([Article].[Branded Label].&[0],[Articlegroup].[Umbrella Art].[Total Branded])
,([Article].[Branded Label].[1],[Articlegroup].[Umbrella Art].[Total Own Branded])
,([Article].[Branded Label], [Articlegroup].[Umbrella Art].[Total])})
}
on rows
FROM TPT0300
where
(StrToMember("([Time].[YM].[Month Name].&[201501])"):StrToMember("([Time].[YM].[Month Name].&[201505])") ,
Strtoset("{[Article].[Buying Domain].[PCG Sub Cat].&[824]&[2],
[Article].[Buying Domain].[PCG Sub Cat].&[1720]&[4],
[Article].[Buying Domain].[PCG Sub Cat].&[863]&[2],
[Article].[Buying Domain].[PCG Sub Cat].&[2400]&[6],
[Article].[Buying Domain].[PCG Sub Cat].&[866]&[2],
[Article].[Buying Domain].[PCG Sub Cat].&[273]&[1],
[Article].[Buying Domain].[PCG Sub Cat].&[247]&[1]}
")
)
August 13, 2015 at 9:10 am
Hi again,
Problem solved!
The source of the issue was the set DescUmbArtCat which come from a parameter.
There was another selection with [Articlegroup].[Umbrella Art].&[-1]. That's why the DESCENDATS takes so long.
Filtering the parameter solved the problem.
Thank you all for your support!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply