Calculations in Presentation Layer

[raj_aries81]

Hello All,

We have 2 fact tables, 2 conformed and a non-conformed dimension. I have created two star schema groupings, first one with a fact and two dimensions while the other star schema is a fact and 3 dimensions (1 non-conformed).

Framework is a three layered approach ---> Import Layer (database tables)--->Intermediate Layer (..where I model my star schema) ---> Presentation Layer (..mere short-cut to my Intermediate Layer using create star schema grouping).


I have to create calculations within Framework Manager that spans multiple dimensions.

As an example, assuming my sales fact is a star schema with Product, Location and Time, I now have to create a calculation in FM as below

case
when Product='Prod1' and Location='Loc1' then 100
when Product='Prod12 and Location='Loc12' then 125
..

end


The intention is to have this calculation within FM so that it can be used across the reports.I dont think I can define this calculation in Intermediate Layer, the only place I could think of is creating a new query subject in my Presentation Layer.

Please let me know if there are any repercussions if I do this or is there a better way to handle this.

Appreciate your inputs..Thanks

Raj

[MFGF]

Hello All,

We have 2 fact tables, 2 conformed and a non-conformed dimension. I have created two star schema groupings, first one with a fact and two dimensions while the other star schema is a fact and 3 dimensions (1 non-conformed).

Framework is a three layered approach ---> Import Layer (database tables)--->Intermediate Layer (..where I model my star schema) ---> Presentation Layer (..mere short-cut to my Intermediate Layer using create star schema grouping).


I have to create calculations within Framework Manager that spans multiple dimensions.

As an example, assuming my sales fact is a star schema with Product, Location and Time, I now have to create a calculation in FM as below

case
when Product='Prod1' and Location='Loc1' then 100
when Product='Prod12 and Location='Loc12' then 125
..

end


The intention is to have this calculation within FM so that it can be used across the reports.I dont think I can define this calculation in Intermediate Layer, the only place I could think of is creating a new query subject in my Presentation Layer.

Please let me know if there are any repercussions if I do this or is there a better way to handle this.

Appreciate your inputs..Thanks

Raj

Hi,

Can you explain why you can't define this within your intermediate layer? That seems to be the most appropriate place for this. Wouldn't this just be a calculated measure in your fact query subject?

MF.

[raj_aries81]

Hi,

Can you explain why you can't define this within your intermediate layer? That seems to be the most appropriate place for this. Wouldn't this just be a calculated measure in your fact query subject?

MF.

Thanks MF,

This calculation is based on two (or more) dimensions, as my joins are defined in Intermediate layer I cant create a calculation that uses multiple dimensions.

I thought creating another model query subject in Intermediate Layer that combines columns from these dimensions make the model convoluted.

PS: Joins between my fact and dimensions are based on Surrogate Key

Thanks
Raj

[MFGF]

Thanks MF,

This calculation is based on two (or more) dimensions, as my joins are defined in Intermediate layer I cant create a calculation that uses multiple dimensions.

I thought creating another model query subject in Intermediate Layer that combines columns from these dimensions make the model convoluted.

PS: Joins between my fact and dimensions are based on Surrogate Key

Thanks
Raj

Hi,

Normally you'd define all the joins in your database layer, and your intermediate layer would be just model query subjects without any relationships.

MF.

[raj_aries81]

Hi MF,

We are using the below approach -

Database Layer
Datasource Query Subjects
Select * From ... only
Create relationships between the tables that can be denormalized (define joins between Product Line, Product Type & Product).

Modeling Layer
Model Query Subjects built from Objects in Database Layer
Denormalize tables (Eg: create Product dimension using Product Line, Product Type & Product) & create Star Schema structure
Business Term renaming
Role Playing Dimensions
Determinants



Business (Presentation) Layer
Logical groupings of objects
Reference Shortcuts only (that are created using star schema grouping)

source:copied from http://www.cognoise.com/index.php/topic,32375.0.html

Regards
Raj