Oracle Analytics Cloud and Server Idea Lab

Products Banner

Allow OAC connect to MS Access

Submitted
200
Views
8
Comments

Organization Name (Required - If you are an Oracle Partner, please provide the organization you are logging the idea on behalf of):


Description (Required):

Is there a plan to allow OAC connect to MS Access? We can see the DV Desktop can connect to MS Access but not OAC

https://www.youtube.com/watch?v=gWLBsRzanqw

https://docs.oracle.com/en/middleware/bi/analytics-desktop/bidvd/data-sources-and-data-types-reference.html#GUID-1DDF52B4-2AE0-4E02-8E9A-EE74D372B1A2

Use Case and Business Need (Required):

We have a lot of company survey data, compensation survey data which are excels and we stored them in MS Access. It'd be good if OAC able to connect to MS Access to allow us do analysis using these data.

Enhancement Request / Service Request:

5
5 votes

Submitted · Last Updated

Comments

  • I believe Access can be configured as a linked server in SQL Server. Have you explored that option?

  • LipLooi
    LipLooi ✭✭✭

    Hi Alan, we do not use SQL Server. Is that the only way we can connect it to OAC?

  • You can also find JDBC drivers for MS Access and use that with OAC. Have not tested them but there are quite a few of them out there and should work with the OAC JDBC connectivity (via RDG).

  • Dan Vlamis
    Dan Vlamis ✭✭✭✭

    This came up in a recent customer implementation, so I feel compelled to argue for this enhancement request...

    OAC and DV in particular is marketed as an END-USER tool. I can kind of understand telling an IT department to set up a linked server via SQL Server (or maybe access via the RPD), but this is not a reasonable answer for the way that Connections are marketed as something that any user can do.

    While MS Access may be seen as a small-time data source, there are a HUGE number of departmental and individual data sources stored in MS Access. If it's not a huge lift to provide a data connector to MS Access, I really think Oracle should consider adding the connection. Indeed, Oracle Data Visualization Desktop had (has?)

    the connection in version 12.2.5.1.0, so it seems a note in the connection could just say "here are the steps" to enable the ODBC connection or whatever needs to happen.

    Thanks for listening.

  • Unlike OAD, it is not logical for OAC/OAS to connect to individual installations of MS Access on a user's desktop. What are the use cases for 'shared' MS Access? Is it ingestion? Server deployments?

  • Dan Vlamis
    Dan Vlamis ✭✭✭✭

    Thanks for the quick reaction, Gabby. I can ask the client directly what the use case is, but I believe the use case is basically the same as combining data with Excel. Agreed MS Access is generally a personal or departmental productivity tool. A user is looking at OAC data. That person says "hmmm...I wonder how that compares to what I see in my MS Access database?" That person can either export the data out of OAC and load into MS Access for mashup purposes or (what we would prefer to do) is mashup their data with data from OAC. The user may not even think of the MS Access data as "local" and the OAC data as "on server". As far as they are concerned, data is data. Perhaps the MS Access data is departmental (on a network-mapped drive?) and they are trying to use OAC for all their analysis because they know that MS Access is suboptimal. All of this is to enable "self-service", instead of having to run this through the IT department. The user doesn't care how it has to be done; they just want to be able to use OAC against their MS Access data. I can ask the client for their specific use case, but I think the need is valid even this doesn't represent my specific client's need.

  • Dan Vlamis
    Dan Vlamis ✭✭✭✭

    Gabby, I checked with the client and asked about the use case. Basically, it's similar to the use case for uploading Excel spreadsheets. The power user has data he has been using in Access for years and wants to combine it with data from OAS (in this case, specifically OAS (not OAC) with a 83MB .MDB file). From a user perspective, he doesn't care where the server is. He just knows he has a MDB file he wants to mash up with his OAS results. He doesn't want to involve IT in creating a SQL Server connection just for his database (he has a couple). It seems a connector to MS Access just kind of makes sense to him, leaving all of the intricacies of servers aside. He would not agree that this is "not logical". I did mention that Analytics Desktop could tap into MS Access databases, but now this gets more complicated. I'm wondering if there's a way to "pipe" data from Analytics Desktop to OAS so he can combine these. Let me know if you want to discuss privately :)

  • LipLooi
    LipLooi ✭✭✭

    @Dan Vlamis thank you for the use case!

    We encountered a similar scenario in our team where we store a significant amount of survey data in MS Access. Our primary use is for analysis, and we prefer managing the setup independently without involving IT. MS Access serves as a convenient local database solution for us.

    If you happen to have a solution for your client that could apply to our scenario, we would greatly appreciate it if you could share the details. Thank you!