This website uses Cookies. Click Accept to agree to our website's cookie use as described in our Cookie Policy. Click Preferences to customize your cookie settings.
on - edited on by
DRay
Introduction
This article will demonstrate one way to create duplicates of your hierarchies that use a different ElastiCubes. This could be useful if you have several ElastiCubes with the same schema, and you don't want to manually re-create the hierarchies for each cube in the web interface. For example, you work with several different customers, and you are creating the same schema for each customer, but for security reasons you choose to keep the data in different ElastiCubes.
This operation will require modifying underlying web metadata on the Sisense server, so this process should only be done by the system administrator. Preferably it should be performed when no one is on the system or in a test environment.
If you would like any assistance implementing this, please click the Submit a Request button above, or email a request to [email protected].
Information on drill hierarchies can be found here: https://docs.sisense.com/main/SisenseLinux/manage-drill-hiearchy.htm
Requirements
1. Admin access to the machine hosting Sisense Web
2. A backup of your Sisense web environment. To do this, go to Task Manager->Services->Open Services. Then stop the service Sisense.Repository. This may produce an error message, which you can ignore. After this, create a backup of this folder: C:\ProgramData\Sisense\PrismWeb\Repository\DB. Then, start the service.
3. The program Robomongo: https://studio3t.com/download/ This free program will provide a GUI for interacting with the data that defines the hierarchies. Once you've installed the program, open it, and create a new connection to connect to Sisense Web. Use the name 'Sisense Web, address of localhost, and post 27018. After, go to View->Explorer, which will open the data explorer. Additionally, go to View->Toolbars->Execution Toolbar. This will allow you to run commands.
4. The program Notepad++: https://notepad-plus-plus.org/downloads/ This is a free text editor that allows for find/replace operations that use regular expressions. Any text editor with regular expression find/replace can be used, but the find/replace syntax may need to be modified for the specific program.
Implementation
1. Backup your web data.
The data being modified will be stored on the server here: C:\ProgramData\Sisense\PrismWeb\Repository\DB.
To do this, go to Task Manager->Services->Open Services. Then stop the service Sisense.Repository. This may produce an error message, which you can ignore. After this, create a backup of this folder: C:\ProgramData\Sisense\PrismWeb\Repository\DB. Then, start the service.
2. Find the definition of the original ElastiCube
Open Robomongo, connect to the Sisense Web collection, open prismWebDB, then Collections, then right click on the collection 'elasticubes', and then click 'View Documents'. In this collection, you can open each element, and find the one with the title of the ElastiCube that has hierarchies defined for it. If you have too many ElastiCubes to sift through, there is a shell for putting in mongoDB commands. In this instance, you would change the .find({}) command to be .find({title:"<Your cube title>"}).
Once you've found the original ElastiCube, right click on the item in RoboMongo, select Copy JSON, and paste the results into a text editor.
3. Find the definition of the new ElastiCube
This will be the same as what you need to do in step #2. However, if you haven't defined any security settings or hierarchies for the new ElastiCube, it won't be present in the collection. If this is the case, manually create a test hierarchy for the new ElastiCube, and then delete that test hierarchy. This will register the ElastiCube in the collection. Reload the elasticubes collection, and perform step #2 for the new ElastiCube.
4. Get the information that defines the hierarchies
Right-click on the hierarchies collection, and select 'View Documents'. Here, you'll need to use the shell to find only the hierarchies that exist in the original ElastiCube. In the shell, edit the .find command to look for the cubeID that was found in step #2. My find command looked like this:
db.getCollection('hierarchies').find({cubeId: ObjectId("5671709892bd57702f00002e")}) You'll want to replace the ObjectId() with the ObjectId for the cube information you found in step #2.
5. Put the hierarchy information into Notepad++
In Robomongo, while viewing the results from step #4, open the information in text mode. Here's a screenshot of where that button is:
Copy the text information into Notepad++.
6. Use Find/Replace to edit this information
Pres ctrl+h to open the replace dialog, and make sure the search mode you're using is Regular expression. This option is in the lower left of the dialog.
The first replace we will do will eliminate all the numeric separators in the data (i.e. /* 1 */). You will find:
\/\*.* And replace all with:
,
The second replace will eliminate the _id fields. These will be automatically generated when we insert the information. Find:
"_id".*
And put nothing in the replace field.
Finally, we will replace the old cubeId with the new cubeId. The cubeId will match the _id that was found in steps 2 & 3. For this step, you should switch the Search mode to normal, since it won't recognize the parentheses in regular expression mode.
7. Finalize the formatting.
First, delete the comma from the beginning of the file, and replace it with a bracket opening- [
Then, put a closing bracket- ] at the end of the data.
We now have a list of hierarchy definitions that are comma separated and within a set of brackets. I've attached the original data and result data I've created with this method, for comparison.
8. Insert the data into mongoDB
Open Robomongo, and then open the hierarchies collection. In the shell, change the .find command to a .insert command. Then delete the braces in the command. Your shell should say:
db.getCollection('hierarchies').insert() Now, copy the code that was made in steps 6 and 7, and put it between the parentheses in the insert() statement. Afterward, click the green play button to execute the code. (If you don't see this button, go to View->Toolbars->Execution Toolbar)
You should get a result message that says "Inserted 1 record(s) in 1ms"
9. Check the hierarchies in Sisense Web
The hierarchies should now be available for use on the new ElastiCube. Go to Sisense Web, and check to see if they are available for the new cube. If not, revert the Prod folder to the backup made in step 1, and try to go through the process again.
Download:
Related Links
