web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SQL Views for Microsof...
Microsoft Dynamics AX (Archived)

SQL Views for Microsoft Dynamics AX

(0) ShareShare
ReportReport
Posted on by Rohin 4,624

HI,

I have one requirement to create some SQL views and procedures to extract data from Microsoft Dynamics AX 2012 R3. These views will assist us create Excel spreadsheets and other reports external to AX.

Can anyone suggest is there anything that can this achieved through AX not go to SQL ? or what are advantage or disadvantage for SQL views ? please share any link for SQL views  

*This post is locked for comments

I have the same question (0)
  • Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    Hi Visvash Walia,

    What kind of SQL view are you supposed to build?

    Have you already checked the standard cubes/cube perspectives? Maybe they already include what you are looking for?

    Best regards,

    Ludwig

  • Rohin Profile Picture
    Rohin 4,624 on at

    where can i used it in place of SQL views? can i use it in excel spreadsheet?

  • Verified answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    Hi Visvash Walia,

    If you take a look at the available perspectives that are used to establish the cubes in the AX2012 AOT then you can identify that they consist of a couple of tables and views. Once the cube is built and processed you can then establish a Connection from Excel to the cube and analyze your data.

    Best regards,

    Ludwig

  • Rohin Profile Picture
    Rohin 4,624 on at

    okay , i would use cubes in excel do i need to make connection in excel from Analysis services options (Excel >Data > From Other sources > from Analysis services) ?

    Is there anything else in AX to fulfill my requirement instead of using SQL views / AX cubes ? and can you please tell me why SQL views not to use?

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    Regarding "I have one requirement to create some SQL views [...] is there anything that can this achieved through AX not go to SQL?": Sure, it's possible to create views in AX. Simply find the Views node in AOT, create a new view (define a query, select fields...) and synchronize it with database.

  • Rohin Profile Picture
    Rohin 4,624 on at

    Martin,

    I have knowledge of AX views  but client requirement is : "These views will assist us create Excel spreadsheets and other reports external to AX."

  • Verified answer
    Ludwig Reinhard Profile Picture
    Ludwig Reinhard Microsoft Employee on at

    Hi Visvash Walia,

    The cubes are basically built upon SQL views; not a single one but multiple ones that are connected. So, you are actually using views when you make use of the cubes.

    About Excel you are right, you would have to establish a connection to other sources from analysis services, just as you mention above.

    Best regards,

    Ludwig

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    Unfortunately "These views will assist us create Excel spreadsheets and other reports external to AX." doesn't contain any question or description of a problem.

    If you mean that you want to avoid accessing database directly, than talking about views in database doesn't  make much sense. Depending an what exactly you need, you can use existing reporting features (including OLAP cubes discussed by Ludwig), Excel add-in, OData and so on.

    Or you can keep using views but avoiding the production database by copying data to an entity store and working against the store.

  • Rohin Profile Picture
    Rohin 4,624 on at

    Thanks Ludwig and Martin,

    I am not getting your below comments Martin:

    " you can keep using views but avoiding the production database by copying data to an entity store and working against the store."

    can you elaborate it  more?

  • Martin Dráb Profile Picture
    Martin Dráb 237,801 Most Valuable Professional on at

    It probably means that you don't know what "entity store" is. When you get into such situation, you can use a search engine to find information. For example, you would find Introducing Entity Store for Dynamics AX 2012 R3, which explains it in detail.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans