Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

New TIBCO Community Coming Soon
In early summer, TIBCO plans to launch a new community—with a new user experience, enhanced search, and expanded capabilities for member engagement with answers and discussions! In advance of that, the current myibi community will be retired on April 30. We will continue to provide updates here on both the retirement of myibi and the new community launch.

What You Need to Know about Our New Community
We value the wealth of knowledge and engagement shared by community members and hope the new community will continue cultivating networking, knowledge sharing, and discussion.

During the transition period, from April 20th until the new community is launched this summer, myibi users should access the TIBCO WebFOCUS page to engage.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SHARING] Aggregate awareness in WF?

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SHARING] Aggregate awareness in WF?
 Login/Join
 
Guru
posted
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,
 
Posts: 318 | Location: Los Angeles, CA | Registered: November 15, 2005Report This Post
Member
posted Hide Post
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.


WebFOCUS 7.7.02 Teradata, Oracle
Windows
Outputs: HTML / PDF / Excel
 
Posts: 13 | Location: Michigan | Registered: December 09, 2008Report This Post
Platinum Member
posted Hide Post
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.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
Member
posted Hide Post
InfoAssist was our primary concern. Obviously, developers could code for the appropriate aggregation level.

Steve


WebFOCUS 7.7.02 Teradata, Oracle
Windows
Outputs: HTML / PDF / Excel
 
Posts: 13 | Location: Michigan | Registered: December 09, 2008Report This Post
Guru
posted Hide Post
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
 
Posts: 318 | Location: Los Angeles, CA | Registered: November 15, 2005Report This Post
Platinum Member
posted Hide Post
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.


Release 7.6.9
Windows
HTML
 
Posts: 226 | Registered: June 08, 2003Report This Post
<JG>
posted
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.
 
Report This Post
Gold member
posted Hide Post
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
 
Posts: 60 | Location: Ellensburg Washington | Registered: May 22, 2009Report This Post
Platinum Member
posted Hide Post
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.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
<JG>
posted
 
Report This Post
Platinum Member
posted Hide Post
JG, I obviously didn't search hard enough!

Thanks



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
<JG>
posted
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.
 
Report This Post
Platinum Member
posted Hide Post
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.



In FOCUS since 1985 - WF 8.009/8.104 Win 8 Outputs: ALL of 'em! Adapters: Sql Server Teradata Oracle
 
Posts: 161 | Location: Dallas, TX | Registered: February 20, 2009Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SHARING] Aggregate awareness in WF?

Copyright © 1996-2020 Information Builders