This discussion is archived
3 Replies Latest reply: Dec 26, 2012 12:37 PM by Srini VEERAVALLI

# Aggregate layer mapping

Currently Being Moderated
All,

I need help ,

1 have an aggregate table with columns (TotalUnits,ManUnits) with week grain and derived table (TotalUnits,ManUnits) with day grain
I am mapping to fact table having columns
TotalUnits : mapped to LTS derived and aggregate columns directly
AutoUnits : Calculation (TotalUnits-ManUnits) at present using only derived table but in dilema if we could add AGG
ManUnits :mapped to LTS derived and aggregate columns directly

Q1 :- how do i map the aggregate table to Autounits- Calculation (TotalUnits-ManUnits) ?
--adding the calc in the LTS aggregate and setting the content level to week !!!!

Q2 when i map the columns directly for Total and Man for both aggregate and derived i get aggregate as source in the Derived From box in the Logical column properties
is this normal to see the aggregate source as the primary source .
• ###### 1. Re: Aggregate layer mapping
Currently Being Moderated
Make sure you got 2 LTS for logica fact table
make sure you have both fact and aggregate table mappings for measures TotalUnits,ManUnits
to confirm the same when you double click on any of the measure you should see mapping with fact and agg table.

For AutoUnits: Two ways to go with:
Create measure AutoUnits with expression as TotalUnits-ManUnits and mapping with fact and other one is with Aggtable.

other way is just create another logical measure using existing column in logical fact

When you use these measures with date; fact will be used for other columns agg will be used.

Edited by: Srini VEERAVALLI on Dec 25, 2012 8:27 AM
• ###### 2. Re: Aggregate layer mapping
Currently Being Moderated
Edited by: Srini VEERAVALLI on Dec 25, 2012 8:27 AM
Thanks Srini
but i havent understood your point here
-Create measure AutoUnits with expression as TotalUnits-ManUnits and mapping with fact and other one is with Aggtable.

other way is just create another logical measure using existing column in logical fact

When you use these measures with date; fact will be used for other columns agg will be used.

the simpler way to explain is i have to have one logical column which should map derived and agg source
one source for day grain has a calculation and the aggregate has a different calculation

or to put it simply how will you map aggregate source to an existing day grain mapped column where calculation is involved
• ###### 3. Re: Aggregate layer mapping
Currently Being Moderated
Logical column properties->tab Data Type->check for Show all logical sources
here you shoul see the fact and agg tables as sources
metric AutoUnits : Calculation (TotalUnits-ManUnits) shoul map for both sources.

when you use this date,AutoUnits columns you see fact in the physical query
when you use this week,AutoUnits columns you see agg fact in the physical query

#### Legend

• Correct Answers - 10 points