BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Lead By Knowing (Opens a new window)  

General Notice: No events within the next 45 days.

Aggregate Awareness


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
matrixonthemove2
Senior Member
Senior Member



Joined: 08 Sep 2006

Posts: 50



PostPosted: Mon Sep 25, 2006 7:39 am 
Post subject: Aggregate Awareness

Hi How and when to use Aggregate awareness in BO?? Please explain me.. anyone! icon_neutral.gif
Back to top
jobjoris
Forum Enthusiast
Forum Enthusiast



Joined: 30 Jun 2004

Posts: 1247
Location: Holland


flag
PostPosted: Mon Sep 25, 2006 8:18 am 
Post subject: Re: Aggregate Awareness

Did you check the Documentation provided by Business Objects? It's perfectly explained in the designer.pdf, page 389.
Back to top
matrixonthemove2
Senior Member
Senior Member



Joined: 08 Sep 2006

Posts: 50



PostPosted: Mon Sep 25, 2006 8:28 am 
Post subject: Re: Aggregate Awareness

Sorry .. i got it.. infact all my answers are available in designer.pdf. well it was not with me , i had to download from businessobject site.
But thanks for the information.
Bedankt
Tot Ziens
Back to top
sujay
Forum Member
Forum Member



Joined: 15 Oct 2004

Posts: 9



PostPosted: Mon Sep 25, 2006 8:33 am 
Post subject: Re: Aggregate Awareness

Aggregate awareness can be used in following situations
1) you have summarized tables and detail tables and you want BO to pick up right table based on what kind of object you have pulled in your query.It helps to associate right objects automatically and hence enhance your BO report performance
2) Also, can be used if someone wats to setup complex logic to pickup certain tables only, when picking certain columns from one table.This can be done be setting up incompatible objects in aggregate awareness panel.

Thanks,
Sujay
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21970
Location: Dallas, Texas


flag
PostPosted: Mon Sep 25, 2006 1:21 pm 
Post subject: Re: Aggregate Awareness

Seems to be resolved, but moving to the Semantic Layer (Designer) forum in case anyone wants to add anything further... thanks.
_________________
Dave's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2014-08-22 Yes, Virginia, You Can Refresh One Data Provider At A Time
• 2014-07-08 Airlines Could Save Millions in Fuel Costs By Providing Everyone An iPad
• 2014-06-24 Did Florida State Win National Championship By Using Big Data?
Back to top
miganos
Senior Member
Senior Member



Joined: 06 Feb 2004

Posts: 47
Location: Latvia- Riga


flag
PostPosted: Wed Oct 04, 2006 9:23 am 
Post subject: Re: Aggregate Awareness

question on this agg_aware.
If i have detail fact table Fact_detail, that references, person dimension People and person has gender.

Then I want to build sumarized table, for example, Fact_sum(agg_gender_count).

There's the 3rd fact table Fact_detail_2, that countains some amounts and references person dimension.

Objects:
dimensions:
"People gender" as Agg_aware(Fact_sum.gender, People.gender)

measures:
"People count" as agg_aware(sum(Fact_sum.ppl_count), count(People.ID))
"Some amount" as agg_aware(sum(Fact_detail_2.amount))

If I select "People gender", "People count", then it takes all from Fact_sum.
If I add to this select object "Some amount", it makes 2 SQL's (because of two contexts) and SQL from Fact_sum doesn.t contain anymore sum(Fact_sum.ppl_count) expression -> gets synchronisation.

Something like:
SQL1:
SELECT
People.gender,
sum(Fact_detail_2.amount)
FROM
people,
Fact_detail_2
where people.id = Fact_detail_2.people_fk
group by People.gender

SQL2:
SELECT
sum(Fact_sum.ppl_count)
FROM
Fact_sum


I expected SQL2 as:
SELECT
Fact_sum.gender,
sum(Fact_sum.ppl_count)
FROM
Fact_sum
group by Fact_sum.gender


Something wrong or BO works correctly there ?

thank you

_________________
Migano

BO 5.1.6, BO 6.5.1 & SybaseIQ
Back to top
Anita Craig
Forum Groupie
Forum Groupie



Joined: 17 Jun 2002

Posts: 8541
Location: Palo Alto, California, U.S.A.


flag
PostPosted: Wed Oct 04, 2006 3:36 pm 
Post subject: Re: Aggregate Awareness

No, if you've defined your objects properly, set your contexts properly, and set the Tools --> Aggregate Navigation -- incompatible objects correctly, you shouldn't get syncrhonized queries.

I don't have enough time to print out and parse the details you've provided here -- that is, if you've provided enough details.

The idea is that if you want to use three different fact tables, at different levels of aggregation, than a single object needs to be defined with the corresponding column from each of the fact tables, from most-aggregated to least-aggregated.

Then you have to set your aggregate navigation incompatibilities correctly, so that if you use an object from a more-detailed fact table that's not in a less-detailed fact table, then the aggregate-aware object will choose the less-aggregated table.

_________________
Anita Craig Image link
Institutional Research & Decision Support
Stanford University Image link
____________________
Search is Your Friendô
Back to top
miganos
Senior Member
Senior Member



Joined: 06 Feb 2004

Posts: 47
Location: Latvia- Riga


