MDX hierarchy problem: February 2012

Saturday, February 25, 2012

MDX Query Filters

Hi,

I am creating report using reporting service and datasource is SQL Server 2005 Cube.

I am using query builder and i am creating query parameters in the query builder but when i run the report is showing the filters but data is not filtering based on which i selected from the list.

How do i create filters? Please help out on this.

Thanks

Switch to MDX mode and see the underlying query. There should be a subselect filter in the WHERE clause.|||

Thanks,

Now i am able to see the subqueries in MDX, but i am getting error when select the 'select all' from the list the following error message getting.

"An error occured during local report processing. Query execution failed for data set 'Dataset1' Query (1,461) The restrictions imposed by the CONSTRAINED flag in the STRTOSET function were violated.

Please help on this.

Thanks

|||

The CONSTRAINED flag requires the selected member name to be resolved to a qualified member name. Drop a textbox on your report and set it to expression:

=Join(Parameters!<parameter name>.Value,", ")

Run the report. Does the output looks like valid member names?

Mdx Query doubt about years?

Hi,
The below query which has hardcoded year ending values works fine.

What i want to do is if I pass on last 3 years or last 4 years etc as parameter along with current year it should bring back the results accordingly for the required years.

with
member [YearEnding].[CurrYear] as '[YearEnding].[All YearEnding].[2005]'
select
non empty {[Product].Children, Product.[All Product]} on columns,
non empty Filter({ {[Measures].[stock Quantity Ola] } * { [YearEnding].Children} }, (([YearEnding] = [YearEnding].[2005]) or ([YearEnding] = [YearEnding].[2004]) or ([YearEnding] = [YearEnding].[2003])) ) on rows
from Stock
Help much appreciated
Many Thanks

VeeraFirstly, you could run into problems using '=' operator to test for dimension members - this blog entry by Mosha Pasumanskyexplains why:

http://sqljunkies.com/WebLog/mosha/archive/2004/11/4.aspx
>>
Comparing members in MDX
...
>>

Since measures are not on columns, this looks like OLE DB Provider for OLAP, for which the MDX query has to be converted to an expression, to add parameters. Assuming a multi-select parameter like "SelectedYears", with selected string values like "[YearEnding].[2003]", etc, then the filter condition can be built using Join():

+ "Count(Intersect({[YearEnding].CurrentMember}, {"
+ Join(Parameters!SelectedYears.Value, ",")
+ "})) = 1) on rows from Stock"

MDX Query designer working correctly?

I've been working with the MDX query designer in Reporting Services 2005 (RTM Developer Edition) and have come across a behavior that does not seem correct.
I can build a report using the wizzard, preview it, deploy it, and all is good with the world. But if I so much as click on the Data tab in the report designer, the report becomes invalid.
Is this by design, or is there some other way to edit a report created with the MDX query designer?
Regards,
Clayton

This is known issue in Report Designer.

Here is workaround:

1. After the report wizard completes, close the report.

2. Open the report in code view

3. Edit the report removing all carriage returns and all whitespace (blanks) between the two tags

<rd:MdxQuery> and <QueryDefinition>

4. Save the report and close the window

5. Open the report in Design Mode.

MDX query designer behaving correctly in Reporting Services?

I've been working with the MDX query designer in Reporting Services 2005 (RTM Developer Edition) and have come across a behavior that does not seem correct.

I can build a report using the wizzard, preview it, deploy it, and all is good with the world. But if I so much as click on the Data tab in the report designer, the report becomes invalid.

Is this by design, or is there some other way to edit a report created with the MDX query designer?

Regards,

Clayton

This is apparently a known problem. I posted this to the Reporting Services forum as well and recieved an answer there. Heres the link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=171034&SiteID=1

Regards,

Clayton

MDX query data displays in data tab but not on the report when using a VB udf

Can someone please confirm if the following is an RS bug? If so, is there a
work around?
The following MDX query returns one record when executed in the Report
Designer(MS Visual Studio .net 2003) data tab:
WITH MEMBER member [Facility].[region_variable] as
'ondemand_udf!calc_weighted_ave(SetToArray({[RIC].[Cmggrpcd].Members}))'
SELECT {[Facility].[region_variable]} ON COLUMNS,
{Measures.[1000 Eating]} ON ROWS
FROM [CMG Demographics]
RETURNS:
Label Facility_region_variable
1000 Eating 6.1233453
When I preview the report(a simple table showing both fields) in report
designer, the 'Facility_region_variable' field displays null in the table.
The label does display correctly.
The 'ondemand_udf!calc_weighted_ave' function is a Visual Basic user defined
library function belonging to ondemand_udf.dll developed using MS Visual
Studio Basic 6.0. I am using RS 2000 enterprise SP2 on MS windows 2003
server. I am calling MS Analysis Services 2000 olap datasrce.
I believe this is a bug, because if I change the query to be the following,
it displays fine:
WITH MEMBER member [Facility].[region_variable] as '999'
SELECT {[Facility].[region_variable]} ON COLUMNS,
{Measures.[1000 Eating]} ON ROWS
FROM [CMG Demographics]
RETURNS:
Label Facility_region_variable
1000 Eating 999
Thank You,
_________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
___________________________Hi Dan,
Welcome to use MSDN Managed Newsgroup!
From your descriptions, I understood you are calling function
ondemand_udf!calc_weighted_ave from your own custom assembly. It looks fine
in Report Desinger Data page but shows NULL in Preview page. If I have
misunderstood your concern, please feel free to point it out.
Based on my knowledge, since it works fine in Data page, it should be OK to
preview the page. Please refer the article below to confirm your deployment
Writing Custom Code in SQL Server Reporting Services
http://blogs.sqlxml.org/bryantlikes/articles/824.aspx
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael,
You seem to have understood my problem. To be clear, the
ondemand_udf!calc_weighted_ave function is used and registered for use in MS
Analysis Services. I am not sure if this is the same as an RS custom
assembly. This udf(user defined function) functions correctly. My MDX
query returns the correct data in the data tab, but will not display in the
preview tab. My report contains one table displaying the label and the
value. I have nothing else on the report(no parameters, no filters, etc.) .
Only the label displays on the report.
This seems like a RS bug to me.
The link you sent in the previous reply does not work.
Thanks for your help,
Dan
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
___________________________
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:aTXvApshFHA.940@.TK2MSFTNGXA01.phx.gbl...
> Hi Dan,
> Welcome to use MSDN Managed Newsgroup!
> From your descriptions, I understood you are calling function
> ondemand_udf!calc_weighted_ave from your own custom assembly. It looks
fine
> in Report Desinger Data page but shows NULL in Preview page. If I have
> misunderstood your concern, please feel free to point it out.
> Based on my knowledge, since it works fine in Data page, it should be OK
to
> preview the page. Please refer the article below to confirm your
deployment
> Writing Custom Code in SQL Server Reporting Services
> http://blogs.sqlxml.org/bryantlikes/articles/824.aspx
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hi Dan,
Thanks for your email.
Would you please send me a reproduce scenario? Just some sample will be OK.
- the dll file that contains ondemand_udf!calc_weighted_ave function (it's
source code is also welcome)
- MDX that based on AdventureWorks
- steps to reproduce this behavior
I understand the information may be sensitive to you, my direct email
address is v-mingqc@.online.microsoft.com (please remove "online" before you
click SEND as "online" is only for SPAM), you may send the file to me
directly and I will keep secure.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I rebooted the machine and re-registered the ondemand_udf.dll for the 2nd
time and now the report works fine.
Thanks for your help,
Dan
--
___________________________
Daniel J. Zaccarine
Hanford Bay Associates, Ltd.
___________________________
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:ey2wkJ5hFHA.2140@.TK2MSFTNGXA01.phx.gbl...
> Hi Dan,
> Thanks for your email.
> Would you please send me a reproduce scenario? Just some sample will be
OK.
> - the dll file that contains ondemand_udf!calc_weighted_ave function
(it's
> source code is also welcome)
> - MDX that based on AdventureWorks
> - steps to reproduce this behavior
> I understand the information may be sensitive to you, my direct email
> address is v-mingqc@.online.microsoft.com (please remove "online" before
you
> click SEND as "online" is only for SPAM), you may send the file to me
> directly and I will keep secure.
> Thank you for your patience and cooperation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others: https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page: http://support.microsoft.com/common/international.aspx
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
rights.
>|||Hi Dan,
Thanks so much for letting me know about this and it's great to hear it
works now :)
If you have any questions or concerns next time, don't hesitate to let me
know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

MDX query count of new customers with a new flag who purchased in this period and following peri

Hi all,

I would like some help with an MDX query that will give the following information - return the count of new customers for the current year that have a new customer flag set and then the count of those returning customers in the following years that have made purchases. Here is an example of the data I'm taking about -

CustomerID NewCustomer Year

1 1 2000

1 0 2001

2 1 2001

2 0 2002

1 0 2002

etc.

So, for the above I'm looking for something similar to the following -

Year 2000 New Customers = 1

Year 2001 New Customers = 1

Year 2001 Repeat Customers = 1

Year 2002 Repeat Customers = 2

Does this make sense and is it possible?

Thanks!

Brian

Please see this thread for the answer: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1225081&SiteID=1

MDX query count of customers who purchased in prior period and this period

Hi there

I'm looking for help with an mdx query that will tell me how many of my customers are returning to purchase each period and thus by default how many existing customers haven't purchased this period.

So in English I want;

Count of distinct customers who were active in this period and were also active in prior period.

Or in T-SQL something like this

