CODEWITHSUNDEEP
sqlmdx
user3289229
user3289229

Reputation: 1

SQL to MDX query conversion

So here is the SQL query :

select date(actor_creation_date),count(*) from fse_master group by (actor_creation_date) limit 3;

So far I have been able to come up with this :

with MEMBER [measures].[actor creation date] AS COUNT(([Measures].[actor creation date]))

select  [Measures].[actor creation date] on columns,
order([fse_master].members ,[Measures].[actor creation date],desc)
on rows
from [fse_masters]

Upvotes: 0

Views: 4368

Answers (2)

SouravA
SouravA

Reputation: 5243

You should not get confused between a "set" and a "measure". Measure is just a numeric/string entity which is logical in nature. You can't count something logical in nature. It needs to be physical in nature to be countable. In MDX terms, it needs to be a set. A set of items to be more precise. 

select date(actor_creation_date),count(*) 
from fse_master 
group by (actor_creation_date) 
limit 3;

In your query, actor_creation_date is an actually set of things(dates) which are present in the database. It is physical in nature. In RDBMS terms, this is a column. In MDX terms, this needs to be a set.

If you have a date measure, you can't just simply expect to put a count on top of it and expect it to work.

COUNT([measures].[actor creation date]) transforms to something like COUNT({12345}) which is a count of set which comprises of only one member that is a value. It's output would always be one.

What you need is to run the COUNT function on a set of dates.

You must be having a Date dimension which will have the actor creation date as one of the attribute.

Lets say it looks like below:

[Date].[actor creation date].[actor creation date]

Now you try to have a calculated member which would hold the count for every date.

WITH MEMBER Measures.CountOfDate
AS
COUNT([Date].[actor creation date].[actor creation date].CURRENTMEMBER)

limit 3 suggests that you want the top 3 dates. HEAD function in MDX takes care of that.

The final query would be :

SELECT HEAD([Date].[actor creation date].[actor creation date].MEMBERS, 3) ON 1,
Measures.CountOfDate ON 0
FROM [fse_masters]

EDIT - As @whytheq told, my initial attempt was incorrect.

You can try something on the lines of the below:

WITH SET ActorCreationDates AS
[Date].[actor creation date].[actor creation date].MEMBERS

MEMBER Measures.CountOfDate
AS
COUNT(EXISTING ActorCreationDates)

SELECT HEAD(ActorCreationDates, 3) ON 1,
Measures.CountOfDate ON 0
FROM [fse_masters]

Or, if you need all the records, not just the top 3, then:

SELECT DISTINCT(ActorCreationDates) ON 1,
Measures.CountOfDate ON 0
FROM [fse_masters]

The EXISTING clause computes the 'CountOfDate' in the current context, i.e. the particular member on Axis 1.

Upvotes: 2

whytheq
whytheq

Reputation: 35557

You're defining a measure with a name the same as an existing measure - does this causes an exception?

with MEMBER [measures].[actor creation date] AS COUNT(([Measures].[actor creation date]))

I would think for any member, of any hierarchy, there is 1 of each measure for that member so your script will just churn out 1 for each count?

In AdvWrks I did this:

WITH 
  MEMBER [Measures].[x] AS 
    Count([Measures].[Internet Sales Amount]) 
SELECT 
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[x]
  } ON 0
 ,[Product].[Product Categories].[Subcategory] ON 1
FROM [Adventure Works];

It returns this:

enter image description here

Can a measure get added to your cube for what you require? It will need to contain the relevant relationships and measure type i.e. a count.

Upvotes: 0

Related Questions