{= include("docs.util"); start_docs_page(docs.technical_manual.page_titles.newsletters); =}

Sawmill Newsletter

June 15, 2009

Welcome to the Sawmill Newsletter!

You’re receiving this newsletter because during the downloading or purchase of Sawmill, you checked the box to join our mailing list. If you wish to be removed from this list, please send an email, with the subject line of “UNSUBSCRIBE” to newsletter@sawmill.net .

News

Sawmill 8.0.8 shipped on May 20, 2009. This is a minor "bug fix" release, and it is free to existing Sawmill 8 users. It is recommended for anyone who is experiencing problems with Sawmill 8.0.7 or earlier. You can download it from http://sawmill.net/download.html .

Sawmill 7 users can upgrade to Sawmill 8 for half of the license price; or if you have Premium Support, the upgrade is free. Major features of Sawmill 8 include support for Oracle and Microsoft SQL Server databases, real-time reporting, a completely redesigned web interface, better multi-processor and multi-core support, and role-based authentication control.

This issue of the Sawmill Newsletter describes the use of cross-reference tables to increase the speed of custom reports.

Get The Most Out Of Sawmill With Professional Services

Looking to get more out of your statistics from Sawmill? Running short on time, but need the information now to make critical business decisions? Our Professional Service Experts are available for just this situation and many others. We will assist in the initial installation of Sawmill using best practices; work with you to integrate and configure Sawmill to generate reports in the shortest possible time. We will tailor Sawmill to your environment, create a customized solution, be sensitive to your requirements and stay focused on what your business needs are. We will show you areas of Sawmill you may not even be aware of, demonstrating these methods will provide you with many streamlined methods to get you the information more quickly. Often you'll find that Sawmill's deep analysis can even provide you with information you've been after but never knew how to reach, or possibly never realized was readily available in reports. Sawmill is an extremely powerful tool for your business, and most users only exercise a fraction of this power. That's where our experts really can make the difference. Our Sawmill experts have many years of experience with Sawmill and with a large cross section of devices and business sectors. Our promise is to very quickly come up with a cost effective solution that fits your business, and greatly expand your ROI with only a few hours of fee based Sawmill Professional Services. For more information, a quote, or to speak directly with a Professional services expert contact consulting@flowerfire.com.

Tips & Techniques: Using Cross-Reference Tables

Cross-reference tables (sometimes called cross-reference groups, or xrefs), are tables created by Sawmill in its back-end database. Cross-reference tables are generated during database builds and updates, and contain aggregated information from the main table of the database. For instance, a particular cross-reference table might contain one row for each day in a media server log dataset, with the number of accesses, play duration, bytes transferred, unique IPs, sessions, etc. for that day. When Sawmill generates an unfiltered Days report, it can generate it directly from this table, which is much smaller than the main table of the database; this allows it to generate this report, and other top-level reports, very quickly.

Cross-reference tables are created by default for each non-aggregating field in the database, which means there is roughly one cross-reference table for each report, so all top-level reports are boosted by cross-reference tables. Furthermore, every default cross-reference table also contains the date/time field, so any report can be filtered by date, and still use a cross-reference table. So all default unfiltered reports, and all default date-filtered reports, are accelerated using cross-reference tables.

If you add a new database field, however, or create a new report, or if you often use a particular combination of filters on a particular report, you may need to modify the cross-reference tables to ensure that this custom or filtered report is also fast. This newsletter gives an example of this.

Building The Report

We'll start with a spam filtering server dataset. Clicking Senders shows the top sender domains in the data:

Senders Report

If we also want to know, in a single report, the top Recipients for each Sender, we can use a Pivot Table, by clicking Customize, then the Pivot Table tab, then "Show pivot table", and then selecting Recipients as the drill-down:

Customize Report Element: Add A Pivot

Clicking OK begins to generate this report. But it takes several minutes, and while it's thinking, it displays this progress bar:

Progress Of Report Using Main Table

The key phrase to watch for is "Querying main table." The main table is the primary table of the database, with one row for each, even in the log data. It can be millions or billions of lines long, depending on the dataset. Querying the main table can take many minutes, especially for an unfiltered report (where every single row must be aggregated to create the report), so it is to be avoided for any reports you use frequently. That's what xref tables are for, so let's cancel this report and head to Config to make it faster. In Config, click More Options, then Cross Reference Groups, and duplicate the existing Recipient group, calling it "Sender x Recipient", and add the Sender field to it:

Adding A Cross-Reference Group

After adding a cross-reference group, we need to rebuild the database. After the build is complete, we can return to the reports, and again do the Recipients x Senders pivot table report. This time, the report comes up quickly:

The Final Report

That's it--from now on, that report will be fast. As an added benefit, this also speeds up Recipient reports filtered on a particular Sender, or Sender reports filtered on a particular Recipient.

Which Fields Do You Need?

In order to get a particular report to use an xref group, you need all the fields in the report, plus all the fields in the filters. So if you have a report with three columns: Sender, Messages, and Bytes; and if you also have two filters on that report, a Date/time (date range) filter and a Recipient filter, you will need all those fields in the xref table: Sender, Recipient, Date/time, Messages, and Bytes.

Professional Services

This newsletter describes optimizing reports with cross-reference tables. This is one example of many types of optimization which are possible to make Sawmill build databases faster, and generate reports faster. Other possibilities include selective indexing, log splitting, field simplification, horizontal shrinkage (eliminating fields), vertical shrinkage (eliminating rows), and hierarchical cross-reference optimization. For large environments where performance is important, we recommend Sawmill Professional Services to help you quickly optimize your installation. If you need assistance with optimization, or with any other Sawmill tasks, our Sawmill Experts can help. Contact sales@sawmill.net for more information.

[Article revision v1.0]
[ClientID: 43726]

{= end_docs_page() =}