In version 1.17, SAP Lumira introduced an extensibility mechanism that allows developers to connect Lumira to new and non-traditional data sources: Data Access Extensions. The developer guide at help.sap.com/lumira is a great reference. To supplement that reference, this post will walk through the development of a simple but complete data access extension, step by step. Some programming experience is assumed.

The source code from this post is available at github. Most of the content is also available in webinar form thanks to SAP’s partner, Simba Technologies. The webinar includes a demo of MongoDB connectivity developed by Simba as a data access extension.

The Basics

Don’t worry, we won’t drown ourselves in abstract theory, but we do need to know a few basics to get started:

  • Data Access Extensions (DAEs) are executable programs (windows exes)
  • Lumira invokes these extension programs when it needs data. Context is passed to them as command line arguments.
  • Extensions write data in CSV format to standard out (aka. “stdout” or “the console”).

For the more visually oriented, here’s the above in diagram format:

/wp-content/uploads/2015/01/dae_desktop_architecture_606002.png

Hello, World!

Let’s start by writing the equivalent of a “Hello, World!” program as a data access extension for Lumira. We’ll write it as a C# Windows Forms Application. You can write DAEs in any language provided the end result is a windows executable. After creating a new project in visual studio, this main function is generated:


static void Main()
{
     Application.EnableVisualStyles();
     Application.SetCompatibleTextRenderingDefault(false);
     Application.Run(new Form1());
}





We don’t need a user interface yet, so we’ll first hide the UI


static void Main()
{
     Application.EnableVisualStyles();
     Application.SetCompatibleTextRenderingDefault(false);
     //Application.Run(new Form1());
}





Now all we have to do is write some CSV to the console in a way that Lumira will understand. Lumira expects you to write out two “blocks”. The first is called the DSInfo block, and it lists some configuration parameters that Lumira uses to interpret the data, such as the CSV delimeter character. This block starts with “beginDSInfo” on a line by itself and ends with “endDSInfo” on a line. Check the developer guide for a list of parameters that you can write between those tags. For now, we don’t need to pass any special configuration to Lumira, so our DSInfo block is written like this:


Console.WriteLine("beginDSInfo");
Console.WriteLine("endDSInfo");





The second block that Lumira needs is the Data block. It too is surrounded by tags. Here’s how we write the “hello, world” data block:


Console.WriteLine("beginData");
Console.WriteLine("hello, world");
Console.WriteLine("endData");





Putting it all together, our complete main function is:


static void Main()
{
     Application.EnableVisualStyles();
     Application.SetCompatibleTextRenderingDefault(false);
     //Application.Run(new Form1());
     Console.WriteLine("beginDSInfo");
     Console.WriteLine("endDSInfo");
     Console.WriteLine("beginData");
     Console.WriteLine("hello, world");
     Console.WriteLine("endData");






}

And that’s it! Now we need to build and test it.

Deploying Data Access Extensions

Although a DAE test utility is provided with Lumira (C:\Program Files\SAP Lumira\Desktop\utilities\DAExtensions) and discussed in the developer guide, we’re going to test in Lumira itself. Let’s follow the happy path for now – assume that all of our code is correct – and I’ll put some debugging tips at the end of the post.

DAEs are not enabled by default with a new install of Lumira. They have to be turned on with a bit of configuration magic. To enable DAEs, open C:\Program Files\SAP Lumira\Desktop\SAPLumira.ini in a text editor and add these lines, as described in the developer guide:


-Dhilo.externalds.folder=C:\Program Files\SAP Lumira\Desktop\daextensions
-Dactivate.externaldatasource.ds=true





Now we’ll copy our newly built DAE into the configured folder, and when we create a new document in Lumira, we select “External Datasource”


new dataset.png

Clicking Next provides a list of all installed DAEs:

new dataset - daes.png

Our DAE is called “hello world” because the file name of the executable is “hello world.exe”. It is “uncategorized” because it was copied directly into the configured data access extensions folder. Had we placed it in a subfolder, the name of that subfolder would have been used as the category.

When we click Next, Lumira invokes the DAE and our program writes to the console before exiting. The result is this:

new dataset - preview - hello world.png

By default, Lumira assumes the first row of data contains the column names. This is configurable in the DSInfo block.

