Direct database connection to read data using Microsoft Query within Excel

(0) Share

Report

Posted on by _MGP

506

A "super-user" who has been used to making his own refreshable, excel reports in the legacy ERP (not AX) using direct SQL connection to the PROD database wants the same access on our AX 2012 R3 system which is a single company implementation.

They want to be able to join the data and have these joined data sources refreshable. They cannot do this via the Excel Add-in.

As a developer I can see they won't get the built-in specific Company access, ValidTimeState functionality or get Enum conversions to text.

I can offer to create Queries and make them available to Excel Add-In (as per this blog post) but the turnaround on ModelStores limits the speed at which new reports can be developed and potentially increases cost of administration.

One solution is to create a SQL user on the PROD database that has read-only access to the database and provide a connection string to allow access via Microsoft Query in Excel.

Are there any other disadvantages to this approach, or would you have any problem doing this on your AX 2012 system?

I have the same question (0)

All responses (6)

Answers (3)

Verified answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report

Hi MGP,

Do you think that this is a good idea?

If this super-user does not know the table structures and the relationships in AX2012 R3 then his analysis might give him wrong data that result in wrong interpretations and conclusions.

Why can't this super-user not work with the standard management reporter reports or the standard cubes?

Best regards,

Ludwig

Was this reply helpful? Yes No
Verified answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

While it's technically possible, it might not be a good idea. Ludwig mentioned some points, but one additional thing to consider is that if the system is updated to D365, there is no direct database access anymore (except with On-premises version). This means that any investment that is made in the direct SQL reports will go to waste.

Was this reply helpful? Yes No
_MGP 506 on at

Like (0)

Report

Hi Ludwig,

Good idea? No, my preference is to have more discussion and control over extracts to consider the needs of the business in a centralised fashion.

This user wants maximum flexibility over the data to enable table-by-table extracts. They do not want to be limited by "standard" anything in case a future requirement for a data point or filter cannot be provided out-of-the-box. They want to eliminate the need to talk to IT. Maybe this is a unique point of view?

You make a good point about potential bad analysis on joins and filtering. Ultimately they would take responsibility for their assumptions.

Was this reply helpful? Yes No
_MGP 506 on at

Like (0)

Report

Hi Nikolaos,

That is a good point about D365 - I will raise this. Although On-premise is probably what they would opt for they have not made a decision about upgrade yet.

Was this reply helpful? Yes No
Verified answer

Brandon Wiese 17,788 on at

Like (0)

Report

We provide quite a lot of direct data to users via IT crafted stored procedures, which provides the best combination of flexibility and security in my opinion. With stored procedures, you can provide for parameters to allow filtering without granting any direct access to the underlying tables at all.

While you may trust your user to be responsible enough with their SQL access, and while proper permissions may protect your data from modification, your user can still harm your production system in unexpected ways.

For example, during development they can run queries that perform poorly (bad joins, not respecting indexes, etc.) and impact the overall performance of your production system. This can largely be mitigated by demanding that development occur on a non-production system to be moved to production only upon completion and performance testing.

Another example, it's not particularly difficult to lock up a large table and cause other processes to hang or the version store of your tempdb database to grow out of hand very quickly, even with read-only access.

Finally, while you may trust your user, there may be some data you simply cannot permit anyone to access. Storing any social security numbers or employee ID's in your database? Sensitive data like payroll? Legally, you may not be allowed to just grant even read-only access to all tables in some cases.

Just food for thought.

Was this reply helpful? Yes No
_MGP 506 on at

Like (0)

Report

Thanks Brandon. Several very interesting points and suggestions that you have raised there.

I've taken a look and you can call sp's from MS Query which is promising.

Meeting about this is tomorrow so your post was somewhat well timed!

Was this reply helpful? Yes No