Translating a T-SQL query to MDX
I am a new MDX user.
I can easily get what I need using T-SQL but getting the equivalent using MDX has proven difficult.
use [AdventureWorksDW2012]
------------------------------------------------------------
--Select customers that purchased specific items during specific time period
------------------------------------------------------------
drop table #Customers_Purchased_SelectedProduct
select
distinct
a.CustomerKey
into #Customers_Purchased_SelectedProduct
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey in (1 , 2)
------------------------------------------------------------
--Get sales metrics for customers identified above
------------------------------------------------------------
select
c.ProductSubcategoryKey
, b.ProductKey
, sum(a.SalesAmount) as SalesAmount
, count(distinct a.CustomerKey) as 'CustomerDistinct_withPurchases'
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
inner join #Customers_Purchased_SelectedProduct bb on a.CustomerKey = bb.CustomerKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey not in (1 , 2)
group by
c.ProductSubcategoryKey
, b.ProductKey
The code below is what I came up with. Seems extremely clunky and after 2 minutes it returns data and isn't correct.
use [AdventureWorksDW2012]
------------------------------------------------------------
--Select customers that purchased specific items during specific time period
------------------------------------------------------------
drop table #Customers_Purchased_SelectedProduct
select
distinct
a.CustomerKey
into #Customers_Purchased_SelectedProduct
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey in (1 , 2)
------------------------------------------------------------
--Get sales metrics for customers identified above
------------------------------------------------------------
select
c.ProductSubcategoryKey
, b.ProductKey
, sum(a.SalesAmount) as SalesAmount
, count(distinct a.CustomerKey) as 'CustomerDistinct_withPurchases'
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
inner join #Customers_Purchased_SelectedProduct bb on a.CustomerKey = bb.CustomerKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey not in (1 , 2)
group by
c.ProductSubcategoryKey
, b.ProductKey
The code below is what I came up with. Seems extremely clunky and after 2 minutes it returns data and isn't correct.
with
------------------------------------------------------------
----Select customers that purchased specific items during specific time period
------------------------------------------------------------
set [Cust] as
nonempty(
[Dim Customer].[Customer Key].[Customer Key].members ,
(
({[Dim Product].[Product Subcategory Key].&[1] ,[Dim Product].[Product Subcategory Key].&[2]}) ,
({[Ship Date].[Date Key].&[20050101]: [Ship Date].[Date Key].&[20081215]}) ,
[Measures].[Sales Amount]
)
)
------------------------------------------------------------
--Create list of subcategories excluding the ones from above
------------------------------------------------------------
set [SubCategory Other] as
except (
[Dim Product].[Product Subcategory Key].[Product Subcategory Key]
, ({[Dim Product].[Product Subcategory Key].&[1] ,[Dim Product].[Product Subcategory Key].&[2]})
)
member [Sales Amount Selected Customers] as sum([Cust] , [Measures].[Sales Amount])
member [Customer Count] as count(nonempty([Cust],[Sales Amount Selected Customers]))
select
{[Sales Amount Selected Customers] , [Customer Count]} on 0
, ([SubCategory Other] * [Dim Product].[Product Key].[Product Key]) on 1
from [Adventure Works DW2012]
Incorrect results set:
The T-SQL query runs in less than 1 second. I'm clearly messing something up.
ssas mdx mdx-query
edited Nov 19 at 22:18
halfer
14.3k758109
asked Nov 19 at 17:10
user2607686
2218
add a comment |
I am a new MDX user.
I can easily get what I need using T-SQL but getting the equivalent using MDX has proven difficult.
use [AdventureWorksDW2012]
------------------------------------------------------------
--Select customers that purchased specific items during specific time period
------------------------------------------------------------
drop table #Customers_Purchased_SelectedProduct
select
distinct
a.CustomerKey
into #Customers_Purchased_SelectedProduct
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey in (1 , 2)
------------------------------------------------------------
--Get sales metrics for customers identified above
------------------------------------------------------------
select
c.ProductSubcategoryKey
, b.ProductKey
, sum(a.SalesAmount) as SalesAmount
, count(distinct a.CustomerKey) as 'CustomerDistinct_withPurchases'
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
inner join #Customers_Purchased_SelectedProduct bb on a.CustomerKey = bb.CustomerKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey not in (1 , 2)
group by
c.ProductSubcategoryKey
, b.ProductKey
The code below is what I came up with. Seems extremely clunky and after 2 minutes it returns data and isn't correct.
use [AdventureWorksDW2012]
------------------------------------------------------------
--Select customers that purchased specific items during specific time period
------------------------------------------------------------
drop table #Customers_Purchased_SelectedProduct
select
distinct
a.CustomerKey
into #Customers_Purchased_SelectedProduct
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey in (1 , 2)
------------------------------------------------------------
--Get sales metrics for customers identified above
------------------------------------------------------------
select
c.ProductSubcategoryKey
, b.ProductKey
, sum(a.SalesAmount) as SalesAmount
, count(distinct a.CustomerKey) as 'CustomerDistinct_withPurchases'
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
inner join #Customers_Purchased_SelectedProduct bb on a.CustomerKey = bb.CustomerKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey not in (1 , 2)
group by
c.ProductSubcategoryKey
, b.ProductKey
The code below is what I came up with. Seems extremely clunky and after 2 minutes it returns data and isn't correct.
with
------------------------------------------------------------
----Select customers that purchased specific items during specific time period
------------------------------------------------------------
set [Cust] as
nonempty(
[Dim Customer].[Customer Key].[Customer Key].members ,
(
({[Dim Product].[Product Subcategory Key].&[1] ,[Dim Product].[Product Subcategory Key].&[2]}) ,
({[Ship Date].[Date Key].&[20050101]: [Ship Date].[Date Key].&[20081215]}) ,
[Measures].[Sales Amount]
)
)
------------------------------------------------------------
--Create list of subcategories excluding the ones from above
------------------------------------------------------------
set [SubCategory Other] as
except (
[Dim Product].[Product Subcategory Key].[Product Subcategory Key]
, ({[Dim Product].[Product Subcategory Key].&[1] ,[Dim Product].[Product Subcategory Key].&[2]})
)
member [Sales Amount Selected Customers] as sum([Cust] , [Measures].[Sales Amount])
member [Customer Count] as count(nonempty([Cust],[Sales Amount Selected Customers]))
select
{[Sales Amount Selected Customers] , [Customer Count]} on 0
, ([SubCategory Other] * [Dim Product].[Product Key].[Product Key]) on 1
from [Adventure Works DW2012]
Incorrect results set:
The T-SQL query runs in less than 1 second. I'm clearly messing something up.
ssas mdx mdx-query
edited Nov 19 at 22:18
halfer
14.3k758109
asked Nov 19 at 17:10
user2607686
2218
add a comment |
I am a new MDX user.
I can easily get what I need using T-SQL but getting the equivalent using MDX has proven difficult.
use [AdventureWorksDW2012]
------------------------------------------------------------
--Select customers that purchased specific items during specific time period
------------------------------------------------------------
drop table #Customers_Purchased_SelectedProduct
select
distinct
a.CustomerKey
into #Customers_Purchased_SelectedProduct
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey in (1 , 2)
------------------------------------------------------------
--Get sales metrics for customers identified above
------------------------------------------------------------
select
c.ProductSubcategoryKey
, b.ProductKey
, sum(a.SalesAmount) as SalesAmount
, count(distinct a.CustomerKey) as 'CustomerDistinct_withPurchases'
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
inner join #Customers_Purchased_SelectedProduct bb on a.CustomerKey = bb.CustomerKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey not in (1 , 2)
group by
c.ProductSubcategoryKey
, b.ProductKey
The code below is what I came up with. Seems extremely clunky and after 2 minutes it returns data and isn't correct.
use [AdventureWorksDW2012]
------------------------------------------------------------
--Select customers that purchased specific items during specific time period
------------------------------------------------------------
drop table #Customers_Purchased_SelectedProduct
select
distinct
a.CustomerKey
into #Customers_Purchased_SelectedProduct
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey in (1 , 2)
------------------------------------------------------------
--Get sales metrics for customers identified above
------------------------------------------------------------
select
c.ProductSubcategoryKey
, b.ProductKey
, sum(a.SalesAmount) as SalesAmount
, count(distinct a.CustomerKey) as 'CustomerDistinct_withPurchases'
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
inner join #Customers_Purchased_SelectedProduct bb on a.CustomerKey = bb.CustomerKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey not in (1 , 2)
group by
c.ProductSubcategoryKey
, b.ProductKey
The code below is what I came up with. Seems extremely clunky and after 2 minutes it returns data and isn't correct.
with
------------------------------------------------------------
----Select customers that purchased specific items during specific time period
------------------------------------------------------------
set [Cust] as
nonempty(
[Dim Customer].[Customer Key].[Customer Key].members ,
(
({[Dim Product].[Product Subcategory Key].&[1] ,[Dim Product].[Product Subcategory Key].&[2]}) ,
({[Ship Date].[Date Key].&[20050101]: [Ship Date].[Date Key].&[20081215]}) ,
[Measures].[Sales Amount]
)
)
------------------------------------------------------------
--Create list of subcategories excluding the ones from above
------------------------------------------------------------
set [SubCategory Other] as
except (
[Dim Product].[Product Subcategory Key].[Product Subcategory Key]
, ({[Dim Product].[Product Subcategory Key].&[1] ,[Dim Product].[Product Subcategory Key].&[2]})
)
member [Sales Amount Selected Customers] as sum([Cust] , [Measures].[Sales Amount])
member [Customer Count] as count(nonempty([Cust],[Sales Amount Selected Customers]))
select
{[Sales Amount Selected Customers] , [Customer Count]} on 0
, ([SubCategory Other] * [Dim Product].[Product Key].[Product Key]) on 1
from [Adventure Works DW2012]
Incorrect results set:
The T-SQL query runs in less than 1 second. I'm clearly messing something up.
ssas mdx mdx-query
edited Nov 19 at 22:18
halfer
14.3k758109
asked Nov 19 at 17:10
user2607686
2218
I am a new MDX user.
I can easily get what I need using T-SQL but getting the equivalent using MDX has proven difficult.
use [AdventureWorksDW2012]
------------------------------------------------------------
--Select customers that purchased specific items during specific time period
------------------------------------------------------------
drop table #Customers_Purchased_SelectedProduct
select
distinct
a.CustomerKey
into #Customers_Purchased_SelectedProduct
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey in (1 , 2)
------------------------------------------------------------
--Get sales metrics for customers identified above
------------------------------------------------------------
select
c.ProductSubcategoryKey
, b.ProductKey
, sum(a.SalesAmount) as SalesAmount
, count(distinct a.CustomerKey) as 'CustomerDistinct_withPurchases'
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
inner join #Customers_Purchased_SelectedProduct bb on a.CustomerKey = bb.CustomerKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey not in (1 , 2)
group by
c.ProductSubcategoryKey
, b.ProductKey
The code below is what I came up with. Seems extremely clunky and after 2 minutes it returns data and isn't correct.
use [AdventureWorksDW2012]
------------------------------------------------------------
--Select customers that purchased specific items during specific time period
------------------------------------------------------------
drop table #Customers_Purchased_SelectedProduct
select
distinct
a.CustomerKey
into #Customers_Purchased_SelectedProduct
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey in (1 , 2)
------------------------------------------------------------
--Get sales metrics for customers identified above
------------------------------------------------------------
select
c.ProductSubcategoryKey
, b.ProductKey
, sum(a.SalesAmount) as SalesAmount
, count(distinct a.CustomerKey) as 'CustomerDistinct_withPurchases'
from [dbo].[FactInternetSales] a
inner join [dbo].[DimProduct] b on a.ProductKey = b.ProductKey
inner join [dbo].[DimProductSubcategory] c on b.ProductSubcategoryKey = c.ProductSubcategoryKey
inner join #Customers_Purchased_SelectedProduct bb on a.CustomerKey = bb.CustomerKey
where
a.ShipDateKey between 20050101 and 20081215
and c.ProductSubcategoryKey not in (1 , 2)
group by
c.ProductSubcategoryKey
, b.ProductKey
The code below is what I came up with. Seems extremely clunky and after 2 minutes it returns data and isn't correct.
with
------------------------------------------------------------
----Select customers that purchased specific items during specific time period
------------------------------------------------------------
set [Cust] as
nonempty(
[Dim Customer].[Customer Key].[Customer Key].members ,
(
({[Dim Product].[Product Subcategory Key].&[1] ,[Dim Product].[Product Subcategory Key].&[2]}) ,
({[Ship Date].[Date Key].&[20050101]: [Ship Date].[Date Key].&[20081215]}) ,
[Measures].[Sales Amount]
)
)
------------------------------------------------------------
--Create list of subcategories excluding the ones from above
------------------------------------------------------------
set [SubCategory Other] as
except (
[Dim Product].[Product Subcategory Key].[Product Subcategory Key]
, ({[Dim Product].[Product Subcategory Key].&[1] ,[Dim Product].[Product Subcategory Key].&[2]})
)
member [Sales Amount Selected Customers] as sum([Cust] , [Measures].[Sales Amount])
member [Customer Count] as count(nonempty([Cust],[Sales Amount Selected Customers]))
select
{[Sales Amount Selected Customers] , [Customer Count]} on 0
, ([SubCategory Other] * [Dim Product].[Product Key].[Product Key]) on 1
from [Adventure Works DW2012]
Incorrect results set:
The T-SQL query runs in less than 1 second. I'm clearly messing something up.
ssas mdx mdx-query
ssas mdx mdx-query
edited Nov 19 at 22:18
halfer
14.3k758109
asked Nov 19 at 17:10
user2607686
2218
edited Nov 19 at 22:18
halfer
14.3k758109
asked Nov 19 at 17:10
user2607686
2218
edited Nov 19 at 22:18
halfer
14.3k758109
edited Nov 19 at 22:18
halfer
14.3k758109
edited Nov 19 at 22:18
halfer
14.3k758109
14.3k758109
asked Nov 19 at 17:10
user2607686
2218
asked Nov 19 at 17:10
user2607686
2218
asked Nov 19 at 17:10
user2607686
2218
2218
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
My understanding is that you want to have the sales Amount and distinct customer count for products and their subcategories within a data range. These products and their subcategories were bought by the customers who bought items from sub category 1 and 2 during the same period. For this you first took the list of customers in a temp table, then for all products bought by these customers you grouped product sales and distinct customer count.
Now there is couple of issues.
1) MDX does' not support sub query. So there is no straight forward way for you to collect the list of customers that you did in SQL.
2) In MDX you cannot put a single dimension attribute on two axes. Translating this to your problem, you want to cross analyze all product sales against, two products (market basket analysis). So Ideally in MDX the solution should be along the lines of putting the same dimension attribute on both axis, but this is not supported.
3) In your SQL query you are only using fact internet sales, in MDX you are also using [Measures].[Sales Amount] which is not from internet sales
4) The reason why 18484 are repeated is that the set is unaware of the query context. In simple terms for row 1 the set does not know it is executing for combination 3, 560
answered Nov 22 at 18:10
MoazRub
24417
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53379586%2ftranslating-a-t-sql-query-to-mdx%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
My understanding is that you want to have the sales Amount and distinct customer count for products and their subcategories within a data range. These products and their subcategories were bought by the customers who bought items from sub category 1 and 2 during the same period. For this you first took the list of customers in a temp table, then for all products bought by these customers you grouped product sales and distinct customer count.
Now there is couple of issues.
1) MDX does' not support sub query. So there is no straight forward way for you to collect the list of customers that you did in SQL.
2) In MDX you cannot put a single dimension attribute on two axes. Translating this to your problem, you want to cross analyze all product sales against, two products (market basket analysis). So Ideally in MDX the solution should be along the lines of putting the same dimension attribute on both axis, but this is not supported.
3) In your SQL query you are only using fact internet sales, in MDX you are also using [Measures].[Sales Amount] which is not from internet sales
4) The reason why 18484 are repeated is that the set is unaware of the query context. In simple terms for row 1 the set does not know it is executing for combination 3, 560
answered Nov 22 at 18:10
MoazRub
24417
add a comment |
My understanding is that you want to have the sales Amount and distinct customer count for products and their subcategories within a data range. These products and their subcategories were bought by the customers who bought items from sub category 1 and 2 during the same period. For this you first took the list of customers in a temp table, then for all products bought by these customers you grouped product sales and distinct customer count.
Now there is couple of issues.
1) MDX does' not support sub query. So there is no straight forward way for you to collect the list of customers that you did in SQL.
2) In MDX you cannot put a single dimension attribute on two axes. Translating this to your problem, you want to cross analyze all product sales against, two products (market basket analysis). So Ideally in MDX the solution should be along the lines of putting the same dimension attribute on both axis, but this is not supported.
3) In your SQL query you are only using fact internet sales, in MDX you are also using [Measures].[Sales Amount] which is not from internet sales
4) The reason why 18484 are repeated is that the set is unaware of the query context. In simple terms for row 1 the set does not know it is executing for combination 3, 560
answered Nov 22 at 18:10
MoazRub
24417
add a comment |
My understanding is that you want to have the sales Amount and distinct customer count for products and their subcategories within a data range. These products and their subcategories were bought by the customers who bought items from sub category 1 and 2 during the same period. For this you first took the list of customers in a temp table, then for all products bought by these customers you grouped product sales and distinct customer count.
Now there is couple of issues.
1) MDX does' not support sub query. So there is no straight forward way for you to collect the list of customers that you did in SQL.
2) In MDX you cannot put a single dimension attribute on two axes. Translating this to your problem, you want to cross analyze all product sales against, two products (market basket analysis). So Ideally in MDX the solution should be along the lines of putting the same dimension attribute on both axis, but this is not supported.
3) In your SQL query you are only using fact internet sales, in MDX you are also using [Measures].[Sales Amount] which is not from internet sales
4) The reason why 18484 are repeated is that the set is unaware of the query context. In simple terms for row 1 the set does not know it is executing for combination 3, 560
answered Nov 22 at 18:10
MoazRub
24417
My understanding is that you want to have the sales Amount and distinct customer count for products and their subcategories within a data range. These products and their subcategories were bought by the customers who bought items from sub category 1 and 2 during the same period. For this you first took the list of customers in a temp table, then for all products bought by these customers you grouped product sales and distinct customer count.
Now there is couple of issues.
1) MDX does' not support sub query. So there is no straight forward way for you to collect the list of customers that you did in SQL.
2) In MDX you cannot put a single dimension attribute on two axes. Translating this to your problem, you want to cross analyze all product sales against, two products (market basket analysis). So Ideally in MDX the solution should be along the lines of putting the same dimension attribute on both axis, but this is not supported.
3) In your SQL query you are only using fact internet sales, in MDX you are also using [Measures].[Sales Amount] which is not from internet sales
4) The reason why 18484 are repeated is that the set is unaware of the query context. In simple terms for row 1 the set does not know it is executing for combination 3, 560
answered Nov 22 at 18:10
MoazRub
24417
answered Nov 22 at 18:10
MoazRub
24417
answered Nov 22 at 18:10
MoazRub
24417
answered Nov 22 at 18:10
MoazRub
24417
24417
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53379586%2ftranslating-a-t-sql-query-to-mdx%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
