Dimension Filter in MDX

Hi guys,

I need to create a report using data from our OLAP. I'm using a MDX query to do it, but I founded some limitations that I want to know if its real or just my mistakes. I have a the following dimensions

- "Projects", that have Project Name and Company name

- "Company", that have my company name too

- "Months", that have the months and years about projects

If I'm developing a sql query, its seems like this:

select * from Projects p, Months m

where p.company in ('A', 'B', 'C')

and m.Year = 2007

But when I'm using MDX queries, I need the same flexibility to filter more than one data (like the IN at the sql query above) from the dimension that I'm looking for (like filter p.company from my PRojects tables)

Here is the sample of my code. If you look inside it, I'm filtering the best I can in the mdx query and after, I'm filtering better using T-sql. Its works fine, but its expensive and I need to get better performance. I want to filter my company using the project dimension and not company dimension. I'm also want to filter the Month dimension at my mdx query, but its says that I cant because I'm using it as a row member.

SELECT

"[Measures].[codProject]" as codProj,

"[Measures].[Month]" asMonth,

isnull("[Measures].[Valor Total]",0)as vlrTotal

FROM

OPENQUERY( DW2007,''

with

member [Measures].[codProject] as'[Project].Properties("key")'

member [Measures].[Month] as'[Month].Properties("key")'

SELECT

NON EMPTY { [Measures].[Valor Total], [Measures].[codProject], [Measures].[Month] }ON COLUMNS,

non empty {[Project].[Project].Members * [Month].[Month].MEMBERS }ON ROWS

FROM [Budget]

where( [Company].['+ rtrim(@.nmCompany) +'], [Year].['+ cast(@.Year as char(4)) +'] )

'')

wherecast("[Project].[Company].[member_caption]" as varchar(10))='''+ rtrim(@.nmCompany) +'''

andcast("[Month].[Year].[MEMBER_CAPTION]" aschar(4))=' + cast(@.Year as char(4)) + ''

Can anyone help me ?

Best Regards,

Tritech - Consultoria e Desenvolvimento de Solu??es em TI

I think the problem is that the "non empty" clauses are not really doing much as the two calculated measures that get the key properties will return a non empty value for every month and project. Something like the following, where I have used the iif() function to only return a value. Possibly even excluding these measures from the MDX query and then joining back to the dimension table in T-SQL might be faster.

SELECT

"[Measures].[codProject]" as codProj,

"[Measures].[Month]" asMonth,

isnull("[Measures].[Valor Total]",0)as vlrTotal

FROM

OPENQUERY( DW2007,''

with

member [Measures].[codProject] as'iif(IsEmpty(Measures.[Valor Total]),NULL,[Project].Properties("key"))'

member [Measures].[Month] as'iif(IsEmpty(Measures.[Valor Total]),NULL,[Month].Properties("key"))'

SELECT

NON EMPTY { [Measures].[Valor Total], [Measures].[codProject], [Measures].[Month] }ON COLUMNS,

non empty {[Project].[Project].Members * [Month].[Month].MEMBERS }ON ROWS

FROM [Budget]

where( [Company].['+ rtrim(@.nmCompany) +'], [Year].['+ cast(@.Year as char(4)) +'] )

'')

wherecast("[Project].[Company].[member_caption]" as varchar(10))='''+ rtrim(@.nmCompany) +'''

andcast("[Month].[Year].[MEMBER_CAPTION]" aschar(4))=' + cast(@.Year as char(4)) + ''

|||

Hi Darren,

Tks for your reply, but I want to know if can I put my sql where clause into the mdx query ? If I can, will be faster and solve many problems for me. I look in some place, that you can select a "cube", using a select like one. as its show in the folowing query that I get from Microsoft Team Foundation Server reports. Here its use a select with another, but using parameter, anyone know if can I do this with queries and dimensions ?

SELECT
NON EMPTY {
[Measures].[Current Work Item Microsoft_VSTS_Scheduling_BaselineWork], [Measures].[Current Work Item Microsoft_VSTS_Scheduling_CompletedWork], [Measures].[Current Work Item Microsoft_VSTS_Scheduling_RemainingWork] } ON COLUMNS,
NON EMPTY {
([Assigned To].[Person].[Person].ALLMEMBERS * [Work Item].[System_Title].[System_Title].ALLMEMBERS * [Work Item].[System_State].[System_State].ALLMEMBERS )
}
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS
FROM (
SELECT
( STRTOSET(@.MicrosoftVSTSSchedulingFinishDateYearMonthDate, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.MicrosoftVSTSSchedulingStartDateYearMonthDate, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.AssignedToPerson, CONSTRAINED) ) ON COLUMNS
FROM (
SELECT ( STRTOSET(@.TeamProjectTeamProject, CONSTRAINED) ) ON COLUMNS
FROM [Team System]
)
)
)
)
WHERE ( IIF( STRTOSET(@.TeamProjectTeamProject, CONSTRAINED).Count = 1,
STRTOSET(@.TeamProjectTeamProject, CONSTRAINED),
[Team Project].[Team Project].currentmember ),
IIF( STRTOSET(@.MicrosoftVSTSSchedulingStartDateYearMonthDate, CONSTRAINED).Count = 1,
STRTOSET(@.MicrosoftVSTSSchedulingStartDateYearMonthDate, CONSTRAINED), [Microsoft_VSTS_Scheduling_StartDate].[Year Month Date].currentmember ), IIF( STRTOSET(@.MicrosoftVSTSSchedulingFinishDateYearMonthDate, CONSTRAINED).Count = 1, STRTOSET(@.MicrosoftVSTSSchedulingFinishDateYearMonthDate, CONSTRAINED), [Microsoft_VSTS_Scheduling_FinishDate].[Year Month Date].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

|||

hi Darrel,

I copy the query that you changed but doesnt work, but after I make some changes, its works. This make sense ? tks

BEFORE CHANGES

with

member [Measures].[codProject] as'iif(IsEmpty(Measures.[Valor Total]),NULL,[Project].Properties("key"))'

AFTER

with

member [Measures].[ProjectCode] as '[Project].Properties("key")'

member [Measures].[codProject] as'iif(IsEmpty(Measures.[Valor Total]),NULL, Measures.[ProjectCode] )'

Tritech Informática
Brasilia
Brasilsql