Next, let’s return some real data to Lumira. We’re going to use the Windows event log as our data source. Data Access Extensions can be written for all kinds of non-traditional, non-database data sources (as well as traditional ones) and the event log is convenient because all of us who run Lumira have one on our machines.

Writing Data to Lumira

First, let’s create a class that’s responsible for writing the event log to the console as CSV. We’ll call it EventLogWriter and start developing it in a top-down fashion, starting with a public Write() function and a couple of stubs for writing dsinfo and data.


class EventLogWriter
{
    public void Write()
    {
            WriteDSInfoBlock();
            WriteDataBlock();
    }
    private void WriteDataBlock()
    {
        throw new NotImplementedException();
    }
    private void WriteDSInfoBlock()
    {
        throw new NotImplementedException();
    }
}





It doesn’t do much yet, but it does motivate us to think about error handling. In its current form, WriteDSInfoBlock() would throw an exception when it was called from Write(), and we’re not yet communicating this failure to Lumira. We should strive to deliver meaningful, localized error messages when things go wrong, but for now we’ll settle for a simple, generic message. Error messages are returned to Lumira via stderr, as follows:



public void Write()
{
    try
    {
        WriteDSInfoBlock();
        WriteDataBlock();
    }
    catch
    {
        // Error messages written to stderr are displayed by Lumira
        Console.Error.WriteLine("System event log could not be retrieved");
    }
}






With basic error handling in place, let’s implement WriteDSInfoBlock(). Just as with “Hello, World”, it needs to start and end with beginDSInfo and endDSInfo tags. We’ll also, for illustrative purposes, be explicit about the fact that our first row contains column names:


protected void WriteDSInfoBlock()
{
  Console.WriteLine("beginDSInfo");
  Console.WriteLine("csv_first_row_has_column_names;true;true;");
  Console.WriteLine("endDSInfo");
}





And now we can implement WriteDataBlock(). It has to write the beginData and endData tags, with CSV data from the event log between them.


protected void WriteDataBlock()
{
    Console.WriteLine("beginData");
    Console.WriteLine("Category,Source,Time Generated"); // The column names
    EventLog eventLog = new EventLog("Application");
    IList<string> columns = new List<string>();
    foreach (EventLogEntry entry in eventLog.Entries)
    {
            columns.Clear();
            columns.Add(entry.EntryType.ToString());
            columns.Add(entry.Source);
            columns.Add(entry.TimeGenerated.ToString());
            Console.WriteLine(string.Join(",", columns));
    }
    Console.WriteLine("endData");
}





Although this does write the data in the CSV format, it’s a little too naive about the actual content of the event log. What would happen if one of the fields contained a comma, for example? Quotation marks also have a special meaning. And there might be null objects, which should be distinguished from empty strings. The rules for this are:

  • Null objects are represent by writing no characters for a field. For example, if I had a row [“Error”, null, “13:00”], it could converted to this CSV:

Error,,13:00

  • Empty strings are quoted. So this row, [“Error”, “”, “13:00”] would become:

Error,””,13:00

  • Fields containing the delimeter must be quoted. For example:

Error,”,”,13:00

  • Quotation mark characters that are part of the data must be doubled up. Here’s how you write a field that is a single quotation mark:

Error,””””,13:00

Let’s capture this logic in a function and call it before writing each field:


protected void WriteDataBlock()
{
    Console.WriteLine("beginData");
    Console.WriteLine("Category,Source,Time Generated"); // The column names
  EventLog eventLog = new EventLog("Application");
    IList<string> columns = new List<string>();
  foreach (System.Diagnostics.EventLogEntry entry in eventLog.Entries)
    {
            columns.Clear();
            columns.Add(EscapeSpecialChars(entry.EntryType));
            columns.Add(EscapeSpecialChars(entry.Source));
            columns.Add(EscapeSpecialChars(entry.TimeGenerated));
            Console.WriteLine(string.Join(",", columns));
    }
  Console.WriteLine("endData");
}
protected string EscapeSpecialChars(Object obj)
{
    if (obj == null)
    {
        return "";
    }
    string field = obj.ToString();
  // Wrapping a field in quotation marks allows it to contain the CSV delimeter
    // Quotation mark characters have to be doubled up (" -> "")
    field = field.Replace("\"", "\"\"");
    return "\"" + field + "\"";
}





