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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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
 
Moogle
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
Steve
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
DavSmith
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
Steve
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
Moogle
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
GCohen
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
M Meagher
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
DavSmith
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
newest

http://documentation.informati.../source/topic515.htm

oldest

http://documentation.informati...win533/dbsqlagg3.htm
 
Report This Post
DavSmith
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
DavSmith
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