Questions tagged [mdx]
MDX (Multi-Dimensional eXpressions) is a query language used by various OLAP servers, notably Microsoft SQL Server Analysis Services.
69
questions
0
votes
0answers
24 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 = ...
0
votes
0answers
8 views
aggregation sum and aggregation last value MDX
I have to design a datawarehouse for the reporting of the stock to date, historized on a daily basis.
I have designed datawarehouse and a fact table
I want to design a mondrian cube with aggregation ...
0
votes
0answers
14 views
SSAS : 2 linked facts, how to get number of items in dimension attribute?
In an SSAS project there is a TRANSFUSION fact, linked to a PATIENT fact. A transfusion has many attributes, and among them there is the patient who received the transfusion. A patient can receive ...
0
votes
0answers
4 views
Get the list of all Weight attribute members
I have, in SSAS, a fact PERSONS which contains the Measures [Number of PERSONS] and [Weight].
The first one has the value 844.
In order to achieve some calculus, I would like to get the list of all ...
0
votes
1answer
49 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 ...
1
vote
1answer
185 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 ...
0
votes
1answer
105 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
165 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
995 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].[...
0
votes
1answer
184 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
57 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 ...
1
vote
0answers
320 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
216 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
27 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
696 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
27 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
694 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
22 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
204 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
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 ...
3
votes
2answers
744 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
714 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
207 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
107 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 ...
3
votes
1answer
514 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
889 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
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 ...
1
vote
1answer
60 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
141 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
394 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
1answer
478 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 ...
2
votes
3answers
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 ...
2
votes
2answers
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 ...
1
vote
0answers
125 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
678 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
555 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 ...
12
votes
3answers
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 ...
2
votes
1answer
426 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
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 ...
3
votes
1answer
696 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
8k 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
764 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
110 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
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 (...
0
votes
1answer
61 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
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 ...
0
votes
0answers
482 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
322 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
859 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 ...
