Questions tagged [mdx]
MDX (Multi-Dimensional eXpressions) is a query language used by various OLAP servers, notably Microsoft SQL Server Analysis Services.
64
questions
0
votes
1answer
29 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
43 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
63 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
26 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
76 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
170 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
62 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
22 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
329 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
429 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
21 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
138 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
564 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
383 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
502 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
164 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
93 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
376 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
505 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
98 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
251 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
272 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
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
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
106 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
574 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
352 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
13k 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
275 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
3k 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
448 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
687 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
352 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
447 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
297 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
688 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
8k 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:
...
