Questions tagged [mdx]
MDX (Multi-Dimensional eXpressions) is a query language used by various OLAP servers, notably Microsoft SQL Server Analysis Services.
67 questions
1 vote
0 answers
32 views
DrillDownLevel causing rows to be duplicates
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 ...
- 111
5 votes
2 answers
2k views
8000 character limit in OPENQUERY against Analysis Server
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 ...
- 51
1 vote
0 answers
103 views
Parsing calculated members to StrToMember() function
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 ...
- 11
1 vote
0 answers
234 views
SSAS 2019: Complex stored proc querying OLAP goes slower after upgrade
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 ...
- 3,253
1 vote
1 answer
794 views
ProcessUpdate of the dimension triggers processing of all partitions of all measure groups in the cube
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 ...
- 111
0 votes
1 answer
265 views
What MDX can and SQL Can't perform on the same datatbase [closed]
Can anyone explain or show me example(s) what MDX can do and SQL can not do on same database? please...
0 votes
1 answer
362 views
SQL to MDX translation?
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 -...
- 557
1 vote
1 answer
2k views
having 'Measure' > 0 syntax does not work in mdx query
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].[...
- 1,502
0 votes
1 answer
459 views
Facing error while working with Aggregate function in MDX (MultiDimensional-Expresson)
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].&[1396] ,[...
- 1,502
0 votes
1 answer
124 views
Compare Validate TSQL Data Warehouse to SSAS
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 ...
user172734
1 vote
0 answers
467 views
How to improve performance of SSRS report using Cubes as data source?
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 ...
- 11
0 votes
0 answers
553 views
How to query SSAS Datasource ConnectionString Properties
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....
- 3,253
2 votes
0 answers
70 views
What is the "Optimize Response" connection string attribute used for?
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 ...
- 21
0 votes
1 answer
2k views
SSAS Cube Process Manually works but MDX query doesnt work
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 ...
- 1
0 votes
1 answer
87 views
Create dynamic set conditionally excluding member if exists
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 ...
- 311