Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2020 release wave 1 Discover the latest updates and new features to Dynamics 365 planned through September 2020
Release overview guides and videos Release Plan | Preview 2020 Release Wave 1 Timeline
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Upcoming TechTalks
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?
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?
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.
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.
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.
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.
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!
Business Applications communities