I have defined a cube with a hieararchy of Dates such: Year is a top level then there's Month level. I have deployed the cube using SSAS VS 2022 and SQL Server 2019. But when I query it using the following MDX expression: WITH MEMBER MEASURES.COUNT AS [Measures].[Count] SELECT MEASURES.COUNT ON COLUMNS, DRILLDOWNLEVEL({[TIME_DIM].[Hierarchy].[YEAR]} ) ON ROWS FROM [CUBE1] I get results such as Year Month Count 2020 1 20 #some rows such as this one are duplicates 2020 1 20 #some rows such as this one are duplicates ... ... ... What could be the reason, did I miss defining a Level in a hieararchy in SSAS ?

This question hurts my brain to talk about. Hopefully I can explain it properly. I have the following calculation defined in my cube: CREATE MEMBER CURRENTCUBE.[Measures].[52 Week Comp Net Sales Total] AS SUM( {[Dim Fiscal Weeks].[Week Id].CurrentMember.Lag(51) : [Dim Fiscal Weeks]. [Week Id].CurrentMember} ,[Measures].[Comp Net Sales]), FORMAT_STRING = "#,##0.00;-#,##0.00", VISIBLE = 1 , DISPLAY_FOLDER = 'Sales / Trans / Units'; This *ought* to give me a 52-week trailing sum of sales, and it does... but ONLY when the cube is expanded to a weekly view in Excel. When it's rolled up to a Quarterly or YTD view, then it displays the sum of sales that occurred in the rolled-up-period *only.* Here is a screen shot explaining what I mean: What I don't understand is why the Quarter and Period (month) roll-ups don't include a full-year's worth of data. If a year's worth of sales trailing back from 1/1/2005 was 954,000, it doesn't make sense that Quarter 1, 2005 shows a year's worth of sales is only 252,000. Can someone help me understand what I'm seeing? Have I done something wrong? How do I write a rolling average calculation that is accurate even when rolled up?