select count(*) from (Select distinct customer_id from fact_table where date = current period and exists (select distinct customer_id from fact_table where date = prior period))

I have a measure called distinct customer count and a dimension called DIm Customer which currently doesn't have customer_id as a level in any hierarchy, although it is the key field in this dimension, as I don't want to go to this level of detail if I can help it, not sure if this makes the above impossible.

Any ideas would be much appreciated.

Regards,

Derek

Hi Derek,

Here's a sample query for Adventure Works, which computes the count of customers with Internet Sales in both the current and prior Calendar periods - it's based on the count of common members being difference between the sum of member counts in each set and the distinct count of members across 2 sets:

With Member [Measures].[ContinuingCustomers] as

Sum(LastPeriods(2, [Date].[Calendar].CurrentMember),

[Measures].[Customer Count]) -

Aggregate(LastPeriods(2, [Date].[Calendar].CurrentMember),

[Measures].[Customer Count])

select {[Measures].[Customer Count],

[Measures].[ContinuingCustomers]} on 0,

{[Date].[Calendar Year].&[2002]:

[Date].[Calendar Year].&[2004]} on 1

from [Adventure Works]

where [Product].[Subcategory].&[1]

Customer Count ContinuingCustomers
CY 2002 615 0
CY 2003 1,961 267
CY 2004 2,094 288

|||

Hi Deepak

Thanks very much that is exactly what I wanted, one thing though when I use this measure my cube response slows dramatically and my cpu use spikes, any idea why?

Cheers,

Derek

|||Derek, I suspect that it has to do with the performance of the dstinct count measure, but I'm not sure. Doing a SQL Profiler trace, with all query events selected, could help identify where the time is spent.|||

Thanks again Deepak

I'll take a look at that, I may have to end up doing some of the work for this query in the data source view.

Cheers,

Derek

MDX Query Builder

Hi All,
I'm looking for a tool that would help me to build mdx queries and
display the resulting mdx so I can copy/paste it wherever I want.
The Report Server in SQL Server 2005 does have one but for some reasons
it does not allow generation of matrix result within the request.
For example, is there any way to view the request performed by the AS
Server when browsing a cube ?
Thanks for your help !
Eric
Assuming you are using AS 2005 (given RS 2005 with MDX), you could
always run a Profile session against Analysis Services. Then you could
capture the MDX being sent from the built-in browser tool in SSMS and
BIDS (its based on OWC11, I believe).
My favorite 3d party tool for creating more complicated MDX is
Proclarity. View | MDX shows you the MDX sent to the server.
Cheers,
Erik
Erik Veerman
erik (at) solidqualitylearning.com
--Original Message--
From: esl [mailto:esl@.tilbury.com]
Posted At: Wednesday, December 21, 2005 9:23 AM
Posted To: microsoft.public.sqlserver.datawarehouse
Conversation: MDX Query Builder
Subject: MDX Query Builder
Hi All,
I'm looking for a tool that would help me to build mdx queries and
display the resulting mdx so I can copy/paste it wherever I want.
The Report Server in SQL Server 2005 does have one but for some reasons
it does not allow generation of matrix result within the request.
For example, is there any way to view the request performed by the AS
Server when browsing a cube ?
Thanks for your help !
Eric
|||Thanks a lot ! I can now get the query from the log...
The only problem is that when I try to run it in the query editor of
SSMS against the cube I get the following error:
"Results cannot be displayed for cellsets with more than two axes"
Basically, the request is
SELECT
NON EMPTY {[Cycle].[Brand - Name].[All]}
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
NON EMPTY {[Cycle].[Type - Name].[All]}
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS,
{
[Measures].[Unit Count]
}
ON PAGES
FROM [CycleCube2005]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR
Would it be possible that the result pane of SSMS is not OWC11 based
and therefore could not displayed this kind of result ?
Thanks !
Eric
Erik Veerman wrote:
> Assuming you are using AS 2005 (given RS 2005 with MDX), you could
> always run a Profile session against Analysis Services. Then you could
> capture the MDX being sent from the built-in browser tool in SSMS and
> BIDS (its based on OWC11, I believe).
> My favorite 3d party tool for creating more complicated MDX is
> Proclarity. View | MDX shows you the MDX sent to the server.
> Cheers,
> Erik
> --
> Erik Veerman
> erik (at) solidqualitylearning.com
> --Original Message--
> From: esl [mailto:esl@.tilbury.com]
> Posted At: Wednesday, December 21, 2005 9:23 AM
> Posted To: microsoft.public.sqlserver.datawarehouse
> Conversation: MDX Query Builder
> Subject: MDX Query Builder
>
> Hi All,
> I'm looking for a tool that would help me to build mdx queries and
> display the resulting mdx so I can copy/paste it wherever I want.
> The Report Server in SQL Server 2005 does have one but for some reasons
> it does not allow generation of matrix result within the request.
> For example, is there any way to view the request performed by the AS
> Server when browsing a cube ?
> Thanks for your help !
> Eric

MDX Query Builder

Hi All,
I'm looking for a tool that would help me to build mdx queries and
display the resulting mdx so I can copy/paste it wherever I want.
The Report Server in SQL Server 2005 does have one but for some reasons
it does not allow generation of matrix result within the request.
For example, is there any way to view the request performed by the AS
Server when browsing a cube ?
Thanks for your help !
EricAssuming you are using AS 2005 (given RS 2005 with MDX), you could
always run a Profile session against Analysis Services. Then you could
capture the MDX being sent from the built-in browser tool in SSMS and
BIDS (its based on OWC11, I believe).
My favorite 3d party tool for creating more complicated MDX is
Proclarity. View | MDX shows you the MDX sent to the server.
Cheers,
Erik
--
Erik Veerman
erik (at) solidqualitylearning.com
--Original Message--
From: esl [mailto:esl@.tilbury.com]
Posted At: Wednesday, December 21, 2005 9:23 AM
Posted To: microsoft.public.sqlserver.datawarehouse
Conversation: MDX Query Builder
Subject: MDX Query Builder
Hi All,
I'm looking for a tool that would help me to build mdx queries and
display the resulting mdx so I can copy/paste it wherever I want.
The Report Server in SQL Server 2005 does have one but for some reasons
it does not allow generation of matrix result within the request.
For example, is there any way to view the request performed by the AS
Server when browsing a cube ?
Thanks for your help !
Eric|||Thanks a lot ! I can now get the query from the log...
The only problem is that when I try to run it in the query editor of
SSMS against the cube I get the following error:
"Results cannot be displayed for cellsets with more than two axes"
Basically, the request is
SELECT
NON EMPTY {[Cycle].[Brand - Name].[All]}
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON COLUMNS,
NON EMPTY {[Cycle].[Type - Name].[All]}
DIMENSION PROPERTIES MEMBER_NAME, PARENT_UNIQUE_NAME ON ROWS,
{
[Measures].[Unit Count]
}
ON PAGES
FROM [CycleCube2005]
CELL PROPERTIES VALUE, FORMATTED_VALUE, FORE_COLOR, BACK_COLOR
Would it be possible that the result pane of SSMS is not OWC11 based
and therefore could not displayed this kind of result ?
Thanks !
Eric
Erik Veerman wrote:
> Assuming you are using AS 2005 (given RS 2005 with MDX), you could
> always run a Profile session against Analysis Services. Then you could
> capture the MDX being sent from the built-in browser tool in SSMS and
> BIDS (its based on OWC11, I believe).
> My favorite 3d party tool for creating more complicated MDX is
> Proclarity. View | MDX shows you the MDX sent to the server.
> Cheers,
> Erik
> --
> Erik Veerman
> erik (at) solidqualitylearning.com
> --Original Message--
> From: esl [mailto:esl@.tilbury.com]
> Posted At: Wednesday, December 21, 2005 9:23 AM
> Posted To: microsoft.public.sqlserver.datawarehouse
> Conversation: MDX Query Builder
> Subject: MDX Query Builder
>
> Hi All,
> I'm looking for a tool that would help me to build mdx queries and
> display the resulting mdx so I can copy/paste it wherever I want.
> The Report Server in SQL Server 2005 does have one but for some reasons
> it does not allow generation of matrix result within the request.
> For example, is there any way to view the request performed by the AS
> Server when browsing a cube ?
> Thanks for your help !
> Eric

MDX Query builder

Good day,

I would like to know if there are any good MDX query builder tools out there. The one that is shipped with AS does not suffice in that it does not support more than 2 axes. Also if there are any are there any that make life a little easier in that they are drag and drop and all but create the statement for you?

I need to create a matrix in SSRS using cubes and in doing so need to write MDX queries, not my strong point. Are there any tools that can help me "auto create" the statement with drag and drop functionality?

Thanks in advanceYou could use Excel 2007 and then use Maro Russo blog to build Excel macro that displays MDX:

http://www.ssas-info.com/component/option,com_bookmarks/Itemid,78/task,view/id,16/

Vidas Matelis
http://www.ssas-info.com

MDX query based on a subquery

Hi,

I hope that some can help me with a small problem I have got. I am new to data warehousing and MDX and am wondering if it is possible to construct a query based on a subquery (something like a T-SQL IN Clause).

Want I want to do is take all the customers who has looked at one product category (ex Dairy products) and see what else they have looked at. I guess what I want to do is create some sort of a filter to be able to only take those users from the measure and slice them by the product dimension again.

I am using ProClarity and will also need to be able to use it in there.

I am thankful for any help or any pushes in the right direction :-).

Kind Regards

Stefan Ghose

