Questions tagged [mdx]

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

26 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
1answer
492 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 ...
3
votes
0answers
6k 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
0answers
31 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 ...
2
votes
0answers
679 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 ...
2
votes
0answers
249 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 ...
1
vote
0answers
13 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 ...
1
vote
0answers
46 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 ...
1
vote
0answers
332 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 ...
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 ...
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 ...
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 ...
1
vote
0answers
126 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, ...
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) ...
1
vote
0answers
470 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: ...
1
vote
0answers
577 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"...
0
votes
0answers
25 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
5 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
53 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 ...
0
votes
1answer
58 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
232 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=...
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: +-----------+----------+-------------------+---------...
0
votes
0answers
484 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 ...
0
votes
0answers
90 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 ...