Dynamics Power BI User Group

1.  New to Power BI - trying to connect to Dynamics GP 2015 in the cloud

Posted 6 days ago

We are new to Power BI and are trying to connect our Dynamics GP 2015 database, which is in the cloud (Concerto). What is the easiest way to do this? Can we simply use the URL that we use to log into the cloud?

Thanks,

Deeann



------------------------------
Deeann Kronenwetter
Directed Capital
St. Petersburg FL
------------------------------


2.  RE: New to Power BI - trying to connect to Dynamics GP 2015 in the cloud

Posted 6 days ago
Edited by David Moss 6 days ago
Hi Dean, So you are in Power BI Desktop and looking to ingest your GP accounts data into Power BI and build your own datamodel and then report off it ? I have done similar for a few CEOs to put their accounts KPIs into a mobile app so they can monitor P&L, BS & CashFlow.
Is that what you are aiming to do ?
Or are you looking at doing the opposite and want Power BI reports in  GP ?

If 1st then I recall using the Power BI SQL Database connector to connect to the raw underlaying tables on the SQL DB. Get the IP address or server name, user permissions, username, login and then connect to the DB to find loads of tables and then you need to struggle to understand which table is for what.
(Suppose you could also consider a ODBC connection OR probably now best to us the Odata API endpoints)
I don't believe you would use the url as that would connect you to the GP frontend where i undersstand you are wanting to connect to the backend.
If its any help i recall the following tables:
GL00105: GL Account codes
GL10110: Actual GL current period TB balances
GL10111: Actual GL historic period TB balances
GL00201: Budget for current FY.
I did that for all the different group companies then did their consolidated accounts that way.
Enjoy :-)  heres a link to more info
http://dyndeveloper.com/DynModule.aspx

Is that what you are aiming to do ?
Or are you looking at doing the opposite and want Power BI reports in  GP ?

------------------------------
David Moss
Power BI & Cortana Solutions Consultant
Wottabyte
London
------------------------------



3.  RE: New to Power BI - trying to connect to Dynamics GP 2015 in the cloud

Posted 6 days ago
David's reply was excellent.  Just a few things I'd like to add on the "How."

  1. I do not suggest using Microsoft OData yet (although it only works with GP2016).  It's getting close, but it's just not there yet.
  2. For automatic refreshing:  I would suggest using Remote Desktop Connection and Power BI Desktop to build your Dataset and Reports accessing SQL directly.  Publish them to the service (PowerBI.com) and create your Dashboards.  You'll need the paid version of Power BI, use the Power BI Gateway to refresh your data.
  3.   If you are using the free version, do the same except access your data via Excel reports in OneDrive for Business (assuming you are using Office 365).  You'll need someone to refresh the Excel reports manually, but everyone who builds a Dataset off that (those) Excel report(s) will automatically refresh within an hour after refresh.

Hope to see you in Nashville at Summit!

------------------------------
Belinda Allen
Partner, MVP, MCT,
Njevity, Inc.
Summerville SC
------------------------------



4.  RE: New to Power BI - trying to connect to Dynamics GP 2015 in the cloud

Posted 3 days ago
Hi Belinda,
I like your how number 3: USing Excel as a staging db in 1drive, as it doesn't require the PBI Pro user license as no gateway used, can see this working in 'small' organisations where the license fee is a 'blocker'.
So you Would ;
1: Use Power Query in excel to ingest the GP tables into excel 'tables' and then use Power BI to connect to those excel tables and then ingest & build the data model in .pbix
2: Can you not then automate an excel refreh ? (so the whole process is automated)
3: The later published Power BI dashboards would then sync hourly with the excel dataset in One Drive as they are both in  Azure.
Use OneDrive for Business links in Power BI Desktop | Microsoft Power BI

Cool David

------------------------------
David Moss
Power BI & Cortana Solutions Consultant
Wottabyte
London
------------------------------



5.  RE: New to Power BI - trying to connect to Dynamics GP 2015 in the cloud

Posted 3 days ago
David,
You are correct.  I like this option a lot as well.  Even if it means 1 person has to manually refresh the data, it's a pretty cool and simple option.
Belinda

------------------------------
Belinda Allen
Partner, MVP, MCT,
Njevity, Inc.
Summerville SC
------------------------------



6.  RE: New to Power BI - trying to connect to Dynamics GP 2015 in the cloud

Posted 2 days ago
Do you all have the SAS cubes that come with Dynamics GP deployed in your environment? That would be the best way to use PowerBI and your Dynamics GP data to be consumed.  I have links to blog post that I created I can link you to that show this full process from start to finish.


------------------------------
Jonathan Cox
Frontier Tubular Solutions, LLC
Oklahoma City OK
------------------------------