Basic Aggregate Awareness Question

building reports webintelligence
1

I know this sounds basics but somehow the data is not coming correct, can you please directly pinpoint as to what is happening.
Let me break down my problem into more basic version so its easier to explain, I have a daily sales table, monthly sales table and quartery sales table.

quarterly table has - item, year, quarter, sales
Monthly table has item, year, quarter, month, sales
Daily table has item, year, quarter , month, date, sales

ps:all these table have data only for one year so year doesnt matter.

I have made the quarter table incompatible with month and week, monthly table incompatible with Week – thts it for incompatible objects.

I have created an aggregate variable as
@Aggregate_Aware(sum(Quarter.sales),sum(Month.sales),sum(Week.sales))
pretty direct isnt it !! now starts the problem

when i run the report I get weird data output. I am going wrong in somewhere basic, can you please explain ?

sorrynoparking :us: (BOB member since 2008-06-04)

2

The query has only these objects year, quarter, item , month, week and sales. When I run The inner query generated with these agg objects in the db I get right data but when I run the full query in the db I am getting wrong data , do I make sense ?
I think the mistake is in the way I am joining these tables. I just joined the item_key fields on all these tables. I am pretty sure this is where I have to act but not sure how

sorrynoparking :us: (BOB member since 2008-06-04)

3

Hi,

I think aggregate tables shouls be independent and shouldn’t be join with any other tables. why do you joing these tables? or am i wrong in aggregate concepts?

Thanks,
Raj.

Raj2010 (BOB member since 2009-09-16)

4
  1. From which tables are the dimensions Year, Quarter, Month, Week coming from?
  2. How are these dimension tables joined to your various fact tables?
  3. How have you defined universe contexts?
  4. And finally, what does the generated SQL code look like for the various scenarions: Year & Sales fact, Quarter and Sales fact, Month and Sales fact, Week and Sales fact?

Andreas :de: (BOB member since 2002-06-20)

5

While standalone aggregate tables are fine for illustrative purposes, it is easier and indeed preferable to aggregate on the surrogate keys in the main fact tables. This gives the benefits of data integrity, performance, accuracy and leveraging the star schema structure that is in place and all the pass through benefits to the universe of a single object with no changes required to its definition.

As for the OP, I’d suggest they answer the questions that Andreas has asked and we’ll be able to help further.

6

Thanks for all your responses, let me try to give more information with this response. Its a very straight forward problem actually, simple aggregation tables and a main fact table , let me answer your questions

  1. From which tables are the dimensions Year, Quarter, Month, Week coming from?
    each table has these relevant dimensions required for its aggregation. For ex week table has year qtr month week colmns, month table has year qtr month column, quarter table has year qtr columns and year table has year only column, Ofcourse all tables have the relevant item_key and sales quantity metric column aggregated at the table level
    so yr table has the aggregated one row (yr, item, quantity)
    2011 ,item1, 30

QTR table wil have the rows (yr,qtr,item,qty)
2011,1,item1,10
2011,2,item1,5
2011,3,item1,15

sum all quarter sales (10+5+15)=30 (for one full year)

and so on and so forth the monthly and weekly tables are expanded…

  1. How are these dimension tables joined to your various fact tables?
    there is only one main fact table which is fact_sales, and all these are joined by the item_key , I joined all the above tables to the fact table using this item_key. If I keep all these tables as standalone tables how will the join between one of these tables and the main fact table happen, so I need to join atleast the week table to this fact table, is it not ?

  2. How have you defined universe contexts?
    not yet, I dont think its required, its a simple 1 main fact table (fact_sales) and 1 dim_item table tht has item information and 4 aggregated tables for yr, qtr,mnth,week etc

  3. And finally, what does the generated SQL code look like for the various scenarions: Year & Sales fact, Quarter and Sales fact, Month and Sales fact, Week and Sales fact?

The generated SQL says
(note I am running this for one item and one year only)

Select * from fact_table, (select all columns with proper joins)Week
where
week.item_key=fact_table.item_Key (this join came because I joined the weekly table and fact_sales table)

there is only one aggre_variable object created in the universe (defntion I have given in my earlier post) so when I pull this object into the query it automatically includes the above inner “week” table, presumably because week is the lowest granular agreegation possible

Hope this is enough information, do let me know if you have any more questions.

Additionally, how dose the SQL generated look like in such conditions, is it supposed to be similar to the above ? The lowest granular table is chosen so aggreagation can hapen from there, am I right ?

sorrynoparking :us: (BOB member since 2008-06-04)

7

You must not join your detail fact table with any aggregate/summary fact table! Because otherwise you will get inflated numbers for your facts.
In your case, all your aggregate/summary tables must be free-floating tables in the universe and you must make all time dimensions (Year, Quarter, Month, Day) aggregate aware as well.

My recommendation though is to create a snowflaked dimension table for time: Year --< Quarter --< Month --< Dayand join it appropriately to your various aggregate/summary fact tables. You then will have to use universe contexts. Example:

Tables:
Year
Year_Quarter
Year_Quarter_Month
Year_Quarter_Month_Day
Detail Day_fact table
Summary Quarter_fact table

Joins (including universe contexts c1 and c2):
c1 & c2: Year --< Year_Quarter
c1: Year_Quarter --< Year_Quarter_Month
c1: Year_Quarter_Month --< Year_Quarter_Month_Day

c1: Day_fact table >-- Year_Quarter_Month_Day
c2: Quarter_fact table >-- Year_Quarter

Universe objects:

  • Aggregate aware fact/measure based on Quarter_fact table and Day_fact table
  • Dimension Year from Year table
  • Dimension Quarter from Year_Quarter table
  • Dimension Month from Year_Quarter_Month table
  • Dimension Day form Year_Quarter_Month_Day table

Aggregate navigation:
Set Dimensions: Day as well as Month to be incompatible with aggregate table Quarter_fact table

Andreas :de: (BOB member since 2002-06-20)