[SHARING] Aggregate awareness in WF?

March 17, 2011, 05:13 PM

Hi Team,

Like many of you, I'm very familiar with pre-aggregating data for performance reasons. I'll do something like have a summary report pointed at some summary data and then drill-down to a detail report that is pointed at granular, detailed data.

Today I learned that some other BI vendor has a feature called aggregate awareness. It seems that you can define your summary tables in the design tool, and then point your report at the granular data table. If your report request can be satisfied by one of the summary tables, it will automatically use the aggregated table. Very efficient and useful.

Now, before I decide that the grass is greener over there, I wonder if there is anything like this in WF? I've been working with PMF almost exclusively for a couple years, so there could have been some pure WF features smuggled past me when I wasn't looking. If so, it's time for some training for me.

Cheers,

Joey
WF 7.6.11

This message has been edited. Last edited by: Kerry,

March 18, 2011, 10:42 AM

Steve

Joey,

We had a similar discussion with IBI representatives. Our previous reporting tool, Business Objects, allowed aggregate awareness. The IBI recommendation was to leverage database solutions for handling aggregate awareness. When this is possible, all queries (regardless of reporting/sql tool) can benefit from the aggregations. For example, Teradata now allows aggregate join indices (AJI). These allow you to always write your SQL at the detail level table, but the optimizer will automatically determine if any of the AJIs can satisfy your query and, if so, it will use them instead of your detail data.

March 18, 2011, 01:29 PM

DavSmith

I wasn't -->AWARE<--- of "Aggregate Awareness" so I looked it up. The concept is not complicated, but there is some set up that must occur to allow the SQL engine [Teradata, SAP BO, etc) to intelligently choose the correct table.

Here's a definition I found at SAP BUSINESSOBJECTS – Aggregate awareness

quote:

What is aggregate awareness?
Aggregate awareness is a term that describes the ability of a universe to make use of aggregate tables in a database. These are tables that contain pre-calculated data. You can use a function called @Aggregate_Aware in the Select statement for an object that directs a query to be run against aggregate tables rather than a table containing non aggregated data.

Using aggregate tables speeds up the execution of queries, improving the performance of SQL transactions.

The reliability and usefulness of aggregate awareness in a universe depends on the accuracy of the aggregate tables. They must be refreshed at the same time as all fact tables.

A universe that has one or more objects with alternative definitions based on aggregate tables is said to be “aggregate aware”. These definitions correspond to levels of aggregation. For example, an object called Profit can be aggregated by month, by quarter, or by year. These objects are called aggregate objects.

Queries built from a universe using aggregate objects return information aggregated to the appropriate level at optimal speed.

Seems it's a nice feature to have by reducing programming by a few Dialogue Manager lines by having the database automatically choose which table to use based on the level aggregation selected for the report. While I don't see a huge benefit for developers maybe there is one for end users using the GUI tools.

March 18, 2011, 02:27 PM

Steve

InfoAssist was our primary concern. Obviously, developers could code for the appropriate aggregation level.

Steve

March 18, 2011, 02:36 PM

Moogle

I am developing a POC to test Oracle's support of this concept. I've got a large table with a lot of history and I'm going to make a materialized view of the most current month of data. With any luck, Oracle will report off the small table anytime I query the current month, even though the report is pointed at the big table. If this holds true, we'll grow this out to have most recent quarter, most recent year, maybe last full quarter, last full year. At that point, we can make fun of BO users; "you have to define your AA? shouldn't that be the database's job?" Here's hoping.

Cheers,

Joey

March 18, 2011, 02:54 PM

GCohen

Aggregate awareness is built into WebFOCUS and is automatically used if present.
The command is:
SQL suffix SET AGGREGAE_AWARENESS { FRESH_ONLY | OLD_OK | OFF }

Where suffix can be DB2 or ORACLE or TERADATA.

March 20, 2011, 04:43 AM

<JG>

quote:
AGGREGATE_AWARENESS

It's been part of WF since 5.3.
In addition to DB2, Oracle and Teradata using this specic SET feature,
systems like BW also implement this capability without you having to do anything.
If they exist they are used automatically.

March 21, 2011, 02:55 PM

M Meagher

I found these comments on aggregate awareness for SQL Server:

http://social.msdn.microsoft.c...9-82d9-8d8b5a7996ef/

- which points to:
http://technet.microsoft.com/e...ibrary/cc917715.aspx

March 21, 2011, 04:05 PM

DavSmith

quote:
SQL suffix SET AGGREGATE_AWARENESS { FRESH_ONLY | OLD_OK | OFF }

Since 5.3? Wow. What do the toggles FRESH_ONLY and OLD_OK do? Is there any documentaion on this commmand? I can't find anything on this site.

March 21, 2011, 04:38 PM

DavSmith

JG, I obviously didn't search hard enough!

Thanks

March 21, 2011, 05:00 PM

<JG>

They are not forum docs, they are IBI documentation site docs.

The searches are very different.

Forum can see Forum for search.

A tech support search can see forum, documentation and other public info.

Kerry, Please corrcet me if that is not correct.

March 21, 2011, 05:14 PM

DavSmith

I keep forgetting to run searches on them all. I did get results using the ECL search and the tech doc search. Why we get different results, I don't know.

Thanks again.