Performance with IOK file

Jasper · June 22, 2011 11:38AM

Hi,

I have an IOK file which contains only 500K records in 70 columns, but it does not seem to perform (click on a filter and it takes 8 seconds to change the view :-s). I'm using a high performance machine (Quadcore, solidstate HD, 4GB ram) but I do run 32bit OS. This will limit my ram availability, but i need it to work also for simple users with simple desktops so this shouldn't be an issue.

To solve this issue, I got the following remarks:

"To optimise your Report file for 32-bit machines, we need to put the existing columns into 4 categories:

A. Columns we really don’t need to send into the 32-bit client world, that can be held only in the Source/Analysis IOK file on the server that in turn refreshes the Report IOK
B. Columns whose information can be summarised via formulae in the Source IOK file, reducing the total columns in the Report IOK file
C. Columns which can be summarised using the Summarise columns transformation to put multiple values into one cell prior to transfer to the Report IOK file as a string of human-friendly text
D. Columns which can be collapsed into fewer columns using Collapse columns before refreshing to the Report IOK file.

In general, values required for filtering need to be maintained in a column format that filters, whereas other columns values can be combined and tokenised such that they are still searchable, but not necessarily filterable row by row..
Once we have categorised all the columns and cloned this file into Server IOK (refresh from source(s) with all columns and heavy calculations) and a child Report IOK (fewer total columns) we can look at further steps to reduce the max RAM footprint and improve the tab-to-tab performance of this Report IOK file."

I did remove some unused columns (A.), but without performance increase. The other options are not usable for me because I can't aggregate/summarize the data on a higher level, nor can I combine columns because they are used seperately in metrics, grids/graphs, filters etc.

I would like to get into contact with someone who can help me with this.

Looking forward to your response.

tjbate · June 22, 2011 1:12PM

Jasper - the next step in optimising your file is to try to reduce the number of formulae that are being evaluated inside the version of the IOK file you are actually putting out on client desktops, i.e. the Report IOK file. Multi-file solutions enable you to clone your file with all the formulae, and use it as a Source IOK file, with the formulae active and recalculating. Another copy of the file becomes the Report IOK file, which has the Source IOK file as its only source, and which by default will import all the formulae as static values, greatly reducing the load on the clients' machines and improving tab-to-tab performance. Can you try this?

Jasper · June 23, 2011 4:00AM

I understand your approach...downside is that when i make the formulas static (by putting them into the source IOK file), omniscope will no longer be able to recalculate the values based on my dynamic filtering in the reporting file, right?

E.G: I have a formula 'cost to date' which is based on brand, periode and category to determine the cost to date. These 3 filters can be adjusted by the user, meaning that omniscope will need to recalculate the actual cost to date 'on the fly'...how should I put these kind of formulas within the Source IOK?

Thanks for your reply

Louise_Peers · June 23, 2011 4:12AM

Hi Thomas,

The problem with that solution is, as Jasper has stated, we are using the formula in the dashboards to recalculate on the fly, this is a feature which we really find valuable in the tool. We have done all the 'preprocessing' in iok files and used the output from those as the single input into this file, so we've done what we can there.

Are there any other tips for optimising performance we can use? Do you have any developments in the pipeline which might help?

Many thanks
Lou

tjbate · June 23, 2011 4:36AM

Louise - we have identified a number of major performance improvements, both in terms of how many rows/columns will fit in the 32-bit OS-imposed limit on addressable RAM, as well as a known hit list of performance improvements in transformations (multi-field aggregation, etc.). Unfortunately, at this stage, we cannot do these low-level improvements in version 2.6 beta, but will do them as soon as we can in version 2.7.

For now, give us a (scrambled) version of this file and we will determine which operation (s) are bottlenecks in terms of both peak RAM memory usage and tab-switching speed. We will then do what we can in terms of file configuration tricks and also any low-risk changes we can make to version 2.6 beta relating to tuning this particular file.

Jasper · June 23, 2011 5:04AM

Hi Thomas,

Is there a planning for the release of 2.7?

tjbate · June 24, 2011 9:39AM

Jasper - OK the dialog authentication relates to accessing the web images refered to in the Tile Views on the top of each tab. Embedding the images will make this file more portable. We have closed these Tile Views to be able to work with the file.

tjbate · June 24, 2011 9:47AM

Jasper - You have a lot of remote images being authenticated and downloaded on each tab! This will be the slowest process on tab opening and when moving tab-to-tab. Please embed the images in the file, and this will doubtless improve tab-to-tab performance.

Jasper · June 24, 2011 10:10AM

Ok, i will do that. Can you also look at the performance within a single tab? take the 'local view overview' tab:

Enable ALL 'Customer divisiongroup' elements. When you than disable 1 customer divisiongroup (uncheck it), it will take omniscope approx 8 seconds to refresh the dashboard grids/graphs (which is very slow).

Can you let me know if we can improve this?

tjbate · June 29, 2011 5:37AM

Jasper - Once we have understood what the binding constraint on the in-tab performance is on the one tab in your file, we can tell you what, if anything, in 2.7 relates to it...right now, we don't know what the binding constraint on the filtering performance on that tab is, other than it has an awful lot of views, each with a lot of different things all going on at once. It is possible that re-configuring the file tab is the answer, not waiting for 2.7.

tjbate · July 4, 2011 7:10AM

Jasper - Two of your named queries, “Online Advertising” and “Billingcube” point at the exact same subset of 268 records, yet within the same Overview tab some views are pointing at one queried data subset, while others are pointed at the other…this is a total duplication of the queries and query evaluation time…and maybe doubling the processing time on this tab to evaluate both subsets rather than just one before displaying all views.

Even with duplicated queries running, this tab is not at all slow on my fast quad-core I-7 machine running 32-bit Omniscope, so processing seems to be the bottleneck, not RAM. Try changing all the views on any given tab to point at the exact same (minimum number) of pre-defined named query subsets.

In multi-file solutions, one or more 'upstream' time-slicing and server-side 'datamart' IOK files can include row classification formula that create new compound 'row classification' fields that anticipate named queries needed in Report IOK files. Passing these new classification fields as static values to the Report IOK files means that most Named Queries and Sidbear filtering can involve only one or two fields, and therefore evaluate faster on the fly within and between tabs.

The same technique can also be applied to aggregation. Pre-aggregating where possible in upstream timeslicing and 'datamart' IOK files often enables a reduction in row count and more importantly, a reduction in relatively slower multi-field aggregations that need to be performed on-the-fly within and between tabs.

Welcome!

Categories

Ideas Parade