October 27, 2014 at 4:30 pm
Hi All,
I would like to know how to get the sum in MDX Query for the below T-sql query . I took Example of all these tables from 'AdventureWorksDW2012' Database.
select sum(SalesAmountQuota) from DimCustomer c
inner join FactInternetSales fis
on c.CustomerKey = fis.CustomerKey
inner join DimDate d
on fis.OrderDateKey = d.DateKey
inner join FactSalesQuota fsq
on fsq.DateKey = d.DateKey
inner join DimEmployee e
on e.EmployeeKey = fsq.EmployeeKey
where c.CustomerKey = 11003
Table - DimCustomer
CustomerKeyGeographyKeyCustomerAlternateKeyTitleFirstNameMiddleNameLastName
------------------------------------------------------------------------------------------------------------------------------------------------
1100311AW00011003NULLChristyNULLZhu
Table - FactInternetSales
ProductKeyOrderDateKeyDueDateKeyShipDateKeyCustomerKey
----------------------------------------------------------------------------------------------------
34620050701200507132005070811003
34420051001200510132005100811003
36120070709200707212007071611003
47820070709200707212007071611003
47720070709200707212007071611003
22520070709200707212007071611003
56420071111200711232007111811003
54120071111200711232007111811003
53020071111200711232007111811003
48020071111200711232007111811003
Table - DimDate
DateKeyFullDateAlternateKeyDayNumberOfWeek
---------------------------------------------------------------------
200507012005-07-016
200510012005-10-017
Table - FactSalesQuota
SalesQuotaKeyEmployeeKeyDateKeyCalendarYearCalendarQuarterSalesAmountQuota
-----------------------------------------------------------------------------------------------------------------------
1272200507012005328000.00
22812005070120053367000.00
32822005070120053637000.00
42832005070120053565000.00
52842005070120053244000.00
62852005070120053669000.00
72862005100120053165000.00
82872005070120053460000.00
92882005070120053525000.00
102892005070120053226000.00
1127220051001200547000.00
122812005100120054556000.00
132822005100120054781000.00
142832005100120054872000.00
152842005100120054356000.00
162852005100120054917000.00
172862005100120054469000.00
182872005100120054549000.00
192882005100120054767000.00
202892005100120054353000.00
Table - DimEmployeed
EmployeeKeyParentEmployeeKey
-----------------------------------------------------------------------------------------------------
281272
282272
283272
284272
285272
286272
287272
288272
289272
293272
I want to write a mdx query to get my total as 9513000 from cube. When I created a cube with many to many relationship, I get below result
SELECT NON EMPTY { [Measures].[Sales Amount Quota] } ON COLUMNS,
NON EMPTY { ([Dim Customer].[Customer Key].[Customer Key].ALLMEMBERS * [Dim Date].[Date Key].[Date Key].ALLMEMBERS ) }
ON ROWS FROM (
SELECT ( { [Dim Customer].[Customer Key].&[11003] } ) ON COLUMNS FROM [AWDW20112withManytoMany])
Result -
Sales Amount Quota
--------------------------------------------
11003200507013721000
11003200510015792000
But, how to get value of 9513000 for customer key -11003 . Thought it easy for me in tsql but mdx is getting tough for me. Any help on this would be really great.
November 13, 2014 at 12:18 am
It is :
SELECT [Measures].[Sales Amount Quota] ON COLUMNS,
[Customer].[Customer Geography].[Customer].&[11003] ON ROWS
FROM [Adventure Works]
You don't need to specify all Dimensions as others allmembers wiil be taken in account by default.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply