SSAS MDX Query The following is the database configuration: Supplier(Supp_ID, Na
ID: 3827044 • Letter: S
Question
SSAS MDX Query
The following is the database configuration:
Supplier(Supp_ID, Name, City, State)
Consumer(Con_ID, Name, City, State)
Product(Prod_ID, Name, Product_Category, Product_Line, Product_Packaging)
Offers(Supp_ID, Prod_ID, Quantity, Price)
Requests(Con_ID, Prod_ID, Quantity, Price)
Transactions(Tran_ID, Supp_ID, Con_ID, Prod_ID, Quantity, Price)
Measures only come from the Transactions table and are the following: Price, Quantity, and Count
I am asked to do the following: For each product, list quantity sold by suppliers in Madison to consumers in Chicago versus quantity sold by suppliers in Chicago to consumers in Madison (result columns will be: product name, quantity Madison_Chicago, quantity Chicago_Madison)?
I cannot seem to figure out how to join the two MDX queries that answer the question and alias the columns. The two separate MDX queries that give me my answers are below:
SELECT NON EMPTY {
[Measures].[Quantity - Tb Transactions]
} ON COLUMNS,
NON EMPTY {(
[Tb Product].[Name].[Name].ALLMEMBERS *
[Tb Supplier].[City].[City].ALLMEMBERS *
[Tb Consumer].[City].[City].ALLMEMBERS
)} ON ROWS
FROM (
SELECT ({
[Tb Consumer].[City].&[Chicago]
}) ON COLUMNS
FROM (
SELECT ({
[Tb Supplier].[City].&[Madison]
}) ON COLUMNS
FROM [DS715]))
and
SELECT NON EMPTY {
[Measures].[Quantity - Tb Transactions]
} ON COLUMNS,
NON EMPTY {(
[Tb Product].[Name].[Name].ALLMEMBERS *
[Tb Supplier].[City].[City].ALLMEMBERS *
[Tb Consumer].[City].[City].ALLMEMBERS
)} ON ROWS
FROM (
SELECT ({
[Tb Consumer].[City].&[Madison]
}) ON COLUMNS
FROM (
SELECT ({
[Tb Supplier].[City].&[Chicago]
}) ON COLUMNS
FROM [DS715]))
I need the results of both queries in a single output with column names of "Quantity_Madison_Chicago" and "Quantity_Chicago_Madison".
Explanation / Answer
To join the above two MDX queries the following query is to be executed:-
Query:-
SELECT NON EMPTY { [Measures].[ Quantity - Tb Transactions] } ON COLUMNS,
NON EMPTY { ( [Tb Product] . [Name] . [Name].ALLMEMBERS ) } ON ROWS
FROM DS715
WHERE ( { ( [Tb Consumer].[City].&[Madison],[Tb Supplier].[City].&[Chicago] ),
{([Tb Consumer].[City].&[Chicago],[Tb Supplier].[City].&[Madison] )} )
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.