Hi,
Suppose i have a prompt containing Calendar.Date,Product.Product_Name,Region.Region_Name and i have checked the constraint option in Product_Name and Region_Name.Now the question is how OBI will get the data as Date ,Product_Name and Region_Name all are coming from different tables.What OBI will do is, it will go to the nearest fact that contains stamping for Date ,Product_Name and Region_Name and it will fetch the data from that fact table.Do u think is it fine? definitely No. the reason behind this is From the Fact table we cannot get data for every permuation and combination of Date ,Product_Name and Region_Name ,so ultimately we will miss some data.
Solution for this is :just create a dummy table in the physical layer of RPD containing a single column and join this table(joining condition 1=1) with all other dimensions from where you want columns in the prompt.This will create a cartesian product between the tables.In the BMM layer also do the same activity .
This wil help you in Avoiding the data loss from the prompt while selecting the constraint option.
Thanks
Sitesh
