This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(105.60000000000001, 89%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ESM%3C/text%3E%3C/svg%3E)
I am getting a wrong result of below mdx query.
WITH SET [#DataSet#] as 'NonEmpty({[Store.State_Size].[All].Children})' SET [#StorePartSet#] as '{[Store.Store].[All].[Boston Region].[CT], [Store.Store].[All].[Boston Region].[MA], [Store.Store].[All].[Boston Region].[RI]}' MEMBER [Store.Store].[#Part#] as 'Aggregate({[Store.Store].[All].[Boston Region].[CT], [Store.Store].[All].[Boston Region].[MA], [Store.Store].[All].[Boston Region].[RI]})' SET [#TimePartSet#] as '{[Time.Time].[All].[2005].[20051].[200501].[20050101], [Time.Time].[All].[2005].[20051].[200501].[20050102], [Time.Time].[All].[2005].[20051].[200501].[20050103], [Time.Time].[All].[2005].[20051].[200501].[20050104], [Time.Time].[All].[2005].[20051].[200501].[20050105]}' MEMBER [Time.Time].[#Part#] as 'Aggregate({[Time.Time].[All].[2005].[20051].[200501].[20050101], [Time.Time].[All].[2005].[20051].[200501].[20050102], [Time.Time].[All].[2005].[20051].[200501].[20050103], [Time.Time].[All].[2005].[20051].[200501].[20050104], [Time.Time].[All].[2005].[20051].[200501].[20050105]})' SELECT {[Measures].[SALES_VOLUME]} on columns, NON EMPTY Hierarchize({[#DataSet#]}) on rows FROM [Sales_Transactions] WHERE ([Product.Product].[All].[Clothes Dryers], [Store.Store].[#Part#], [Time.Time].[#Part#])
Here I am filtering store at State level i.e MA, RI and CT but in my result its showing sales volume of all states under the Boston Region.
Important thing to note here is that the State_Size is an attribute defined at State level.
Store Dimension
<Dimension xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:ddl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns:ddl2_2="http://schemas.microsoft.com/analysisservices/2003/engine/2/2" xmlns:ddl100_100="http://schemas.microsoft.com/analysisservices/2008/engine/100/100" xmlns:ddl200="http://schemas.microsoft.com/analysisservices/2010/engine/200" xmlns:ddl200_200="http://schemas.microsoft.com/analysisservices/2010/engine/200/200" xmlns:ddl300="http://schemas.microsoft.com/analysisservices/2011/engine/300" xmlns:ddl300_300="http://schemas.microsoft.com/analysisservices/2011/engine/300/300" xmlns:ddl400="http://schemas.microsoft.com/analysisservices/2012/engine/400" xmlns:ddl400_400="http://schemas.microsoft.com/analysisservices/2012/engine/400/400" xmlns:ddl500="http://schemas.microsoft.com/analysisservices/2013/engine/500" xmlns:ddl500_500="http://schemas.microsoft.com/analysisservices/2013/engine/500/500">
<ID>Store</ID>
<Name>Store</Name>
<Source xsi:type="DataSourceViewBinding">
<DataSourceViewID>PPSS_AS</DataSourceViewID>
</Source>
<UnknownMember>Hidden</UnknownMember>
<Language>1033</Language>
<Collation>Latin1_General_CI_AS</Collation>
<UnknownMemberName>Unknown</UnknownMemberName>
<Attributes>
<Attribute>
<ID>Region_Attribute</ID>
<Name>Region_Attribute</Name>
<KeyColumns>
<KeyColumn>
<DataType>WChar</DataType>
<DataSize>255</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>PD_STORE</TableID>
<ColumnID>REGION</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize>255</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>PD_STORE</TableID>
<ColumnID>REGION</ColumnID>
</Source>
</NameColumn>
</Attribute>
<Attribute>
<ID>State_Attribute</ID>
<Name>State_Attribute</Name>
<KeyColumns>
<KeyColumn>
<DataType>WChar</DataType>
<DataSize>255</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>PD_STORE</TableID>
<ColumnID>STATE</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize>255</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>PD_STORE</TableID>
<ColumnID>STATE</ColumnID>
</Source>
</NameColumn>
<AttributeRelationships>
<AttributeRelationship>
<AttributeID>Region_Attribute</AttributeID>
<Name>Region_Attribute</Name>
</AttributeRelationship>
<AttributeRelationship>
<AttributeID>State_Size_Attribute</AttributeID>
<Name>State_Size</Name>
</AttributeRelationship>
</AttributeRelationships>
</Attribute>
<Attribute>
<ID>State_Size_Attribute</ID>
<Name>State_Size_Attribute</Name>
<KeyColumns>
<KeyColumn>
<DataType>WChar</DataType>
<DataSize>255</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>PD_STORE</TableID>
<ColumnID>STATE_SIZE</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize>255</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>PD_STORE</TableID>
<ColumnID>STATE_SIZE</ColumnID>
</Source>
</NameColumn>
</Attribute>
<Attribute>
<ID>City_Attribute</ID>
<Name>City_Attribute</Name>
<KeyColumns>
<KeyColumn>
<DataType>WChar</DataType>
<DataSize>255</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>PD_STORE</TableID>
<ColumnID>CITY</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize>255</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>PD_STORE</TableID>
<ColumnID>CITY</ColumnID>
</Source>
</NameColumn>
<AttributeRelationships>
<AttributeRelationship>
<AttributeID>State_Attribute</AttributeID>
<Name>State_Attribute</Name>
</AttributeRelationship>
</AttributeRelationships>
</Attribute>
<Attribute>
<ID>Store_Attribute</ID>
<Name>Store_Attribute</Name>
<Usage>Key</Usage>
<KeyColumns>
<KeyColumn>
<DataType>WChar</DataType>
<DataSize>256</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>PD_STORE</TableID>
<ColumnID>CUSTOMER_STORE_ID</ColumnID>
</Source>
</KeyColumn>
</KeyColumns>
<NameColumn>
<DataType>WChar</DataType>
<DataSize>256</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>PD_STORE</TableID>
<ColumnID>CUSTOMER_STORE_ID</ColumnID>
</Source>
</NameColumn>
<Translations>
<Translation>
<Language>1033</Language>
<CaptionColumn>
<DataType>WChar</DataType>
<DataSize>256</DataSize>
<Source xsi:type="ColumnBinding">
<TableID>PD_STORE</TableID>
<ColumnID>STORE</ColumnID>
</Source>
</CaptionColumn>
</Translation>
</Translations>
<AttributeRelationships>
<AttributeRelationship>
<AttributeID>City_Attribute</AttributeID>
<Name>City_Attribute</Name>
</AttributeRelationship>
</AttributeRelationships>
</Attribute>
</Attributes>
<Hierarchies>
<Hierarchy>
<ID>Store</ID>
<Name>Store</Name>
<AllMemberName>All</AllMemberName>
<Levels>
<Level>
<ID>Region</ID>
<Name>Region</Name>
<SourceAttributeID>Region_Attribute</SourceAttributeID>
</Level>
<Level>
<ID>State</ID>
<Name>State</Name>
<SourceAttributeID>State_Attribute</SourceAttributeID>
</Level>
<Level>
<ID>City</ID>
<Name>City</Name>
<SourceAttributeID>City_Attribute</SourceAttributeID>
</Level>
<Level>
<ID>Store</ID>
<Name>Store</Name>
<SourceAttributeID>Store_Attribute</SourceAttributeID>
</Level>
</Levels>
</Hierarchy>
<Hierarchy>
<ID>State_Size</ID>
<Name>State_Size</Name>
<AllMemberName>All</AllMemberName>
<Levels>
<Level>
<ID>State_Size</ID>
<Name>State_Size</Name>
<SourceAttributeID>State_Size_Attribute</SourceAttributeID>
</Level>
</Levels>
</Hierarchy>
</Hierarchies>
<ProactiveCaching>
<SilenceInterval>-PT1S</SilenceInterval>
<Latency>-PT1S</Latency>
<SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
<ForceRebuildInterval>-PT1S</ForceRebuildInterval>
<Source xsi:type="ProactiveCachingInheritedBinding" />
</ProactiveCaching>
</Dimension>
Hi @Shaharyar Mahmood , welcome to Microsoft Q&A! May I ask which version of SQL Server do you used?
Hi @CarrinWu-MSFT , I am using SQL server 2019 and using multidimensional model.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(272, 47%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDG%3C/text%3E%3C/svg%3E)
I don't believe attempting to filter by a calculated member will work how you expect. Calculated members have no relationship to other members in other attribute hierarchies so the auto-exists functionality will not activate. I think a better way to write your query would be to use a sub-select with the sets directly
eg
WITH
SET [#DataSet#] as NonEmpty({[Store.State_Size].[All].Children})
SET [#StorePartSet#] as {[Store.Store].[All].[Boston Region].[CT],
[Store.Store].[All].[Boston Region].[MA],
[Store.Store].[All].[Boston Region].[RI]}
SET [#TimePartSet#] as {[Time.Time].[All].[2005].[20051].[200501].[20050101],
[Time.Time].[All].[2005].[20051].[200501].[20050102],
[Time.Time].[All].[2005].[20051].[200501].[20050103],
[Time.Time].[All].[2005].[20051].[200501].[20050104],
[Time.Time].[All].[2005].[20051].[200501].[20050105]}
SELECT
{[Measures].[SALES_VOLUME]} on columns,
NON EMPTY Hierarchize({[#DataSet#]}) on rows
FROM
(SELECT [#StorePartSet#] ON 0,
[#TimePartSet#] ON 1
FROM [Sales_Transactions] )
WHERE ([Product.Product].[All].[Clothes Dryers])
@Darren Gosbell State_Attribute has a relationship with [Store.State_Size] attribute. So I expects it to filter data based on State_Attribute.
Yes, but attribute relationships are only established between physical members based off the data loaded from your dimension table(s). Calculated members are created after the physical members are loaded. And in your specific example I can see how you might think that the attribute relationships could be used, but calculated member expressions could be anything. You could even create a calculated member in the store dimension that did not reference any store attributes eg Year = 2020. Because of this calculated members never filter other attributes using attribute relationships. You may see implied filtering due to non empty clauses, but there is no direct attribute relationship filtering going on.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 95%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
You would have gotten the filtering were you using the two corresponding sets, which, by the way, you are declaring in the query but never using.
To reiterate Darren's point, referencing a dimension member in the MDX for a calculated member that you use in a query doesn't apply this dimension member to any parts of the query other than the specific member where the reference is.
As a side note, get MDX Studio. Its MDX formatting function improves the readability of complex MDX like yours by orders of magnitude.
@Darren Gosbell the above solution solved the actual issue. But now I am facing another issue with mdx type measures that are using EXISTING function.
Formula : Sum(Existing([Store].[Store].[State]), IIf([Measures].[NET_REVENUE] <> 0 , [Measures].[NET_REVENUE],Null)
The above measure is not filtering data based on sub-query. It's ignoring the subquery filtering. Even in Analysis server designer I am getting incorrect result for this measure.
Do you know if that's an issue with EXISTING function and is there any alternative to this.
Hi @Shaharyar Mahmood ,
We checked your MDX query, it seems that there is no wrong with it. Because we don't know your model design and detailed data, you can use subquery to verified your script. If you get same results, you should check your data first.
WITH
MEMBER [Store.Store].[#Part#] as
'Aggregate({[Store.Store].[All].[Boston Region].[CT],
[Store.Store].[All].[Boston Region].[MA],
[Store.Store].[All].[Boston Region].[RI]})'
MEMBER [Time.Time].[#Part#] as
'Aggregate({[Time.Time].[All].[2005].[20051].[200501].[20050101],
[Time.Time].[All].[2005].[20051].[200501].[20050102],
[Time.Time].[All].[2005].[20051].[200501].[20050103],
[Time.Time].[All].[2005].[20051].[200501].[20050104],
[Time.Time].[All].[2005].[20051].[200501].[20050105]})'
SELECT {[Measures].[SALES_VOLUME]} on columns,
NonEmpty({[Store.State_Size].[All].Children}) on rows
FROM ( SELECT {[Product.Product].[All].[Clothes Dryers],
[Store.Store].[#Part#],
[Time.Time].[#Part#]} on 0
FROM [Sales_Transactions] );
Best regards,
Carrin
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
@CarrinWu-MSFT when I use sub-query I get the correct result.
The question here is State_Attribute ( i.e [Store.Store].[Boston Region].[CT]) has a relationship with [Store.State_Size] attribute. Shouldn't it filter based on State_Attribute?
Hi @Shaharyar Mahmood , good to know that your issue has been resolved. There have some complicated AND/OR logic in the script. For example, someone buy two type product at the same time, but the other one just buy one type product. You cannot simply put two different scenario into one WHERE clause. So you should combine these two members from the same hierarchy so that we perform logic AND. In addition, you could refer to MDX with SSAS 2012 Cookbook (page 48) to get more information.