Fact identification in FM

[user_src]

Hi Gurus,

I am a bit new to FM. I have imported the tables fro the underlying database and trying to set up the facts and dimensions in Cognos.

But I have a very basic question- is it necessary to have only 1 fact per subject area? Eg if I have orders and products coming under the same SA - customer requests , is it necessary to have the order amount and the product cost be under the same fact ? orders are stored at order level and products at product level.

[bdbits]

No, you can have multiple fact tables. You do need to be somewhat more diligent to model your underlying data relationships correctly with multiple fact tables, so that aggregation works properly.

[bus_pass_man]

The answer to that depends on where the fact data exists in your data base and the nature of the fact data.  For example, product cost is not necessarily the same for any particular product.  If you lengthen the period, any sort of fixed cost becomes variable.  Keep in mind that the modeling objective for the query layer is planning virtual star schemas for each fact table (modeling for predictable results and all that).

By 'fact' do you mean fact table?  If so yes.  (see the virtual star schema bit in the previous paragraph)  You can have conformed dimensions ( the relational query thing ).

The answer to that is you need to have clear unambiguous query paths in your query layer.   You can lump a bunch of stuff from different fact tables into one model query subject in your business layer rather than segregating them.   Provided the query layer is modeled correctly you will get the fact streams and coalesces etc. which you want.

Another thing to consider is the grains of the dimensions relative to your facts. If I recall correctly, the FM modeling course explains that's why the sample great outdoors sales sample model has order header and order details modeled as they are (isolated and all relationships between them and any other table moved to the model query subject).

Does that help?