Skip to Content

Hello Everyone,

For a purely internal project to better analyze IdeaPlace data, i came across a sittuation that data consist of many relational tables. Tried merging them on Lumira, but i had many questions to answer with data, and each question required different kind of table merges and calculations. I had to find another way!

If you’re curious, one of my questions was, which customers are active on ideaplace and whose ideas are they voting for? I’d get customer information from the e-mail addresses of members.

ScreenHunter_739 Sep. 25 21.45.jpg

A better idea could be putting this data in a database and querying with Lumira, but it would require setting up servers, it would require too much effort. Also, i’d have to remember all table relations and make up my SQL query from memory, still i’m not happy with this method.

1) Microsoft Access?? What is it good for?             

Actually a BusinessObjects Universe is perfect fit for my sittuation, but it would require a BI Server, which is no good for an agile scenario like mine. Even though i posted my idea to Allow Local Universe Support For SAP Lumira here i currently have 9 votes (would you vote for my idea?) and it is probably not coming anytime soon, i had to try another option :

I decided to try Microsoft Access, and it is supposed to be a “Database” right? I loaded my individual excel files as “Tables”.

ScreenHunter_740 Sep. 25 22.02.jpg

At that point i discovered something ! You can also create pre-define “Queries”. It allows you to create your table joins visually and you can even define parameters to get input from the user!!! Very powerful :

See the image : Now that’s what i needed!!!

ScreenHunter_719 Sep. 25 15.56.jpg

I also defined 2 parameters to filter data, Idea Session Name and Idea Category :

ScreenHunter_741 Sep. 25 22.17.jpg

Finally I created my query, adding all the fields and calculations i want. perfectly re-usable!

ScreenHunter_721 Sep. 25 15.57.jpg

2) Back To The Lumira Part                                    

Now how do i use this within Lumira? Microsoft Access is not in the connection options for Lumira. It may be used with “Generic JDBC” option but that would leave me alone with a “copy-paste” sheet where i had to find or create a working SQL query out of my mind. But i want to use this very useful query in Access with the parameters…. There comes my new challenge : Creating a Data Access Extension that is re-usable and can utilize predefined queries with parameters. Wow looks like it’s lots of work. No actually it’s 250 lines of code (depending on your white-space preferences) 🙂

          ScreenHunter_743 Sep. 25 22.29.jpg

3) Most Of It’s Already Done By Someone Else Again

ScreenHunter_742 Sep. 25 22.27.jpg

Ok i may not be the fastest, but i know where to search… Google!!! Quickly found a way to utilize MsAccess within Python.

Here are some parts of the quick&dirty codes of my extension :

ScreenHunter_723 Sep. 25 16.54.jpg

ScreenHunter_724 Sep. 25 16.55.jpg

4) Use Your New Extension And Get Insights           

Let’s see what this new extension will bring us as insights :

* First lets start Lumira and select our connector (see i have a cool collection here):

ScreenHunter_727 Sep. 25 16.56.jpg

* At the second step it will ask you to chose access database file :

ScreenHunter_728 Sep. 25 16.57.jpg

* Afterwards, it will read the DB and provide you a list of pre-defined queries to choose one :

ScreenHunter_730 Sep. 25 16.57.jpg

* Next step is to get prompts dynamically from the database and popup to get user input (see i use % as wildcard because i used a “LIKE” statement in my query :

ScreenHunter_734 Sep. 25 17.18.jpg

*Here we go. All the data beautifully joined and filtered for my taste 🙂 Now i can visualize it.

Note: i changed the data not to expose customer names,

ScreenHunter_735 Sep. 25 17.18.jpg

* Here comes the insight. One can easily analyze the interactions between customers and SAP through ideas and votes. Well we started with extensions, we can also use some extension-magic on the visualization part too.

ScreenHunter_744 Sep. 25 23.12.gif

You can see on the right cluster that these customers are voting on the ideas that SAP employees created.

ScreenHunter_745 Sep. 25 23.15.gif

Obviously there are many social interactions going on in this platform. Thanks to SAP Lumira we can analyze them now to understand and make better-informed decisions!

5) Final Comments, Code, Executable And Call To Action

I think i will be using this extension often because it will improve my productivity with different datasets and pre-defined queries.

As usual, you can find my python code and executable on GitHub. One word of warning, these extensions are not officially supported by SAP, but you can use them as an example of what can be done, and if you are familiar with these technologies please feel free to contribute and improve them.

Does this extension help you work more “Agile” within Lumira? Please share your toughts and comments here.

Want to have a look at my other posts?

A Lumira Extension To Acquire Twitter Data!!!

Creating My First Data Access Extension For Lumira

My Journey With Predictive Analysis Custom R Scripts

To report this post you need to login first.

16 Comments

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

  1. Astrid Gambill

    Nicely done Alper.

    I’ve been an MS Access user for many years but am now using Excel more instead.  I think Power Pivot gives you the same kind of drag/drop for joining relational tables, though I don’t know if it gives you the option of runtime parameter selction.

    Regards

    Astrid

    (0) 
  2. Karolina Maria J

    Hello,

    really nice article…

    I am wondering how you can connect directly to MS Access db – I need to install jdbc driver for Ms Access and then use generic jdbc driver right? Csn use bridge to connect to odbc ?

    (0) 
    1. Alper Derici Post author

      hi,

      data access extension allows you to create a seperate executable so i am completely independent from the Lumira codes. There i decided to use python and win32com client ADODB connection.

      potentially an odbc interface can be created.

      Do you have a specific case that you want to connect to a specific data source? could you provide details?

      (0) 
    2. Karolina Maria J

      I am trying to connect from Lumira Desktop to MS Access DB.

      There is no native driver for MS Access in Lumira, so I have to use Generic JDBC Driver, right? Since MS did not release jdbc driver for MS Access, I guess I would need to use one of the 3rd party tools, right?  Do you recommend any specific JDBC dirvers provider?

      (0) 
      1. Alper Derici Post author

        then i think the data access extension could fit your case,

        did you notice there is a download available for both source and executable files of my extension. You may want to try that to see if it fits your use case.

        please be informed that this extension is not meant for productive use and is not supported by SAP. But it can give you an idea on how to develop something similar,

        (0) 
        1. Karolina Maria J

          As you mentioned DAE is not supported by SAP so I do not whink it fits my case.

          I am trying to figure out how to connect to MS Access db without modifying the code.

          You mentioned: “Now how do i use this within Lumira? Microsoft Access is not in the connection options for Lumira. It may be used with “Generic JDBC” option but that would leave me alone with a “copy-paste” sheet where i had to find or create a working SQL query out of my mind. “

          I dont mind creating a working SQL query out of my mind. But I failing to connect to MS Access. Would it be possible for you to wirte a blog post about it?

          Do I need to dwonload a JDBC driver for MS Access? Can you recommend any aone working with Lumira? What about jdbc odbc bridge? Can I try this one too?

          (0) 
              1. Jawahar Konduru

                Apler,

                We have a situation. We installed add-on to be able to see the queries from MS access DB. But when we look in the query list, it lists queries, but also some other which are not queries. Did you encounter the issue? How can we see only queries? Lumira MS Access issue.PNG

                (0) 
                  1. Alper Derici Post author

                    Hi,

                    Queries, that accept input parameters are called procedures in msaccess. So i get a list of procedures. Technically you may have similar objects that are in the procedure list.

                    simple solution could be to put a name filter to the list since your unwanted objects seem to start with ~sq

                    best regards,

                    (0) 

Leave a Reply