Now we’re writing the event log data to Lumira as CSV – let’s try it out!

After copying the extension exe into the configured extensions folder and creating a new document with it as a data source, we end up with the full content of the log in Lumira as a data set. We can now cleanse, mashup, and otherwise manipulate the data. Next time we refresh, those manipulations will be reapplied.

prep room.png

This is a good start. In many cases, though, we will want to allow the end user to provide some input that affects the query. For the event log data source, we’ll let them choose the number of rows to return.

Adding a User Interface

Our very simple prompt UI will look like this:

prompt ui.png

When the OK button is clicked, we’ll write the number of specified rows from the event log. Here’s an event handler for the OK button:


private void ok_button_Click(object sender, EventArgs e)
{
    EventLogWriter writer = new EventLogWriter();
    writer.NumRows = Decimal.ToInt32(numrows_numericUpDown.Value);
    writer.Write();
    Close();
}





Pretty straightforward. We instantiate an EventLogWriter as before and then set a NumRows property using the value from the UI. After that, we simply close the application. Here are the updated portions of EventLogWriter:


public int NumRows { get; set; }
public EventLogWriter()
{
    NumRows = 50; // Arbitrary default
}
protected void WriteDataBlock()
{
    Console.WriteLine("beginData");
    Console.WriteLine("Category,Source,Time Generated");
    EventLog eventLog = new EventLog("Application");
    IList<string> columns = new List<string>();
    for (int i = 0; i < eventLog.Entries.Count && i < NumRows; i++ )
    {
        EventLogEntry entry = eventLog.Entries[i];
        columns.Clear();
        columns.Add(EscapeSpecialChars(entry.EntryType));
        columns.Add(EscapeSpecialChars(entry.Source));
        columns.Add(EscapeSpecialChars(entry.TimeGenerated));
        Console.WriteLine(string.Join(",", columns));
    }
    Console.WriteLine("endData");
}





We’ve added the NumRows property, initialized it with a default value (arbitrarily chosen), and then modified the loop in WriteDataBlock so that it will write no more than the specified number of rows. Lastly, now that we have a UI, we have to display it. Returning to our Main() function, we uncomment the line that displays the UI and comment out the line that creates an EventLogWriter:


static void Main()
{
    Application.EnableVisualStyles();
    Application.SetCompatibleTextRenderingDefault(false);
    Application.Run(new Form1());
    //new EventLogWriter().Write();
}





This is a good time to test our work in Lumira. After selecting the event log data source, Lumira invokes our program and the UI appears.

/wp-content/uploads/2015/01/uiv1_606829.png

After clicking OK, the extension sends data to Lumira and exits. If we look in the top-right of the New Dataset dialog, we see that the number of rows matches the number we selected:

preview wrong num rows.png

This is as we intended, but actually not what Lumira wanted! This first screen in the data acquisition workflow is called “preview”, and its intended to allow the end user to sample the data before deciding whether to acquire the entire data set. With very large data sets, initial acquisition can take some time, and preview allows the user to sample the data before undertaking that process. When Lumira wants to preview the data, it notifies the extension of this fact by passing in some command line arguments. We’ll take a look at those momentarily.

Ignoring the preview problem for the time being, let’s click Create to finish data acquisition and see what happens.

extra prompt.png

The prompt appears a second time! Not what we want… Let’s fix it.

Parsing Command Line Arguments

For our extension program to understand what Lumira wants from it, it must inspect the command line arguments that Lumira gives it. We’ll implement the parsing of arguments in an “Args” class for use within the Main() function. The class will at first glance look more complex than it is, due to its length. Lumira’s input to extensions is very deterministic so we don’t need a lot of validation or error handling, but we do need to reconcile the way that command line arguments are understood by the system/OS with the way they are understood in our program.

When Lumira wants to preview data, for example, it could invoke our program like this:

eventlogdatasource.exe -mode preview -size 300

Command line arguments are delimited by white space, so although we know when reading this that what Lumira means is that it is in preview mode (mode=preview) and wants us to return 300 rows (size=300), the system sees each of these arguments independently. We have to write a function, which we’ll call ParseCommandLineArgs(), to match keys with values and save them as properties that our program understands. We’re going to parse all four arguments that Lumira can pass now, and I’ll explain the ambiguously named “parameters” argument a little bit later.


