Need to join dimension tables

Hi,
I have a requirement to build canned reports in WEBI XI R3. The requirements are such that most of the reports are on the dimension tables and needs dimension tables to be joined.

From a starschema prospective we cannot join dimension tables. My way of looking at it is
1.Build custom tables or views
2.Free style sql

I wanted to know your views from a best practice standpoint for my kind of scenario.

Appreciate your response. Thanks in Advance.

Dimension tables are associated through fact tables. But a typical fact table only draws associations based on business events, which may not be all possible associations.

If you need to maintain relationships between dimensions independent of business events, you create factless fact tables referencing those dimensions you need to relate. You may include effective dates should you need to maintain a historical perspective. (In the ER modeling world, these are called associative entities).

I also have same requirement. Can I use the same dimension to snowflake with other dimension and fact table? For example account should have product. Account and product dimension are joined with sales fact independently. Can account dimension have product surrogate key for reports like number of account by product rather creating fact less fact. Thanks in advance.

In a proper star schema (i.e. no snowflaking) your would either replicate account attributes to product, if it is a hierarchical relationship as you describe (i.e. a product has one and only one account), or as a factless fact if some other relationship exists (i.e. many to many).

I don't know what an 'account' represents in your context, but generally speaking, a factless fact gives you a lot more flexibility in maintaining true relationships between such entities.

In multivalued dimension situations, I would normally use Bridge tables to show that a Fact relates to a group of dimension records equally. This assumes there's no relationships between the dimension groups - the group tables are just 'pools' of dimension values, related to the Fact, but unrelated to each other.

There may be cases however where the dimensions are related to each other, as well as to the Fact. in the case of a Policy, a fact table is not related to a 'pool' of Sales Offices and to a 'pool' of Sales Reps, but to specifc Reps assigned to specific Offices:

Policy Rep Office
X A New York
X B Boston
X C Boston

Rep A is assigned to Office New York. Rep B & C are assigned to Office Boston. Using a bridge table approach, the Policy would list those Reps and Offices relating to it, but the relationship between Reps and Offices would be hidden and perhaps be misleading. Depending on sorting, it might appear as if A could be assigned to Office Boston, for example:

Policy Rep Office
X A Boston
X B Boston
X C New York


Would it be that a factless table is then the preferred mechanism to use to show these inter-dimension relationships (if they're required to be shown)? If so, this in turn lead night necessitate further breaking down the Fact grain to a detail beyond which is naturally collected - say, Policy premium.

Your thoughts?

-Seadog