1

When browsing my cube it shows the correct invoice values by date when I select only a date type (the date table was generated by SSAS with hierarchies), but if I add the customer name it shows the combined value of the invoices for the date period, rather than the total per customer. I expect to see the total value for the customer that month, not the overall monthly total.

Excel Example

My tables are joined with foreign keys and the DSV shows a star schema. A simplified version of the schema is below.

factInvoice invoicePK customerFK -> customerPK dateFK -> datePK dimDate datePK dimCustomer customerPK

The dimension usage tab shows both dimensions are linked to the invoice table.

dimension usage tab

Please can somebody shed some light on why this is happening? I'm sure I'm missing something pretty basic here! Thanks.

Improve this question
9
  • "if I add the customer name it shows the combined value of the invoices for the date period, rather than the total per customer." I'm not sure I follow. A screenshot would help. Commented Mar 31, 2018 at 0:19
  • @GregGalloway sure I have added an image. Hopefully that explains it better. I expect to see the total value for each customer that month, not the overall monthly total. Thanks for looking. Commented Mar 31, 2018 at 15:22
  • still don't follow. Give me a specific example. What do you expect to see for January for Company A and how should it be calculated. Commented Mar 31, 2018 at 15:36
  • 1
    I bet I know what's going on. The Invoice measure group is joined to the Customer dimension not at the key attribute but some other attribute. Is that correct? That would explain the repeating data. Commented Mar 31, 2018 at 18:28
  • 1
    surrogate keys are recommended. For example if you have two CRM systems both with a Customer number 100 then it may be difficult to use natural keys. And sometimes you need Slowly Changing Dimensions to properly represent history which requires a surrogate key. Even Date dimensions should use an integer surrogate key because that gives you flexibility to setup a -1 key which means Unknown and a -2 key which means "before the beginning of the date dimension" etc. Commented Mar 31, 2018 at 19:13

1 Answer 1

Reset to default
1

The Customer dimension is joined to the measure group at a non-key granularity. If the Customer Name attribute isn't related to the attribute you join on then data repeats in reports. You need to do one of two things:

  1. Recommended. Change the fact table to have the surrogate key of the dimension. Then change the Dimension Usage tab to join to the key of the dimension.
  2. Change the dimension to change which column is used as the key of the dimension. Make sure the fact table has this column. Fix the Dimension Usage tab to join to the dimension key.
Improve this answer
2
  • Thank you. Just to make sure I understand... During SSIS import of the invoice data I should lookup the surrogate key in the customer dimension and add that to the invoice fact table? Commented Mar 31, 2018 at 19:53
  • 1
    @scampbell right. Not required (since you could do #2) but recommended Commented Mar 31, 2018 at 19:58

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.