Hi Stefan,

Here are 2 sample Adventure Works queries, which identify the customers who bought Clothing category products on Jan.1,2004, and their purchases in other product categories on the same day:

-- Customers who bought Clothing on Jan.1,2004:

select

Non Empty [Product].[Product Categories].[Category] on 0,

NonEmpty([Customer].[Customer Geography].[Full Name]

* {[Date].[Calendar].[Date].&[915]},

{([Product].[Product Categories].[Category].&[3],

[Measures].[Internet Sales Amount])}) on 1

from [Adventure Works]

where [Measures].[Internet Sales Amount]

-- Purchases in each Category on Jan.1,2004, by Customers who bought Clothing:

select {[Measures].[Internet Sales Amount]} on 0,

Non Empty [Product].[Product Categories].[Category] on 1

from [Adventure Works]

where NonEmpty([Customer].[Customer Geography].[Full Name]

* {[Date].[Calendar].[Date].&[915]},

{([Product].[Product Categories].[Category].&[3],

[Measures].[Internet Sales Amount])})

|||

Thank you soo much for your help Deepak! This was exactly what I was looking for! Now I can use this to try to make a dynamic Measuregroup that I can use in ProClarity...

-Stefan Ghose-

|||

I have found the exact answer that I was looking for. To be able to create a query that is based on a subquery you only need to select write the subquery in the FROM clause i.e

SELECT

NONEMPTY([User Dimension].[All].CHILDREN, [Measures].[Count]) ON ROWS,

[Measures].[Count] ON COLUMNS

FROM

( SELECT

NONEMPTY ([User Dimension].[All].CHILDREN) ON COLUMNS

FROM

[Cube]

WHERE

(

[Measures].[Count],

[Slicer Dimension].[Name].&[Value],

[Date Dimension].[Year - Month].[Year].&[2006].&[11]

)

WHERE

(

[Date Dimension].[Year - Month].[Year].&[2006].&[11],

[User Dimension.CHILDREN

)

An example of this can be found here:

http://sqljunkies.com/WebLog/hitachiconsulting/archive/2004/09/21/4295.aspx

MDX query based on a subquery

Hi,

I am using ProClarity and will also need to be able to use it in there.

I am thankful for any help or any pushes in the right direction :-).

Kind Regards

Stefan Ghose

Hi Stefan,

Here are 2 sample Adventure Works queries, which identify the customers who bought Clothing category products on Jan.1,2004, and their purchases in other product categories on the same day:

-- Customers who bought Clothing on Jan.1,2004:

select

Non Empty [Product].[Product Categories].[Category] on 0,

NonEmpty([Customer].[Customer Geography].[Full Name]

* {[Date].[Calendar].[Date].&[915]},

{([Product].[Product Categories].[Category].&[3],

[Measures].[Internet Sales Amount])}) on 1

from [Adventure Works]

where [Measures].[Internet Sales Amount]

-- Purchases in each Category on Jan.1,2004, by Customers who bought Clothing:

select {[Measures].[Internet Sales Amount]} on 0,

Non Empty [Product].[Product Categories].[Category] on 1

from [Adventure Works]

where NonEmpty([Customer].[Customer Geography].[Full Name]

* {[Date].[Calendar].[Date].&[915]},

{([Product].[Product Categories].[Category].&[3],

[Measures].[Internet Sales Amount])})

|||

Thank you soo much for your help Deepak! This was exactly what I was looking for! Now I can use this to try to make a dynamic Measuregroup that I can use in ProClarity...

-Stefan Ghose-

|||

I have found the exact answer that I was looking for. To be able to create a query that is based on a subquery you only need to select write the subquery in the FROM clause i.e

SELECT

NONEMPTY([User Dimension].[All].CHILDREN, [Measures].[Count]) ON ROWS,

[Measures].[Count] ON COLUMNS

FROM

( SELECT

NONEMPTY ([User Dimension].[All].CHILDREN) ON COLUMNS

FROM

[Cube]

WHERE

(

[Measures].[Count],

[Slicer Dimension].[Name].&[Value],

[Date Dimension].[Year - Month].[Year].&[2006].&[11]

)

WHERE

(

[Date Dimension].[Year - Month].[Year].&[2006].&[11],

[User Dimension.CHILDREN

)

An example of this can be found here:

http://sqljunkies.com/WebLog/hitachiconsulting/archive/2004/09/21/4295.aspx

MDX query based on a subquery

Hi,

I am using ProClarity and will also need to be able to use it in there.

I am thankful for any help or any pushes in the right direction :-).

Kind Regards

Stefan Ghose

Hi Stefan,

Here are 2 sample Adventure Works queries, which identify the customers who bought Clothing category products on Jan.1,2004, and their purchases in other product categories on the same day:

-- Customers who bought Clothing on Jan.1,2004:

select

Non Empty [Product].[Product Categories].[Category] on 0,

NonEmpty([Customer].[Customer Geography].[Full Name]

* {[Date].[Calendar].[Date].&[915]},

{([Product].[Product Categories].[Category].&[3],

[Measures].[Internet Sales Amount])}) on 1

from [Adventure Works]

where [Measures].[Internet Sales Amount]

-- Purchases in each Category on Jan.1,2004, by Customers who bought Clothing:

select {[Measures].[Internet Sales Amount]} on 0,

Non Empty [Product].[Product Categories].[Category] on 1

from [Adventure Works]

where NonEmpty([Customer].[Customer Geography].[Full Name]

* {[Date].[Calendar].[Date].&[915]},

{([Product].[Product Categories].[Category].&[3],

[Measures].[Internet Sales Amount])})

|||

Thank you soo much for your help Deepak! This was exactly what I was looking for! Now I can use this to try to make a dynamic Measuregroup that I can use in ProClarity...

-Stefan Ghose-

|||

I have found the exact answer that I was looking for. To be able to create a query that is based on a subquery you only need to select write the subquery in the FROM clause i.e

SELECT

NONEMPTY([User Dimension].[All].CHILDREN, [Measures].[Count]) ON ROWS,

[Measures].[Count] ON COLUMNS

FROM

( SELECT

NONEMPTY ([User Dimension].[All].CHILDREN) ON COLUMNS

FROM

[Cube]

WHERE

(

[Measures].[Count],

[Slicer Dimension].[Name].&[Value],

[Date Dimension].[Year - Month].[Year].&[2006].&[11]

)

WHERE

(

[Date Dimension].[Year - Month].[Year].&[2006].&[11],

[User Dimension.CHILDREN

)

An example of this can be found here:

http://sqljunkies.com/WebLog/hitachiconsulting/archive/2004/09/21/4295.aspx

MDX Query above 8000 characters

Hi,

I have a MDX query which is of an aprox length of 10000 characters. I
have to execute the query from within the stored procedure in sql. To
run this query I use the openrowset method.

If the length of my query is less than 8000 characters my query
executes perfectly, but the moment it exceeds 8000 characters it stop
working. Please suggest a solution for the same.

Sample Code:

declare @.mdxqry varchar(8000)
declare @.SearchCond varchar(8000)

set @.SearchCond = @.SearchCond + '
[ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @.TDate
+ '" '

set @.mdxqry = '''WITH ' +
'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
Amount] - [Measures].[Adjusted Interest]'''' ' +
'MEMBER [Measures].[Loan Closed within Report Period] as ' +
''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
Date")) < cdate("' + @.ToDate + '"), "Yes", "No")'' +
'MEMBER [Measures].[ClosedBeforeLastInstallment] as
''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
' +
'SELECT ' +
'{[Measures].[Expected Interest Amount], [Measures].[Adjusted
Interest], [Measures].[Difference], ' +
'[Measures].[Zero Interest Transactions],
[Measures].[ClosedBeforeLastInstallment], ' +
'[Measures].[Loan Closed within Report Period]} ON 0, '

set @.mdxqry = @.mdxqry +
'{Filter([ProductsAccounts].[Account Id].Members, (' + @.SearchCond +
'))} on 2, ' +
@.BranchFilter +
'FROM InterestAnalysis'''

set @.mdxqry = 'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
Catalog="DATABASENAME";'',' + @.mdxqry + ') as a'

exec(@.mdxqry)

I have already tried splitting my query into smalled chunks and
executing it, but still I face the same problem.

This is how I have Done it:

declare @.mdxqry1 varchar(8000)
declare @.mdxqry2 varchar(8000)
declare @.SearchCond varchar(8000)

set @.SearchCond = @.SearchCond + '
[ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @.TDate
+ '" '

set @.mdxqry1 = '''WITH ' +
'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
Amount] - [Measures].[Adjusted Interest]'''' ' +
'MEMBER [Measures].[Loan Closed within Report Period] as ' +
''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
Date")) < cdate("' + @.ToDate + '"), "Yes", "No")'' +
'MEMBER [Measures].[ClosedBeforeLastInstallment] as
''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
'

set @.mdxqry2 = 'SELECT ' +
'{[Measures].[Expected Interest Amount], [Measures].[Adjusted
Interest], [Measures].[Difference], ' +
'[Measures].[Zero Interest Transactions],
[Measures].[ClosedBeforeLastInstallment], ' +
'[Measures].[Loan Closed within Report Period]} ON 0, '

set @.mdxqry2 = @.mdxqry2 +
'{Filter([ProductsAccounts].[Account Id].Members, (' + @.SearchCond +
'))} on 2, ' +
@.BranchFilter +
'FROM InterestAnalysis'''

set @.mdxqry2 = 'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
Catalog="DATABASENAME";'',' + @.mdxqry + ') as a'

exec(@.mdxqry1 + @.mdxqry2)

Thanks in Advance

Charuvarchar is limited to 8000 characters, but EXEC can take several varchar parameters concatenated together.

Try breaking @.mdxqry into several strings that you know will be below the 8k limit (@.mdxqry1, @.mdxqry2, ...@.mdxqryN). Then call it like this:

exec (@.mdxqry1 + @.mdxqry2 + ... @.mdxqryN)|||I have already tried breaking up the varchar into multiple variables but the same problem exists...Please see if there is any other solution...

Thanks
Charu|||I have already tried breaking up the varchar into multiple variables but the same problem exists...How? Not according to the code you posted.

MDX query (was "syntax error")

" ORDER ( [Accounting Date].[Account Week].Members , DESC ) ON ROWS "
what is wrong with this line?1. the BY keyword is missing
2. the database/table/column names appear mixed up
3. the comma shouldn't be there
4. the parentheses shouldn't be there
5. the ON ROWS shouldn't be there|||im using MDX not sql yeah?

any ideas for my date sorting query

THANKS 4 REPLYIN!|||what's MDX?|||when using cubes in analysis manager??|||yeah, sorry, i had never heard of it, but a quick search on google showed some interesting results

for example, i don't see an ORDER clause

http://msdn.microsoft.com/library/en-us/olapdmad/agmdxbasics_0v7d.asp|||thanks mate...
order is introduced when you wnat to sort teh result!!!

Thanks enyway!!! : )|||I am creating a OLAP system, where a result table row is a time property. They have options of year, quarter, month or week.

It is all working fine, and actually creates the MDX query depending on the users choice of time property!!! problem is if they choose month or week, the result is in no order... I want the system to display the results in order eg week 1 - week 52

any ideas??|||I am creating a system, where the result table has a row with time in it The user has the option of seeing the data on year, quarter, month or week.

It is all working fine, and actually creates the MDX query depending on the users choice is of teh above!!! problem is if they choose month or week, the result is in no order... I want the system to display the results in order eg week 1 - week 52

any ideas??|||[ moderator note: merging this thread with your other one that is essentially the same question ]

MDX Query "paging file too small" problem

...but the SSAS 2005 server is running on a machine with a 12 Gig page file.

Although it is a big select statement (with "intersect()"s and "hierarchize()"s and multiple cross-joins), I would expect the query to complete successfully. If there is a shortage of memory, I would think the server would simply make use of the paging file (all 12 Gig).

The machine is an HP dual-IA64 server with 4 Gig of RAM running W2K3 SP1. The SSAS 2005 instance has been upgraded to SP1 recently.

Any ideas?

Thanks!

Which flavour of Analysis Services 2005 are you using?

We've also had problems with memory usage - typically, though, when processing the dimensions or measure groups. Is the machine dedicated to SSAS, or does it have e.g. SQL Server installed as well?

Regards,

Will.

|||

Hi, Hippunky! Thanks for replying.

The machine has both IIS and the full "suite" of SQL Server services installed (Sql Server, SSAS, SSIS, RS, SQL Agent, etc.) The SSAS installation is the full (mostly default) install.

Bob

|||

I've bumped the paging file to 30 Gig (24 Gig wasn't enough), and the MDX query starts successfully (before it was failing almost immediately with the "paging file too small" message). I'll post if it completes successfully.

"Just talking to myself...",

Bob Hodgman

|||

Hmmm... I didn't mark this as the answer although it certainly took care of the initial failure. So I guess it is the "answer" to that issue.

However, the query has been running for 22 hours and 53 minutes, so far.

TGIF.

Bob

|||

We've been having the same problem when we process our dimensions and cube. How did you change the size of the paging file?

TIA

MDX Query "paging file too small" problem

...but the SSAS 2005 server is running on a machine with a 12 Gig page file.

The machine is an HP dual-IA64 server with 4 Gig of RAM running W2K3 SP1. The SSAS 2005 instance has been upgraded to SP1 recently.

Any ideas?

Thanks!

Which flavour of Analysis Services 2005 are you using?

We've also had problems with memory usage - typically, though, when processing the dimensions or measure groups. Is the machine dedicated to SSAS, or does it have e.g. SQL Server installed as well?

Regards,

Will.

|||

Hi, Hippunky! Thanks for replying.

The machine has both IIS and the full "suite" of SQL Server services installed (Sql Server, SSAS, SSIS, RS, SQL Agent, etc.) The SSAS installation is the full (mostly default) install.

Bob

|||

"Just talking to myself...",

Bob Hodgman

|||

Hmmm... I didn't mark this as the answer although it certainly took care of the initial failure. So I guess it is the "answer" to that issue.

However, the query has been running for 22 hours and 53 minutes, so far.

TGIF.

Bob

|||

We've been having the same problem when we process our dimensions and cube. How did you change the size of the paging file?

TIA

MDX Query - Help needed from experienced

Ingen
=====
GA - Georgia
--County1
-- SharedFlag
--County2
-- SharedFlag
NC - North Carolina
--County1
-- SharedFlag
--County2
-- SharedFlag
MD - Maryland
--County1
-- SharedFlag
--County2
-- SharedFlag
Query 1:
Select Non Empty [InGen].[State Name].Members on Columns from [F
irm
Reporting]
.................................
Could get all states as columns
Query 2:
with
set [SLA] as 'Filter( [InGen].[County Name].Members,
[InGen].CurrentMember.Properties("SharedFlag") = "A" OR
[InGen].CurrentMember.Properties("SharedFlag") = "Y" )'
Select Non Empty {SLA} On Columns From [Firm Reporting]
................
Could get all county names those are having SharedProperty of A or Y
Question: How can I get states on columns if any one of their counties has a
Shared Property of A or Y ?
I do not have much experience but I tried hard to get this query
(essentially it is a clubbing the above two queries) but with no luck. Can
any body help me ? Thanks in advance.I think the following should work, drilling up from the counties to the
State Name level.
eg.

with
set [SLA] as 'DrillUpLevel(Filter( [InGen].[County Name].Members
,
[InGen].CurrentMember.Properties("SharedFlag") = "A" OR
[InGen].CurrentMember.Properties("SharedFlag") = "Y" ),[Ingen].[
State
Name])'
Select Non Empty {SLA} On Columns From [Firm Reporting]
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <emQQiftUGHA.224@.TK2MSFTNGP10.phx.gbl>, "saiser" <norep>
says...[vbcol=seagreen]
> Ingen
> =====
> GA - Georgia
> --County1
> -- SharedFlag
> --County2
> -- SharedFlag
> NC - North Carolina
> --County1
> -- SharedFlag
> --County2
> -- SharedFlag
> MD - Maryland
> --County1
> -- SharedFlag
> --County2
> -- SharedFlag
> Query 1:
> Select Non Empty [InGen].[State Name].Members on Columns from [
;Firm
> Reporting]
> .................................
> Could get all states as columns
>
> Query 2:
> with
> set [SLA] as 'Filter( [InGen].[County Name].Members,
> [InGen].CurrentMember.Properties("SharedFlag") = "A" OR
> [InGen].CurrentMember.Properties("SharedFlag") = "Y" )'
> Select Non Empty {SLA} On Columns From [Firm Reporting]
> ................
> Could get all county names those are having SharedProperty of A or Y
>
> Question: How can I get states on columns if any one of their counties has
a
> Shared Property of A or Y ?
> I do not have much experience but I tried hard to get this query
> (essentially it is a clubbing the above two queries) but with no luck. Can
> any body help me ? Thanks in advance.
>
>|||Darren
The following query helped me (See Deepak's posting at the link below)
......
.....
So based on the above, the flg query tested well and solved my problem.
with
set [SLA] as 'FILTER([InGen].[State Name].Members,
Count(Filter(Descendants([InGen].CurrentMember, [InGen].[County
Name]),
[InGen].CurrentMember.Properties("SharedFlag") = "A" OR
[InGen].CurrentMember.Properties("SharedFlag") = "Y")) > 0)'
Select Non Empty (SLA) On Columns From [Firm Reporting]
But my initial trials are with drill down and drill up queries. Somehow I
erred in my testing. Anyway many thanks for the hint and I will test with
DrillUpLevel also and can post results soon here.
--saiser [MCSD]
"Darren Gosbell" <jam@.newsgroups.nospam> wrote in message
news:MPG.1ea0dbcf8bc7468e9898e2@.news.microsoft.com...[vbcol=seagreen]
>I think the following should work, drilling up from the counties to the
> State Name level.
> eg.
>
> with
> set [SLA] as 'DrillUpLevel(Filter( [InGen].[County Name].Membe
rs,
> [InGen].CurrentMember.Properties("SharedFlag") = "A" OR
> [InGen].CurrentMember.Properties("SharedFlag") = "Y" ),[Ingen].
1;State
> Name])'
> Select Non Empty {SLA} On Columns From [Firm Reporting]
> --
> Regards
> Darren Gosbell [MCSD]
> Blog: http://www.geekswithblogs.net/darrengosbell
> In article <emQQiftUGHA.224@.TK2MSFTNGP10.phx.gbl>, "saiser" <norep>
> says...
>

mdx query

I write the following MDX query that functions correctly:

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { [PERIOD CONTAB].[FY MONTH].&[2007-01-01T00:00:00] : [PERIOD CONTAB].[FY MONTH].&[2007-09-01T00:00:00] } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS ,

{

[Operating Cost]

,[COST ELEMENT].[MACRO COST].[Total Operating Cost]

,[COST ELEMENT].[MACRO COST].&[Capital Charge]

,[COST ELEMENT].[MACRO COST].[Total Contract Cost]

}

UNION (

{[PERIOD CONTAB].[FY MONTH].&[2007-01-01T00:00:00] :[PERIOD CONTAB].[FY MONTH].&[2007-09-01T00:00:00] }

, [PERIOD CONTAB].[FY MONTH].[Totale])

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

from [PMO_MDB]

If I try to insert the query inside Reporting Services substituting some data with query parameter in the following way I get an error:

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END) } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS ,

{

[Operating Cost]

,[COST ELEMENT].[MACRO COST].[Total Operating Cost]

,[COST ELEMENT].[MACRO COST].&[Capital Charge]

,[COST ELEMENT].[MACRO COST].[Total Contract Cost]

}

UNION (

{ STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END) }

, [PERIOD CONTAB].[FY MONTH].[Totale])

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM ( SELECT ( STRTOSET(@.PROJECTSTREAM, CONSTRAINED) ) ON COLUMNS FROM [PMO_MDB])

WHERE ( IIF( STRTOSET(@.PROJECTSTREAM, CONSTRAINED).Count = 1, STRTOSET(@.PROJECTSTREAM, CONSTRAINED), [PROJECT].[STREAM].currentmember ) )

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

The query seems sintaticcally correct !

So what happened !

Here is the error:

TITLE: Microsoft Visual Studio

Query preparation failed.

ADDITIONAL INFORMATION:

Parser: The FY_MONTH_START parameter could not be resolved because it was referenced in an inner subexpression. (msmgdsrv)

BUTTONS:

Having the same problem in reporting services. Have you found anything?|||

Sounds like it's having a problem with the STRTOMEMBER function inside of the custom member definition. Since you know that your member will only be a single value, you could always write the query in dynamic mdx and pass the parameter values in. The top would look something like this:

Code Snippet

=" WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as

'AGGREGATE ( { " + Parameters!FY_MONTH_START.Value + " : " + Parameters!FY_MONTH_END.Value + ") } )'

SELECT NON EMPTY { [Measures].[EUR] } ON COLUMNS , " +

It's a little tricky to get the designer to accept dynamic mdx. You have to write the original mdx query first. Then you have to click on the elipses for the dataset to bring up the dataset dialog. Then in the dialog, click the "f(x)" button to fill in the text. The dialog box that comes up will allow you to enter in the "=" sign and do the rest of your dynamic mdx.

|||

Thanks for the reply

I solved simply removing the character ' in the member definition !

Don't know why .... but now it functions -)

WITH MEMBER [PERIOD CONTAB].[FY MONTH].[Totale] as AGGREGATE ( { " + Parameters!FY_MONTH_START.Value + " : " + Parameters!FY_MONTH_END.Value + ") } )

.....

Cosimo

MDX Query

Hi,

Before I start, all the queries here have been used for my learning process and they may don't make any sense.
I've got a little bit confused about the way a specific MDX query executes.
Underestanding the following query is simple: (I used AdventureWorks sample database and DW)

WITH MEMBER MEASURES.PROFIT AS ([Measures].[Internet Sales Amount]-[Measures].[Total Product Cost])
SELECT MEASURES.PROFIT ON COLUMNS, [Customer].[Country].MEMBERS ON ROWS
FROM [Adventure Works]

for each member of country hierarchy in customer dimention it calculates the profit.
but I don't get how the following query is executed:

WITH MEMBER MEASURES.LOCATIONNAME AS [Customer].[Country].CURRENTMEMBER.NAME
SELECT MEASURES.LOCATIONNAME ON COLUMNS,
[Customer].[Country].MEMBERS ON ROWS
FROM [Adventure Works]

I mean there is nothing from the measures dimention except what we've located there (LOCATIONNAME). So does SQL Server for each row find the appropriate locationname by searching in [Customer].[Country]? what if the query was like this: (I know it's useless, just curious)

WITH MEMBER MEASURES.LOCATIONNAME AS [Customer].[Country].CURRENTMEMBER.NAME
SELECT MEASURES.LOCATIONNAME ON COLUMNS,
[Date].[Calendar].[Calendar Semester] ON rows
FROM [Adventure Works]

the [Customer].[Country].CURRENTMEMBER matches to nothing on rows. So again, does SQL Server search and find nothing and return default member of [Customer].[Country]?
How does it do that?

Thanks in advance

Welcome to the multidimensional world where things are a bit different from the relational world.

An MDX query defines a query space, a collection of cells, within a cube. A cube cell is determined by its coordinate which contains a member from every hierarchy of the cube dimensions.

For example, the query space of your second query is a set of cells which looks like:

{
( [LOCATIONNAME], [USA], ... ), // cell 1
( [LOCATIONNAME], [Canada], ... ), // cell 2
...
}

Here I only listed the attribute hierarchy members explicitly specified in your query. Since every cell is defined by taking one member from every hierarchy, the other hierarchy members in the cell coordinates are implicitly provided by the OLAP engine using the default members.

Given a query space, OLAP engine will evaluate all cell properties requested by the query. Value is one of the default cell properties returned unless your specify cell properties explicitly in your query.

After the OLAP engine parses your query, it iterates over the query space cell by cell and evaluates cell values among other cell properties. In your 2nd and 3rd queries, cell values are determined by the values of LOCATIONNAME. For each cell, LOCATIONNAME tells the OLAP engine to retrieve the name of the current member of the [Country] attribute hierarchy. Since every cell coordinate must have a member from every hierarchy, including the [Country] attribute hierarchy, the OLAP engine retrieves that member and returns its name as the cell value.

Therefore the answers to both your questions are yes.

|||Thanks for such an exponential reply.
What I understand is if I have for example 3 dimentions excluding Measures and each one of them has 2 hierarchy, each cell should contain 3*2+1=7 items of data. (3 dimentions * 2 Hierarchy + the data from Measures dimention)
Did I get it right?

I have one more question:
Could you please make it clear the difference between Hierarchy and Attribute Hierarchy?

Thanks again,
Amir|||

There are two types of hierarchies: attribute hierarchies and user defined hierarchies. Attribute hierarchies completely define the space of a cube. In your example, assume all 6 hierarchies are attribute hierarchies, each cell coordinate is defined by six attribute members + one measure member as you said.

Here is some information on attribute hierarchy.

http://msdn2.microsoft.com/en-us/library/ms174939.aspx

MDX Query

Hi,

I am having a query like this

SELECT NON EMPTY { [Measures].[Check Status],

[Measures].[Payment Amount], [Measures].[Check Type] }

ON COLUMNS, NON EMPTY

{ ([Activity].[Activity Identity].[Activity Identity].ALLMEMBERS *

[Activity].[Activity Description].[Activity Description].ALLMEMBERS

* [Faculty].[Faculty Name].[Faculty Name].ALLMEMBERS ) }

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [cubActivity]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,

FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Now i want to add a filter condition for this.

Where [Measures].[Payment Amount] > 4500 and [Measures].[Check Type] = 1

how to add and where to add this line the above MDX query

Can you please help me

Thanks

Dinesh

Something like the following might work

SELECT NON EMPTY { [Measures].[Check Status],

[Measures].[Payment Amount], [Measures].[Check Type] }

ON COLUMNS, NON EMPTY

FILTER({ ([Activity].[Activity Identity].[Activity Identity].ALLMEMBERS *

[Activity].[Activity Description].[Activity Description].ALLMEMBERS

* [Faculty].[Faculty Name].[Faculty Name].ALLMEMBERS ) }

, [Measures].[Payment Amount] > 4500 and [Measures].[Check Type] = 1)

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS

FROM [cubActivity]

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,

FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

MDX query

I'm building a report from SSAS 2005 with three parameters.

start date

end date

project stream

I would filter only the non empty project stream in the actual cost fact table included in the time interval specified with the first two paramenters.

SSRS automatically compose an MDX query that I try to modify to get what I want:

WITHMEMBER [Measures].[ParameterCaption] AS'[PROJECT].[STREAM].CURRENTMEMBER.MEMBER_CAPTION'MEMBER [Measures].[ParameterValue] AS'[PROJECT].[STREAM].CURRENTMEMBER.UNIQUENAME'MEMBER [Measures].[ParameterLevel] AS'[PROJECT].[STREAM].CURRENTMEMBER.LEVEL.ORDINAL'

SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel], [Measures].[EUR] } ONCOLUMNS ,

NONEMPTY [PROJECT].[STREAM].ALLMEMBERSONROWS

FROM [PMO_MDB]

WHERE { STRTOMEMBER(@.FY_MONTH_START) : STRTOMEMBER(@.FY_MONTH_END)}

In this way I get all project streams and I'm not able to filter away the empty cell (using the [Measures].[EUR] defined in the actual cost fact table). In fact SSRS put some intrisict member properties on columns as calculated measure !

There is another way to extract the same info but filtering away the empty cell ?

Cosimo

Take a look at the EXISTING function. I believe you could use it on your row axis to limit Project.Stream members to those associated with a fact entry.

Bryan

|||

Thanks Brjan ... I tried but I get the same result also using EXISTING !

I think the problem is that SSRS put on columns as a calculated measure some properties.

There is another way to extract such properies ?

I try the dimension properties clause ... but nothing happen !

Cosimo

|||

Sorry. I meant the EXISTS function. (The names are so similar I always screw them up.) Here is an example of what I'm describing:

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

EXISTS(

[Product].[SubCategory].Members,

{ STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")},

'Reseller Sales'

) on 1

from [Adventure Works]

WHERE { STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")}

For comparison:

Code Snippet

select

[Measures].[Reseller Sales Amount] on 0,

[Product].[SubCategory].Memberson 1

from [Adventure Works]

WHERE { STRTOMEMBER("[Date].[Date].[June 1, 2003]") : STRTOMEMBER("[Date].[Date].[June 30, 2004]")}

Sorry for the confusion,

Bryan

Mdx query

Hello guys,

Assume I have a date dimention in a cube and I want to retrive data based on the StartDate and EndDate value. In short, I want to filter all records between StartDate value and EndDate value which is given by a user. Can any body give a general syntax or mdx query to achive this goal?

Sincerely,

Amde

Hello Amde

Assuming Analysis Services 2005:

SELECT {[Measures].[MyMeasures]} ON 0,

[MyDimension].[MyHierarchy].[MyLevel].members ON 1

FROM [MyCube]

WHERE {[Time].[Date].[<StartDate>] : [Time].[Date].[<EndDate>]}

|||

Thanks a lot michael,

But what if the the <StartDate> and <EndDate> values are parameter values that should be specified by a user? What will be the where condition in this case?

Sincerely,

Amde

|||The query itself needs to be the same. How would the user specify the parameters? Which client is the user using?|||

Hey michael,

That is a good question. I am going to write this query in the Report Designer; there will be report parameters <StartDate>and<EndDate> by which the user will specify and based on the parameter, I want to filter the record. I think this makes sense.

Sincerely,

Amde

|||

Ahhh... OK. Then I guess you just need to create two report parameters (for instance, named @.StartDate and @.EndDate). The values have to contain the unique member name for the dates. This could be something like [Time].[Date].&[20060518]. This value can the be passed to the MDX query. Now, I don't remember the exact syntax when you want to use report parameters in an MDX query, but I believe the WHERE statement should be something like this:

WHERE {StrToSet(@.StartDate.Value + ":" + @.EndDate.Value)}

|||

Hey Michael,

Thank you for sharing your idea, I know the syntax in reporting service; I will change the syntax and use it properly.

Sincerely,

Amde

MDX Query

Hello,

1.

I would like to execute my mdx query from dos prompt? is there any way? I was fighting with MDXsampl.exe but could not find solution.

2. Is it possible to schedule MDX query and save the output to an output file like using "isql/osql"

Appreciate any help on this.

Thanks,
VaradMDX Queries can be executed through OLEDB/MD Connections. It would be possible to create an application (10 - 20 lines of code), possibly even a vbs to execute an MDX query. You will end-up with a cellset that you would be responsible for writing out to the file if that's what you are looking for.

I'll check to see if I have any code samples.

MDX Query

Hi,

I have an Dimension with name Employee and in it there is Attributes Employee ID, Employee Name, Parent Employee Id etc. I want to Reterive the Details of the Employee and their reportees. I have Given Dimension Security as the User who enters the system can see only his and his reportees details.

I need an MDX Query to Display only the details of the user who acessing the system and the employees reporting to him.

Please Help me to solve this..................

Thanks in Advance................

If you have implemented dimension data security on the Emloyee dimension then you should see only the allowed members when you drag the Employee dimension.

|||

Hi Teo,

Thanks for the reply...

I have to give the Dimesion Data Security to hide the data of others who were not reporting to the person who enters...

for Eg:

Dimension: Employee

EmpKey EmpName ParentEmpKey

1 aaa -

2 bbb -

3 ccc 1

4 ddd 2

5 eee 4

I want to give security like this....

assume that Employee ddd is logged into the system then it should displays only the details of ddd, eee

assume that Employee bbb is logged into the system then it should displays only the details of bbb,ddd, eee

assume that Employee aaa is logged into the system then it should displays only the details of aaa,ccc

assume that Employee ccc is logged into the system then it should displays only the details of ccc

To implement this i need the MDX Query to be placed in Dimension Data Security of the Role.

please help to solve this....

|||

This question should have posted to the Analysis Services discussion list. Your allowed set MDX expression should be something like:

LinkMember
(
Exists
(
[Employee].[EmpName].[EmpName].Members,
StrToMember("[Employee].[Login ID].&[" + Username + "]")
).Item(0),
[Employee].[Employees] //parent-chid hiearchy here
)|||

Thanks Teo......

This is working fine.........

But one thing for eg. the Employee is involved in sales and the report to be taken as the empolyee name and the order quantity, then if the employee has not done any sales even though he is under some employee and in the report his name is displaying..

to ur understanding:

EmpName OrderQuantity Parent Employee

aaa 1 -

bbb 2 1

ccc null 1

when selectin the employee aaa the result displaying as...

aaa 1

bbb 2

total 3

but i want the result as..

aaa 1

bbb 2

ccc 0

total 3

|||

Try removing the Non Empty clause from the query.

|||

Thanks Teo...

But I didnt add Non Empty Clause in the Query

MDX query

I have a dimension and a fact count.

How do i create a query to filter out the dimension using the filter function?

I tried something like :

SELECT {[Measures].[XX Count]} ON COLUMNS,

{ filter([RT].[RT].Members,[RT].[RT].CurrentMember < 10) } ON ROWS

FROM [YYYY]

but invalid.

for eg i have dimension RT with values 1,11,13,50 etc.... so in the row i would expect a row of RT with value 1 and its respective XXCount.

Thanks.

Regards

Alu

This is most likely caused by the fact that you have multiple measures in your cube and when you did not specify a particular measure in the filter clause the default one was used instead of the count measure. The measure on the columns defines the context for the value that is returned in the cells, but not necessarily for the value that is used to evaluate the filter condition. You should change your filter expression to explicitly mention the measure you want to use.

eg.

SELECT {[Measures].[XX Count]} ON COLUMNS,

{ filter([RT].[RT].Members,([RT].[RT].CurrentMember,[Measures].[XX Count] ) < 10) } ON ROWS

FROM [YYYY]

mdx query

I'm tring to extract some data from an ssas 2005 cube to build a report with ssrs 2005.

The report should have a variable number of columns and a fixed number of rows ... so I think I cannot use a table control but I must use a matrix control ...

So I would group the column for the fiscal month and the row for the measure name or caption ... an put the measure value inside the matrix.

To do that I sholu run a query to extract data in the following form ...

fiscal month mesaure name measure value month 1 measure 1 xxx month 1 measure 2 xxx month 1 measure 3 xxx month 2 measure 1 xxx month 2 measure 2 xxx month 2 measure 3 xxx

The problem is ... when running an mdx query on reporting services I need to put the meausure only on the columns ...

so any idea on how can I extract data from ssas in that form ?

Cosimo

If the list of measures is fixed/static (ie. always 1, 2, 3), you could place the fixed measures on columns in the MDX query, but then place these measures on static rows in the matrix - months would go on dynamic columns:

http://msdn2.microsoft.com/en-us/library/ms159652(SQL.90).aspx

SQL Server 2005 Books Online

How to: Add a Static Column or Row to a Matrix (Report Designer)

...

MDX Queries within .NET SSAS stored procedures (using ADOMD.NET Server library)

It seems like it is not possible to write MDX queries within Analysis Services Stored procedures utilizing the Microsoft.AnalysisServices.AdomdServer library. Is this correct ?

I am able to execute DMX queries but not sure about MDX

Can anyone elaborate on this ?

thanks

anil

Not sure with it, but you can check in:

http://technet.microsoft.com/en-us/library/ms175314.aspx

http://www.codeplex.com/ASStoredProcedures/Project/ProjectRss.aspx

|||

That is basically correct. Stored Procedures in SSAS can return things like values, members, sets, tuples, but they are not built to execute queries. At least in their current form, "Stored Procedures" is not really a good name, they are more like .Net user defined functions.

Technically it would be possible to use the CALL syntax to open up a second ADOMD connection and execute an MDX query and return the result as a rowset, but that would require that the stored proc have unrestricted permissions and it is not really a recommended practice.

|||

Darren

Thanks, i agree that this should really be called a .NET user defined function

I was hoping to obtain a AdomdDataReader for an MDX query and process the results in the .NET user defined function, so getting a rowset will not work for me. I will have to resort to processing on the client, using the client libraries

anil

MDX queries on SQL Server OLAP using Java

Hi,

We are trying use MS SQL Server OLAP and we want to fire MDX queries to SQL Server OLAP using Java. Can someone guide me on how can we do this?

Regards,

~Kanchan.

As an example you can take a look at this open source project: REX http://sourceforge.net/projects/whex/

It is written in Java, and it is general purpose OLAP client tool which works against Analysis Services. Since it's open source you can go through the code and see how it works.

HTH,

Mosha (http://www.mosha.com/msolap)

MDX Queries in SSIS

Posted to SSIS forum but was told this forum might be of better help.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=215075&SiteID=1

Essentially I'm using an OLE DB Source to fire off an MDX Query and I want to dump the results into a csv file.

I think I have this setup properly but when I run the package I get the following error.

An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2005" Hresult: 0x80004005 Description: "Internal error: An unexpected error occurred (file 'pcrsstore.cpp', line 325, function 'PCRSStore::UnLock').".

Can seem to find anything about this error, can anyone help?

Keith

Hi Keith.

First
Have you installed SP1 on you machine. If not, please try installing it and trying your experiment again.

Second.
Can you explain in a bit more details the way you've build your task in SSIS

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Yes I have installed SP1 on both the Ananlysis Server and my Client tools.

The tasks are pretty simple. I have 2 connections and 1 Data Flow Task.

Create OLEDB Connection, select
Native OLE DB\Microsoft OLE DB Provider for Analysis Services 9.0
as OLE DB provider, enter connection information.

In data flow, create OLE DB Source component, choose this connection, in Data access mode field select SQL command. Enter your MDX query in SQL command text box.

At this point I can preview the results of the query after receiving the following warning.

Warning at {C498A441-B272-48F7-AA8E-1C1BC11DDCD2} [OLE DB Source [1]]: The output "OLE DB Source Output" (11) references an external data type that cannot be mapped to a Data Flow task data type. The Data Flow task data type DT_WSTR will be used instead.

I then see the results of the MDX query and the data is returned as expected.

I then have a Flat File Destination setup where I point the Data Flow Output to. It's a simple 2 column 1 row result set. I recieve no warnings or errors in the designer but when I run the package I get the error as explained above.

Just as a sanity check becuase this is my first time working in SSIS. I Changed the query to be a simple SQL query returning 2 rows and was able to run that just fine so I think I have the data flow setup properly.

In addition I have run a SQL Profile and I can see the query being sent to AS and the results being returned.

Appreciate the help.

|||

This looks like a bug with Analysis Services.

If you have some ideas try to work around it using SQL like syntax when sending query to Analysis services.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

MDX queries from Reporting Services

I have been having some issues with generating reports in Reporting Services off Analysis Services, in that the reports take a very long time and I have to often kill the queries. They are using 100%CPU. I have then replicated as closely as possible the report using the Cube Analysis add in for Excel, and the response is almost instantaneous.

It seems it is the MDX that Reporting Services is very inefficient compared to that generated by Cube Analysis. It uses Selects in the From clause to restrict the scope of the query using the Filter parameters. Is this something anyone has come across, and are there any suggestions to get round it? I did try to paste the Cube Analysis MDX into Reporting Services but the parser rejected it as invalid.

I attach the comparitive MDX below for reference

Cube Analysis
SELECT NON EMPTY{Hierarchize({CROSSJOIN({CROSSJOIN({{{[Portfolio].[Benchmark Portfolio].[All],[Portfolio].[Benchmark Portfolio].[All].children} + {{[Portfolio].[Benchmark Portfolio].[Benchmark].&[1].children}}}},{{[Scenario].[Scenario].&[1],[Scenario].[Scenario].&[1].children} + {[Scenario].[Scenario].&[3],[Scenario].[Scenario].&[3].children} + {[Scenario].[Scenario].[Benchmark],[Scenario].[Scenario].[Benchmark].children} + {[Scenario].[Scenario].[Variance to Benchmark],[Scenario].[Scenario].[Variance to Benchmark].children}})},{{[Measures].[Duration]} + {[Measures].[Weighting]}})})} on 0, NON EMPTY{Hierarchize({{[Sector].[Sector Category].[All],[Sector].[Sector Category].[All].children}})} on 1 from [MIQB27] where ([Period].[Year- Month].[Month].&[2006-03-01T00:00:00],[Benchmark].[Benchmark].&[1],[Rating].[Rating Category].[All],[Currency of Exposure].[Currency].&[2]) CELL PROPERTIES VALUE, FORMATTED_VALUE, FONT_NAME, FONT_SIZE, FONT_FLAGS,FORE_COLOR, BACK_COLOR, FORMAT_STRING

Reporting Services

SELECT NON EMPTY { [Measures].[Duration], [Measures].[Weighting] } ON COLUMNS, NON EMPTY { ([Sector].[Sector Category].[Sector Category].ALLMEMBERS * [Benchmark].[Benchmark].[Benchmark].ALLMEMBERS * [Portfolio].[Portfolio].[Portfolio].ALLMEMBERS * [Scenario].[Scenario].[Scenario].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Scenario].[Scenario].&[1] } ) ON COLUMNS FROM ( SELECT ( { [Rating].[Rating Category].&[38] } ) ON COLUMNS FROM ( SELECT ( { [Benchmark].[Benchmark].&[1] } ) ON COLUMNS FROM ( SELECT ( { [Period].[Year- Month].[Month].&[2006-03-01T00:00:00] } ) ON COLUMNS FROM [MIQB27])))) WHERE ( [Period].[Year- Month].[Month].&[2006-03-01T00:00:00], [Rating].[Rating Category].&[38] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

It's hard to say without seeing your cube, but I suspect one reason for the difference is the fact that RS forces you to pivot your queries so that you only have the measures dimension on columns. This means that you have to put all the rest of your dimensions crossjoined on rows, which could potentially mean that the NON EMPTY operation has a lot of tuples to work on. How many members are there in the Sector Category, Benchmark, Portfolio and Scenario attributes?

Does changing the MDX in RS to be something like the following make a difference?

SELECT NON EMPTY { [Measures].[Duration], [Measures].[Weighting] } ON COLUMNS,
NON EMPTY { (
NONEMPTY(
[Sector].[Sector Category].[Sector Category].ALLMEMBERS
, { [Measures].[Duration], [Measures].[Weighting] })
* [Benchmark].[Benchmark].[Benchmark].ALLMEMBERS * [Portfolio].[Portfolio].[Portfolio].ALLMEMBERS *
[Scenario].[Scenario].[Scenario].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
( SELECT ( { [Scenario].[Scenario].&[1] } ) ON COLUMNS FROM ( SELECT ( { [Rating].[Rating Category].&[38] } ) ON COLUMNS FROM ( SELECT ( { [Benchmark].[Benchmark].&[1] } ) ON COLUMNS FROM ( SELECT ( { [Period].[Year- Month].[Month].&[2006-03-01T00:00:00] } ) ON COLUMNS FROM [MIQB27])))) WHERE ( [Period].[Year- Month].[Month].&[2006-03-01T00:00:00], [Rating].[Rating Category].&[38] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Chris

|||I have changed the cube slightly - I had one dimension called Benchmark which I removed it only has 3 leaf members and the ALL level. I had the Benchmark code on another dimension as an attribute so I thought I would use this instead. The RS query now worlks very quickly. On the face of it the MDX query looks just as complex (see below). The only thing I canm think of is as Benchmark was an attribute of Portfolio and a dimension in its own right, it might have been doing some Cartesian Product. But even so there were only 3 members.
Anyway, I am glad the issue has gone away - I was beginning to get worried about the use of RS on top of AS.
Regards
Paul

SELECT NON EMPTY { [Measures].[Weighting], [Measures].[Duration] } ON COLUMNS, NON EMPTY { ([Rating].[Rating Category].[Rating Category].ALLMEMBERS * [Sector].[Sector Category].[Sector Category].ALLMEMBERS * [Portfolio].[Benchmark Portfolio].[Portfolio].ALLMEMBERS * [Scenario].[Scenario].[Scenario].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@.CurrencyofExposureCurrency, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Scenario].[Scenario].&[1], [Scenario].[Scenario].&[3] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.PortfolioBenchmark, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.PeriodYearMonth, CONSTRAINED) ) ON COLUMNS FROM [MIQB28])))) WHERE ( IIF( STRTOSET(@.PeriodYearMonth, CONSTRAINED).Count = 1, STRTOSET(@.PeriodYearMonth, CONSTRAINED), [Period].[Year- Month].currentmember ), IIF( STRTOSET(@.PortfolioBenchmark, CONSTRAINED).Count = 1, STRTOSET(@.PortfolioBenchmark, CONSTRAINED), [Portfolio].[Benchmark].currentmember ), IIF( STRTOSET(@.CurrencyofExposureCurrency, CONSTRAINED).Count = 1, STRTOSET(@.CurrencyofExposureCurrency, CONSTRAINED), [Currency of Exposure].[Currency].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

|||

I am curious about your table structure. It looks like you are trying to do the same thing I am. Comparing portfolios to benchmarks for various groupings. Are weightings a calculated field? Do you have an overweight/underweight calculation? Do you use a PortfolioBenchmark dimension table or break them out?

Thanks for any help.

MDX queries from Reporting Services

I have been having some issues with generating reports in Reporting

Services off Analysis Services, in that the reports take a very long

time and I have to often kill the queries. They are using 100%CPU. I have then replicated as

closely as possible the report using the Cube Analysis add in for

Excel, and the response is almost instantaneous.

seems it is the MDX that Reporting Services is very inefficient

compared to that generated by Cube Analysis. It uses Selects in the From clause to restrict the scope of the query using the Filter parameters. Is this something anyone has come across, and are there any suggestions to get round it? I did try

to paste the Cube Analysis MDX into Reporting Services but the parser

rejected it as invalid.

I attach the comparitive MDX below for reference

Cube Analysis
SELECT

NON EMPTY{Hierarchize({CROSSJOIN({CROSSJOIN({{{[Portfolio].[Benchmark

Portfolio].[All],[Portfolio].[Benchmark Portfolio].[All].children} +

{{[Portfolio].[Benchmark

Portfolio].[Benchmark].&[1].children}}}},{{[Scenario].[Scenario].&[1],[Scenario].[Scenario].&[1].children}

{[Scenario].[Scenario].&[3],[Scenario].[Scenario].&[3].children}

{[Scenario].[Scenario].[Benchmark],[Scenario].[Scenario].[Benchmark].children}

+ {[Scenario].[Scenario].[Variance to

Benchmark],[Scenario].[Scenario].[Variance to

Benchmark].children}})},{{[Measures].[Duration]} +

{[Measures].[Weighting]}})})} on 0, NON

EMPTY{Hierarchize({{[Sector].[Sector Category].[All],[Sector].[Sector

Category].[All].children}})} on 1 from [MIQB27] where

([Period].[Year-

Month].[Month].&[2006-03-01T00:00:00],[Benchmark].[Benchmark].&[1],[Rating].[Rating

Category].[All],[Currency of Exposure].[Currency].&[2]) CELL

PROPERTIES VALUE, FORMATTED_VALUE, FONT_NAME, FONT_SIZE,

FONT_FLAGS,FORE_COLOR, BACK_COLOR, FORMAT_STRING

Reporting Services

SELECT NON EMPTY

{ [Measures].[Duration], [Measures].[Weighting] } ON

COLUMNS, NON EMPTY { ([Sector].[Sector Category].[Sector

Category].ALLMEMBERS *

[Benchmark].[Benchmark].[Benchmark].ALLMEMBERS

* [Portfolio].[Portfolio].[Portfolio].ALLMEMBERS

* [Scenario].[Scenario].[Scenario].ALLMEMBERS

) } DIMENSION PROPERTIES

MEMBER_CAPTION, MEMBER_UNIQUE_NAME

ON ROWS FROM ( SELECT ( {

[Scenario].[Scenario].&[1] } ) ON COLUMNS FROM ( SELECT ( { [Rating].[Rating Category].&[38] } ) ON COLUMNS FROM ( SELECT ( {

[Benchmark].[Benchmark].&[1] } ) ON COLUMNS FROM ( SELECT ( { [Period].[Year-

Month].[Month].&[2006-03-01T00:00:00] } ) ON

COLUMNS FROM

[MIQB27])))) WHERE ( [Period].[Year-

Month].[Month].&[2006-03-01T00:00:00], [Rating].[Rating Category].&[38]

) CELL PROPERTIES

VALUE, BACK_COLOR,

FORE_COLOR, FORMATTED_VALUE,

FORMAT_STRING, FONT_NAME,

FONT_SIZE, FONT_FLAGS

Does changing the MDX in RS to be something like the following make a difference?

Chris

|||I have changed the cube slightly - I had one dimension called Benchmark

which I removed it only has 3 leaf members and the ALL level. I had

the Benchmark code on another dimension as an attribute so I thought I

would use this instead. The RS query now worlks very quickly. On the

face of it the MDX query looks just as complex (see below). The only

thing I canm think of is as Benchmark was an attribute of Portfolio and

a dimension in its own right, it might have been doing some Cartesian

Product. But even so there were only 3 members.

Anyway, I am glad the issue has gone away - I was beginning to get worried about the use of RS on top of AS.

Regards

Paul

SELECT NON EMPTY

{ [Measures].[Weighting], [Measures].[Duration] } ON

COLUMNS, NON EMPTY { ([Rating].[Rating Category].[Rating

Category].ALLMEMBERS * [Sector].[Sector

Category].[Sector Category].ALLMEMBERS *

[Portfolio].[Benchmark Portfolio].[Portfolio].ALLMEMBERS

* [Scenario].[Scenario].[Scenario].ALLMEMBERS

) } DIMENSION PROPERTIES

MEMBER_CAPTION, MEMBER_UNIQUE_NAME

ON ROWS FROM ( SELECT ( STRTOSET(@.CurrencyofExposureCurrency, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Scenario].[Scenario].&[1],

[Scenario].[Scenario].&[3] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@.PortfolioBenchmark,

CONSTRAINED) ) ON

COLUMNS FROM ( SELECT ( STRTOSET(@.PeriodYearMonth,

CONSTRAINED) ) ON

COLUMNS FROM

[MIQB28])))) WHERE ( IIF(

STRTOSET(@.PeriodYearMonth, CONSTRAINED).Count =

1, STRTOSET(@.PeriodYearMonth, CONSTRAINED), [Period].[Year- Month].currentmember ), IIF(

STRTOSET(@.PortfolioBenchmark, CONSTRAINED).Count =

1, STRTOSET(@.PortfolioBenchmark, CONSTRAINED), [Portfolio].[Benchmark].currentmember ), IIF(

STRTOSET(@.CurrencyofExposureCurrency, CONSTRAINED).Count =

1, STRTOSET(@.CurrencyofExposureCurrency, CONSTRAINED), [Currency of Exposure].[Currency].currentmember ) ) CELL

PROPERTIES VALUE,

BACK_COLOR, FORE_COLOR,

FORMATTED_VALUE, FORMAT_STRING,

FONT_NAME, FONT_SIZE,

FONT_FLAGS

|||

Thanks for any help.

MDX queries

Hi ,
I am planning to do datawarehousing and develop some cubes but i am not too
sure of how to use MDX queries .
Where can i get help on MDX queries ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...house/200601/1
I guess you can find good material at dev.hyperion.com or even in msdn
site. or best is grab yourself a copy of "MDX solutions by geroge
spafford".
Abhijeet

MDX queries

Hi ,
I am planning to do datawarehousing and develop some cubes but i am not too
sure of how to use MDX queries .
Where can i get help on MDX queries ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...ehouse/200601/1I guess you can find good material at dev.hyperion.com or even in msdn
site. or best is grab yourself a copy of "MDX solutions by geroge
spafford".
Abhijeet

MDX problem in AS2005

When executing the following MDX statements I get an error with AS2005 although the query seems to be valid, at least AS2000 has no problems with it.

CREATE SET [Sales].[Y1] AS '{[Product].[All Products].[Drink]}'

CREATE MEMBER [Sales].[Product].[X] AS 'Sum([Sales].[Y1])'

SELECT Order({[Product].[X], [Y1]}, [Measures].[Unit Sales]) ON AXIS(0) FROM [Sales]

