Can we go for outtrigger for this business requirement?

View previous topic View next topic Go down

Can we go for outtrigger for this business requirement?

Post  Jayarch on Tue Dec 18, 2012 12:45 am

I have a security dimension which stores cusip attributes.This design already in production.Now business wants to add more classications(assetclass,group etc. to cusip(higher granular than cusip) and want to generate reports based on GROUP BY on these new classification.Many cusips may fall under one assetclass.(i.e.1-M relation exists between assetclass to cusip).

Option 1:Can i add new dimension called 'Assetclass' and refer from fact table.add one dummy 'unknown' row to tie to historical facts.

Option 2:Add one outtrigger dimension ' 'Assetclass'' which shall be refered from 'Cusip' dimension via foreign key.


Please let me know which option will be better and suggest new option if any.


Jayarch

Posts : 4
Join date : 2012-02-27

View user profile

Back to top Go down

Re: Can we go for outtrigger for this business requirement?

Post  hkandpal on Tue Dec 18, 2012 12:49 pm

Hi,

one option i would go about is to store the asset class of the product directyl in the dimension. Do you have a requirement where you will also report on the asset class changes happening on the CUSIP or no ?

thanks

Himanshu

hkandpal

Posts : 113
Join date : 2010-08-16

View user profile

Back to top Go down

Re: Can we go for outtrigger for this business requirement?

Post  Jayarch on Tue Dec 18, 2012 3:13 pm

These asset class category attributes are very static in nature and will not change.one cusip will always fall into one asssetclas category only and it never change to other assetclass category.There may be many cusips may fall under one assetclass category(1-M relation).





Jayarch

Posts : 4
Join date : 2012-02-27

View user profile

Back to top Go down

Re: Can we go for outtrigger for this business requirement?

Post  Jayarch on Tue Dec 18, 2012 3:22 pm

Below are few columns in cusip assetclass dimensions

cusip dimension
--------------
cusip
sedol
currency
price
issuer


assetclass_dim
--------------
assetclass
assetgroup
policytype
fintype
risktype

here policytype,fintype and risktype are the attributes of column assetclass.Hardly there will 20 rows exists for assetclass dimension.


Is it good idea to add assetclass dimension columns to cusip dimension as more flatened version and hence only one cusip dimension exists.




Jayarch

Posts : 4
Join date : 2012-02-27

View user profile

Back to top Go down

Re: Can we go for outtrigger for this business requirement?

Post  Jeff Smith on Tue Dec 18, 2012 3:27 pm

You could denormalize the AssetClass Dimension to the Cusip Dimension. In my opinion, it would dependent on the sizes of the 2 dimensions, how often fields from the 2 dimensions were queried. In other words, it would depend on the performance vs the required performance.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Can we go for outtrigger for this business requirement?

Post  Jayarch on Tue Dec 18, 2012 4:52 pm

Mostly in the reports assetclass attributes will be used in GROUP BY classe while joining with FACT table.It is exepected by business that that assetclass attibutes will not be used as predicates in most cases.Cusip dimension(Type2) is loaded daily with 40k rows.

Jayarch

Posts : 4
Join date : 2012-02-27

View user profile

Back to top Go down

Re: Can we go for outtrigger for this business requirement?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum