Cube design and logical primary keys

Question

I don't do much in SSAS, mostly work with SQL Server. I have a DBA that usually gives me MDX to run via OPENQUERY when I need data from that server. This is my issue:

I have an invoice table:

Invoice #
Store Identifier
Customer Identifier
Date Invoice
Manufacturer Code
SKU
Qty Sold
Sales Total

I am querying the cube for to get the total qty sold by Customer Identifier for Store Identifier TEST3, Manufacturer Code DOR and a date range. So my return values are:

This is the MDX query my DBA gave me to use:

SELECT { [Measures].[Qty Sold] } ON COLUMNS, { ([Invoices].[Customer].[Customer].ALLMEMBERS ) }  
DIMENSION PROPERTIES [Invoices].[Customer].[Cust Number] ON ROWS  
FROM ( SELECT ( [Invoice Date].[Invoice Date].[Inv Date].&[20201116] : [Invoice Date].[Invoice Date].[Inv Date].&[20210214] ) ON COLUMNS  
FROM ( SELECT ( { [Invoices].[Manufacturer Code].&[TEST3DOR] } ) ON COLUMNS  
FROM ( SELECT ( { [Invoices].[Store].&[TEST3] } ) ON COLUMNS  
FROM [Invoices]))) WHERE ( [Invoices].[Store].&[TEST3], [Invoices].[Manufacturer Code].&[TEST3DOR] )  

The part that concerns me is the [Invoices].[Manufacturer Code].&[TEST3DOR]. He is telling me that I must concatenate the Store Identifier and Manufacturer Code together or I will get data for another Store Identifier, regardless of the fact that I am selecting on [Invoices].[Store].&[TEST3]. This seems like it is bad design to me... If this was configured with a Logical Primary Key wouldn't I be able to use [Invoices].[Manufacturer Code].&[DOR]?

Answer 1

Logical primary key on the Invoices table in the DSV wouldn't affect the key of the Manufacturer Code attribute of the Invoices dimension.
However, if that key is defined as a concatenation of store id and manufacturer code proper, without so much as a delimiter, this does look like questionable design for several reasons. There's the potential for having several distinct members for a single manufacturer, a chance of code clashes between e.g. 'test3'+'dor' and 'test'+'3dor', and why hasn't a composite key been used?

Answer 2

Hi @SBALT427 ,

Did you make any progress? We did not hear from you back.
I think Alexei's answer has made great clarification, could we help further ? You could mark helpful post as answer or give some feedback on how we could help.

Regards,
Lukas