Execute SQL Command Plugin

[intapiuser]

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.