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

learn more… | top users | synonyms

1
vote
1answer
13 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 ...
0
votes
0answers
34 views

Handling time-zones with separate date and time dimensions

I have separate date and time dimensions. My cube stores data in UTC+0. I would like to display my measure aggregated per date in UTC-5. Here is my code: with MEMBER [Measures].[UTC-5] AS ...
0
votes
0answers
12 views

MDX query with multiple dimensions on date, including “previous business week”

What is the simplest MDX that satisfies this query: The frequency of an event during each daily 10 minute interval, comparing today and the previous business week (Mon-Fri). The output should look ...
6
votes
3answers
188 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 ...
2
votes
1answer
58 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 ...
3
votes
1answer
124 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 ...
0
votes
1answer
61 views

Filtering by many-to-many table

I'm having a hard time to understand how to filter out result of multidimensional expression. This is my database schema. And this is my data. Manufacturer ╔════╦═══════════════════╗ ║ Id ║ ...
0
votes
0answers
12 views

Need MDX help in crossjoin

So, I am developing a cube for the first time and here is the situation. Cube contains multiple dimensions and 1 measure group. There are 2 same Date dimensions - one for Booking date and one for ...
2
votes
1answer
52 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); ...
4
votes
1answer
1k 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 ...
0
votes
1answer
138 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 "...
0
votes
1answer
28 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]; ...
2
votes
1answer
136 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 (...
0
votes
1answer
44 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: +-----------+----------+-------------------+-------...
1
vote
2answers
2k 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 ...
0
votes
0answers
206 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 ...
2
votes
1answer
126 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 ...
1
vote
1answer
510 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....
0
votes
1answer
244 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 ...
0
votes
0answers
33 views

Efficient settings for linked server to SSAS database

We are using OPENQUERY scripts to run MDX from sql-server. We're experiencing efficiency issues when we first run these queries each morning. The linked server objects which link to our SSAS ...
1
vote
0answers
38 views

Datamining using holidays

My setup: I am currently running a SQLServer 2012 database paired with a SSAS. My database features sales per store at specific times. My goal: I am interested in predicting future sales (amounts) ...
0
votes
1answer
533 views

SSAS / MDX Query - Need row total

I would like to add an additional row that will display the Total order count for all the different Customers in the Customer Dimension. Below is the query that I have been trying to modify. SELECT ...
3
votes
2answers
3k views

MDX calculated member to band a measure

I am trying to add a calculated member to an MDX query which will create a set of "Age Bands" for people in my database. I have a measure which contains the age of each person. I have started with ...
1
vote
1answer
3k views

Use MDX to select Dimension-Members instead of Measure Values

Is it possible, with MDX, to select Dimension Members instead of Measure Values? Here is what I currently do (simplified): SELECT {[Time]...} ON 0, {[Gender]...} ON 1 FROM [Cube]; Result: ...
1
vote
0answers
335 views

OLAP Advanced custom filters date

How do i filter, in a Mondrian MDX Query, just the FACTS that have ocurred in a Range of Months over last 3 years? I need to make it dynamic so i can use parameters latter, so i need the following: ...
3
votes
1answer
258 views

BI - Cube Concept and MDX Query Help [closed]

In a Mondrian Schema, I have a fact table for Sales, a dimension table Time and a dimension table Client. I need to create a Saiku report where i can show how many clients joined per year. I have a ...
2
votes
1answer
1k views

Wrong aggregation of Measures in Analysis Service in combination with UNION and DESCENDANTS

Update: When I tried to provide a simple example which reproduces the issue I found the leveller in my database which introduces this issue. Therefore I did an update to this question in order to ...
2
votes
0answers
4k views

MDX: How do I combine/merge/union two Measures dimensions with calculated members?

I am trying to combine two dimensions in an MDX query. SELECT NON EMPTY ( [XXX].[YYY].children ) ON ROWS, NON EMPTY { [Measures].[Dimension1A], [Measures].[Dimension1B], [Measures].[...
2
votes
2answers
6k views

MDX Crossjoin filtering on different hierarchies from the same dimension is very slow

I have an MDX query that was written by an MDX novice that is excessively slow (the query, that is, not the MDX novice). And I am also an MDX novice. Here is the query: SELECT NON EMPTY ( [...
1
vote
0answers
309 views

Need to write MDX for time dimension in Mondrian

This is my Dimesion and Measure: <Dimension type="TimeDimension" foreignKey="ReqDateKey" name="Date"> <Hierarchy name="YQMD" hasAll="true" allMemberName="All Dates" primaryKey="date_id"...
1
vote
1answer
71 views

Leaf nodes for averages [closed]

I have the following MDX tuple calculation for my KPI in Dashboard Designer: AVG([Person].[Person].children,[Measures].[Hours]) This works perfectly when, for instance, I view it by Team name. ...
6
votes
1answer
632 views

Time as a measure

Is it possible to have times as measures in a cube? We're trying to view employee start times by day, aggregating as an average over time. Even with a No Aggregation measure type I'm getting an error ...
10
votes
1answer
1k views

MDX Query for RANK regardless of hierarchy

I'm hoping to add a calculation to my SSAS Cube (SSAS 2012) that will show the relative rank of whatever the current member is against all other members in that grouper, without specifying the ...
2
votes
0answers
200 views

MDX Block Calculation Optimisation

I have a query I'm trying to optimise. I have read an article here http://web.archive.org/web/20100823233821/http://www.biblogs.com/2008/03/30/optimizing-countfilter-expressions-in-mdx/ about ...
2
votes
1answer
2k views

MDX - Sum values greater than then a median calculated measure

I'm relatively new to SSAS / MDX. I have a cube with a calculated field which calculates the Median value for a range of prices. MEDIAN( [DimItems].[Dim Items Id].CurrentMember.Children, [Measures].[...
5
votes
1answer
842 views

Custom aggregation for non-leaf level nodes to avoid double counting

I have a fact table with the following columns: WorkerName OrderId NumberOfPackagesPerOrder Note that neither WorkerName nor OrderId is unique in this fact table. Multiple users could work on a ...
2
votes
1answer
2k views

Need to filter dimension where two attributes are the same

I've obfuscated the scenario but the general idea still applies, so if the schema seems retarded, it probably is in this example... Let's say I have a Student dimension, and in it are two attributes -...
0
votes
1answer
850 views

NULL values on MDX query (Total Column has Values)

I'm a novice with MDX, so sorry if I've missed something. (and sorry about my english too!) The following query: WITH SET [ROWMEMBERS] AS '{([BIN_SCH_INV_GRO].&[1]) , ([BIN_SCH_INV_GRO].&[...