Cube design and logical primary keys

SBALT427 1 Reputation point
2021-02-19T22:03:38.253+00:00

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:

70110-results.jpg

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]?

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,256 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Alexei Stoyanovsky 3,416 Reputation points
    2021-02-20T07:53:03.5+00:00

    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?


  2. Lukas Yu -MSFT 5,816 Reputation points
    2021-02-25T09:45:58.673+00:00

    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

    0 comments No comments