Application not loading

[#1]

Hello,

I have an application which has been working until recently. I recently updated a formula in one expression on a combo chart which chokes the whole application.

Some background:

*The application disk size is about 2.5GB

* The largest table has about 150 million records in 3 columns of data.

I needed to get a cumulative count total from the beginning of reporting history. In order to do that I use this formula:

=SUM({< [Year]=,[Month]=,date_week_key ={"$(= '<=' & max(date_week_key))"}>}AGGR(Count({< [Year]=,[Month]=,date_week_key ={"$(= '<=' & max(date_week_key))"}>} application_key),device_key_dist))

I have tried everything

1. Made sure the right data types are being used

2. Made sure to use minimal set of data

3. Create distinct tables on load to limit any extra filtering.

We are running on a super strong server.

Any ideas or suggestions would be much appreciated.

[Anonymous]

First thing i can think of is...

Does this work perfectly well with less data-set...

Check the Calculation time of this object with or without this expression and with different size of data-sets. That should tell you enough to decide on to optimize the expression or build some parts of this calculation in script and then use them in the expression.

[greg-anderson]

With a formula like that, I would be tempted to put it in the script rather than front-end logic.

How many tables do you have in your model?  Is there any complexity in there that could be contributing to a delay?

Also, has it worked in the past?    

[#4]

I would be very reluctant using such complex expressions on a 150mm records table. Did you consider calculating cumulative count in loading script?

[#5]

Thanks for all the comments. It works fine on about 50M records.

I calculate everything I can in the load script - but the count/sum I am trying to do is a product of one table that has 90K records and another table with 150M rows.

I don't see how I can simplify this anymore.

Any ideas?

[greg-anderson]

Unfortunately, I don't have any straightforward ideas here except you need bigger hardware (specifically more RAM on the machine handling the QVW).

Any time you have a product of two tables that puts you into billions of combinations, I would look for alternative ways to structure the data model. 

That or break the 150M table into chunks, if possible.