class Args
{
    public string mode { get; set; }
    public int size { get; set; }
    public string locale { get; set; }
    public string parameters { get; set; }
  public Args()
    {
        mode = "preview";
        size = 0;
        locale = "en";
        parameters = "";
    }
  public static Args ParseCommandLineArgs()
    {
        Args args = new Args();
      string[] argsList = Environment.GetCommandLineArgs();
      using (IEnumerator<string> iter = ((IEnumerable<string>)argsList).GetEnumerator())
        {
            while (iter.MoveNext())
            {
                string arg = iter.Current;
                if (arg == "-mode")
                {
                    iter.MoveNext();
                    args.mode = iter.Current;
                }
                else if (arg == "-size")
                {
                    iter.MoveNext();
                    args.size = Int32.Parse(iter.Current);
                }
                else if (arg == "-locale")
                {
                    iter.MoveNext();
                    args.locale = iter.Current;
                }
                else if (arg == "-params")
                {
                    iter.MoveNext();
                    args.parameters = iter.Current;
                }
            }
        }
        return args;
    }
};





We’re going to use Args to help with two things: deciding whether to display the UI and deciding how many rows to return to Lumira. Modifying our Main() function to use Args, we end up with this:


static void Main()
{
    Application.EnableVisualStyles();
    Application.SetCompatibleTextRenderingDefault(false);
  Args args = Args.ParseCommandLineArgs();
    EventLogWriter logWriter = CreateEventLogWriter(args);
    if (UiRequired(args))
    {
        Application.Run(new Form1(logWriter));
    }
    else
    {
        logWriter.Write();
    }
}





First, we use Args to parse the command line arguments. Next, we delegate instantiation of the EventLogWriter to a new factory function called “CreateEventLogWriter()”. Then, we decide whether to display a UI by calling the new function “UiRequired()”. Let’s look at CreateEventLogWriter() first:


static EventLogWriter CreateEventLogWriter(Args args)
{
    EventLogWriter logWriter = new EventLogWriter();
    logWriter.NumRows = args.size;
    return logWriter;
}





It does the same thing that we did previously in our OK button click handler. Now we need this logic in more than one location, though, so it makes sense to move it to a factory function where it can be reused. Notice in the Main() function that we are now passing an EventLogWriter instance into the UI (“Form1”, the default name that the WinForm project generated). We have to use that instance instead of creating a new one. The UI code now looks like this:


public partial class Form1 : Form
{
    private EventLogWriter logWriter;
  public Form1(EventLogWriter logWriter)
    {
        InitializeComponent();
        this.logWriter = logWriter;
    }
  private void ok_button_Click(object sender, EventArgs e)
    {
        logWriter.NumRows = Decimal.ToInt32(numrows_numericUpDown.Value);
        logWriter.Write();
        Close();
    }
}





Yes, we are at present overwriting the only property set by the factory function, but the factory function is still a good practice and we’ll see the benefits of it soon. Incidentally, speaking of best practices, now might be a good time to note that I have no experience whatsoever as a .Net/C#/WinForms developer. It’s very likely that the code presented here is not idiomatic – it does nonetheless serve the purpose.

OK, now let’s write some logic for deciding whether to display a UI. When Lumira is in preview mode, it will tell the extension how many rows (maximum) to return. When it’s refreshing, following a preview, it expects the extension to return the full data set. In the refresh case we will prompt the end user for the number of rows. This logic can be implemented as follows:


static bool UiRequired(Args args)
{
    return args.mode == "refresh";
}





And with that, we’re again ready to test in Lumira. After creating a new document using the event log data source extension, we see the preview screen. It now shows 300 rows, which is the number specified by Lumira using a command line argument.

preview correct num rows.png

After previewing, click Create to acquire the full data set and Lumira will again invoke our extension, this time passing “refresh” as the mode argument. Our program detects this and displays a prompt to the end user.

prompt with default value for num rows.png

Finally, after setting the number of rows and clicking OK, our extension writes the data to standard output for Lumira to read. So far so good! We’re not done yet, though. If we acquired, say, 30,000 rows, next time we refresh the document we might reasonably expect that the prompt dialog will default to 30,000 rows. Instead, it defaults to 50. We can confirm this by selecting Data -> Refresh document from Lumira’s toolbar.

refresh after acquisition.png

Solving this problem requires us to look more closely at the DSInfo block and the “parameters” argument that Lumira passes to our extension.

Parameter Saving

Lumira calls the key,value pairs written into the DSInfo block “parameters”. The developer guide has a list of reserved parameters that Lumira understands. You can also write out your own parameters and Lumira will pass them back into your extension program each time it is invoked. This is a way to save small amounts of state. In our case, we’d like to remember the number of rows the user selected in between invocations. Let’s add this to the DSInfo block by modifying EventLogWriter.WriteDSInfoBlock():


protected void WriteDSInfoBlock()
{
    Console.WriteLine("beginDSInfo");
    Console.WriteLine("csv_first_row_has_column_names;true;true;");
    Console.WriteLine("num_rows;" + NumRows + ";true;");
    Console.WriteLine("endDSInfo");
}





On each line, write the name of a parameter, a semicolon, the value of the parameter, another semicolon and then a boolean value that appears to be ignored by Lumira at the moment. This extra “true” value is present in the examples in the developer guide, so we’ll add it in case it is used in the future. The number of rows is saved as the “num_rows” parameter.

When our extension starts, we have to read the num_rows value back in from the command line and set the EventLogWriter.NumRows property. Lumira passes parameters (a.k.a. the DSInfo block) back in as one big string following the “-params” argument, like this:

eventlogdatasource.exe -mode refresh -params num_rows=40000;csv_first_row_has_column_names=true

We’ll add a function to into EventLogWriter that parses the params:


public void ParseParameters(string parameters)
{
    string[] paramsArr = parameters.Split(';');
    foreach(string param in paramsArr)
    {
        if (param.StartsWith("num_rows"))
        {
            this.NumRows = Int32.Parse(param.Split('=')[1]);
            break;
        }
    }
}





The function takes as an argument the string that follows “-params” on the command line. We have already saved this string in the Args class and all we have to do is call ParseParameters() after instantiating the EventLogWriter. That will be done in our factory function:


static EventLogWriter CreateEventLogWriter(Args args)
{
    EventLogWriter logWriter = new EventLogWriter();
    if (args.size > 0)
    {
        logWriter.NumRows = args.size;
    }
    else if (args.parameters.Length > 0)
    {
        logWriter.ParseParameters(args.parameters);
    }
    return logWriter;
}





When Lumira hasn’t specified the maximum number of rows via the size argument, we check whether there’s a num_rows parameter. If a num_rows parameter exists, it’s used to set the initial value of the EventLogWriter’s NumRows property. With that, we have handled the case of user-driven refresh. Now if we acquire 50,000 rows and then select Data-> Refresh document, the prompt that appears defaults to 50,000 rows. Much nicer!

Edit Workflow

We’re almost there! But one more issue remains to be solved before we call it a blog post. Lumira has a data set “editing” workflow, which is triggered from the toolbar by clicking Data -> Edit. The user guide describes the purpose of this workflow:

● Add new columns that had been removed from the data source when it was originally acquired

● Remove columns that were included in the original data source

● Change values selected for SAP HANA variables and input parameters

On the last point about HANA variables/parameters – we can use it to change parameters for data access extensions as well.

When the edit workflow is triggered, instead of Lumira invoking the extension first with mode=preview and then with mode=refresh, it invokes the extension first with mode=edit and then with mode=refresh. Unfortunately, although edit mode is essentially equivalent to preview mode, Lumira doesn’t pass a size parameter in edit mode. Still, we will want to limit the data returned at this stage.

With our extension in its present state, invoking edit mode causes the extension to return to the edit/preview screen the full number of records that it returned during the last refresh.  We will hard code a 300 row limit in edit mode because this is what Lumira normally asks for in preview. We also want to make sure that when the refresh occurs, the number of rows the user previously selected is the default value in the prompt UI (i.e. we don’t want the UI prompt to show 300 rows just because that’s our hard-coded edit/preview limit).

Let’s start by detecting edit mode and limiting the number of rows returned to 300. We’ll again modify our factory function:


static EventLogWriter CreateEventLogWriter(Args args)
{
    EventLogWriter logWriter = new EventLogWriter();
    if (args.size > 0)
    {
        logWriter.NumRows = args.size;
    }
    else if (args.parameters.Length > 0)
    {
        logWriter.ParseParameters(args.parameters);
        if (args.mode == "edit")
        {
            // edit mode is equivalent to preview mode, but Lumira doesn't specify the
            // number of rows to return. In preview mode, Lumira asks for 300 rows, so
            // that's how many we'll return for edit mode.
            logWriter.NumRows = 300;
        }
    }
    return logWriter;
}





Now, to prevent the prompt UI from showing 300 when it next appears, we separate the value of the DSInfo num_rows parameter from the EventLogWriter.NumRows property. By making these two distinct things, we can save a value for the num_rows parameter (the number of rows we want the UI to display next time), that differs from the number of rows being written this time.

To do this, we add a NumRowsParameter property to EventLogWriter that controls the value saved in the DSInfo. When the EventLogWriter restores its state by reading DSInfo, it will use the parameter value to set both the NumRows and NumRowsParameter properties. We can then set NumRows for the duration of this invocation without affecting the number of rows displayed in the prompt UI during next invocation. This isn’t elegant, but it’s a simple solution.


public class EventLogWriter
{
    public int NumRows { get; set; }
    public int NumRowsParameter { get; set; }
    public EventLogWriter()
    {
        NumRows = 50;
        NumRowsParameter = NumRows;
    }
    //...
    public void ParseParameters(string parameters)
    {
        string[] paramsArr = parameters.Split(';');
        foreach(string param in paramsArr)
        {
            if (param.StartsWith("num_rows"))
            {
                this.NumRows = Int32.Parse(param.Split('=')[1]);
                this.NumRowsParameter = this.NumRows;
                break;
            }
        }
    }
   //...
}





There’s one final change we need to make. When an end user changes the prompt value we want it be saved in the DSInfo. This means that EventLogWriter’s NumRowsParameter property has to be set. We must do this in our OK button click handler:


private void ok_button_Click(object sender, EventArgs e)
{
    logWriter.NumRows = Decimal.ToInt32(numrows_numericUpDown.Value);
    logWriter.NumRowsParameter = logWriter.NumRows;
    logWriter.Write();
    Close();
}





Now the edit workflow will be as desired. First, the extension is invoked with mode=edit and 300 rows are returned:

edit mode.png

Next the extension is invoked with mode=refresh and the previous user input is remembered:

edit mode refresh.png

If you’ve been following along and writing the code yourself, you should now have the same code that was tagged “v0.7” in the git repository. Congratulations!

Debugging

Even with test tools, TDD, documented interfaces, etc., it is still helpful to be able to debug the actual workflow using Lumira. The way in which this is accomplished will differ with each tool chain. For those using Visual Studio and new to .Net (like me), you will find it helpful to know that VS can attach to your program after it starts running.

First set some breakpoints. Next, invoke the extension from Lumira and then select Debug -> “Attach to Process”  in Visual Studio (2013 shown)

debug attach.png

A dialog appears showing all running processes. Select your extension by name.

debug select ps.png

Typically, your program will not wait for the debugger to attach and might even be finished running by the time you start the debugger. To get around this, make your program wait when it first starts. One way to do this is to display a (blocking) message box at the very beginning of the program. The program will wait for you to dismiss the dialog and you can use this time to attach the debugger:


static void Main()
{
    MessageBox.Show("Start the debugger now");
    Application.EnableVisualStyles();
    Application.SetCompatibleTextRenderingDefault(false);
    Args args = Args.ParseCommandLineArgs();
    EventLogWriter logWriter = CreateEventLogWriter(args);
    if (UiRequired(args))
    {
        Application.Run(new Form1(logWriter));
    }
    else
    {
        logWriter.Write();
    }
}





For .net extensions without UI, you might use the System.Diagnostics.Debugger class instead:


static void WaitForDebugger()
{
    while (!System.Diagnostics.Debugger.IsAttached)
    {
        System.Threading.Thread.Sleep(100);
    }
}





Fin

And that, at last, is the end. Happy coding!

To report this post you need to login first.

4 Comments

You must be Logged on to comment or reply to a post.

  1. Mohammed Nouman

    Hi David,

     

    We have worked on this example  and it is working Fine for PREVIEW mode. however the same is not working for REFRESH and EDIT mode can you please suggest the necessary changes to be done for the above modes.

    waiting for your reply.

     

    Thanks & Regards,

    Mohammed Nouman

    (0) 

Leave a Reply