Creating CLR Functions

Prerequisites

SQL Server Instance and SQL Server Management Studio.  Can be downloaded here for free, I use the Developer version.

Access to an Essbase Application.  I use applications included on Oracle’s OBIEE Sample VM Image

1)   Create a new Database called Freecast. 

a.    In SQL Server Management Studio Right click on Databases and select New Database

2)      Download all the files from here. 

I included the source code, so you can see what the code does. You can use the assembly in the Assemblies folder or you can compile the Freecast.cs file yourself into an Assembly so you know what is in Freecast.dll. Restsharp.dll can be downloaded here.  If you want to create your own Assembly, I suggest creating a Visual Studio C# Console App then copy my source code in and get the Restsharp NuGet package.

3)      Add the Assembly to the Freecast database

a.    Modify the ‘DLL Add and CLR Function Creation.sql’ file to point to the Freecast.dll file on your machine. The Restsharp.dll needs to be in the same folder

b.     Run the ‘DLL Add and CLR Function Creation.sql’ script to add the assembly and the base CLR Functions

4)      Create the Wrapper Functions

a.     Run the ‘Create Wrapper Functions.sql’ script to add the wrapper functions

5)     Use ‘Example Calls To Sample App.sql’ to create metadata and data calls.  More examples are in this post