you need to make a database view with
1. All Requisitions from source with related Purchase order and receipts or with out them ( outerjoin)
2. All Purchase orders with receipts or without receipts (outerjoin) and with out Requisitions ( not in or null)
3. All Receipts without Purchase orders(null) and with out Requisitions(null)
note that you need to create a primary key for maintaining the granularity. the least level ID can do this job.
but since you "Union" data you can make use of some key generator or identity.
then use this view and import to physical layer. create 2 alias one for fact and one for dimension.
join fact and dimension using the same key
create a new Subject area and drag and drop fact and dimension. (note: for a star schema you need bare minimum a fact and and dimension, here we are making use of the same table as fact and dimension join 1:1 though typically its n:1 still this will work)
do the same with presentation layer.
The above design is suggested based on an assumption that there is no other (table/view) dimension involved and there are no measure apart from count.
I suggest a view because you want the report real time. Please use appropriate filters to get the data in the view so that it doesn't bring performance issue on source system or on the report e.g. a day's or a week's data.
keep refreshing the view in frequent intervals.
Or you can try putting them in stored proc. and call the stored proc every time you run the report ( disable cache). This you will do it in physical layer.
mark if this helps
Thanks for taking your time to reply.
Unfortunately they method you suggested is the only method I can think of achieving the result, but with that approach I'll have the following issue. I'll need to create and maintain 3 DB views which each might contain some duplicate information from the other view. The performance of the view which has requisitions as the source with an outer-join to purchase orders would be slow and unnecessary when the report needed to be developed only requires information on Requisitions.
I was learning towards a solution which involved creating 3 separate views for each area (Requisitions, Purchase Orders and Receipts) and modeling the joins within the RPD rather than in the views itself. This way, you could create a report only on Requisitions if required, or Requisitions and whether it has a Purchase Orders or not. Going back to the good old discoverer days, this was possible but now seems tricky, especially if you're trying to follow Oracle's Best Practices in RPD design and modelling.
I think I answered your question on how to make a subject area with no facts and dimensions.
You can change your design as per your need. But if you want to build a Subject area without facts and still make it work, the approach I mentioned will be of help.
If this makes sense mark as helpful or correct as applicable.
Senthil, my second post indicated that the answer you provided wasn't helpful and i will be looking for someone else to comment. As stated in my original question "My dilemma is that I'm not sure where to start modelling these tables in the recommended star topology". What you provided is not what I am asking for, therefore the answer was unhelpful.
Please let someone else respond, because this is not a competition on how many helpful / correct answers you can receive.
1 person found this helpful
if you want a star with requisition, purchase order and receipts you need another table which will behave as a fact.
So you have to build a fact with foreign keys from these 3 tables which are related. If you know any table that will readily do that, for your criteria of reporting, make use of it. if not you need to create a new one.
The fact needs to be made the way I mentioned in the earlier post (requistion Union Purchase order union receipts).
there are many ways to model a report/subject area. If you have a better approach pls enlighten me.
Besides there is no need for me to compete in getting you started or to bring you out of your Dilemma.