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

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) / Developing custom repo...
Microsoft Dynamics AX (Archived)

Developing custom reports on Power BI with Dynamics AX 2012 R2?

(0) ShareShare
ReportReport
Posted on by Rana Anees 1,813

Hii,

We want to use Microsoft Power BI for our Dynamics AX 2012 R2 Data Analysis, Reporting and Visualization.

I have following questions,

1. Can we connect Power BI Desktop with Dynamics AX 2012 R2 database? 
2. As you know, we have lot of AOT queries in AX, can we design same queries and relationships in Power BI?
3. We have SSRS reports in AX, some SSRS reports using AOT queries and some using RDP classes, can we get the same results in Power BI?
4. We have some reports, that are using complex stored procedures, how to manage them in Power BI?
5. SSRS reports show live data and transactions, does the Power BI show the live data?

Please guide with your experience, because its a big step for us to move to Power BI.

Thanks,
 

 

*This post is locked for comments

I have the same question (0)
  • Rana Anees Profile Picture
    Rana Anees 1,813 on at

    Help Please

  • Rana Anees Profile Picture
    Rana Anees 1,813 on at

    Help Please

  • Suggested answer
    Martin Dráb Profile Picture
    Martin Dráb 239,364 Most Valuable Professional on at

    1) Power BI is able to connect to on-premise databases through a gateway. But it's questionable whether accessing an AX database directly is a good idea (actually it's not recommended at all).

    You would have to deal with all AX implementation details in Power BI (converting enum values from numbers to something meaningful, filtering and joining by legal entities, dealing table inheritance, date-effective tables and so on). You also wouldn't be able to execute any AX business logic, such as display methods. And you would risk that requests from Power BI would cause performance problems for your (or your client's) everyday business.

    Therefore exporting a simplified schema to a separate database would make a better sense to me. You could utilize all AX functionality on export and queries for Power BI reports wouldn't influence AX database.

    2) You can, but it's more complicated, as mentioned above.

    3) RDP classes can execute any arbitrary X++ code. You wouldn't have such an option if you read data directly from database, but the suggested workaround would make it possible.

    4) I think you can "execute" stored procedures from Power BI, but you "manage" them in a database, not in Power BI.

    5) It depends on how you use it.

  • Suggested answer
    Ajit Profile Picture
    Ajit 8,788 on at

    As Martin mentioned, we can connect on-Prem DB using gateway provided by the PowerBI (https://powerbi.microsoft.com/en-us/gateway/).  And regarding reports, you can try deploying the out of the box cubes available in AX 2012 for your aggregated reports, charts and all.

  • Rana Anees Profile Picture
    Rana Anees 1,813 on at

    Thanks Martin Dráb and Ajit Srivastava for your value able suggestions.

    I have connected Power BI by Database and loaded tables/views, Direct Query, AOT Query by OData. This aspect is ok now.

    There is a very good article as well explaining how to create Items On-Hand report in Power BI www.axpulse.com/.../using-microsoft-dynamics-ax-as-power-bi-data-source.

    I created this report in Power BI and published on Power BI cloud service with scheduled refresh by on-Prem Datagateway.

    Now I want to bring Sales data and want to build Dimensional Schema or Star Schema and Fact Tables and I am stuck at this stage. I couldn't find any help on the internet. I need it for AX 2012 R2.

    Here are some of my questions, 

    1.  How to bring Financial Dimensions? e.g. CustInvoiceTrans table has DefaultDimension field and its PK of DimensionAttribute table and so on, there are more than 7 dimension tables. Should I bring all dimension tables in Power BI and make relations, but in this case it will not be a good Dimensional Model for a BI application. Very important topic If someone could write article on it.

    2. For sales dashboard what views/tables and fields to fetch in Power BI for Fact Tables?

    If someone could write in details on this topic will be a great help and support to the AX and Power BI community.

    Thanks,

        

  • Suggested answer
    Ajit Profile Picture
    Ajit 8,788 on at

    Good News.

    Did you review the cubes available out of the box? you can deploy them from File ->Tools -> Business Intelligence Tools -> SQL Server Services Project Wizard.

    And answer to your question regarding dimensions, we have a view 'DefaultDimensionsView', you can use that joining with the defaultDimension recid.  

    You can use the standard sales cube for fact tables related to sales details.

    sales-cube.jpg

  • Rana Anees Profile Picture
    Rana Anees 1,813 on at

    I just saw two brilliant videos from Mr. Berat Menlikli.

    These are the best videos on the topic, that how to work with Power BI and Dynamics AX.

    www.youtube.com/watch

    www.youtube.com/watch

    He has not published the third video, where he was to explain, how to connect Dimensions with Sales Fact table.

    Can some one do that for the AX community.

    Thanks and Regards,

  • Rana Anees Profile Picture
    Rana Anees 1,813 on at

    Hii Ajit Srivastava,

    I have SalesTable and DefaultDimensionsView in Power BI Desktop. SalesTable and DefaultDimensionsView have many to many relationship that we cant create in Power BI. How to manage this scenario to link financial dimensions?

    Thanks

  • Rana Anees Profile Picture
    Rana Anees 1,813 on at

    Help please...

  • Mahmood Ul Hassan Profile Picture
    Mahmood Ul Hassan 69 on at

    1. Can we connect Power BI Desktop with Dynamics AX 2012 R2 database?

    2. Answer: yes all databases either they are in SQL server, Oracle or other platform and website urls as well as external files can be connected with power BI.

    2. As you know, we have lot of AOT queries in AX, can we design same queries and relationships in Power BI?

    3. Answer: yes you can design same queries and table relations to get data from the database.

    3. We have SSRS reports in AX, some SSRS reports using AOT queries and some using RDP classes, can we get the same results in Power BI?

    4. Answer: Power BI do not use AOT Queries OR RDP but it has its own designer to relate data from different tables. In addition Power BI uses Dynamics AX Cubes for report development.

    4. We have some reports, that are using complex stored procedures, how to manage them in Power BI?

    5. Answer : they can be managed through Power BI own designer you need to redevelop them in Power BI.

    5. SSRS reports show live data and transactions, does the Power BI show the live data?

    6. Answer: yes Power BI gets latest data because it connects to your database.

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

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Meet the Microsoft Dynamics 365 Contact Center Champions

We are thrilled to have these Champions in our Community!

Congratulations to the March Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
CP04-islander Profile Picture

CP04-islander 26

#2
Michel ROY Profile Picture

Michel ROY 14

#3
imran ul haq Profile Picture

imran ul haq 8

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans