Execute SQL Command Plugin

Follow

Mitch Galehouse

April 06, 2017 16:15

Elasticube.Plugin.ExecuteSqlCommand.dll (plugin DLL)
Elasticube.Plugin.ExecuteSqlCommand.zip (source code)

Introduction

This plugin allows you to run a command against a SQL database before or after an ElastiCube build. This is useful for invoking ETL routines in a SQL data warehouse that extract and transform data to prepare it for loading into Sisense.

Installation Steps

1. Copy the DLL to “%ProgramFiles%\Sisense\Prism\Server\Plugins”. (Create the “Plugins” folder if it doesn’t exist.)
2. Ensure the DLL can run by opening its file properties and clicking the “Unblock” button from the “General” tab of file properties, if it exists.
3. Restart ElastiCubeManagementService using services.msc to ensure the DLL is loaded by Sisense.

Configuration Steps

1. Open ElastiCube Manager
2. Open the “Pre & Post Execution Plugins” dialog from the “ElasiCube” menu
3. Add a plugin and name it “ExecuteSqlCommand”
4. Configure arguments for the plugin like this: <Attributes connectionString="Server=X;Database=X;User ID=X;Password=X;" commandText="exec [dbo].[ExtractAndTransform]" commandTimeout="300" />

Plugin Arguments

• connectionString
• commandText
• commandTimeout - Optional, defaults to 30, units in seconds.
• providerName - Optional, defaults to “System.Data.SqlClient”, options include “System.Data.Odbc”, “System.Data.OracleClient”, and others.

~-~

3

• 

  Ramon Lopez April 06, 2017 19:32

  very cool! This would be useful in the case where you have a stored procedure that is populating another table - and you would like to load that other table into the elasticube during the build.

  Thanks for bringing it to the table!! looking forward to more Mitch!

  0

  Comment actions Permalink
• 

  Bobbie Peterson November 20, 2017 17:13

  I've never done a pre plugin for an Elasticube. Could you please provide more through instructions? What an I supposed to do with the zip file attached?

  0

  Comment actions Permalink
• 

  Oxana Noa Umansky November 21, 2017 20:48

  Hi Bobbie, you can find more information on Elasticube plugins here: https://developer.sisense.com/display/API2/ElastiCube+Plugins

  0

  Comment actions Permalink
• 

  sean o'farrell January 17, 2018 16:09

  Hi Oxana 

   

  2 things - link above prodices 404 error.

  Second question. Is there a return code expected from SP or is there another mechnism that will ensure that cube BUILD does NOT start before the SP finished execution ? We have a fairly complex rebuild SP that prepares tables for reporting that may take as long as 30 mins to execute.

   

  Thanx

   

  0

  Comment actions Permalink
• 

  Oxana Noa Umansky January 18, 2018 18:40

  Hi Sean,

  The URL appears to be wrong. The page is here: https://developer.sisense.com/display/API2/ElastiCube+Plugins

  @Mitch might be more suited to help you with the second question, as he is the one who developed this plugin :)

  Thanks,

  Oxana

  0

  Comment actions Permalink
• 

  Mitch Galehouse January 19, 2018 16:08

  The cube build is terminated and failed immediately when a SQL error of an severity level is raised.

  0

  Comment actions Permalink
• 

  Simon Tuson March 20, 2019 16:09

  I am running into an issue where the elasticube manager report an error at build time in the pre-plugin phase.

  Plugin Execution Failed: Plugin Name: ExecuteSqlCommand keyword not supported" 'database'

  My Pre Execute Plugin parameters are as follows

  <Attributes connectionString="server=xxxxx.com;database=xxxx;User ID=xxxx;Password=xxxx;" commandText="Exec pimviewapipck.setviewcontext('Global', 'Main')" commandTimeout="300" providerName="System.Data.OracleClient" />

  Any thoughts?

  Version: 7.3.0.10434

  0

  Comment actions Permalink
• 

  Simon Tuson March 21, 2019 13:44 (Edited March 21, 2019 13:47)

  Any idea why my post yesterday is pending approval?

  I am running into an issue where the elasticube manager report an error at build time in the pre-plugin phase.

  Plugin Execution Failed: Plugin Name: ExecuteSqlCommand keyword not supported" 'database'

  My Pre Execute Plugin parameters are as follows

  <Attributes connectionString="server=abc.com;database=abc;User ID=abc;Password=abcd;" commandText="Exec pimviewapipck.setviewcontext('Global', 'Main')" commandTimeout="300" providerName="System.Data.OracleClient" />

  Any thoughts?

  Version: 7.3.0.10434

  1

  Comment actions Permalink
• 

  Katie Garrison March 21, 2019 13:51

  Hi Simon, I just approved your post. It was inadvertently flagged for approval as the comment included 'xxx'.
  You should see it now.

  Katie Garrison | Technical Solutions Consultant

  0

  Comment actions Permalink
• 

  Laurence Henderson August 22, 2019 22:14

  Has anyone been able to get a common sql stored procedure to return data into a cube as if its a table?

  1

  Comment actions Permalink

Please sign in to leave a comment.