SQL Server Performance

Joining two fact tables

Discussion in 'SQL Server 2005 Analysis Services' started by mundlarsreddy, Apr 9, 2008.

  1. mundlarsreddy New Member

    I have the following two fact tables, upon which a cube was built. As you can see, these two tables have most of the common (shared) dimensions except for ProductTypeID (product dimension) on fct_productsales table. The relationship between these two fact tables is 1(fct_loansales) to Many (fct_productsales).
    When I browse these measures from the cube, they look fine (and break down perfectly) through all the shared dimensions. However, when I try to filter or browse through product dimension (along with other dimensions), the measure values from fct_productsales will filter down correctly, but the values from fct_loansales does not change. I tryied to create degenerate dimension from fct_loansales, but no luck.
    Any help is appreciated.
    Fct_LoanSales AccountNumber charLoanTypeID intBranchID intStateID intLoanEffectiveDateID int
    AccountDateID int TotalPayment numericAmtFinancedVolume numericCMP numeric
    APR numeric
    Fct_ProductSales ProductTypeID intAccountNumber charLoanTypeID intBranchID intStateID intLoanEffectiveDateID int
    AccountDateID intSinglePremium numericMonthlyPremium numeric
    MonthlyEquiv numeric
  2. ranjitjain New Member

    One way could be to create intermediate measure group holding relation
    between product dimension and your key column from fact table.
    After this in your data source view, you can link the product dimension with loan fact table through this intermediate measure group.
    Finally check the same established relation in dimension usage.
  3. mundlarsreddy New Member

    Can you be more specific and outline the steps to achieve this?

Share This Page