I create a set, a calculated member as the sum of this set, and then query both the calculated member and the set. The error is: "The dimension [Sales] was not found in the cube when the string, [Sales].[Y1], was parsed." It seems that [Y1] is not recognized as the formerly defined named set.

This query works if query scope is used (WITH SET, WITH MEMBER), or if you don't use the Order() function. It's always ok with AS2000.

The error occurs on every AS2005 cube I tested. The example above is the Foodmart 2000 database migrated to AS2005.

What's wrong?

Thanks. Guido.

Hi Guido,

I was able to reproduce this error in query scope with Adventure Works as follows:

With Member [Product].[Product Categories].[X] as

Sum([Adventure Works].[Core Product Group],

[Measures].[Sales Amount])

select Order({[Product].[Product Categories].[X],

[Core Product Group]},

[Measures].[Sales Amount]) on 0

from [Adventure Works]

Query (2, 5) The dimension '[Adventure Works]' was not found in the cube when the string, [Adventure Works].[Core Product Group], was parsed.

But this other version, without specifying the cube, works as expected:

With Member [Product].[Product Categories].[X] as

Sum([Core Product Group],

[Measures].[Sales Amount])

select Order({[Product].[Product Categories].[X],

[Core Product Group]},

[Measures].[Sales Amount]) on 0

from [Adventure Works]

Accessories Clothing Components Bikes Touring Bikes Mountain Bikes Road Bikes X
$571,297.93 $1,777,840.84 $11,799,076.66 $66,302,381.56 $10,451,490.22 $26,492,684.38 $29,358,206.96 $204,429,800.41

So I suspect the issue may be that AS 2005 is trying to cast a member to a set. But wouldn't this syntax for the calculated member work with both?

CREATE MEMBER [Sales].[Product].[X] AS 'Sum([Y1])'

|||

Thanks for your answer.

You are right. "Sum([V1])" solves this problem. I've included the cube name due to another problem:

CREATE SET [Sales].[Y1] AS '{}'
CREATE MEMBER [Sales].[Product].[X] AS 'Sum([Y1])'
DROP MEMBER [Sales].[Product].[X]
DROP SET [Sales].[Y1]

The last statement yields error message "Unable to drop the object 'Y1', because it is referenced by at least one another object."

No other calculations are involved, and the calculated member is dropped before the named set. If the calculated member is defined as "Sum([Sales].[Y1])" the named set is dropped correctly. There are no such problems with AS2000.

Any idea what's wrong?

Thanks

Guido

|||

Guido,

Unfortunately, I can't shed much light on this problem - I didn't work with CREATE/DROP in AS 2005, since I typically make changes to the MDX Script and redeploy. If you don't receive any response here, you could open an issue at:

http://lab.msdn.microsoft.com/ProductFeedback/.

Saturday, February 25, 2012

MDX Query Filters

Mdx Query doubt about years?

MDX Query designer working correctly?

MDX query designer behaving correctly in Reporting Services?

MDX query data displays in data tab but not on the report when using a VB udf

MDX query count of new customers with a new flag who purchased in this period and following peri

MDX query count of customers who purchased in prior period and this period

MDX Query Builder

MDX Query Builder

MDX Query builder

MDX query based on a subquery

MDX query based on a subquery

MDX query based on a subquery

MDX Query above 8000 characters

MDX query (was "syntax error")

MDX Query "paging file too small" problem

MDX Query "paging file too small" problem

MDX Query - Help needed from experienced

MDX Query - Help needed from experienced

mdx query

MDX Query

MDX Query

MDX query

Mdx query

MDX Query

MDX Query

MDX query

mdx query

MDX Queries within .NET SSAS stored procedures (using ADOMD.NET Server library)

MDX queries on SQL Server OLAP using Java

MDX Queries in SSIS

MDX queries from Reporting Services

MDX queries from Reporting Services

MDX queries

MDX queries

MDX problem in AS2005

MDX hierarchy problem

Blog Archive

About Me