Hi
How to implement OR clause in MDX Queries so that i can write a query with OR condition on two dimensions.
If possible please post some example.
Regards;
Rakesh
Sets containing either members or tuples are now supported in the WHERE clause of an MDX statement.
Here is an example:
SELECT { [Time].[Calendar].[Quarter].&[2005904] } ON COLUMNS ,
NON EMPTY { TOPCOUNT( { DESCENDANTS( [Customer].[Customer].[All Customer], [Customer].[Customer].[Customer] ) }, 10, ( [Time].[Calendar].[Quarter].&[2005904], [Measures].[Sales Amt] ) ) } ON ROWS
FROM [Sales]
WHERE (
{ ([Product].[Family].[Business PCs], [Geography].[City].[Albany] )
,([Product].[Family].[Business PCs], [Geography].[City].[New York] )
,([Product].[Family].[Home PCs], [Geography].[City].[Albany] ) },
[Measures].[Sales Amt] )
The result of this query will be the top 10 customers based on Q4 2005 sales for customers who:
Bought a "Business PC" and live in "Albany"
OR
Bought a "Business PC" and live in "New York"
OR
Bought a "Home PC" and live in "Albany"
HTH,
- Steve
|||Hi Steve
Thanks for your reply. The solution you provided doesn't work at my end. Am i making any mistake in impletementing the same? Here is the my case:
Let us take an example to illustrate this. Let say we have four tables; Users, UserAccessBranch, Branch and FactLoan. User’s access to particular loan is based on his access to loan’s Branch (BranchID or OperatingBranchID). If user has access to at-least one of the two branches (BranchID and OperatingBranchID) then the loan is accessible to the user.
Users.UserIDà BranchAccess(UserID,BranchID)?Branch.BranchIDàFcatLoan.BranchID
àFactLoan.OperationalBranchID
In case of T-SQL it’s very easy to implement. In case of MDX it’s easy to implement if there is only one column of BranchID is there in FactLoan. I have implemented this case (having one BranchID) by having Many-to-Many relationship in the “Dimension Usage” of cube designer. How can we implement the logic for two Branch access (either Or) in MDX and cube design.
The MDX queries i am trying are:
This works for BranchID check
SELECT [Measures].[Fact Loan Count] on 0
FROM (SELECT ([BranchUsers].[Users].&[1.])
ON COLUMNS FROM [SANDBOX])
This works for OperatingBranchID check
SELECT [Measures].[Fact Loan Count] on 0
FROM (SELECT ([OperatingBranchUsers].[Users].&[1.])
ON COLUMNS FROM [SANDBOX])
This DOESN'T works for both
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {(([BranchUsers].[Users].&[1.]),([OperatingBranchUsers].[Users].&[1.]))}
Any suggestion is most welcome. Thanks a lot.
|||Rakesh,
Try the following:
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {([BranchUsers].[Users].&[1.],[OperatingBranchUsers].[Users].DefaultMember),
([BranchUsers].[Users].DefaultMember,[OperatingBranchUsers].[Users].&[1.])}
The tuples should have the same "signature". So what you are seeing is "BranchUsers = 1 AND OperatingBranchUsers = Any" OR "BranchUsers = Any AND OperatingBranchUsers = 1".
HTH,
Steve
|||Steve
Thanks for you prompt reply. Sorry, but its giving me following error.
#Error Arbitrary shape is not allowed when its elements cross a reference dimension.
Am I missing something? Is it because i am using Many-to-Many relationship between Fact table and Users dimension?
Please suggest.
Rakesh
|||Rakesh,
I have not tried this with a Many-to-Many dimension relationship involved and you may have found a bug. I don't think you are missing anything, so you should report this to support and hopefully it is already fixed in SP2.
HTH,
Steve
|||Yes - this is fixed in SP2 - both for many-to-many and for reference dimensions, and not only for arbitrary shapes in WHERE clause but also for arbitrary shapes in subselects. I briefly mentioned it here: http://www.sql.ru/forum/actualthread.aspx?tid=360352#3400111
HTH,
Mosha (http://www.mosha.com/msolap)
|||You might like to look at the Analysis Services Stored Procedure Project http://www.codeplex.com/Wiki/View.aspx?ProjectName=ASStoredProcedures. I built a function called AsymmetricSet to facilitate the easy construction of sets like these. So your query would look like the following.
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE ASSP.AsymmectricSet([BranchUsers].[Users].&[1.],[OperatingBranchUsers].[Users].&[1.])
|||
Hi Mosha
Thanks for your post on the forum.
On SP1 the MDX script gives me the error “#Error Arbitrary shape is not allowed when its elements cross a reference dimension”. Whereas on SP2 it displays the result. So; I think they have made the required changes for referenced dimension or M2M relationships. But I still believe that there is some issue with this kind of query and cube design. The result of the query
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {
(([Users].[Users].&[2]),([Operating Users].[Operating Users].DefaultMember)),
(([Users].[Users].DefaultMember),([Operating Users].[Operating Users].&[2]))
}
is not as per expectations. Following is the SQL query:
Select Count(*)
From FactLoan r
Where
( CASE WHEN (SELECT Top 1 1 from UserAccessBranch ba with (nolock) where ba.BranchID = r.OperatingBranchID and UserID = 2 ) > 0 THEN
1
ELSE
CASE WHEN (SELECT Top 1 1 from UserAccessBranch ba with (nolock) where ba.BranchID = r.BranchID and UserID = 2 ) > 0 THEN
1
ELSE
0
END
END
)=1
The output of the MDX query doesn’t matches with the SQL query output.
Please help out in understanding the cause for this. Thanks a lot. ?
Thanks & Regards;
Rakesh
|||
Hi Darren
Thanks for your post on the forum.
I have successfully installed the ASSP DLL but the output of the query
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE [ASSP].[AsymmetricSet]([Users].[Users].&[1.],[Operating Users].[Operating Users].&[1.])
doesn’t give the correct result. Am I missing some thing?
Regards,
Rakesh
|||It's hard to say without knowing what result you are getting and what you were expecting.
You can display the output of the function by running the following query. This should let you validate if the function is generating the same set as the one coded by hand.
WITH Member [Measures].[OrSetStr] AS SetToStr([ASSP].[AsymmetricSet]([Users].[Users].&[1.],[Operating Users].[Operating Users].&[1.]))
SELECT [Measures].[OrSetStr] on 0
FROM [SANDBOX]
|||
Rakesh,
I just had a read of your reply to Mosha's post and have the following observations.
If you are using a many-to-many relationship this would imply that one branch can have many users, and that one user can belong to many branches.
Therefore there would have to be a many-to-many "bridge table" which would be set up in the UDM as a measure group which would be used in the many-to-many relationship. In your SQL query the dimension table links straight to the fact table and there does not appear to be any joins to the table (or view) that would be facilitating the many-to-many relationship in the UDM. This may be why you are seeing different results between the MDX and the SQL.
|||Hey Darren
Thnaks for you time and reply to this question.
Actually I have the "bridge table" in my UDM and that "bridge table"is being used as "fact less" fact table. I have got the correct MDX (that meets my requirement) from Chris. The correct query that matches with my requirement is:
SELECT [Measures].[Fact Loan Count] on 0
FROM [SANDBOX]
WHERE {
( [Operating Branch].[Operating Branch].defaultmember *
exists([Branch].[Branch].[Branch].members, [Users].[Users].&[2], "User Access Branch")
),
( exists([Operating Branch].[Operating Branch].[Operating Branch].members, [Operating Users].[Operating Users].&[2], "Operating Branch Access")
* [Branch].[Branch].defaultmember
)
}
Thanks again for all your support and advice. :)
Regards;
Rakesh
Having seen Rakesh's data model offline this approach won't work: going through the two m2m dimensions he set up it only seemed possible to get an AND and not an OR. Doing an Exists through the two intermediate measure groups that each User dimension was using to join to the main measure group as in my query above, so you found all the Branches each user had a relationship with, was the only thing that worked.
Chris
No comments:
Post a Comment