DSXchange

can anybody explain me the role playing dimension with real 2-3 examples ?????

Welcome Aboard.

"Role playing dimension"? Not really sure what that means, except perhaps where World of Warcraft players live. Where did you hear this, in what context?

Ah.. silly me... Google is a better place to start than here. For example:

"http://en.wikipedia.org/wiki/Dimension_(data_warehouse)"

Dimensions are often recycled for multiple applications within the same database. For instance, a "Date" dimension can be used for "Date of Sale", as well as "Date of Delivery", or "Date of Hire". This is often referred to as a "role-playing dimension".

Role playing dimension is nothing but a tables or views segrgated from a dimension.
lets take the above example

let say business wants a report based sales report on a particular product whcih is ordered on Monday and delivered on Friday.
if we are keeping the dimension as single table it can't able to adress this scenario

the query will come like

select saleamount,orderdate,delverydate,product
from salefact sf,
productdim pd,
timedim td
where sf.prodid=pd.prodid
and sf.ordr_dateid=td.dateid
and sf.del_dateid=td.dateid

the query result will be nothing

to adress these scenari either we have to go with self join on the timeDim or we can create new table or views.


select saleamount,orderdate,delverydate,product
from salefact sf,
productdim pd,
MV_Ordrtimedim otd,
MV_delvtimedim dtd
where sf.prodid=pd.prodid
and sf.ordr_dateid=otd.dateid
and sf.del_dateid=dtd.dateid

now MV_Ordrtimedim , MV_delvtimedim are materialized views this is called as Role playing dimension.

Thanks for ur response . the Sql really helped me in gettin the thing

Then time to mark your post as Resolved it seems. Big button at the top of the screen.