Export Tables To CSV Post Plugin

Follow
Avatar
Oxana Noa Umansky

Download Zip: ExportTablesToCSVPostPlugin

Files included:

  • ectools.zip
  • ectools_sisense_ver<6.4.zip - (only for versions 6.4 and below)
  • python-2.7.11.amd64.msi
  • config_example.txt
  • EC_export.dll

This post explains how to install and configure the post plugin for exporting tables into CSVs . This plugin enables the user to define which tables will be exported into CSVs during the build finalization process. With this plugin you will be able to:

  1. Reduce build time. For example, a user can store historical data in a CSV file and just union it with the new data on every new build (handy when pulling data from web services).

  2. Define and maintain a unified repository cube. In cases when several cubes need to use the same data table, the user can define one cube which create the data table, all other cubes will use the CSV extracted from it.

  3. Push the data into other reporting systems. Some customers also use the Elasticube manager as an ETL tool and transfer the data forward.

Important! The Build process will not end until the post plugin has finished. Exporting millions of rows to CSV files may take a considerable amount of time and thus will result with a longer ElastiCube build process.

Steps

Step 1: Download and install Python x64 on your machine. 

Note! In order to run the plugin, an installation of Python x64 is required.

  1. Install The attached Python version.

  2. In the Windows Search Box, located next to the Start button, type in Edit the system environment variable and click on it. 

  3. Under Advanced, click on the Environment Variables button.

  4. Under the System Variables pane, click on Path variable and then click Edit.

  5. In the Edit environment variable window, click on New and add C:\Python27 at the bottom of the list. Screenshot_125.png

Step 2: Install the Export to CSV Post plugin

  1. Create a new directory in C:\Program Files\Sisense\Prism\Server and name it Plugins.

  2. Download the EC_export_dll dll file and place it in C:\Program Files\Sisense\Prism\Server\Plugins

  3. Right Click the DLL file, and choose Properties.

  4. In the  General pane, if a button with a Unblock caption exists, click on it (now Windows allows executing the DLL). See image below:

  6. Extract the contents of the ectools.zip into C:\Program Files\Sisense\Prism

  7. Open Services and restart the ElastiCubeService Service.

Step 3: Executing the plugin in the Elasticube Manager

  1. Once in ElastiCube Manager, open ElastiCube ==> Pre & Post Execution Plugins.Screenshot_126.png
  2. Add to the post plugin section a new plugin called EC_export.
  3. Click on Add to configure how the plugin should behave:
<Attributes path="c:\data" 
csv="true" 
tables="brand,categorypath"
pythonPath="C:\Python27\python.exe" 
timestamp="true">
</Attributes>
  • These are the attributes we can add:

    1. path - the directory where the TDE/CSV files will be created at. (e.g - path="C:\TDE")

    2. row_limit (optional) - limit the number of rows imported from each table in the Elasticube. (e.g - row_limit="1000" , only 1000 rows will be imported from each table).

    3. csv (optional) - determines if TDE files will be created, or CSV files instead. TDE files are automatically created if this attribute is not found. (e.g - csv="true", CSV files will be created).

    4. tables (optional) - determines which tables will be imported from the ElastiCube. Tables are separated by comma. If this attribute isn't found, all the tables will be imported. (e.g - tables="brand,categorypath" , "brand" and "categorypath" tables will be the only ones imported.)

    5. pythonPath (optional) - direct path to Python.exe (if PATH variable is not working on your environment)
    6. timestamp (optional) -  adds timestamp to CSV filename

Step 4: Run Build.

In the build's finalizing step the plugin execution will appear in the ElastiCube manager log window. In the ElastiCube Manager console:


csv.png

0

Comments

17 comments

