Help to optimize this query
hi, need sum of volume year, of volumes when month , client between 10 , 100.
10 , 100 dynamic values, can't use ranges in fact table.
i'm using query, takes long.
select non empty( [time].[ano].members) on 0 from ( select filter( nonempty(crossjoin([time].[id data].members, [cliente].[codigo instalacao].members)), [measures].[volume faturado]>10 , [measures].[volume faturado]<100) on 0 from [volume facturado]) where [measures].[volume faturado]
[cliente].[codigo instalacao] has 1.500.000 memberes
[time].[id data] has 250 members
thank you.
hi perdixero,
in scenario, since specify condition fact data in filter() function, think it's better remove nonempty formula engine not detect empty cells before filtering.
select non empty( [time].[ano].members) on 0 from ( select filter( [time].[id data].members * [cliente].[codigo instalacao].members, [measures].[volume faturado]>10 , [measures].[volume faturado]<100) on 0 from [volume facturado]) where [measures].[volume faturado]
for more information query optimization, please refer link below:
https://www.packtpub.com/books/content/query-performance-tuning-microsoft-analysis-services-part-2
https://technet.microsoft.com/en-us/library/cc966527.aspx
simon hou
technet community support
SQL Server > SQL Server Analysis Services
Comments
Post a Comment