Post reply

Problem with DB design or SSAS settings?

  • April 26, 2010 at 10:15 am

    #142783

    OK, I have a FactSale table that basically looks like this:

    FactSaleKey int,

    SaleDateKey int (FK to DimDate),

    BudgetKey int (FK to FactBudget),

    NetSaleAmount money

    A FactBudget table that looks like this:

    BudgetKey int,

    BudgetDateKey int (FK to DimDate),

    BudgetNetSaleAmount money

    And a DimDate that looks like this:

    DateKey int,

    Date datetime

    I have a measure on FactSale.NetSaleAmount and on FactBudget.BudgetNetSaleAmount.

    I have a measure group for both FactSale and FactBudget.

    The relationship between FactSale and SaleDate (DimDate) is Regular, via DateKey. Same for the relationship between FactSale and FactBudget via BudgetKey. The relationship between FactBudget and SaleDate (DimDate) is Referenced, with Intermediate Dimension of FactBudget, Reference Dimension Attribute of DateKey, and Intermediate Dimension Attribute of BudgetDateKey. It's materialized.

    Everything is fine as long as there are FactSales for every date that has a FactBudget entry, but if there are Budgeted dates that don't have Sales, I don't get those amounts, and they don't get totalled in MDX. I'm using Excel 2007 to "consume" the data from the cube. Here's a rough example of what I"m getting, and what I'd like:

    Note the extra row in the right-hand image, which swings us from over-budget (good) to under-budget (bad!).

    I suspect it's because the BudgetKey is in FactSales, but I'm loathe to change that because there are a variety of levels that the business users want to associate a budget to a sale, and this is far and away the easiest way to join them together.

    So, is it a problem in the relationship between FactSales and FactBudget, FactBudget and SaleDate (DimDate), in Excel, or something else entirely?

    Thanks,


    Rick Todd

  • April 28, 2010 at 3:19 am

    #1158716

    Hello Rick,

    As i see it this problem could be caused by the referenced relationship between FactBudget and SaleDate(DimDate). Only the budget facts with a related (referenced) salesfact will be displayed by using this reference relationship type.

    Why don't you use a regular relationship between FactBudget and SaleDate(DimDate) ?

    Kind regards,

    Cees

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply