MeasureGroups at different granularity in the UDM

We have run into an issue with Report Builder and was wondering if anyone else has experienced this problem. We have two different measure groups. The first group is transactional at the daily grain and the other is an accounting balance table at the month grain. The measures in the second group are semi-additive. Everything against the cube is working fine. However, when a report model is generated against the cube, queries cannot be built between the semi-additive measures and the date dimension. The transactional table is fine. Has anyone else seen this behavior? I should also note that the month key is the same key as the last day in the month for business reasons. Month account balances should be converted to foreign currencies on the basis of the month end conversion rate and we are only tracking a single conversion rate table. We could always redesign this if needed.

I’m afraid that Report Builder models over a cube can only expose relationships from a measure group to a dimension that are at the key granularity. Other relationships (from the accounting balance measure group in your case) are ignored. The only alternative is to model in the cube as being at the day level (related to the last day of the month), though this would effect how it is seen through other clients.

|||

Thanks for your answer even that's not the answer I was hoping to get. I originally had the Account Balance measure group associated with the last day of the month but the query performance was very poor. By changing the association to the month level, a representative query that was running for minutes returned in less than a second. Even with a NonEmpty crossjoin of six levels, one with 130,000 members and another with 5,000 members! Gee, think AS 2005 is a bit of an improvement over AS 2000? Therefore, switching back is not an option.

Any chance this will be fixed in the future? Because I actually think the interface for Report Builder is more intuitive than ProClarity. We just haven't really found an Adhoc query tool for AS that we really like yet.

sql

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 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