[Mondrian] MemberGrantTable feature Proposal

[Julian Hyde]

Nick,

This is a feature that will be useful to many people. Thanks for bringing up
the discussion.

If I may, I will pick this up and run with it... and suggest an alternate
approach for discussion.

The idea would be to use an MDX expression in the HierarchyGrant that would
be evaluated for each member to determine whether it is visible.

This is powerful because MDX is very expressive, for example

Case
when Ancestor([Store], [Store].[Store City]) is [Store].[USA].[CA].[Los
Angeles]
then 'none'
when Ancestor([Store], [Store].[Store State]) is [Store].[USA].[CA]
then 'all'
else 'none'
End

expresses the 'static' access rule you used in your example. A dynamic rule
can be coded by using a property of the member, for example

[Store].CurrentMember.Properties("visibility")

where you have defined the property "visibility" to map to a particular
column in the Customer table. Or it can read from a table. First make an
auxiliary cube, whose fact table is the table describing what access each
member has:

<Cube name="StoreGrants">
   <Table name="STORE_GRANTS"/>
   <Dimension name="Role">
     <Hierarchy>
       <Level name="Role" column="therole"/>
     </Hierarchy>
   </Dimension>
   <DimensionUsage name="Store"/>
   <Measure name="Visibility" type="String"/>
</Cube>

Then map the cube into a virtual cube on the conforming Store dimension.
Finally, specify an MDX rule:

CoalesceEmpty([Measures].[Visibility], 'none')

The system will automatically hook up the Role dimension to the current
role, and evaluate the expression for each member.

I admit that there are a couple of holes in this example.

First, the StoreGrants cube has a particular granularity, like any cube, and
in this case it is Store. If you wanted to assign grants at the City level,
you'd need another cube. But how common is the requirement to be able to
grant at several levels?

Second, I haven't spelled out how [Measures].[Visibility] rolls up.

But I think that the example serves its purpose, and illustrates how MDX can
be used to access a metadata table.

There are several significant advantages:

1. It is expressive, as I already mentioned. As expressive as MDX.

2. It is efficient, even if there are hundreds or thousands of
access-control rules, because these kinds of filters can use the cache and
can be pushed down as SQL filters.

3. It is flexible. It can map onto a wide variety of schemas.

4. It is a minimal extension to the schema format (therefore a minimal
change to the UI)

5. It doesn't conflict with the major rework of mondrian's table-mapping
innards that I am currently undertaking to introduce the <PhysicalSchema>
concept. (OK, this is somewhat of a selfish goal. But it is in everyone's
interest not to introduce more table-mapping code into mondrian, so we can
spend time optimizing the code we already have.)

Let me know what you think of this. Does this fulfill your requirement
better or worse than your original proposal? I'm not wedded to my
counter-proposal, but I thought I'd raise it because it leverages MDX
nicely.

Julian