mdx

The best MDX references on the net

What is the best references/links,cheatsheets for the MDX language? Here are a few starters that I have collected. Please share yours :) http://www.gumper.com/mdx.html http://www.sqlserveranalysisservices.com/OLAPPapers/InventoryManagement%20in%20AS2005v2.htm http://www.ssas-info.com/ssas-cheat-sheets/72-cheat-sheets/880-time-mdx-cheat...

MDX: Filtering a member set by a measure's table values

I have some numbers in a fact table, and have generated a measure which use the SUM aggregator to summarize the numbers. But the problem is that I only want to sum the numbers that are higher than, say 10. I tried using a generic expression in the measure definition, and that works of course, but the problem is that I need to be able to ...

Best Way to Access MS Analysis Services Cube from Java

I want to issue MDX against a MSAS cube from a Java client. What connectivity approaches have proven to be reliable for this? ...

MDX Query/Expression not working

Hi, I am new to MDX expressions and queries and I am currently looking for something like this - I have two dates 'Date1' & 'Date2' in Fact Table as Foreign Keys to DATE dimension. I need to do a count of rows where Date1 < Date 2, keeping in account that I don't want to count NULLS. I wrote an expression something like this - WITH M...

MDX Calculating Time Between Events

I have a Cube which draws its data from 4 fact/dim tables. FactCaseEvents (EventID,CaseID,TimeID) DimEvents (EventID, EventName) DimCases (CaseID,StateID,ClientID) DimTime (TimeID,FullDate) Events would be: CaseReceived,CaseOpened,CaseClientContacted,CaseClosed DimTime holds an entry for every hour. I would like to write an ...

UNION a final ROW in MDX

Hi, I'm quite new to MDX and im having some trouble getting the following t-sql query to MDX. select distinct System from Systen where System <> 'MIS' UNION SELECT 'ALL' So far i got something like this. But i have no idea how to add that final row 'ALL'. SELECT {} ON COLUMNS, {[Concesionario].[Sistema].[Sistema].ALLMEMBE...

MDX - Sum at lowest time, then Max it out...

I have; a Time dimension (Year, Month, Day, Hour), a Product dimension (Product, Feature) and a User dimension (User) The measure I have available is: Used (Number of features that are in use) What I want to do, is to display the Max of concurrent usage. (This would be the Max of the Sum of Used for each Feature used by the same Use...

Joining fact tables in an MDX query

Hello All, I am building and Anaysis Services project using VS 2005. The goal is to analyse advertising campaigns. I have a single cube with 2 fact tables factCampaign: which contains details of what people interviewed thought of an advertising campaign factDemographics: which contains demographic information of the people interviewe...

MDX question

Hello, I am developing a cube with Analysis Services 2000 for a web application where users can register and unregister to the site. So, the "user" table has these three fields: activo (1 or 0) fechaAlta fechaBaja When the user activates his account, the application saves the "fechaAlta" and puts 1 on "activo" field. When the user ...

A simple MDX question

Hi, I am new to MDX and I know that this must be a simple question but I haven't been able to find an answer. I am modeling a a questionnaire that has questions and answers. What I am trying to achieve is to find out the number of people who gave specific answers to questions., e.g. the number of males aged between 20-25 When I run t...

MDX equivalent to SQL subqueries with aggregation

I'm new to MDX and trying to solve the following problem. Investigated calculated members, subselects, scope statements, etc but can't quite get it to do what I want. Let's say I'm trying to come up with the MDX equivalent to the following SQL query: SELECT SUM(netMarketValue) net, SUM(CASE WHEN netMarketValue > 0 THEN netMarketV...

MDX Filter

I have a cube in Analysis Services 2005 with sell quotes we make for our customers. Each quote can have many revisions. Each revisions has a date when it was made. [Quotes Data] is my Fact table I need to filter (see "true" in the query below) to get only the latest revision per quote (in bold). Quote Revision Date ...

CurrentMember (MDX) Ignores Slicer Dimension

I am using the CurrentMember function in a role's CellData expression to restrict access to a cube via a specific dimension. This is working as expected with one exception. Even if the slicer imension is used to filter out data that the role is not entitled to see, the string '#N/A' is displayed in all cells. Only after including the di...

Using MDX Parameters in Report Builder 2.0

I currently have a query that looks like this: SELECT NON EMPTY ([Measures].[TOTAL]) ON 0, NON EMPTY (([ENROLL DATE].[CALENDAR].[YEAR].[" + Parameters!EnrollDate.Value + "] * [DIM ENROLLMENT].[ENROLLMENT].[ENROLLMENT PROG].ALLMEMBERS)) ON 1 FROM (SELECT (([DIM ENROLLMENT].[ENROLLMENT].[TERMINATION REASON].[Still Enrolled])) ON 0...

MDX - TopCount plus 'Other' or 'The Rest'

I have created an MDX query which calculates the TOP 10 ZipCodes (according to my Patient Stay measure) as such: WITH MEMBER [Discharge Date].[Y-M-D].[ Aggregation] AS 'AGGREGATE( EXISTING { [Current Month] } )', SOLVE_ORDER = 0 SELECT NON EMPTY { [Measures].[Patient Stays] } ON COLUMNS, TOPCOUNT({ ORDER( HIERARCHIZE( { [Patient].[By...

Sorting and Paging in MDX Query (MS Analysis Services)

I built an MDX query, to retrieve specific articles from an OLAP Cube. Basically it returns articles below a specific article-category node that are produced by a specific manufacturer: SELECT NON EMPTY ( Hierarchize ( { DrilldownLevel ( { [T DAT Article].[Ar ID].[All] } ) } ) ) DIMENSION PROPERTIES PAR...

MDX - Filter multiple dimensions

I'm trying to form an MDX query such that it returns only the combinations of two dimensions where a measure meets a certain criteria. I thought this would be pretty straight forward using the FILTER function, i.e. SELECT NON EMPTY FILTER({[Program].[ByRegion].[Program] * [Performance Metric].[Metric].CHILDREN }, [Measures].[Point...

How can I return Level Property values in an MDX query?

I've defined a Dimension in a schema file containing multiple Levels. One of my Levels contains multiple properties, like: <Level name="MyLevel" column="MyLevelColumn" nameColumn="MyLevelName"> <Property name="Property1" column="PropertyColumn1"/> <Property name="Property2" column="PropertyColumn2"/> <Property name="Property...

Reporting Services Aggregate not working for parent-child relationships

I have a parent-child attribute relationship in an Analysis services dimension, say [Dim].[Child] is a child of [Dim].[Parent] and I have a query, that returns the set UNION( [Dim].[Parent].Members * [Dim].[Child].Members, [Dim].[Parent].[ALL] * [Dim].[Child].Members ) on axis 1 and some output on axis 0. The query works fine whe...

Dimension Member as Calculated Measure in MDX

I need to get a dimension member returned as a calculated measure. Given: Dimensions Customer {ACME, EMCA, Universal Imports, Universal Exports} Salesperson {Bob, Fred, Mary, Joe} Credit Type {Director, Manager} Measures Credited Value Value Relationships The Customer is a dimension of the facts that contain Value The Custome...