When copying the database via the ETL the user management cubes are omitted. One of the things the user management cubes retain, is the information of which group and thus which user can access elements/attributes and cubes cells.

Jedox Database Copying – Security

One way avoid the omission of the user management cubes is copying the database via the file system, however that will require the help of IT to give access to the server. Also you will have to copy the system database and follow the steps outlined in this blog:

http://thenakedleaf.org/2014/03/11/jedox-migration-moving-from-one-server-to-another/

However, in order to circumvent the dependence on IT and avoid having to take additional steps we can extend the standard ETL Tasks a bit to include transferring the permissions.

1:

 

2:

 

After a dimension has been created, the security cubes for that specific dimension are extracted. For the extract part of the process“Cube_Extract2” is used instead of “Cube_Extract”. Cube_Extract2 is an extract that has been added to the “ETL Tasks”. The difference between the two is that “Base elements only” has not been check marked. The reason for this that consolidated elements / attributes can also have user rights assigned.

Jedox Database Copying – Security

The next step is checking if we are dealing with a normal dimension as the name of these dimensions can be used in creating  the proper user management cube name (via concatenation). Then the normal process of loading the data to a cube is followed. After this the step which deals with user management on dimension elements (is responsible for user management on elements), we move on to the attributes. We follow the exact same procedure albeit we skip certain dimensions. These dimensions, such as “#_#_CUBE__” are not accessible via the Jedox Excel plugin, but are via ETL. To verify, do a data preview on “DimensionsTransform” which is a TableView Transform.

Jedox Database Copying – Security

The above is a snippet of the data preview. The original code goes through each entry. The extensions in the code only respond, as said before, under certain conditions, which are based on the values in the columns isNormalDimensionand isAttributeDimension. The first block of code only activates ifisNormalDimension equals to true and isAttributeDimension equals to false. For the second block of code it is exactly the opposite.

After the dimensions have been transferred, the cubes and their data will be copied. With cubes you can set permissions on individual cell level. Therefore we need another block of code:

 

The principle is exactly the same as with dimensions, meaning checking which type we are dealing with.

After the job has run, you have to manually set the Cube #_CONFIGURATION and the value for HideElements via a paste view in case you want to hide the restricted elements for unauthorized users. This cannot be done automatically because it yields an error, namely:

Cannot import Data into Cube #_CONFIGURATION: Failed to initialize cube #_CONFIGURATION: not authorized for operation,parameter ‘user’ value ’0′(Palo error code: 1009)

Attached is the ETL Project with the newly created “DatabaseCopy – Security”. This project has been made in Jedox 5.1. In case you want to use it with other versions, I suggest do it manually.

  • Create a copy of Cube_Extract and rename it to Cube_Extract2 (and don’t forget to uncheck ” “Base elements only”);
  • Create a new Groovy job called for example “DatabaseCopy – Security” and paste the code contained in the attached link Code Snippet