Dear Community,
I'm wondering if I can set up a MS Access connection with ARIS MashZone and use it as a data feed source. Is that possible? I've checked the ARIS MashZone Help Files and the in-app help, but couldn't find any information regarding database requirements.
Thanks in advance. :)
Hello Markus,
yes it is possible but an unsupported feature and absolutely not recommended for productive use. Performance and reliability can not be guaranteed.
First you have to register your Access DB with an alias as an ODBC Datasource on your windows machine using the ODBC-DataSource-Administrator.
Then, in place of the normal jdbc connections, you have to use the default JdbcOdbcDriver and insert "jdbc:odbc:<alias name>" as the database url in the mashzone administration.
All further steps are the same like using a "normal" JDBC connection...
Kind regards
Thomas
Dear Thomas,
Thank you very much for your quick reply! :)
I set up a new User DSN (Microsoft Access driver):
Then I created a new database connection in the administration settings of MashZone:
When I hit the save button, I get the following error message (no matter if I enter the user credentials or not):
Is there something wrong with the sun JDBC/ODBC driver? Any help is highly appreciated :)
Hello Markus,
as far as i can see, your configuration seems to be fine.
You might configure your DSN using the alternative driver "Driver do Microsoft Access" and give it a try.
Are you running MashZone as a service or as an application? In case of running as a service, it might also be necessary to configure a System-DSN instead of a User-DSN.
Kind regards
Thomas
Dear Thomas,
It works! This "Driver do Microsoft Access" works (surprisingly).
As you're working for Software AG, have you made any negative experiences with ARIS MashZone and MS Access as a data source? As far as I see, it works pretty smooth (MS Access file >1GB)
Hello Markus,
the problem is in the nature of the default JDBC-ODBC-Bridge/Driver implementation, which does not support/implement the common JDBC connection controlling mechanisms, due to this fact e.g. open connections or the size of transfered data packages might harm or slow down the whole application.
To address MS Access in a productive context, it would be recommended to buy a commercial driver implementations, provided by different vendors, with complete jdbc functionality.
Kind regards
Thomas
Dear Thomas,
Is it possible that this unsupported JDCB-ODCB could lead to incomplete data?
For example, I've got 10 data rows in MS Access with an additional date field, let's say 19.04.2011
If I filter in MashZone, I just see 8 rows. It's so weird.
Or does this error occur due to the different date formats?:
This is the original MS Access format:
And this is how it looks like after my SQL Query in the feed preview result:
INVDAT is the original column. I tried to cut off the times and used the INV_Date as my data for the calendar.
Strangely, the data in the feed preview result is exactly the same as the data in the MS Access file. As soon as I use the calendar in the mashboard GUI, I lose data entries. And I don't know why!
I've found the error by myself:
The table cuts of every result after a certain amount of data rows.
The entire MS Access table has about 40.000 data rows, starting from October 2010 until today.
When I put all the data into a MashZone table, it just shows the data from today until 21.01.2011. That's the problem. Why is that?
Is there a natural limitation of a query result in MashZone? I think I will aggregate the data in a Pivot Table first, that should solve the problem. Still, any help is highly appreciated :)
