0

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.

  • "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. – GregGalloway Mar 31 '18 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. – scampbell Mar 31 '18 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. – GregGalloway Mar 31 '18 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. – GregGalloway Mar 31 '18 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. – GregGalloway Mar 31 '18 at 19:13
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.
  • 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? – scampbell Mar 31 '18 at 19:53
  • 1
    @scampbell right. Not required (since you could do #2) but recommended – GregGalloway Mar 31 '18 at 19:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.