Need to join dimension tables

View previous topic View next topic Go down

Need to join dimension tables

Post  bstr on Fri Apr 09, 2010 6:23 pm

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.

bstr

Posts : 3
Join date : 2010-04-01

View user profile

Back to top Go down

Re: Need to join dimension tables

Post  ngalemmo on Fri Apr 09, 2010 7:16 pm

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).
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Need to join dimension tables

Post  kvpt123 on Tue Apr 13, 2010 7:52 pm

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.

kvpt123

Posts : 1
Join date : 2010-04-13

View user profile

Back to top Go down

Re: Need to join dimension tables

Post  ngalemmo on Wed Apr 14, 2010 12:20 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Need to join dimension tables

Post  seadog2010 on Mon Apr 19, 2010 8:52 am

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

seadog2010

Posts : 23
Join date : 2010-03-04

View user profile

Back to top Go down

Re: Need to join dimension tables

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum