pentahorolesmondrian

mondrian role: filter by hierarchy and hide hierarchy


I have a DB with different clients (table TD_CLIENTS), which shall have access to a mondrian Cube. Obviously, I want each client to be able to see only data of its own.

I've accomplished this with a role per client (I have few clients, so this is an acceptable trade-off. Anyway, if anyone knows of a better way, I'll be SO happy to hear it):

<Schema name="my_schema" description="my  schema">
  <Dimension type="StandardDimension" visible="true" highCardinality="false" name="Clients">
    <Hierarchy name="Clients hierarchy" visible="true" hasAll="true">
      <Table name="td_clients" schema="public">
      </Table>
      <Level name="Client" visible="true" table="td_clients" column="client_id" nameColumn="client_name" type="String" uniqueMembers="false" levelType="Regular" hideMemberIf="Never">
      </Level>
    </Hierarchy>
  </Dimension>
  <Cube name="my cube" visible="true" cache="true" enabled="true">
    <Table name="my_facts_table" schema="public">
    </Table>
    <DimensionUsage source="Clients" name="Clients" visible="true" foreignKey="client_id" highCardinality="false">
    </DimensionUsage>
    <Measure name="my measure" column="amount" aggregator="sum" visible="true">
    </Measure>
  </Cube>
  <Role name="Client 2 test">
    <SchemaGrant access="all">
      <CubeGrant cube="my cube" access="all">
        <HierarchyGrant hierarchy="[Clients.Clients hierarchy]" topLevel="[Clients.Clients hierarchy].[Client]" bottomLevel="[Clients.Clients hierarchy].[Client]" rollupPolicy="partial" access="custom">
          <MemberGrant member="[Clients.Clients hierarchy].[A CERTAIN CLIENT]" access="all">
          </MemberGrant>
        </HierarchyGrant>
      </CubeGrant>
    </SchemaGrant>
  </Role>
</Schema>

But now, Since each logged-in user (associated with its client role) can see only one Client, I'd like to hide the Client hierarchy.

How can I accomplish it?

Thank you and best regards!


Solution

  • You're close:

    Add a second HierarchyGrant to restrict its visibility, such as:

            <HierarchyGrant hierarchy="[Clients.Clients hierarchy]" rollupPolicy="partial" access="custom">
              <MemberGrant member="[Clients.Clients hierarchy].[A CERTAIN CLIENT]" access="all">
              </MemberGrant>
            </HierarchyGrant>
            <HierarchyGrant hierarchy="[Clients.Clients hierarchy]" rollupPolicy="partial" access="none">
            </HierarchyGrant>
    

    Yet, keep in mind that tools like jpivot will not work unless you add a second (visible) dimension.