I am trying to create a new linked server to an Analysis Services database and I would like to use this linked server to import data into a SQL Server table. example: SELECT * into raw.example FROM openquery( Test1, 'MDX CODE/etc etc' Below is a screenshot of what I have done (just putting in examples/test names instead of the names I need to use). The data source is pointing to a valid server name, which I know I have access to. After I click "ok" to create this linked server this message comes up: >The linked server has been created but failed a connection test. Do you want to keep the linked server? > >OLE DB provider 'MSOLAP' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode. (Microsoft SQL Server, Error: 7308) I am not sure why it isn't connecting. Is it a server issue where I have to ask for permission to access it, or do I somehow need to revert away from the provider being configured to run in a single-threaded apartment mode? I am doing this on my local SQL Server Express Edition machine, whereas before my script and OPENQUERY/MDX query worked on a remote desktop server etc. I have looked at other posts but those solutions do not solve my problem. If it matters, the MDX I'm trying to execute was generated by XLCubed.

I've performed an in place upgrade of Windows 2012 to 2019 and SQL Server from 2016 to 2019 on my Dev server. Since the upgrade we have found one *process* related to financial forecasting has slowed from 20 minutes to 35-40 minutes. Which is considered an unacceptable loss of performance. The core of this process is a stored procedure which works through iterations of recursive queries including a Multidimensional OLAP cube. The stored proc prepares an MDX query, then queries OLAP via a Linked server to a cube on the same server. The query returns the results to SQL Server and inserts the results in a table, which is then re-processed by the OLAP Cube. (The Cube uses ROLAP to update between iterations.) Depending on how many years data is being processed (between 1 & 10) and the options selected, it could run through a step as described above 300-1500 times. As far as I can tell SQL Server and SSAS Queries are running just as fast, yet the overall process is slower. What have I done: I ran SQL benchmark tests using Hammer Db. Comparing my Dev and Test servers. Win 2012 vs 2019 & SQL 2016 vs 2019. Both have 4 CPU's & 64Gb RAM with the same drive layout running on the same VMWare Host and SAN. The test returned comparible results. If anything Dev was marginally faster. Timed results has me focussing on the SQL-SSAS-SQL loop. I created a test stored proc to run on on my Dev and Test servers. The SP prepares a simple query, hits olap, returns the result and inserts it into table. It does this 5 times. Not sure if it's improtant but i'll share it: CREATE TABLE [dbo].[Loggy]( [Idx] [int] IDENTITY(1,1) NOT NULL, [RunDT] [datetime] NULL, [Measure] [varchar](255) NULL, [Cnt] [int] NULL, [Min] [float] NULL, [Max] [float] NULL, [DT] [datetime] NULL, CONSTRAINT [PK_Loggy] PRIMARY KEY CLUSTERED ( [Idx] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO CREATE PROCEDURE [dbo].[Loopy] AS BEGIN SET NOCOUNT ON; --I Do... DECLARE @RunDt as datetime, @mdx_query as varchar(max), @open_query as nvarchar(max), @linked_server as varchar(max) = 'LNK_EBICUBE' , @Counter INT SET @Counter=1 SET @RunDt = (Select GETDATE()) -- Prep MDX query SET @mdx_query = ' SELECT NON EMPTY { [Measures].[Count of Values], [Measures].[Minimum Value], [Measures].[Maximum Value]} ON COLUMNS, NON EMPTY { [Data Source].[Data Source Name].[Data Source Name].[Water Level] } ON ROWS FROM ( SELECT ( { [Site].[Site Name].&[Waipaoa River at Kanakanaia] } ) ON COLUMNS FROM [Environmental Data]) ' -- Prep query for inserting to log table SET @open_query = ' INSERT INTO LOGGY ([Measure], [Cnt] , [Min] , [Max] ) SELECT * FROM OpenQuery(' + @linked_server + ',''' + @mdx_query + ''')' -- Create a while loop.. Run this 5 times. WHILE ( @Counter <= 5) BEGIN -- Log the start of the execution. INSERT INTO LOGGY ([RunDT], [Measure], [Cnt], [DT] ) VALUES (@RunDt, 'Start', @Counter ,Getdate()) --Execute open query EXECUTE sp_executesql @open_query -- Log the end of the execution. INSERT INTO LOGGY ([RunDT], [Measure], [Cnt], [DT] ) VALUES (@RunDt, 'Stop', @Counter ,Getdate()) SET @Counter = @Counter + 1 END END GO Using an extended events trace I have found each OLAP query seems about the same or even faster than before. But the overall process is slower. The image above shows total time (start to finish) in milliseceonds running the same test SP on 2 servers, querying the same data in matching cubes. Note: the test above was querying only and not processing a cube. This was only done to try and isolate and identify delays. What I need to do is identify the causes of the delays. My boss wants an explanation. Cheers Pete Edit: I need to rethink this: It's not just one query it's dozens. Hence providing an individual Query plan isn't going to cut it.

I have a query like

SELECT column1, column2 FROM OPENQUERY(AnalysisServerName, 'MDX QUERY ...').

It is inside a stored procedure. A MDX query is dynamically built in it and the length of the query much longer than 8000 characters (can reach up to 400 000 signs). The MDX query returns something about 200 columns and I need only some of them. I am not able to reduce the amount because of dimensions complexity. So, I have to write results to a temporary table or directly SELECT them as the result of the procedure. I decided to SELECT them. But, when I try to omit the limitation of 8000 characters with EXEC OPENQUERY(AnalysisServerName, 'MDX QUERY ...') AT AnalysisServerName, I can't SELECT or even save them to a temporary table as it happens inside the stored procedure and nesting is not allowed (following [this thread](https://stackoverflow.com/q/1492411/13828439])) . I wish I can do

SELECT column1, column2 FROM (EXEC OPENQUERY(AnalysisServerName, 'MDX Query') AT AnalysisServerName)

Even if I can execute a very long MDX query directly on Analysis Server

DECLARE @myStatement VARCHAR(MAX)
SET @myStatement = 'OPENQUERY(AnalysisServerName, 'MDX Query')'

EXECUTE (@myStatement) AT AnalysisServerName

I can't do anything with the results in my stored procedure, because

DECLARE @myStatement VARCHAR(MAX)
SET @myStatement = 'SELECT column1, column2 FROM OPENQUERY(AnalysisServerName, 'MDX Query')'

EXECUTE (@myStatement) AT AnalysisServerName

is not allowed. --- So what can I do in such situation? I need to - query Analysis Service with OPENQUERY, - handle the query of length greater than 8000, - save the results to a temporary table or select them directly.

The 1st 9 calculated members in my script below define start and end date strings (e.g. 'StartDateStr' and 'EndDateStr'), that I then parse into a StrToMember() function within my SELECT command, but it throws the following error: - Query (27, 22) The Dimension '[StartDateStr]' was not found in the cube when the string, [StartDateStr], was parsed. These calculated members resolve as follows: - StartDateStr = [Date].[Day].[Day].&[2020-07-22T00:00:00] - EndDateStr = [Date].[Day].[Day].&[2021-07-20T00:00:00] If I place these strings directly in my SELECT command (which compromises the point of them being dynamic parameters), the query works fine. I've tried parsing the calculated members into my SELECT command without the StrToMember() function also, but it throws the same error. I've also tried renaming my calculated members (e.g. '[Date].[Day].[Day].StartDateStr') and only parsing the variable (member) portion of the dimension, hierarchy, and level reference. Can I not use the StrToMember() function this way, or parse calculated members like this, or does my script have some other syntactic issue I'm missing? I've hit a bit of a dead-end after an extensive Google search, so would greatly appreciate any help. Thanks

WITH
    MEMBER TodayStr AS FORMAT(NOW(),'yyyy-MM-dd')
    MEMBER TodayDate AS CDATE(CSTR(LEFT(TodayStr,4)+'-'+LEFT(RIGHT(TodayStr,5),2)+'-'+RIGHT(TodayStr,2)))
    MEMBER No1 AS WEEKDAY(TodayDate,1) + 3
    MEMBER No2 AS 7
    MEMBER Mod AS No1 - INT(No1 / No2) * No2
    MEMBER EndDate AS IIF(WEEKDAY(TodayDate) = 4,TodayDate - Mod - 8,TodayDate - Mod - 1)
    MEMBER EndDateStr AS '[Date].[Day].[Day].&['+RIGHT(LEFT(EndDate,10),4)+'-'+RIGHT(LEFT(EndDate,5),2)+'-'+LEFT(EndDate,2)+'T00:00:00]'
    MEMBER StartDate AS EndDate - (7 * 52) + 1
    MEMBER StartDateStr AS '[Date].[Day].[Day].&['+RIGHT(LEFT(StartDate,10),4)+'-'+RIGHT(LEFT(StartDate,5),2)+'-'+LEFT(StartDate,2)+'T00:00:00]'
    MEMBER [Measures].[FilteredHrs] AS
        IIF([Measures].[Roster Actual Sum Hours Nett] = 24, [Measures].[Roster Actual Sum Hours Nett], NULL)
SELECT 
    NON EMPTY 
        {[Measures].[FilteredHrs]} ON COLUMNS,
    NON EMPTY 
        {
                [Staff].[StaffNumber].[StaffNumber].ALLMEMBERS*
                [Date].[Roster Week].[Roster Week].ALLMEMBERS*
                [Date].[Day].[Day].ALLMEMBERS*
                [Pay Type].[Pay Type].[Pay Type].ALLMEMBERS
        }
     ON ROWS
FROM 
(
    SELECT
        {STRTOMEMBER(StartDateStr):STRTOMEMBER(EndDateStr)} ON COLUMNS
    FROM [Model]
)

Hi everyone hope you're doing well I'm using Aggregate function to aggregate the value of my measure for two specific years as you can see below : select aggregate ({[DimTime].[Year].& ,[DimTime].[Year].& },[Measures].[IndentCount]) on 0 from [Procurement] according to the fact that aggregate function accepts two parameters : Aggregate (Set_expression , Numeric_Expression) What is wrong with my MDX query? I get this error : The Axis0 function expects a tuple set expression for the argument. A string or numeric expression was used. Thanks in advance

I'm trying to calculate a variety of statistical calculation across a range of data such as Standard Deviation and Average. The data comes from many different "Data sources". These are are held in a Dimension. I tried using STDDEVP function as shown in this example which operates on a measure. But the results were wrong. I assume it's wrong because it's working on totals rather than raw data. [Sum of Values] is a standard aggregate measure summing the data in the fact table. Following the link supplied by Greg I am now using this: CREATE MEMBER CURRENTCUBE.[Measures].[Standard Deviation] AS (([Measures].[Sum of Values]^2 - (([Measures].[Sum of Values]^2)/[Measures].[Count of Values])/[Measures].[Count of Values])^0.5), FORMAT_STRING = "#,##0.00;-#,##0.00", VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Fact TS Data'; Which gives me this: Which still isn't correct. Using SQL If I calculate STDDEV for Air Temp with the following query: SELECT stdevp([Value]) FROM [EnvironmentalDataMart].[DDS_HILLTOP].[factTimeSeries] where [DETL_DataSourceSurrogateKey] = 78 I get: 6.13770334742149 How do to get the MDX query to calculate a STDDEV per Datasource in my Dimension? I've also tried using a Measure with "No aggregation" but it didn't return anything.

I have Account and Customer dimensions in the cube that are connected to the same measure groups (there are about 15 - 20 measure groups in the cube). When I run XMLA command to process update these two dimensions, like this: My Database Dim Customer ProcessUpdate UseExisting in the case of Account dimension it finishes in a couple of minutes because it doesn't trigger processing of all partitions of all measure groups. ***But*** in the case of Customer dimension it triggers processing of all partitions of all measure groups, so process update of this dimension lasts longer then full processing of entire cube. I am not sure what can be the reason from which the dimension will trigger all this processing in the case of one dimension and not in the case of the other. For both dimensions Process affected objects is set to Do not process. Where should I look, what to check, can I somehow prevent this reprocessing happen? Thanks!

Can anyone explain or show me example(s) what MDX can do and SQL can not do on same database? please...

An MDX (a language dealing with multidimensional data, in particularly dealing with the cube metaphor) statement can be translated to an SQL (a well-known language for the relational model) statement - Mondrian is one example solution which provides such translation. I was wondering if there are any available solutions to provide a translation in the other direction, i.e. from SQL to MDX. If not, what would be involved in creating such a functionality?

Hope you're doing well I've written an MDX query as you can see below : with member [Measures].[TOTAL] AS SUM(Periodstodate([DimTime].[Year].[(All)] ,[DimTime].[Year].currentmember) ,[Measures].[IndentCount]) Select {[Measures].[IndentCount],[Measures].[TOTAL]} having [Measures].[IndentCount] > 0 on 0 , non empty [DimTime].[Year].[Year] on 1 from [Procurement] The problem is despite using having [Measures].[IndentCount] > 0 I still see the null value for my measure as you can see below : . I want to know what the problem would be??? I have this problem even when I use Filter function Thanks in advance

How do I compare Data in T-SQL DataWarehouse to SSAS Cube? I would like to conduct this from SSMS (SQL Server Studio Management Studio). Example Select CustomerName from dbo.DimCustomer where CustomerId = 5 SHOULD EQUAL SELECT [Customer].[CustomerName].Members ON ROWS FROM [DimCustomer] where [Customer].[CustomerId]. https://xzwang.wordpress.com/2013/06/17/validatingcubesvsrelational/ Following blog recommends OPENROWSET. Is there any other method to validate data in same environment rather than OPENROWSET in SSMS? Otherwise, team will utilize this or may research Powershell or C#. select cast("[Customer].[Customer].[Customer].[Email Address]" as nvarchar(50)) EmailAddress, "[Measures].[Internet Sales Amount]" SalesAmount from openrowset('MSOLAP', 'datasource=localhost;Initial Catalog=AdventureWorksDW2012Multidimensional-EE;', ' SELECT NON EMPTY { [Measures].[Internet Sales Amount] } ON COLUMNS, NON EMPTY { ([Customer].[Customer].[Customer].ALLMEMBERS ) } DIMENSION PROPERTIES [Customer].[Customer].[Email Address] ON ROWS FROM [Adventure Works] ')

I am trying to build an SSRS report with SSAS as the data source. It has 4 cascading parameters. I used the SSRS Query designer to generate MDX because I am not very familiar with Cubes/MDX. It works but takes too long to render the report (3 minutes or more in both Visual Studio and on the Reporting Server). I also tried to generate MDX using Excel OLAP Pivot table extensions and modified the MDX to use cascading parameters, but the performance isn't getting any better. If I use SQL queries and SQL server as the data source for the same report, I get the output in a few seconds. But implementing this using cubes should make this scale much better. Does anyone know what steps I can take to improve the performance of this report? This is the MDX from SSRS query designer - SELECT NON EMPTY { [Measures].[FactCount], [Measures].[Rate], [Measures].[Benchmark1], [Measures].[Benchmark2] } ON COLUMNS, NON EMPTY {{[DimOrg].[DivisionName].[All]} * {[DimMeasure].[MeasureName].[All]} * {[DimOrg].[FacilityName].[All]} * {[DimLoadDate].[RunDate].[All]} * [DimOrg].[DepartmentName].[DepartmentName].ALLMEMBERS, {[DimOrg].[DivisionName].[All]} * {[DimMeasure].[MeasureName].[All]} * {[DimOrg].[FacilityName].[All]} * {[DimLoadDate].[RunDate].[All]} * {[DimOrg].[DepartmentName].[All]}, ([DimOrg].[DivisionName].[DivisionName].ALLMEMBERS * [DimMeasure].[MeasureName].[MeasureName].ALLMEMBERS * [DimOrg].[FacilityName].[FacilityName].ALLMEMBERS * [DimLoadDate].[RunDate].[RunDate].ALLMEMBERS * [DimOrg].[DepartmentName].[DepartmentName].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DimOrgFacilityName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimEmployeeCategory, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimOrgDivisionName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [DimLoadDate].[SomeFlag].&[True] } ) ON COLUMNS FROM ( SELECT ( { [DimLoadDate].[someColumn].&[SomeValue] } ) ON COLUMNS FROM ( SELECT ( { [DimMeasure].[MeasureName].&[someValue1], [DimMeasure].[MeasureName].&[someValue2] } ) ON COLUMNS FROM [DW]))))))) WHERE ( [DimLoadDate].[someColumn].&[somevalue], [DimLoadDate].[someFlag].&[True], IIF( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED).Count = 1, STRTOSET(@DimLoadDateLoadDate, CONSTRAINED), [DimLoadDate].[LoadDate].currentmember ), IIF( STRTOSET(@DimEmployeeCategory, CONSTRAINED).Count = 1, STRTOSET(@DimEmployeeCategory, CONSTRAINED), [DimEmployee].[Category].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS I built another SSRS report using cubes with the same tables with a higher granularity - used 3 parameters instead of 4 and that worked faster (only a few seconds) for the report generation. Here is the MDX of that - SELECT NON EMPTY { [Measures].[FactCount], [Measures].[Rate], [Measures].[Benchmark1], [Measures].[Benchmark2] } ON COLUMNS, NON EMPTY {{[DimOrg].[DivisionName].[All]} * {[DimMeasure].[MeasureName].[All]} * [DimOrg].[FacilityName].[FacilityName].ALLMEMBERS * {[DimLoadDate].[RunDate].[All]}, {[DimOrg].[DivisionName].[All]} * {[DimMeasure].[MeasureName].[All]} * {[DimOrg].[FacilityName].[All]} * {[DimLoadDate].[RunDate].[All]}, ([DimOrg].[DivisionName].[DivisionName].ALLMEMBERS *[DimMeasure].[MeasureName].[MeasureName].ALLMEMBERS * [DimOrg].[FacilityName].[FacilityName].ALLMEMBERS * [DimLoadDate].[RunDate].[RunDate].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_VALUE, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( -{ [DimOrg].[FacilityName].&[someValue] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimEmployeeCategory, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimOrgDivisionName, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [DimLoadDate].[SomeFlag].&[True] } ) ON COLUMNS FROM ( SELECT ( { [DimLoadDate].[someColumn].&[SomeValue] } ) ON COLUMNS FROM ( SELECT ( { [DimMeasure].[MeasureName].&[someValue1], [DimMeasure].[MeasureName].&[someValue2] } ) ON COLUMNS FROM [DW]))))))) WHERE ( [DimLoadDate].[someColumn].&[somevalue], [DimLoadDate].[someFlag].&[True], IIF( STRTOSET(@DimLoadDateLoadDate, CONSTRAINED).Count = 1, STRTOSET(@DimLoadDateLoadDate, CONSTRAINED), [DimLoadDate].[LoadDate].currentmember ), IIF( STRTOSET(@DimEmployeeCategory, CONSTRAINED).Count = 1, STRTOSET(@DimEmployeeCategory, CONSTRAINED), [DimEmployee].[Category].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS Thanks, Gopika

I've been asked to audit the Connection strings used by our SSAS OLAP cubes and where they are *sourcing data from*. I can view the properties manually and can even script them: > Provider=SQLNCLI11.1;Data Source=DEV-SERVER;Integrated > Security=SSPI;Initial Catalog=EnviroDataMart But I can't figure out how to query them. Can this be done with SQL or MDX?

My apologies if this is not the correct forum to be asking this in. I am having trouble finding information regarding the "Optimize Response" connection string attribute. The most promising source of information I have found is [here](https://learn.microsoft.com/en-us/sql/analysis-services/instances/connection-string-properties-analysis-services?view=sql-server-2017) , but it doesn't really make clear to me what the settings are, or how it will really impact an MDX query on the server side. There are two cases for optimization? One is to use the "NormalTupleSet", which I can't even find what that is anywhere. The other is for when the slicers are empty. What do each of these really do? Would a client use the second option for empty slicers, when they are instead passing a subquery to the FROM clause? Thank you for any help anyone can give me in this regard, especially if you can point me to accessible resources on the topic.

I am new to SSAS. Here's a cube which is rebuilt by a SSIS package everyday but it seems to cause duplication. We manually 'Process' the cube using the GUI, it then updates this table with the correct date and time and the data is correct after we do this: SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES However, we have been trying to automate the 'Process' at cube level and have added this code to a SQL Agent Job which runs as type = SQL Server Analysis Services Command and as the SQL Server Agent Service Account: DBNAME CUBENAME ProcessFull UseExisting This does NOT fix the cube data and also doesn't update the data in table: SELECT CUBE_NAME, LAST_DATA_UPDATE FROM $System.MDSCHEMA_CUBES Is there anything else I can check or do to get the MDX query to work? I've also used the same code in an SSMS window to process the dimensions, then the measure group and then the ProcessFull on the Cube. To no avail. Any help will be appreciated.

As part of my cube definition I am creating a dynamic set of all claim status' minus the PEND status. This has worked great for years, but now we have someone who does not have the PEND status which is resulting in an error when opening the cube. I would like to create a set that is all Claim status minus PEND _if it exists_. The error I am getting is "The level '&['PEND]' object was not found in the cube when the string, '[Claim].[Claim Status].%[PEND], was parsed." CREATE DYNAMIC SET CURRENTCUBE.[Non Pending Claims] AS [Claim].[Claim Status].[All Claims].Children - [Claim].[Claim Status].&[PEND]; I tried using except without luck. CREATE DYNAMIC SET CURRENTCUBE.[Non Pending Claims] AS Except ([Claim].[Claim Status].[All Claims].Children, [Claim].[Claim Status].&[PEND]); Even tried Except ([Claim].[Claim Status].[All Claims].Children, StrToSet("[Claim].[Claim Status].&[PEND]")) Does anyone know how to safely tell if a member exists then exclude it?

I am trying to help a contractor implement a cube in Multi-Dimensional SSAS 2017 and do not have very much experience in MDX. I have a table that looks something like this: The goal is to only include rows in the query that have the lowest [seq] per each combo of [Part], [CustomerID], and PersonID after filters have been applied, and then sum the value column. So if no filters are applied, only rows 1 and 3 should be returned and the sum of the value column should be 2. But if the user filters data for only the month of June, only rows 2 and 3 should be returned and the sum of the value column should be 1. We accomplished this in SQL like this: SELECT SUM(Value) FROM (SELECT * ,ROW_NUMBER() OVER (PARTITION BY CustomerID,PersonID, Part ORDER BY SEQ asc) AS Seq FROM Table WHERE DATEKeY BETWEEN @StartDate AND @EndDate )A WHERE Seq=1; But the contractor is having big problems with performance of the MDX rank function. Not having very much experience with MDX, I am having a hard time knowing what alternatives there are that will perform well and the contractor has not provided any alternatives that match our business need. Another approach I came up with is this: SELECT SUM(Value) FROM [Table] AS A WHERE DATEKey BETWEEN @StartDate AND @ENDDate AND CustomerID=@CustomerID AND NOT EXISTS(SELECT 1 FROM Table AS B WHERE B.Part=A.Part AND B.CustomerID=@CustomerID AND B.PersonID=A.PersonID AND B.SEQ