How to determine whether a particular query subject is dimension or fact table?

[saumil287]

Hi All,

I am having an doubt regarding query subject type:fact/dimension.

In a sample of gosales (great outdoors sales) provided by ibm.
There is a table branch and country where branch is on many side(1:n) of a relationship and country is having 1:1 cardinalities.
  So In terms of cognos, the table which is on many side of relationship is a fact table.

But it is not having any fact columns.

So can you give me an idea. how do we know whther a querysubject is a fact or dimension query subject?

Thanks
saumil

[MFGF]

Hi,

In a snowflake dimension, each "level" table of a dimension will usually have a 1-to-many relationship with the "level" table at the next granular level. This is quite normal. In reality the fact/dimension determination is only important where a query resolves (or should resolve) to use more than one "fact" query subject. In the scenario you cite, the Branch table links in turn to a number of real "fact" tables - Sales, Product Forecast and Inventory Levels (if memory serves). For all of these the Branch query subject is at the 1 end of the 1-to-many relationship, so is treated as a dimension.

Cheers!

MF.

[saumil287]

Hi MFGF,
Thanks very much for your reply.

If say for eg Branch is only linked to country and not to other query subjects which is on many side but not having a fact column is a dimension table.?

So I can say that a querysubject containing a fact column is a fact table?

1>query subject which is on many side of relationship is a fact table but having no fact columns?
2> query subject which is having fact columns and is on 1 side of 1 to many relationships is a fact table?
3>query subject having and identifier and fact columns is a fact table?

4>query subject which is on many side but not having a fact column is a dimension table?

Thanks
saumil

[MFGF]

Hi MFGF,
Thanks very much for your reply.

If say for eg Branch is only linked to country and not to other query subjects which is on many side but not having a fact column is a dimension table.?

So I can say that a querysubject containing a fact column is a fact table?

1>query subject which is on many side of relationship is a fact table but having no fact columns?
2> query subject which is having fact columns and is on 1 side of 1 to many relationships is a fact table?
3>query subject having and identifier and fact columns is a fact table?

4>query subject which is on many side but not having a fact column is a dimension table?

Thanks
saumil

The question is how the query engine *treats* the query subject when generating a query. It makes no difference whether a query subject is identified as "fact" or "dimension" as long as the query contains no more than one (assumed) "fact" query subject - the generated query will be the same. If Branch links to nothing else other than Country, then - yes - it will be treated as a "fact" by the query engine when used in conjunction with Country, but since the query contains only one assumed "fact" query subject (Branch), the generated query would be the same as if Branch had been assumed to be a "dimension".

If the query engine determines that there is more than one "fact" query subject being used, then it generates a stitch query. In this case the fact/dimension determination is critically important. If there are really two fact tables, it is the correct thing to do, but if a dimension is being incorrectly treated as a fact, then it will deliver incorrect/inconsistent results.

In the great_outdoors_sales model, if you bring in items from Country and Branch, there is only one "fact" query subject - Branch. Since there is only one "fact" a simple join is used in the query and correct results are returned. If you bring in items from Country, Branch and Sales, there is again only one "fact" query subject - Sales. This is because Branch is at the 1 end of the relationship that links Branch and Sales. This is fine - Branch is being treated as a dimension. A simple join Country - Branch - Sales is used and correct results are returned. If you bring in items from Country, Branch, Sales and Product Forecast there are two "fact" query subjects - Sales and Product Forecast. This is because both Sales and Product Forecast are at the many end of all relationships that link to them. This is fine - Branch is still being treated as a dimension, and a stitch between Sales and Product Forecast is performed using the common dimensional item(s) from Branch/Country coalesced to form stitch keys.

The rules the query engine uses to determine fact or dimension are:

If the query subject is at the many end of ALL relationships used, it is assumed to be a fact
If the query subject is at the one end of ANY relationship used, it is assumed to be a dimension

So, to answer your questions:

So I can say that a querysubject containing a fact column is a fact table?

No. the query item usage property is not used in fact/dimension determination. Cardinalities of relationships are used.

1. Yes - the query subject is treated as a fact (if it is at the many end of ALL relationships used), regardless of whether or not it contains measures.
2. No - the query subject would be treated as a dimension if it is at the one end of any relationship.
3. No - the usage of a query subject (fact/dimension) is driven by the cardinalities of the relationships used in the query
4. No - the usage of a query subject (fact/dimension) is driven by the cardinalities of the relationships used in the query

Cheers!

MF.

[saumil287]

Hi MFGF,

Thanks much for the information given.


Thanks & Regards
saumil