Small and medium business | Business Central, N...

Business Central equivalent of GP Analysis Cubes

(0) Share

Report

Posted on by Jason Yeung

Hi,

My company is currently planning to migrate from an on prem version of GP to a cloud version of Business Central. One of the items we currently use in GP are the Analysis Cubes for reporting (https://www.gpug.com/blogs/jeff-smith/2016/04/12/why-choose-analysis-cubes-for-gp-reporting). These are used to display sales data and we connect to it with Power BI via Analysis Services. Majority of our reports query the out of the box cubes, but some has a bit of customization.

Becaose we're moving to the cloud, we won't have access to SQL Server when we migrate to Business Central. I was wondering is there an equivalent out of the box solution that exists for the cloud version of Business Central? It doesn't have to match the cubes exactly, but it should allow us to connect to it with Power BI and display the data. We may need some customizations depending on the report(s) we need.

I think I can use Power BI to connect to the cloud Business Central via oData. But this gives me the raw data (individual entities) and I would have to use Power BI's data modeller to decide what columns I want, create the relationships, measures, etc., from scratch. This would probably take a long time.

Sincerely,

Jason

I have the same question (0)

All responses (2)

Answers (0)

Suggested answer

MahGah 15,529 on at

Like (0)

Report

Hi

I am not familiar with GP. But this is a very interesting concept. I cannot recall the exact match. But

1) As you mentioned you can connect to BC through Power BI. You can publish any page (you need to publish Page and not table) from Web Service in BC then access it from Power BI.

2) You can ask your partner to create Query (which define relationship etc in BC) then publish the query and use it in Power BI (very close to Cubes). This will save you lots of time in Power BI

3) BC has some pre-built Power BI Query which designed to pull some of these info. See the list below. Also, see fields in "Power BI Item Sales List" as an example. Notice how it pulls from SO header and line.

Was this reply helpful? Yes No
Suggested answer

Steven Renders 5,672 Moderator on at

Like (0)

Report

Hi,

The simple answer is no, BC does not have that functionality out of the box.

There are account schedules, however those are not really what I would call cubes or a dwh.

You can use the APIs (odata and rest) based on query/page objects to query the database, from for example Power BI or Excel or any tool that can connect to APIs (rest, odata).

Dataflows is a very good alternative to build an online dwh, but it does not have a connector to BC (yet). Odata might work in dataflows.

You can create an Azure Data lake and integrate: github.com/.../bc2adls

And then there are third party solutions that can create customizable dwh, cubes,... like Jet Reports, BI4Dynamics, and others... but they come with a certain cost and learning curve and you also need to create your own Power BI reports to connect to them, I think.

Was this reply helpful? Yes No