Getting dimensions from MS Dynamics Business Central into Power BI

[IntaBruce]

Hi,

I am trying to create a number of reports in Power BI using Business Central data.  I initially posted this question in the Business Central forum but despite over 70 views I haven't had an answer so I am trying here now.

Original post 

I need to report on Sales by Dimension by month ( and later various other things by dimension too ).

However, I can't see a way of getting the dimension data into Power BI.  I have the Dimensions and DimensionValues tables and I also have the GeneralLedgerEntries and SalesInvoices tables but these don't provide the dimension details at transaction level.

I suspect that what I need is in either the DimensionSetLines or SalesInvoiceLines tables, would I be right?

Unfortunately Power BI does not let me import those tables, giving the following error messages respectively:

DataSource.Error: Dynamics365BusinessCentral: Request failed: The remote server returned an error: (400) Bad Request. (You must get to the parent first to get to the dimension set line. CorrelationId: 764c75fa-39e8-41b9-b70d-82642d05a9aa.)
Details:
DataSourceKind=Dynamics365BusinessCentral

DataSource.Error: Dynamics365BusinessCentral: Request failed: The remote server returned an error: (400) Bad Request. (You must specify an Id or a Document Id to get the lines. CorrelationId: 5ae5e9ba-0db6-43ba-a47c-587817c454e8.)
Details:
DataSourceKind=Dynamics365BusinessCentral

Can anyone guide me as to how to resolve this?

Please bear with me as I have limited experience with Power BI and even less with MS Dynamics.

Many thanks,   Bruce

[IntaBruce]

Thank you.

I have found that table and loaded it into my model, but how do I link it to the Sales Invoices table?  SalesInvoices does not contain any reference to Dimension Set.

Kind regards,   Bruce

[Payeras_BI]

Hi @IntaBruce ,

Do you mean the Sales Invoice Header table? If so, you need to look for the "Dimension Set ID" field.

[IntaBruce]

I cannot see "Dimension Set ID" as a column in the SalesInvoices table

[Payeras_BI]

Hi @IntaBruce ,

Looks like you are looking at the Standard APIs v2.0 within the PBI native connector.

You'll need to build a dataset or to get someone to build it for you with all the required fields for your report.

Please go through this to learn more about it:

Use Power BI in Microsoft Dynamics 365 Business Central 

[Payeras_BI]

Hi @IntaBruce ,

Dimension Set Entry table is what you need to extract all the detail behind each Dimension Set Id.

Find below a link on how to proceed:

Dealing with Dimensions - Hooking Power BI up to Microsoft Dynamics NAV (and Dynamics 365 for Financ...