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

Business Central & Power BI

(1) Share

Report

Posted on by Tris15

400

Hi Everyone

I want some insight on tables that are loaded into Power Bi - I am using the Posted Sales Invoice Lines web service as data source for my Power BI report
I notice that when the table is loaded, that only Shortcut Dimension 1 and 2 is visible, I need the rest of my dimensions to be available - how can I ensure that the remainder of my dimensions are loaded as well?

Thank You!

Categories:

Dynamics 365 Business Central

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Gerardo Rentería Ga... 25,225 Most Valuable Professional on at

Like (0)

Report

Hi

I hope this can help you and give you some hints

Getting dimensions from MS Dynamics Business Centr... - Microsoft Fabric Community

The Key to Reporting on Business Central Dimensions Using Power BI - Bond Consulting Services

Data model in Power BI and Dimension Set ID (dynamics.com)

(7) Dealing with Dimensions - Hooking Power BI up to Microsoft Dynamics NAV (and Dynamics 365 for Financials!) | LinkedIn

BR

GR

Was this reply helpful? Yes No
Netjacker2097 286 on at

Like (0)

Report

Hi Tris15,

Just so I can further help you, what other dimensions you are after that is associated to the posted sales invoice?

Thanks,

M

Was this reply helpful? Yes No
Suggested answer

Jun Wang 8,202 Super User 2025 Season 2 on at

Like (0)

Report

are you sure the posted sales invoice has the rest of dimension populated? normally user don't populate all dimensions for such transaction. Or check if you pull in the dimension table.

alternatively, you can export the data via config package, and import into powerbi

Jun Wang

Was this reply helpful? Yes No
Tris15 400 on at

Like (0)

Report

All the necessary dimensions appear on the captured invoice for those specific transactions - but when load the Posted Sales Invoices table into Power BI, the other dimensions are not added, so I need it to display in that table to do the power BI report

So in short only Shortcut Dimension 1 and 2 appears, I need shortcut 3-8 as well

Was this reply helpful? Yes No
Suggested answer

Steven Renders 5,672 Moderator on at

Like (0)

Report

I always suggest to create your own Query APIs, in this case one for the Facts (Sales) and another for the DimensionSetEntries.

There's one API out of the box that you can maybe use:

But then you also need the DimensionSetId in your Saleslines query, to link the two.

And here are some very good trainings:

Business Central Power BI Training - Business Central Booster

Microsoft Business Central Advanced Power BI training (businesscentralbooster.com)

Was this reply helpful? Yes No
CU29041409-0 35 on at

Like (0)

Report

In Power BI, when loading data from a web service like the Posted Sales Invoice Lines, you may encounter limitations in the initial data load, especially regarding the visibility of certain dimensions. To ensure that all dimensions are available, you can follow these steps: 1.After loading the data, navigate to the "Model" view in Power BI Desktop. Here, you can see all the tables and their relationships. Ensure that the necessary dimensions are correctly linked to the fact table (Posted Sales Invoice Lines) using appropriate relationships. 2. Check the settings during the data load process. Sometimes, certain fields might be excluded due to default settings or limitations in the web service connector. Adjust the settings to include all relevant dimensions. 3. Use the Power Query Editor to modify the data transformation steps. You can explicitly include additional dimensions by selecting them from the available fields during the data loading process. 4. Consider using Direct Query mode if applicable. This mode allows Power BI to query the data source in real-time, ensuring that all dimensions are available dynamically. However, keep in mind that Direct Query mode may have performance implications depending on the size and complexity of your dataset. 5. After making any changes to the data model or query settings, refresh the data in Power BI to apply the modifications. This ensures that all dimensions are loaded and available for analysis in your report. By following these steps, you can ensure that all dimensions from the Posted Sales Invoice Lines web service are loaded and available for analysis in your Power BI report.

Was this reply helpful? Yes No