Sort by Date Votes
  • Avatar
    Andrew Block

    I show both a ectools zip and an ectools_sisense_ver6.4 zip, I am assuming if we are on 6.4 or higher we would extract the 6.4 zip into C:\Program Files\Sisense\Prism?

    0
    Permalink
  • Avatar
    Yoni Lerner

    For higher than 6.4 use ectools.zip 

    0
    Permalink
  • Avatar
    Oxana Noa Umansky

    Hi Andrew,

    Thanks for your comment. To add to Yoni's reply and clear any confusion I've also updated the post (see at the beginning) with the following:

    Files included:

    • ectools.zip
    • ectools_sisense_ver<6.4.zip - (only for versions 6.4 and below)
    • python-2.7.11.amd64.msi
    • config_example.txt
    • EC_export.dll

     

    0
    Permalink
  • Avatar
    Sonal Bansal

    We configured the above plug in and while executing the build , we are getting below error:

    Can you help me resolve this error?

     

    Regards,

    Sonal

    0
    Permalink
  • Avatar
    Oxana Noa Umansky

    Hi Sonal,

    I'll create a support ticket for you, so that our team can investigate this issue. 

    You'll get a confirmation email with all the relevant information. You can also track any correspondence on this ticket via "My Activities". 

    Thanks,

    Oxana

    0
    Permalink
  • Avatar
    Maxim Havva

    Hi Oxana,

    Can you confirm if the plugin is working with the 7-th version of Sisense?

    I was using it successfully with older versions, but now on 7-th it says "failed to find the plugin"

    Have any dependencies changed?

    Just to clarify: all the contents of the ectools.zip is wrapped into "ectools" folder. I placed it into C:\Program Files\Sisense\Prism so the path looks like:

    C:\Program Files\Sisense\Prism\ectools\BuildTester\ ..

     

    Thanks,

    0
    Permalink
  • Avatar
    Maxim Havva

    Nevermind, I figured this out,

    best regards

    0
    Permalink
  • Avatar
    Javier Irazazabal

    Hi all,

    Running the plugin, I'm getting an error: "Directory name not valid"

    I've tryed with several CSV's data target, but getting the same error.

    Do you have an idea about this issue cause?

    Thanks in advance!

    2
    Permalink
  • Avatar
    Colton Porter

    I am getting the same issue. Can anyone help? 

    0
    Permalink
  • Avatar
    Javier Irazazabal

    Colton Porter I have just solved it reviewing the installation files, to be sure that every file and folder should be located in the right place. Doing this, I put the plugin to work. Hope it helps

    0
    Permalink
  • Avatar
    GEHC Large Dev

    Just to summarize the solution to the error "Directory name not valid" like it appears on the screenshots above. This error happens when the ectools folder is inside another ectools folder due to the unzip.

    To work the path should be C:\Program Files\Sisense\Prism\ectools\BuildTester\

    0
    Permalink
  • Avatar
    Roopa Raghavan

    Hi,

     

    I tried working this today with a much smaller dataset just to get it working. I'm not sure what I am doing wrong, I don't see any CSV file in C:\data folder. The post execution plugin runs fine. The tables I'm trying to export to CVS format are SiteInfo and EnergyPoints which is given as 2 out of the 3 tables in the post execution plugin attributes. 

    Anyone that has got this working fine - any suggestions or obvious errors on what I'm doing wrong?

     

    0
    Permalink
  • Avatar
    Elliott Herz

    Hey,

    I would try 1 table at a time. You can always add in multiple scripts in the post build plugin area that each just execute 1 table.

    Best,

    Elliott

    0
    Permalink
  • Avatar
    Roopa Raghavan

    @Elliott Thanks for your reply. Oleksandr from Sisense support worked with me today to help troubleshoot and turns out I hadn;t given the table names correctly. I had them as tables="table1, table2"

    2 mistakes there. The table names in my cube were dbo.table1, dbo.table2. Also in the plugin XML code, there should be no space between table names. Correcting both these helped solve the problem.

    My XML code ~

    <Attributes path="E:\data" csv="true" tables="dbo.EnergyPoints,dbo.SiteInfo" pythonPath="C:\Python27\python.exe" timestamp="true"></Attributes>

    If anyone is trying out this plugin, it works fine. Just be sure to follow and double check each step.

     

    Cheers,

    Roopa

    0
    Permalink
  • Avatar
    Ian Emmons

    I don't know if this will help anyone else, but I was unable to get this to work until I escaped the backslashes in my path.

    ie, changed path="C:\Sisense\BackupData\" to path="C:\\Sisense\\BackupData\\"

    1
    Permalink
  • Avatar
    Denis Pesa

    Excellent plugin, I have installed it on Sisense 7.3 according to the instructions here and it worked perfectly. Thanks!

    0
    Permalink
  • Avatar
    Andrew Block (Edited )

    Has anyone else ran into issues where the export works the first time then doesn't produce an output after that build? I have been seeing strange results from this. If I restart Sisense.ECMS and build again it works the first time and then after that build it no longer produces the csv file. I don't get an error for the plugin itself but get this message 

    2019-05-16 08:37:15,970 - INFO - Error: [Error 126] The specified module could not be found
    2019-05-16 08:37:15,971 - INFO - Will not perform data extraction from ECDATA, program will be terminated

    Here is a screenshot showing the plugin executed but it did not produce the output

     

    Here is the logfile from the succesfull one right before it

    2019-05-15 15:17:48,956 - INFO - ----------------------------------------
    2019-05-15 15:17:48,956 - INFO - 4.Creating tables from ECDATA and importing them into DB - starting time:2019-05-15 15:17:48.956000
    2019-05-15 15:17:48,957 - INFO - Creating Customers.csv ... (Customers)
    2019-05-15 15:17:58,905 - INFO - Creating Customers.csv created successfully!
    2019-05-15 15:17:58,905 - INFO - CSV files creation ended at: 2019-05-15 15:17:58.906000

    Would love for this to work without having to restart services each time, has anyone else experienced this or have a workaround?

     

    0
    Permalink

Please sign in to leave a comment.

Didn't find what you were looking for?

New post