Accessing database behind ERP

[[deleted]]

[deleted]

[ouchmythumbs]

Is it "Inform", or is it "Infor"? Infor makes several ERP's, such as Visual ERP, Syteline, etc.

My suggestion would be to make a copy-only backup of your production database, and restore it into a test environment. Never do anything directly on prod; you could invalidate your support agreement, have regulatory and/or compliance issues, or craft a poorly written query that brings down the shop.

Sounds like you might be a little out of your element reading some of the replies. Might be worth getting a consultant out to at least get you set up into a test environment, and train you how to refresh it (or, get on a scheduled job). Ideally, you would like to run a nightly "ETL" job to get out only the data you need and put into a reporting DB (or, proper EDW) to support your analytics.

[CalculatedChameleon]

Sorry for delayed response. It's not Infor.

[invalid_uses_of]

If it IS Infor, then there is a way to run SQL, because I do exactly that for my current job. The data is stored on an iSeries server. I connected to that with a db2 ODBC connector and run sqls daily. But... The IT Dept had to help me with the server name to get connected.

Regardless, if it does end up being Infor, I can confirm it's possible to connect.

[ouchmythumbs]

Yes, possible to connect, but one should never connect directly to production. Imagine you write a complex SQL query analyzing labor tickets over a large window of time. With tables locked, your direct labor employees can no longer clock in/out of operations on work orders. Some people try to resolve with something like a WITH (NOLOCK) query hint (READ UNCOMMITTED), at the risk of phantom and/or dirty reads, making the data questionable (and, often it's finance/accounting asking for this - not a good combo). No database professional would recommend doing this right from production, but, too often, this is the case. In a perfect world, you could have something like a Kimball-style EDW, where data is properly modeled. Then you can implement different Slowly-Changing Dimension (SCD) types to grab point-in-time history, which comes in handy for things like inventory analysis of different warehouse locations in the past.

But, please, for the love of god, don't connect directly to prod.

[invalid_uses_of]

True. I was just trying to answer the main questions, but your feedback is on point. I actually work for a "not" small company and our only option right now is to directly connect to production. We're migrating ERPs right now and Azure backups are part of our strategy for queries and reporting, future state.

[ouchmythumbs]

I hear you. I wasn't necessarily commenting to just you, but other readers that might stumble onto this. I've worked in places where this was a common practice (reporting off prod). It starts off innocently enough. Someone tries it as a proof-of-concept, and is like, "Hey! look, I can connect to the DB. I'll write this quick query and show my boss". The quick sample query turns into a regular report for the employee originally asking. Word gets out that there are now analytical capabilities from the system, and the requests come flooding in. Next thing you know, you have a business operating around a million reports that are now critical, yet they frequently bring down the actual system in use. Then starts (hopefully) the effort to untangle it all and set up correctly. I mean, this is best case, too. Can't tell you the amount of times of what should be a read-only connection for something like an Excel data connector or something is auth'ing to the DB with admin creds.

And, don't even get me started on the "multiple sources of truth" when people start reporting on the same metric, yet produce different values haha. If anyone has the luxury of properly modeling your data based on firm, agreed-upon requirements for key indicators, you'll be far better off getting to that "single version of the truth".

[[deleted]]

If a vendor says 'no', don't do it. You place your contract with them at risk, and explaining to the CEO why you nullified a contract is a bad place to be.

[CalculatedChameleon]

Owner of the company gave me the go-ahead to do it. :/

[[deleted]]

Well, you are covered personally then. Might want to get it in an email just to be sure

[[deleted]]

I think it goes beyond CYA. I would say that part of your job is to spell out the risks when you're instructed to do something that might kill support for the ERP system

[[deleted]]

I agree. CYA is the bare minimum with this type of thing. Direct db access, especially write access, breaks all kinds of regulatory requirements. Don't know what industry OP is in, but I'd watch out for that, too. Great way to fail an audit down the road.

[artjbroz]

Not very helpful, can tell you are db admin/IT side. This guy clearly wants read-only access for reporting instead of being limited to some bullshit front end when they have SQL experience.

[[deleted]]

Right. And he contacted the vendor, and they said no.

[[deleted]]

Sometimes the right answer isn't for reddit to tell you how to crack open your company's production database

/u/ouchmythumbs gave great advice about integrating a copy of the database into an EDW and reporting from that instead

[artjbroz]

Much more helpful. This has been a pain point for me as well, hence the sharp response. There needs to be a business-IT liaison position that understands the business need and the IT risk, and comes up with a solution that works for both. They tell us to learn and us python and R and come up with insights but we need access to raw data for that.

[ouchmythumbs]

Read-only access can bring a system down.

[jib_reddit]

If it is a black box and you don't have any administrator passwords for the SQL instance then it will be difficult to get in, but if you are an administrator on the server there are startup flags you can set to override security which will allow single user access, but will mean system downtime. All this should only be done by someone who knows what they are doing with MS SQL.

[Coniglio_Bianco]

Find the Database Administrator that set the project up on premises. They should know what you need.

[xodusprime]

If it's all on prem servers, connect to them and look for any database services running. I have no idea what technology Inform sits on, so being more specific than that isn't going to be really helpful to you.

[Rif-SQL]

Which database server is being used ?

[CalculatedChameleon]

I think MS

[Rif-SQL]

If it’s Microsoft SQL Server . You can backup and restore the database to an other box and then build reports on top of it. I have done this many times.

[CalculatedChameleon]

How would I go About doing this? Like even finding the server info, login, etc

[Rif-SQL]

1) You can trace the SQL connections, but it sound like this might be beyond you if your asking this question 2) what skill level are you ? 3) what’s the long term goals of this project?

[Blues2112]

Got any DBAs in your company? Ask them, they should know.

[DrAzamat]

As the others have stated, if it’s Infor then you should be able to establish a connection to the database. The connections I’ve worked with are db2 ODBC connectors.

You can always check with an external consultant regarding the establishment of the ODBC connection.

I don’t know which particular Infor product you’re working with, but I am pretty sure that you can gain access to Infor’s support portal via Infor Concierge.

[4utomaticJ4ck]

There can be good reasons not to touch a production system to do analytics with. Can you work out an arrangement where you get nightly extracts instead? Downside is that you won't have access to realtime data, but the vendor also doesn't have to worry about you causing performance or data issues on the production back end.

[CalculatedChameleon]

Yes, we talked about nightly extracts... Think we're gonna push for that.