Para aumentar nossos conhecimentos, fizemos alguns testes utilizando Multi Tenant com Pentaho 5.1.

A idéia é fazer com que o Pentaho identifique qual Banco de Dados deve ser utilizado de acordo com o usuário logado.

Encontramos alguns artigos utilizando Pentaho 4.8, mas sem muita documentação sobre o 5.

Ainda não conseguimos fazer o Saiku remover o cache em caso de mudança de Banco de Dados, porém, ao limpar o cache, funciona normalmente. Se alguém tiver alguma sugestão, sobre esse assunto, por favor, coloque nos comentários.

Segue os passos:

Configuração de arquivo

Edite o arquivo pentaho-solutions/system/pentahoObjects.spring.xml

substitua o trecho:

<bean id="IDBDatasourceService" class="org.pentaho.platform.engine.services.connection.datasource.dbcp.DynamicallyPooledOrJndiDatasourceService" scope="singleton">

    <property name="pooledDatasourceService" ref="pooledOrJndiDatasourceService" />

    <property name="nonPooledDatasourceService" ref="nonPooledOrJndiDatasourceService" />

  </bean>
por:


<!--Inicio Tenant --><br>

  <bean id="IDBDatasourceService" class="org.pentaho.platform.engine.services.connection.datasource.dbcp.tenantaware.TenantAwareLoginParsingDatasourceService" scope="singleton"><br><br>      <property name="requireTenantId" value="false" /> <br><br>      <property name="datasourceNameFormat" value="{0}-{1}" /><br><br>      <property name="tenantSeparator" value="@" /><br><br>      <property name="tenantOnLeft" value="false" /> <br><br>  </bean><br><br><!-- Fim Tenant -->

Configuração de ambiente

No Pentaho, crie um Datasource para cada Banco de Dados, utilizando o formato [CODIGO]-[NOME]. Por exemplo: 1-Conexao, 2-Conexao, etc..

É importante que todos tenham o mesmo NOME, modificando apenas o código.

Após a criação dos DataSources, crie usuários no formato [USUARIO]@[CODIGO]. Por exemplo: knowsolution@1, knowsolution@2, etc…

O código no nome do usuário deve corresponder ao código do Datasource. No exemplo anterior, o usuário knowsolution@1 irá acessar o DataSource 1-Conexao, equanto o usuário knowsolution@2, o DataSource 2-Conexao

Testando

Para testar, logue com um dos usuário no Pentaho, crie uma nova visualização no saiku, limpe o cache e veja os dados. Faça logout e conecte com o outro usuário (ou utilize outro navegador), e faça o mesmo processos no Saiku. Você verá que os dados são os referentes ao outro banco de dados.

Outra forma de testar é utilizando um Dashboard no CDE, marque a opção cache do Datasource no CDE como false e faça o login com os diferentes usuários.

O que o site do Pentaho diz:

Multi-Tenancy in Pentaho

Pentaho has three categories of multi-tenancy:

  • Data multi-tenancy allows developers and integrators to apply custom security and business rules to control access to data.
  • Content multi-tenancy separates content, such as reports and folders, among tenants.
  • UI multi-tenancy presents different styles of the user interface for each tenant.

There are two required components to make multi-tenancy work.  Users need to be associated with tenants via roles, tenant IDs, or other identifiers which indicate what content and data users see.  Similarly, there must be something in the data that can be used to restrict access.  The combination of user information and data make the multi-tenancy approaches described here possible.  Since these approaches are data model and data-driven, they are very flexible.

Preparing for Multi-Tenancy in Pentaho

Before you can apply multi-tenancy to your Pentaho system, you need to associate users to a tenant. The most likely methods are to assign a specific role to users who belong to the same tenant or to designate a session variable which identifies the tenant ID. Other approaches include associating users with some data, such as geographic region or business unit.  The association of a user with tenant identifiers is accomplished through one of the following approaches:

  • The user information can be set via single sign-on if it is used.  This approach has the advantage of requiring a single point to set user ID, roles, and tenant info.  However, if users will be scheduling their own content, this approach will not work because the SSO filter is not called by the scheduler.
  • A session startup action that is run when a user session is created.  The advantage to this approach is that the action is called by the scheduler.  The downside is that an action sequence is required, which means understanding a new technology.

You can use an action sequence to add an indicator to the user’s session which identifies the user as a member of the tenant. The following document illustrates techniques for controlling access to data used in action sequences in the Pentaho BI Platform. You should be familiar with the Pentaho BI Platform, creating action sequences and SQL database queries.

For more information, see: https://bit.ly/3sNTYpA

Data Multi-Tenancy and Supported Methods

The most common category of multi-tenancy is data multi-tenancy. Data multi-tenancy allows developers to apply their own custom data access rules at run time. For example, each tenant might only be allowed to see data which is associated with their tenant ID. Here are the most common methods for data multi-tenancy in Pentaho Business Analytics.

  • Sharding: Each tenant has its own database or schema.  This approach has the advantage of controlling per database and ensuring data is separated.  Note that with this approach, multiple databases and servers will need to be managed.
  • Striping: Tenants share a database, but the tables have a tenant ID column to indicate which tenant can see the specified data.  This approach has the advantage of managing only a single database.  Note that with this approach, databases can become very large.
  • Data Models: Tenancy is controlled at the data level where different tenants (or sub-tenants) are only able to see certain data.  This approach is very flexible, but the data to restrict on must usually be known in advance.
  • Hybrid: Combinations of sharding, striping, and data model.  Each of the approaches above can be combined into a single, flexible solution to data multi-tenancy.

Each database implementation has advantages and disadvantages, based on factors of performance management, maintenance, security, efficiency, available resources, and database vendor features. For best practices in database architecture, Pentaho recommends working with an expert in your chosen database and data modeling.

Sharding Data Multi-Tenancy

Sharding involves separating data by database or by database connection properties. The Pentaho Business Analytics platform manages access to database connections through a central mechanism called a data source. The platform delegates to the IDBDatasource object whenever a connection is requested to a given data source. Through Java code and Pentaho configuration files, developers can plug in their own object to apply rules at run time as shown in the following example.

Caso não consiga ou identifique algum problema neste pequeno tutorial, deixe nos comentários!

Leandro Guimarães
Leandro Guimarães
Leandro Guimarães é o fundador da Know Solutions e trabalha com Business Intelligence desde 2009. Possui amplo conhecimento em Modelagem Dimensional, Data Warehouse e na plataforma Pentaho.

Foi aluno de Ralph Kimball, maior referência mundial no assunto, no curso de Modelagem Dimensional realizado pela Kimball University, em Estocolmo – Suécia.

Já ministrou diversas palestras sobre o tema e atualmente mantêm o blog da Know Solutions, com referências sobre Business Intelligence.

Pós Graduado em Gestão de Projetos de Software pela PUC – Paraná. Trabalhou durante 7 anos na empresa Siemens onde participou de projetos em diferentes países.