Set Analysis Vs. IF()

I still see many people using IF() statements inside expressions.
I have decided to make a simple test with unique values going from 1,000 to 10,000,000 records.
I have created two streight tables. One using IF() and the other one using Set Analysis.

In the first table I used this expression: sum(if(Sales >=10, Sales))
The second table had this other expression: sum({< Sales = {“>=10”} >} Sales)

I then took notes of the calculation time and the memory used. See the charts below showing the results.

Calculation Time

Calculation Time

Memory Usage

Memory Usage

Conclusion

Regarding the memory, in all the cases, both charts will consume nearly the same amount of RAM, This was expected as both charts contained the same data, dimensions and expressions.

However, when we take a look at the calculation time chart, we see how the expression using IF() is always greater than the one using Set Analysis.

When working with small data, there is no problem in using IF() in expressions, but we need to be careful when working with big data. In the latter case, it’s always recommended Set Analysis.

I hope you find this useful.

JV

Advertisements

Tags: , , , , , , , ,

8 responses to “Set Analysis Vs. IF()”

  1. Keyu Wang says :

    Hi JV,

    great post. It helps me a lot.

    I am confused at the point of calculation time chart. What time is there exactly be treated as calculation time? Could you guide me how do I get the calculation time for a certain .qvw file?

    I appreciate your response and thank you in advance.

    Regards,

    KW

    • Business Intelligence Experience says :

      Hi Keyu,

      In this case, Calculation Time refers to the time needed by a Chart to be rendered or “calculated”. Ex: How much time does it take for a straight table/pie chart/ line chart/ etc, to be calculated and shown to the user?

      This doesn’t refer to the time it takes to a .qvw file to load (script execution).

      Does this make it clearer?

      JV

      • Keyu Wang says :

        Hi JV,

        thanks for your fast reply.

        Does that mean that the calculation time refers to the properties of objects which come from the sheets property?

        A further question about that: How do I export the CalcTime and Mem from that table (sheets property -> Objects)?

        Thanks again 🙂

        Best Regards,

        Keyu

      • Business Intelligence Experience says :

        That’s correct Keyu. It’s the value you see under Sheet Properties -> Objects.

        If you want to export the statistics, you need to the to the menu option “Settings” -> Document Properties – >General tab. On the left hand side click the button “Memory Statistics”. This will create a .mem file with all the statistics for your file. You can then open with an text editor and there are some apps out there that will read the file show you the the data in pre defined charts.

        JV

      • Keyu Wang says :

        It works. Thanks for your help. Great! 🙂

  2. Eric Vilarrasa Canadell says :

    Hello, I want to know how to do this kind of test for mysef. I am interested to do comparations between formulas with different amount of data. T

    Thank You

Trackbacks / Pingbacks

  1. Set Analysis Vs. IF() | Andrew Landry - May 30, 2014

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: