Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Querying only Dimension Tables. Fact Table with missing Dimension Records.

Answered Querying only Dimension Tables. Fact Table with missing Dimension Records.

  • Sunday, December 23, 2012 8:24 PM
     
     
    Sign In to Vote
    0

    Part of my Datawarehouse consists of

    Company Dimension
    Address Dimension
    FactTable with Company and Address Surrogate keys.
    A company can have multiple addresses.

    All of the dimension records might not exists in FactTable.

    If want to query on all the addresses of a company, Do I need to have a bridge table with association of Company and Address? What is the best way to handle this ?

    Thanks!!

     

All Replies

  • Monday, December 24, 2012 5:21 AM
     
     Answered
    Sign In to Vote
    0

    Hi,

    Understand that the there is a 1 to many relation exist between company and address, and the entries is the address dimension is only for the company.

    In this case add company id in the address dimension table. That solves the problem.

    If the company and address dims are of many to many relationship use a bridge table.

    Regards,

    Vipin 

    Regards, Vipin -------------------------- Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

     
  • Friday, December 28, 2012 1:56 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    how many attributes do you have for the company dim? if they are not too much and if each address belongs to just one company not more, then you can add all attributes of company dim to address, instead of having 2 different dimensions.

    and then you can use a view simply to get distinct of companies as DimCompany.

    http://www.rad.pasfu.com

     
  • Wednesday, January 02, 2013 5:38 PM
     
     
    Sign In to Vote
    0
    since a company can have mutiple addresses and not all addresses will be present in the fact table I think you need a fact less fact table.

    Jayanth Kurup - www.enabledbusiness.com

     
  • Sunday, January 06, 2013 2:07 PM
     
     
    Sign In to Vote
    0

    There are several ways to accomplish your request.

    1) You can have a synthetic key adjacent  to address id in the address dimension table.  keep them as a composite primary key which is a parent table for the fact table. 

    2) you can adopt type III dimension table but you must comprehend they are not slowly changing dimension but they are all active records.

    I have listed all these combination in my iOS app called data-iq.  try downloading this and you can figure out all possible combination.

    https://itunes.apple.com/us/app/data-iq/id588271917?mt=8