flag
PostPosted: Thu Oct 05, 2006 9:47 am 
Post subject: Re: Aggregate Awareness

Ok, I'll try once more icon_smile.gif
The problem is if define dimensions as @Aggregate_Aware(as column in summary table, as dimension for facts detail table) and this dimension is used for several type of facts.
Detect incompatibility works fine for symmary fact tables, but if I use agg_aware for dimensions, it's even complicated to set incompatibilities manualy icon_sad.gif


As I understood (see universe attached, there got to work as have to), I have to define incompatibility for some symmary table with measure from summary tables that stores some dimension table column.

For example, D_people.gender have to be taken from D_people for detailed facts, but from "some_summary_table'.gender for summary facts.

This is just sample universe where tried agg_aware on dimensions and objects. The real universe is so complicated that not sure i'll be able to implement agg_aware on such dimensions (more correctly, dimension attributes).

Maybe I'm trying to develop impossible things ?

_________________
Migano

BO 5.1.6, BO 6.5.1 & SybaseIQ
Back to top
Andreas
Forum Advocate
Forum Advocate



Joined: 20 Jun 2002

medal_silver.gif*2medal_gold.gif
Posts: 16508
Location: Dreaming of Africa..


flag
PostPosted: Thu Oct 05, 2006 10:09 am 
Post subject: Re: Aggregate Awareness

A quick shot:
You will need a conformed dimension table (for gender only), which can be joined to your summary table (directly) as well as to to the detail fact table (via the employee table). In other words, snowflake your dimension tables. This will save you a lot of headaches.

icon_arrow.gif Then you will get joined SQL statements in Business Objects Reporter just as required (instead of synchronized ones) yesnod.gif

You can try this quickly using a derived table (for the gender), which only has 2 rows (male/female).

_________________
Follow me on Twitter
...Reading: The Silence of the Lambs by Thomas Harris..

Focusing on Data Visualization, SAP BI 4.0, SAP connectivity, Data Modeling, and SAP HANA Certified Associate
Back to top
Blacksmith
Senior Member
Senior Member



Joined: 12 Apr 2005

Posts: 47
Location: Russia


flag
PostPosted: Thu Oct 05, 2006 10:23 am 
Post subject: Re: Aggregate Awareness

Try the following:

1) Create a self-restricted join:
Fact_amt_agg_2.id=Fact_amt_agg_2.id
2) Create new context on the join created

3) Detect incompatibilies in the Aggregate Navigation window.

Get a result

Rgrds, Blacksmith
Back to top
aposullivan
Principal Member
Principal Member



Joined: 22 Jul 2004
ASUG Icon
Posts: 176
Location: Australia


flag
PostPosted: Thu Oct 05, 2006 6:34 pm 
Post subject: Re: Aggregate Awareness

Is it possible to add something in the WHERE clause of an aggregate awareness measure?

where start_date between date1 and date2

or do you need to create a filter for this?
Back to top
miganos
Senior Member
Senior Member



Joined: 06 Feb 2004

Posts: 47
Location: Latvia- Riga


flag
PostPosted: Fri Oct 06, 2006 12:52 am 
Post subject: Re: Aggregate Awareness

Andreas wrote:
A quick shot:
You will need a conformed dimension table (for gender only), which can be joined to your summary table (directly) as well as to to the detail fact table (via the employee table). In other words, snowflake your dimension tables. This will save you a lot of headaches.

icon_arrow.gif Then you will get joined SQL statements in Business Objects Reporter just as required (instead of synchronized ones) yesnod.gif

You can try this quickly using a derived table (for the gender), which only has 2 rows (male/female).


Yes, I know that this is solution - to break D_PEOPLE and the other dimension in snowflake. But I tried to make this like eFashion did.
For simple universe - it's ok, but complicate to define if you select type_1 facts (agg_aware selects D_PEOPLE.GENDER because of selected dimension in data provider) and type_2 facts (agg_aware selects some_summary_table.GENDER because of selected dimension in data provider).
So, you have to define in the way to get GENDER from dimension in one SQL and from some_summary_table.GENDER in the 2nd SQL.

To snowflake dimensions ? I think it will slow down detail facts and a lot to do in transformations (we have a history data) and it will slow down summary_facts too.

At the end, question if it is impossible to realize idea as I implemented in attached universe ?! icon_rolleyes.gif

_________________
Migano

BO 5.1.6, BO 6.5.1 & SybaseIQ
Back to top
miganos
Senior Member
Senior Member



Joined: 06 Feb 2004

Posts: 47
Location: Latvia- Riga


flag
PostPosted: Fri Oct 06, 2006 3:24 am 
Post subject: Re: Aggregate Awareness

Blacksmith wrote:
Try the following:

1) Create a self-restricted join:
Fact_amt_agg_2.id=Fact_amt_agg_2.id
2) Create new context on the join created

3) Detect incompatibilies in the Aggregate Navigation window.

Get a result

Rgrds, Blacksmith


Tried this ... doesnt work for me icon_sad.gif

_________________
Migano

BO 5.1.6, BO 6.5.1 & SybaseIQ
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0657 seconds using 17 queries. (SQL 0.0201 Parse 0.0347 Other 0.0108)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo