Oracle Communities

Oracle Analytics and AI

Child Item

Allow OAC connect to MS Access

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:

Find more posts tagged with

Data Connectivity

Needs Votes

Status: Needs Votes

Comments

Alan Lee - Oracle-Oracle

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

LipLooi

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

Gabby Rubin-Oracle

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

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?)

dvdesktop_PeKz3iBPyQ.png

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.

Gabby Rubin-Oracle

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

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

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

@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!