Replacing Access Database Ecosystem during Upgrade

[JMast]

Hello All,

I am posting to the CNC Forum because of the database impact and connection aspects. However, I am also going to post in either Application or Development to get opinions there, so my apologies for similar posts.

We are upgrading from ERP8 to 9.1 this summer. We do not have an external reporting system. We have many Access databases using the ODA (we are all fat client) currently.

Five years ago we attempted an upgrade to 8.11 which crashed and burned due to COK failures. During that upgrade, I was working with DAS to find a solution to replace Access and failed due to our requirements.

So, I am curious what you would recommend to handle the following:
1) Complex queries/reports that require showing sales for YTD and PYR by customer - the kicker: show all customers whether they have sales or not. DAS struggled with this concept.

It handles nicely in Access by creating a goup of queries which create a temp table of customers first then gets sales.

2) Access users that want to monitor specific items (can be large lists) temporarily and changing frequently.

Again, Access is great since the user can create their own custom table to hold the items and create needed queries against JDE. We do not have to maintain some way to group the items in JDE and we do not have to clutter the Production database with useless temp tables. I also don't want to have to manage a bunch of users with write access to the Production database, even if it is restricted to non-JDE tables.

We are a small company growing very quickly with very dynamic product lines and very independent users who love their Access.

Any tips or help or external systems you think we should look at would be great.

Thanks,

Jer

 

[mdalton]

try this product -- your users will love it. You can download a free fully functional suite and start playing around.

http://www.qlikview.com/

 

[Larry_Jones]

Jer,

you won't want to hear this but its time to leave access behind.
Here's why:

1. Web Client = No ODA. No ODA = no ODBC for Access. This is really a good thing because ODA is REALLY slow with very restrictive limits on the number of tables you can link. However You can find/purchase a JDE ODBC driver to replace ODA.

2. MSAccess is a "OK" reporting tool for access. After you upgrade to 9.1 you may start experiencing #DELETED issues due to UniCode.

3. There are so many better tools out there for reporting against JDE. Your example "show all customers whether they have sales or not" is a very simple thing to do in almost any real BI Solution. I don't know what DAS is, but here are two at least that you should look at:

- ReportsNow (www.reportsnow.com)
- Business Objects via Preferred Strategies (PreferredStrategies.com)

 

[cncjunior1]

I also second Qlikview as a valuable replacement. We use it at my company with great ease of use and development and the users haven't had any complaints from it.

And to second Larry's suggestion, it's time to leave Access behind, and making a major jump in your JDE version is the right time to do it.

 

[JMast]

Thanks for the recommendation. I will take a look at it. I have ReportsNow, Insight Software and, now, qlikview on my list.

 

[JMast]

Larry,

DAS stands for Data Access Studio which was/is a Data Browser type product based on an ODA replacement ODBC. I beleive it is now part of ReportsNow, but I haven't confirmed that yet.

I don't believe we are going to Unicode for the upgrade. We have recieved recommendations that we will not see a major performance hit and we don't have the requirements for multiple languages. However, that is good to know if we do decide to go Unicode.

My understanding of Business Objects is that it is very expensive and will blow the budget of a small company like us. I may give it a look though.

I hear you about leaving Access behind, however, you haven't given me your recommendation on how to handle the Access users that have temp tables to maintain lists. Any ideas?

Thanks for your time,

Jer

 

[JMast]

Thanks for adding your opinion, I appreciate it. What would be your approach to the users that have temp tables they maintain in their Access databases?

Jer

 

[Larry_Jones]

Jer,

I hate to be a enabler of bad behavior

but there is a solution that will let you keep using Access. Look at JDEDirect . Haven't tried it myself but its supposed to have all the benefits of ODA with few to none of the shortcomings.

As far as using temp tables to maintain lists go . . . I'd want to hear a specific example of how this is used. I want to say use a category code(s) but I'd guess that was considered and rejected?

 

[JMast]

Larry,

I hear you on the bad behaviour. I would like to be on a proper BI reporting tool. I have been promoting it for years, but the priorities in this quickly growing company are always outward focused toward capturing market share and new industries. I believe that the new JDE functionality combined with some management changes will bring it into focus. The trouble is the budget is shot with the upgrade this year.

As for examples, it is exactly the kind of thing you could use category codes for. The problem is you have an item that needs to be tracked by the inventory guy on his list, the sales guy on his list, and the manufacturing guy on his list. All have different purposes and lengths of lists which include that item (ie, one might have 5 items, the other 200). The lists can change frequently, from a day to months or years. If you allocate category codes, which we don't have available, then maintaining them falls to IT since they will not want to change the category code on 200 items manually.

So, it becomes much cleaner to let them create, change and destroy their own lists of items or customers to track.

Some of this is the "building the airplane while flying" mentality of a family owned dynamic company and some of it is the messiness that comes from truly treating customers as individuals and not numbers.

The "cheap" option we have is to use a standard SQL Server ODBC giving them a generic readonly database user id. The hassle there is converting queries to handle the date and decimal type issues and retraining users on that.

Any suggestions or experience you have breaking the Access "habit" is appreciated.

Jer

 

[Larry_Jones]

I don't have any more suggestions but on the category thing . . .
Is the issue one of item definition workflow? Each group has to do something to finish the definition of the product?

I certainly understand the "building the airplane while flying" thing. We have more then one of the albatrosses here.

 

[JMR]

Jer,
[ QUOTE ]
We do not have to maintain some way to group the items in JDE and we do not have to clutter the Production database with useless temp tables.

[/ QUOTE ]
I'm struggling a bit with exactly why you don't create a JDE table that your users can update via a JDE application. This table would essentially represent their temp table(s) they're currently using. There shouldn't be any need for any more than one table to accommodate all users.

Simplistically: USER, LITM as table columns. The application would restrict each user to modifying only his/her own records. If each user maintains multiple lists of items, you could just add some kind of List identifier to the primary key.

[ QUOTE ]
I also don't want to have to manage a bunch of users with write access to the Production database, even if it is restricted to non-JDE tables.

[/ QUOTE ]
This would not be necessary if their only access to such a table is via a JDE application.

By your own admission, your company is growing rapidly...a great reason to nudge your users and their data onto one consolidated system. I'd try to find a way to store/maintain these lists in JDE.

 

[JMast]

Larry,

No, it is not a workflow process. These items could be in any stage of the lifecycle. It is just a situation where it occurs to someone (or someones boss

) to track some aspect of a list of items (sales, how many open work orders, inventory, should we discontinue,....) that are not necessarily related by anything in JDE. Typically, they don't want the entire product line, vendor, .... so it becomes "random".

Thanks for challenging me, though. It helps to have a sanity check from an outside perspective every once in a while.

Jer

 

[JMast]

Hmmmmm... very interesting idea. If we can get an adhoc reporting tool that can handle their queries, this could be a soution. I haven't thought it all the way through yet, and it may not cover all the bases but it has some real potential.

Thanks for the idea!

Jer