The cross-drilling basically means that you can navigate across multiple
fact tables. In other words, it's a join across two fact tables. This is possible
only if you have absolutely same dimensions. This is the same principle when
you are creating a join across two tables in a transactional system. The only
difference is that even if the transactional tables have fields with a different
name, the joins can be made as long as the data structure and format is same.
In transactional system, the specific queries are made against specific transactions.
It is not much different in a dimensional model and multi-dimensional (OLAP
)
storage. However the scenarios of joins could be unlimited in a Data Warehouse. Given that dimensional
model could be exposed to planned and unplanned queries, the non-conformed
dimensions would not be able to handle unplanned query. Also for planned queries
as well, we will have to do a manual intervention.
You can refer query across cubes to get some more perspective. It is always preferred to have automated joins across multiple fact tables. |