Hi!
I have slow report with many calculated measures like
total ([measure] within set filter(...), filter(...) ,filter(...))
If I split this query for two and run them in parralel (Execution Method - Concurrent) it works faster. In this way i will split crosstab 1 crosstab for 1 measure in 1 query and it will work faster?
Quote from: en1 on 29 Sep 2015 02:55:17 AM
Hi!
I have slow report with many calculated measures like
total ([measure] within set filter(...), filter(...) ,filter(...))
If I split this query for two and run them in parralel (Execution Method - Concurrent) it works faster. In this way i will split crosstab 1 crosstab for 1 measure in 1 query and it will work faster?
The big performance hit here is in the filter() expressions you are using. What is the requirement and why did you use this approach? If I was you I'd be looking for a more efficient way of performing the calculated expressions...
MF.
report with crosstab that have strict form (many rows in header) and each column calculated with different filter expressions. i try use many queries and union but in this approach were some side effects and less performance.
Quote from: en1 on 29 Sep 2015 03:46:53 AM
report with crosstab that have strict form (many rows in header) and each column calculated with different filter expressions. i try use many queries and union but in this approach were some side effects and less performance.
Why are you using so many filter() functions? What is the requirement? Is there a reason why can't you can't select the desired members from the package tree?
MF.
date conditions, members selected on prompt page from hierarchy , filter members by atribute etc.
Quote from: en1 on 29 Sep 2015 04:14:51 AM
date conditions, members selected on prompt page from hierarchy , filter members by atribute etc.
You don't need a filter function to derive members selected on a prompt page from a hierarchy. You can simply use [Your hierarchy] -> ?Your parameter?
What does "Date conditions" mean?
MF.
measure1
date 1 between start_date and end_date
date 2 between start_date and end_date + 1 year
department in (departmen1,department2)
measure2
date 1 between start_date and end_date
date 2 between start_date and end_date + 2 year
department in (departmen1)
....
start_date and end_date ,
department1,department2 user select on promt page (tree_list)
Quote from: en1 on 29 Sep 2015 04:39:26 AM
measure1
date 1 between start_date and end_date
date 2 between start_date and end_date + 1 year
department in (departmen1,department2)
measure2
date 1 between start_date and end_date
date 2 between start_date and end_date + 2 year
department in (departmen1)
....
start_date and end_date ,
department1,department2 user select on promt page (tree_list)
Assuming start date and end date are members, why would you need a filter() function?
user can select different period from hierarchy, start_date and end_date calculated from period and use in calculations (diffrent date hierarchies) in some calculations uses for exaple end_date - year
Quote from: en1 on 29 Sep 2015 05:04:10 AM
user can select different period from hierarchy, start_date and end_date calculated from period and use in calculations (diffrent date hierarchies) in some calculations uses for exaple end_date - year
It's entirely possible you have over-complicated this, but we can't say for sure based on the sketchy details you have provided so far. I'd be looking for ways to simplify the approach if I was you. Replacing the filter() functions with more efficient alternatives would be my first objective.
MF.