Skip to main content

Notifications

Announcements

Check out the New Agent Creator Community
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Does Documentation exi...
Finance | Project Operations, Human Resources, ...
Answered

Does Documentation exist for creating a Dynamics GP Data warehouse or Datamart for Power BI Data Modeling?

(1) ShareShare
ReportReport
Posted on by jgdynamicsBI 25

I would like to create a Dynamics GP Datawarehouse / Datamart that get updated every few hours, to make it easier to create reports in Power bi.

I am willing to write some queries or store procedure  and create a SSIS job to right nightly to update the data warehouse / data mart database.

I would then use PowerBI to connect my reports to the data mart instead.

I would be using Expense / Invoice / Analytical Accounting Budgeting. Also Look at GL200 and GL 300, and query multiple Companies (three at most).

What I have is messy, would to see what other have done.
DataModel_5F00_For_5F00_Datawarehouse_5F00_Datamart_5F00_PBI.png

Categories:
Microsoft Dynamics GP
  • jgdynamicsBI Profile Picture
    jgdynamicsBI 25 on at
    RE: Does Documentation exist for creating a Dynamics GP Data warehouse or Datamart for Power BI Data Modeling?

    thank for this link, very helpful.

  • jgdynamicsBI Profile Picture
    jgdynamicsBI 25 on at
    RE: Does Documentation exist for creating a Dynamics GP Data warehouse or Datamart for Power BI Data Modeling?

    Yea at least how you created the data model or the sql queries that made up the staging / dataware dimension / fact tables.

    Thanks in advance.

  • Gerald Buenafe BTP Profile Picture
    Gerald Buenafe BTP 280 on at
    RE: Does Documentation exist for creating a Dynamics GP Data warehouse or Datamart for Power BI Data Modeling?

    Hi,

    We have created a datawarehouse for our clients that include Analytical Accounting. Now the data is being consumed in powerbi and in excel reporting.Let me know if you are interested.

  • Verified answer
    Almas Mahfooz Profile Picture
    Almas Mahfooz 11,009 User Group Leader on at
    RE: Does Documentation exist for creating a Dynamics GP Data warehouse or Datamart for Power BI Data Modeling?

    Check this post.

    https://community.dynamics.com/gp/b/gpmohammad/posts/sql-view-to-extract-analytical-accounting-budget-data-with-accounts-in-dynamics-gp

    There are also many other post , try to search in google.

    Also for GL view there are many views available in company database you can also use them.

  • jgdynamicsBI Profile Picture
    jgdynamicsBI 25 on at
    RE: Does Documentation exist for creating a Dynamics GP Data warehouse or Datamart for Power BI Data Modeling?

    Thanks Almas, do you happen to know the link for this.

    Also if I wanted to create a view for Budget AA data, and GL Transactions from Gl20000 and Gl30000 for three companies to report. Does any example of the complex join to create view exist?

    Thanks in Advance.

  • Suggested answer
    Almas Mahfooz Profile Picture
    Almas Mahfooz 11,009 User Group Leader on at
    RE: Does Documentation exist for creating a Dynamics GP Data warehouse or Datamart for Power BI Data Modeling?

    There are documents for AnalysisCubes where you can create datawarehouse and can run and schedule Integration Services packages for populating the data warehouse database from GP tables/data.

    Or

    You can create views for required data and then use them for PowerBI.

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

News and Announcements

Check out the New Agent Creator Community

Quick Links

Jainam Kothari – Community Spotlight

We are honored to recognize Jainam Kothari as our June 2025 Community…

Congratulations to the May Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 601 Most Valuable Professional

#2
Abhilash Warrier Profile Picture

Abhilash Warrier 416

#3
Adis Profile Picture

Adis 384 Super User 2025 Season 1

Last month Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans