Can we go for outtrigger for this business requirement?

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.

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

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).

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.

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.

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.