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

(1) Share

Report

Posted on by jgdynamicsBI

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

I have the same question (0)

All responses (6)

Answers (1)

Sort by

Suggested answer

Almas Mahfooz 11,009 User Group Leader on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
jgdynamicsBI 25 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Verified answer

Almas Mahfooz 11,009 User Group Leader on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
Gerald Buenafe BTP 280 on at

Like (1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
jgdynamicsBI 25 on at

Like (0)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
jgdynamicsBI 25 on at

Like (0)

Report
Copy link

Link copied!

thank for this link, very helpful.

Was this reply helpful? Yes No