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
0
votes
1answer
20 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
1answer
25 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 -...
1
vote
1answer
22 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
vote
1answer
20 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] ,[...
0
votes
1answer
39 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 ...
0
votes
0answers
69 views

mdx tabular 2017 model, calculated member with solve order fails

In tabular 2017 (compatibility level 1400) my query gets this error: Query Error - MdxScript(Model) (3, 38) Calculation error in measure 'Fact Order'[Sum of Quantity]: The expression invokes an ...
1
vote
0answers
132 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 ...
0
votes
0answers
46 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=...
1
vote
0answers
21 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 ...
0
votes
1answer
263 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 ...
0
votes
1answer
22 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 ...
3
votes
2answers
374 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 ...
1
vote
0answers
19 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 ...
2
votes
1answer
130 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 ...
0
votes
1answer
496 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 ...
3
votes
2answers
358 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 ...
3
votes
1answer
465 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 ...
3
votes
1answer
141 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] ...
1
vote
0answers
82 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 ...
2
votes
1answer
347 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 ...
2
votes
2answers
480 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 ...
1
vote
0answers
59 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 ...
1
vote
1answer
53 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 ...
3
votes
1answer
97 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 ...
0
votes
1answer
224 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 ...
3
votes
1answer
249 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 ...
2
votes
3answers
969 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 ...
2
votes
2answers
3k 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 ...
1
vote
0answers
98 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, ...
2
votes
0answers
555 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 ...
1
vote
1answer
343 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 ...
11
votes
3answers
12k 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
253 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
2k 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 ...
3
votes
1answer
420 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 ...
4
votes
1answer
7k 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
676 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
104 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
349 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
60 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: +-----------+----------+-------------------+---------...
2
votes
2answers
5k 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
437 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
295 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
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....
0
votes
1answer
659 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
84 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
47 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
2k 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
5k 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
7k 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: ...