Questions tagged [mdx]

MDX (Multi-Dimensional eXpressions) is a query language used by various OLAP servers, notably Microsoft SQL Server Analysis Services.

Filter by
Sorted by
Tagged with
5 votes
2 answers
283 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 ...
user avatar
1 vote
0 answers
22 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 ...
user avatar
1 vote
0 answers
77 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 ...
user avatar
0 votes
0 answers
26 views

MDX - AVG for two sets

I am beginner in MDX and I would like to ask you for the advice. Below I wrote the code for SUM of the last 12 months, where the data for the previous year are taken from the dimension Scenario = ...
user avatar
0 votes
1 answer
77 views

SSRS : How to give parameter values in URL Http requests?

I created a dataset in an SSRS report and this dataset uses a parameter. Here is the request: select { [Measures].[ID Distinct Count]} on COLUMNS, non empty filter({ [DIM ANESTH].[PBM ANE NOM].[PBM ...
user avatar
  • 141
1 vote
1 answer
290 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 ...
user avatar
0 votes
1 answer
151 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...
user avatar
0 votes
1 answer
198 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 -...
user avatar
  • 407
1 vote
1 answer
1k 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].[...
user avatar
  • 1,294
0 votes
1 answer
224 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] ,[...
user avatar
  • 1,294
0 votes
1 answer
65 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 ...
user avatar
1 vote
0 answers
354 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 ...
user avatar
0 votes
0 answers
274 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=...
user avatar
2 votes
0 answers
40 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 ...
user avatar
  • 21
0 votes
1 answer
805 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 ...
user avatar
  • 1
0 votes
1 answer
34 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 ...
user avatar
3 votes
2 answers
767 views

Equivalent of SQL Not Exists in MDX (or finding another way to accomplish same result)

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 ...
user avatar
1 vote
0 answers
27 views

Tier Dimension based on measure value

I have a case where i need to have a dimension based on measure value. example : the fact table is linked to date dimension through DateID, and what i want to add is another dimension for Tier, but ...
user avatar
2 votes
1 answer
233 views

What are the most relevant similarities between T-SQL and MDX?

I have used T-SQL (CTEs, windowed functions, recursive CTEs, large data, table-valued functions, APPLY, PIVOT, stored procedures, etc.), but have never queried a Microsoft SSAS cube. Are there ...
user avatar
  • 683
0 votes
1 answer
1k views

SSAS Calculated Member Conditional Count

I have a table representing Accidents and a column with Accident Severity ranging from 'Slight' to 'Fatal'. I want to create a measure counting Fatal Accidents depending on other columns on the ...
user avatar
  • 99
3 votes
2 answers
862 views

How to calculate a percentage over a dimension attribute and get correct aggregates?

We have a fact table with the weight trasported by truck/driver/day. And a dimension with the max weight per truck. We would like to have the % of weight transported, so we started with create ...
user avatar
  • 161
3 votes
1 answer
790 views

Getting formatted MDX values using OPENQUERY

Is there any way to convert a value in MDX to a string using its FORMAT_STRING? I have code similar to the following: SELECT * FROM OPENQUERY(DWDB001AS, ' WITH MEMBER [Measures].[ROH Total Sold ...
user avatar
3 votes
1 answer
212 views

Overriding a row FORMAT_STRING with a column FORMAT_STRING in MDX

Does anyone have an idea how you could override the format string on a row with a format string on a column? I have a query like the following: WITH MEMBER [Measures].[ROH Total Sold Gross] ...
user avatar
1 vote
0 answers
108 views

How to specify MDX set by property range?

I'm trying to create a named set representing the next 12 fiscal months. I have [Fiscal Month] with [Fiscal Month Ordinal] as a property, with '0' as the current period and positive and negative ...
user avatar
3 votes
1 answer
548 views

Altering SSAS writeback behavior?

I'm trying to implement a writeback measure group for required quantities by month and part number. I'm puzzled by the behavior I am seeing in Excel 2013, but I don't know if SSAS or Excel is ...
user avatar
2 votes
2 answers
1k views

MDX datetime comparison

I have a MDX calculated measure for rolling 30-day totals, and I want to mask the calculation for future members. I'm trying to use a CASE statement and the comparison does not work correctly. If the ...
user avatar
1 vote
0 answers
68 views

Incorrect hierarchy relationship while joining with parent-child tables

I have two tables with 1:M relation in my DB: GROUP (GroupId, GroupName, AccountId) USER (UserId, UserName, GroupId, AccountId) and ACCOUNT (AccountId, AccountName). I added all needed links from ...
user avatar
  • 11
1 vote
1 answer
63 views

MDX - Average for a Cube

I am trying to find the average value(val) for every month(1,2,3,4,...10) for a,b,c on the given cube below. Here is my query: select ([Measures].[VAL1],[DIMPROPERTY TIME].[TIMEDIM].[MONTH]) on ...
user avatar
3 votes
1 answer
156 views

PostgreSQL: translating user-defined calculations into executable calculation in trigger

I am looking to convert user defined machine readable string formulas into a set of queries and calculations, the result of which would then be saved in another non-normal table. The calcs would be ...
user avatar
0 votes
1 answer
426 views

STRTOMEMBER function throws error about finding a dimension [closed]

I'm really stumped here...trying to create some year-to-date measures for a Cube, with NOW() specifying the current month and day (minus a two-day lag), with year being specified by the current member ...
user avatar
4 votes
1 answer
536 views

OLAP linked server fails connection test

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 ...
user avatar
  • 41
2 votes
3 answers
1k views

SSAS How to calculate Standard Deviation

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 ...
user avatar
2 votes
2 answers
4k views

SSAS Calculated measure with SCOPE.

I'm building an SSAS OLAP cube on Meteorological data. Some of this data can be totaled (eg rainfall) and some can't (like wind direction). I want totals, but only on data sources where it makes ...
user avatar
1 vote
0 answers
136 views

SSAS 2014, Relation OLAP - Poor performance, high resource usage, what did I do wrong?

Let me start with the background, I was working on a project that needed a back end that was scalable and fast. Having worked quite a bit with SSAS, I thought I'd try SSAS's in memory solution, ...
user avatar
  • 121
2 votes
0 answers
691 views

MDX calculation of an Average Over Time with delta update on the fact table

I am having a problem creating this MDX calculation : Average Price of products over time knowing that my fact table gets price updates incrementally; In order to calculate the average price of ...
user avatar
1 vote
1 answer
639 views

MDX measure to DAX conversion

I needed a little help on working with MDX and DAX. Recently I have just started working with these two terms and am having a little trouble figuring out the differences and possible examples of how ...
user avatar
12 votes
3 answers
16k views

Good example of MDX vs SQL for analytical queries

Can anybody show me a good example of MDX's advantages over regular SQL when doing analytical queries? I would like to compare an MDX query with an SQL query that gives similar results. Wikipedia ...
user avatar
  • 739
2 votes
1 answer
493 views

Prevent SQL Job Agent from interpreting a quotation mark as apostrophe

I'm struggling with my SQL Job Agent interpreting a quotation mark " as an apostrophe '. I have a T-SQL query which works in SQL Server Management Studio but not when I insert the exact same ...
user avatar
3 votes
1 answer
5k views

MDX - Getting the last 6 month Dynamically

I have a Dim table that called DimAccounts. It is describing a User account and the dates that the account has been created. for Example: I would like to get the list of accounts that have been ...
user avatar
3 votes
1 answer
765 views

MDX Intersection - how to?

A similar question has been asked here, but I'll give a brief intro. In SQL Server there are 3 tables: Order (Id, Date); Product (Id, Name); OrderRefProduct (Id, OrderId, ProductId); OrderRefProduct ...
user avatar
4 votes
1 answer
9k views

How can I get the measures of the last month with MDX?

I would like to get the values of the measures of the previous month. That's what I have: SELECT [Measures].[Recuent distinct] ON ROWS, TAIL(NonEmptyCrossJoin([Dim Date].[Calendar].[Month ...
user avatar
  • 345
0 votes
1 answer
792 views

SSAS Calculated Field - rolling sum, for a year of days

I have a fact table storing employee occurrences incurred day over day. I need to have a running sum over the past year of an employee's life. I had, on first pass, done this on ETL, making this "...
user avatar
0 votes
1 answer
118 views

Tools to visualize adhoc multi dimentional data

With SSMS I cannot run a MDX query such as Select [Measures].[Internet Average Sales Amount] on 0, [Product].[Category].children on 1, Product.[Model Name].children on 2 From [Adventure Works]; ...
user avatar
  • 1,002
2 votes
1 answer
375 views

In MDX what is the ParallelPeriod of UnknownMember?

Lets say I have a time dimension with the levels all, year, quarter, month, day. I have some sales per month and a calculated measere [sales last year] WITH MEMBER [Measures].[sales last year] AS (...
user avatar
0 votes
1 answer
67 views

Dimension design and MDX query issue

I'm new to MDX and SSAS but trying to learn. I'm on SQL Server 2012. I have the following table setup. My dimension relationships look like this: +-----------+----------+-------------------+---------...
user avatar
2 votes
2 answers
6k views

Get distinct count on calculated member?

I'm using SSAS and created a measure group that is linked to Fact_SalesMetrics on SQL Server. In that, I have seven measures. All measures have aggregation of Sum except Sales Invoice which has ...
user avatar
0 votes
0 answers
486 views

Why is MDX Moving Average Calculation Inconsistent in Excel?

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 ...
user avatar
2 votes
1 answer
331 views

MDX query is SLOWER with warm cache

I have a MDX query that executes faster on cold cache than warm cache. If I ran the same MDX query after restarting SSAS it takes 14 seconds. All subsequent runs of the same statement then take 50 ...
user avatar
2 votes
1 answer
1k views

MDX Max, Average and StDev functions not giving correct results

I have a scenario where I have Date dimension and Time dimension along with some other dimensions. The time dimension contains only Hours since there was no need to save data at minute or second level....
user avatar
0 votes
1 answer
874 views

Helpme undestand MDX exists function

After spend almost decade in OLTP world I got the courage to try to build my first CUBE. I installed VS2012 and created Business Intelligence -> Analysis Services. I did build Data Source Views, Cube ...
user avatar
  • 2,323