Questions tagged [mdx]
MDX (Multi-Dimensional eXpressions) is a query language used by various OLAP servers, notably Microsoft SQL Server Analysis Services.
69
questions
1
vote
0
answers
15
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
0
votes
0
answers
43
views
MDX Calculation for fact value through dimension attribute
I've got a dimension [D] in my model containing an id [d_id] and an attribute [power].
And I'm having a Meassurement containing an value [prod] which is connected to the dimension over it's id.
Now I ...
- 123
5
votes
2
answers
1k
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
56
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
155
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,203
0
votes
1
answer
331
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 ...
- 141
1
vote
1
answer
589
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
216
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
293
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 -...
- 457
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,412
0
votes
1
answer
331
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,412
0
votes
1
answer
93
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
435
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
407
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=...
- 3,203
2
votes
0
answers
52
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
1k
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
68
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
3
votes
2
answers
963
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 ...
- 31
1
vote
0
answers
32
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 ...
- 11
2
votes
1
answer
276
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 ...
- 703
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 ...
- 99
3
votes
2
answers
1k
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 ...
- 161
3
votes
1
answer
936
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 ...
- 171
3
votes
1
answer
295
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] ...
- 171
1
vote
0
answers
112
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 ...
- 1,307
3
votes
1
answer
644
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 ...
- 1,307
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 ...
- 1,307
1
vote
0
answers
86
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 ...
- 11
1
vote
1
answer
84
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 ...
- 11
3
votes
1
answer
234
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
1
answer
531
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 ...
4
votes
1
answer
709
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 ...
- 41
2
votes
3
answers
2k
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 ...
- 3,203
2
votes
2
answers
5k
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 ...
- 3,203
1
vote
0
answers
163
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, ...
- 121
2
votes
0
answers
821
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
1
answer
880
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 ...
- 111
12
votes
3
answers
18k
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 ...
- 759
2
votes
1
answer
676
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 ...
- 23
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 ...
- 111
3
votes
1
answer
1k
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
1
answer
10k
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 ...
- 345
0
votes
1
answer
902
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
1
answer
122
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];
...
- 1,012
2
votes
1
answer
384
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 (...
- 105
0
votes
1
answer
76
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:
+-----------+----------+-------------------+---------...
- 101
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 ...
- 141
0
votes
1
answer
510
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 ...
- 187
2
votes
1
answer
360
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 ...
- 21
2
votes
1
answer
2k
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....
- 525