Hierarchy is used more than once in the Crossjoin function

When I do this in Adventure Works:

SELECT NON EMPTY {
[Customer].[Customer Geography].[State-Province].Members *
[Customer].[Customer Geography].[City].Members
}
ON AXIS(0)
FROM [Adventure Works]

I get this: "The Customer Geography hierarchy is used more than once in the Crossjoin function."

How do I do the equivalent in an MDX statement that works? I want users to be able to drag multiple hierarchy levels onto the same axis. I have spyed on the MDX that BIDS is generating, and it seems to be cheating by not getting the states and citys back in the same MDX statement. It looks like its combining the results of two queries at run-time as the user expands nodes in the tree. Isn't there a better way?

Thanks,

Terry

Hi Terry,

Any reason why cross-joining the attribute hierarchies instead won't work for you, like:

SELECT NON EMPTY {

[Customer].[State-Province].[State-Province].Members *

[Customer].[City].[City].Members

}

ON AXIS(0)

FROM [Adventure Works]

|||

Hi Deepak,

Thanks for your reply. Our application allows users to drag-and-drop whatever they want, so in this instance I don't want to prevent the user from putting two hierarchy levels on the same axis. Maybe the solution is somehow use metadata to find the attribute names that built the hierarchy levels and use the attribute names behind the scenes in the crossjoin function? Is that possible?

|||You can use AMO to retrieve the necessary metadata to identify which attribute underlies a hierarchy level and its corresponding attribute hierarchy.

Hiding table contents without sacrificing too much on performance?

An application uses a database table having proprietary information. We do not want our customer to be able to look at that.

This being a real-time application, performance can not be sacrificed. What is the best way to keep the table data non-viewable without sacrificing the performance?

The easiest way to do this without sacrificing performance is to use permissions and deny select or view permissions on the table in question. Create a special group and only grant that one group access to the table.

This, of course, only protects against an online attack (i.e. the server is running). To protect your database against an offline attack (i.e. someone steals your hard drive), you will need to use some form of encryption. Unfortunately, this does require a performance hit. Depending on how you structure your data, this could be either very large or pretty minor.

If your customer is a database admin, the only way to secure against this would be to use encryption.

Sung

|||

I think Rajeev is looking for some DRM type of solution, in which case this has already been discussed on threads like http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=52094&SiteID=1.

Thanks
Laurentiu