Avoiding Filter function

Question

Hi All,

I need to create a calculated member that finds the number of 'active' customers, which is the number of customers who had a sales > 0 excluding certain transaction types. I have created the following calculated measure but it is taking 10 minutes to get the results of this measure!

Here is the 'Active' measure calculation:

Count(Filter(Descendants([Member].[Member Id]),[Measures].[NetSales]>0))

Is there a more efficient way to calculate this? I did not yet exclude the transaction type, any help with that too is appreciated.

Thanks in advance.

Answer 1

Hi tsoukieh,

You can replace the filter function with iif and sum functions which will  be block computation rather than cell-by-cell computation with count and filter function.

Sum(Descendants([Member].[Member Id]),IIF([Measures].[NetSales]>0,1,NULL))

Follow the link for more info :

http://sqlblog.com/blogs/mosha/archive/2007/11/22/optimizing-count-filter-expressions-in-mdx.aspx

---

Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

Dont forget to mark it as Answered if found useful | myspeakonbi

Answer 2

Thanks Rakesh. I tried it, it is a little faster but performance is still slow taking minutes to show up. Above that, I still need to exclude certain transaction types.

Any other ideas? Thanks again.

Answer 3

 

Hi there

I think please first create the calculated member as null and then put a scope statement to calculate the desired value. I found this fast as compared to creating calculated member directly

I hope this will help

Many thanks

Syed Qazafi Anjum

Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

Answer 4

Hi,

• Create a set of which would hld members you wish to exclude
• Use a combination of EXCEPT() and FILTER()

Also, how are you benefitting by using the DESCENDANTDS() function?

With

Set ExludeMe AS {<<MDX_Expression>>}

Set IncludeMe AS {EXCEPT(FILTER(Member.Member.MemberID, Sales > 0),ExcludeMe)}

---

Please vote as helpful or mark as answer, if it helps
Cheers, Raunak | t: @raunakjhawar | My Blog

Answer 5

Can you give us the profiler screen shots. It might help to debug few things ?

Aslo you can use nonempty :

Sum(Nonempty(Descendants([Member].[Member Id]),[Measures].[NetSales]),IIF([Measures].[NetSales]>0,1,NULL))

---

Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

Dont forget to mark it as Answered if found useful | myspeakonbi

Answer 6

Thanks Rakesh, I tried nonempty and it ran just a little better but it is still largerly slow. I changed the measure inside NonEmpty function to TransCount and it got a little better but it is till very slow. Here is what I mean:

Sum(Nonempty(Descendants([Member].[Member Id]),[Measures].[TransCount]),IIF([Measures].[NetSales]>0,1,NULL))

Profiler trace is showing a lot of output, what events do you need from the profiler?

---

Answer 7

* Query Sub cube verbose

* Query begin

* Query end

* Progress begin

* Progress end

Also, how many members you have and what is the size of fact data(Partitions )?

---

Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

Dont forget to mark it as Answered if found useful | myspeakonbi

Answer 8

Please find attached the trace file. I have 9,087,635 members and the the fact table has only a few months worth of transactions which is around 40 million records. By the way the server I am running this on is super fast.

Answer 9

I see that Dimension data is cached and it taking 3 secs and overall Query 262 secs (4.5 min !!! ) .

Can you kindly clear the dimension cache and give us the results

* Also include the serializable events and progress start and end ,Nonempty in your profiler trace.

* Also check for pre-fetching as descendents will get more data than required

http://mdxdax.blogspot.in/2011/11/avoid-pitfalls-of-fact-data-prefetching.html

Code to clear the cache.

    <Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
      <ClearCache>
        <Object>
          <DatabaseID>[YourDatabaseID]</DatabaseID>  
        </Object>
      </ClearCache>
    </Batch>
    GO 

---

Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

Dont forget to mark it as Answered if found useful | myspeakonbi

Answer 10

I have cleared the cache, and ran the trace for the query with what you requested.

Answer 11

One more thing, I am getting the same count when I view that measure against the [Member] hierarchy, that maybe contributing to the slow response. How can I change the calculation to show actual count?

Answer 12

The descendants function will bring the data which is not requested by the formula engine which is pre-fetch. Kindly disable the pre-fetch and try your Query or remove descendents. Also,In the TextData is not visible.

---

Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

Dont forget to mark it as Answered if found useful | myspeakonbi

Answer 13

Rather computing on all you members ([Member].[Member Id]). You can do something like this to perform better.

WITH MEMBER Customer AS 
IIF([Measures].[Internet Order Count] > 2,[Measures].[Internet Order Count], NULL) 
Select {Measures.Customer, [Measures].[Internet Order Count]} ON COLUMNS, 
NONEMPTY([Customer].[Customer].[Customer],Measures.Customer) ON ROWS
FROM [Adventure Works]

Answer 14

How do I disable pre-fetch? I am not using a connection string, I am creating this as a calculated member in BIDS.

Answer 15

Hi Thameem:  If I do not put ([Member].[Member Id]) and use your code, it is showing a count of 1.

Answer 16

OR Remove descendants from your Query and check for the Trace.

[Member].[Member Id].[Member Id]

---

Rakesh M J | MCTS,MCITP ( SQL SERVER 2008 )

Dont forget to mark it as Answered if found useful | myspeakonbi

Answer 17

I tried [Member].[Member Id].[Member Id]and it is still giving me the same count for all member groups with a slow response time. I highly appreciate your patience Rakesh.