mdx

Parser: The end of the input was reached

I'm very new to MDX and don't know what this error "Parser: The end of the input was reached" means. I ran my code in mdx and it is pulling correct data. However, when I take my code and put it into my SSRS report, I get the above error. What am I be doing wrong? Here's my MDX: SELECT NON EMPTY { [Measures].[Cash Expend] } ON COL...

Which pentaho mondrian library to include in a Java application to have mapping MDX to SQL

I want to implement an application that provides support for MDX queries. For this purpose I would like to use one of libraries from pentaho mondrian (an open source OLTP server with the MDX interface) that transforms MDX queries into SQL of underlining database (based on a xml description), unfortunately I can not find any information w...

Data Access Layer for Analysis Services w/Dynamic MDX

We have project that uses Analysis Services as it's datasource. To try to avoid having to create 100's of queries because of all the selection options we allow, we create our mdx queries with alot of switches and string concatenation. This is our "Data Access Layer". It is a beast to manage and the smallest mistake: missing spaces, m...

Query olap mondrian (mdx,xmla) with python interface ?

Actually i'm using R + python with RPY2 to manipulate data and ggplot to create beautiful graphics.. i have some data in postgresql database, and i'm using psycopg2 to querying data. I'm starting thesis, and in the future i need OLAP cube to store my (very big) simulation data : multiple dimension, aggregation query, etc. Is there any ...

Reading SQL Analysis Services Metadata

I have a number of objects in my SSAS cube, many of which were created to be invisible. For example: CREATE MEMBER CURRENTCUBE.[Measures].[Latency Backcolor] AS CASE WHEN [Average Latency] > [Web Alert] THEN 6384849 WHEN [Average Latency] > [Web Warn] THEN 4577517 ELSE IIF ( [measures].[Query count] > NULL, 14876123, null) END, VI...

Using MDX, how to get only a few selected rows?

I have a Color dimension with many colors, but I want to show a table with just two rows (black and red). I tried this: SELECT [Color].[black] || [Color].[red] ON ROWS, {[Measures].defaultMember} ON COLUMNS from [SalesAnalysis] The result I was expecting was a table with one column and two rows. One cell for black sales, one cell f...

In MDX, calculate the sales rank of a given shop

I have an OLAP cube containing the sales count for each of my shops. Using MDX, how can I output the rank of a given shop? I am dreaming about something like below (does not work), it would return 8 if SomeShop is the 8th most-selling shop: SELECT RANK( [Shop].CHILDREN, [Shop].[SomeShop]) from [Sales] ...

MDX / Analysis Services Calculating Month To Date

I don't usually get involved with MDX if I dont have to, but.... I have a cube with simple fact table linked to a time dimension (with date,month,year columns and so on) and I want to be able for a given day of the month calculate the month to date total for that day. In SQL it would be a doddle, however I'm not sure of how to achieve ...

How to get calculated measure to rollup correctly?

I have created a calculated measure that computes a weighted ratio for the current balance of all loans that fall into various risk ratings: WITH MEMBER [MEASURES].[Weighted Ratio] AS (Measures.[Current Balance] * [Risk Rating].[Loan Rating Code].CurrentMember.member_value) / ( [Measures].[Current Balance], Ances...

MDX Rank request returning unexpected results

I wrote this fairly simple MDX request to get the rank by sales count of each gender: WITH MEMBER [Measures].[rank] AS RANK( [Gender].CurrentMember, Order( [Gender].Members, [Measures].[salescount], BDESC ), [Measures].[salescount] ) SELECT [Gender].Members ON COLUMNS, [Measures].[ra...

Adding an extra row to an MDX result set

I have a very simple MDX query that retuns the contents of a dimension. I would like to inject one more row to the result set as part of the MDX. Is this possible? ...

User defined hierarchy in an MDX query

Following on from this question regarding calculating a member I have 2 calculated members defined as: MEMBER [Asset].[Class].[Fixed Income Derivatives] AS AGGREGATE( { [Asset].[Class].&[Fixed Income], [Asset].[Sub Class].&[Derivatives] }, [Measures].CurrentMember ) MEMBER [Asset].[Class].[Fixed Income Non-deri...

Speed up Running Total MDX calculated measure?

Hi all, I'm using the follow mdx to keep a running total of the Period Balance measure in my cube: SUM({[Due Date].[Date].CurrentMember.Level.Item(0):[Due Date].[Date].CurrentMember}, [Measures].[Period Balance]) It works great, however it's really slow as the amount of data displayed increases. I can't use a MTD or YTD because the use...

Using MDX Correlation() function to find correlation of 2 dimentional members.

I'm trying to find the Pearson correlation of a single measure between two members of a dimension, but the msdn docs are a bit sparse. Specifically I have a cube with a fact count measure, a date dimension, and a tool dimension and I'd like to find the correlation of tool X and tool Y over the date dimension. ...

MDX WHERE: "AND" between several conditions

This MDX request works: SELECT [Measures].salescount ON COLUMNS, [Date].[2010] ON ROWS FROM [SalesAnalysis] WHERE [Area].[Shanghai] This one works too (different WHERE condition): SELECT [Measures].salescount ON COLUMNS, [Date].[2010] ON ROWS FROM [SalesAnalysis] WHERE EXCEPT([Product].[All Products].Children, {[Product].[All Produc...

MDX calculation has wrong order of precendence

Im having an issue with an MDX query, and I think it boils down to the order of precedence between calculating an aggregate and a calculated member. Let me start with the underlying data, which revolves around a valuation (which has a date, and some other data such as a member type, a scheme - and crucially for this question; a loading ...

Parameter Pentaho Report Designer with MDX

I am doing a report, using Pentaho Report Designer. My datasource is a Mondrian Cube with mysql jdbc. I am using mdx queries and i want to put some of parameters. I have configure a parameter with its own query. The problem is the way the relation between the main query and the parameter query. For that, i am using the function paramet...