Skip to main content
Dynamics 365 Community / Forums / Business Central forum / Business Central & Pow...
Business Central forum
Suggested answer

Business Central & Power BI

editSubscribe (2) ShareShare
ReportReport
Posted on by Tris15 17
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:
Integration to Power BI, Power Apps, Power Automate and Dataverse
Reports and Business Intelligence
Attachments
  • Community member Profile Picture
    Community member 33 on at
    Business Central & Power BI
    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.
  • Suggested answer
    Steven Renders Profile Picture
    Steven Renders 4,953 Super User on at
    Business Central & Power BI
    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:
  • Tris15 Profile Picture
    Tris15 17 on at
    Business Central & Power BI
    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
  • Suggested answer
    Jun Wang Profile Picture
    Jun Wang 3,693 Super User on at
    Business Central & Power BI
    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
  • Netjacker2097 Profile Picture
    Netjacker2097 118 on at
    Business Central & Power BI
    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
  • Suggested answer
    gdrenteria Profile Picture
    gdrenteria 2,827 Super User on at
    Business Central & Power BI

Helpful resources

Quick Links

New Blog Features Released!

Check out the new community blog features for viewers and authors…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

more Community News

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,183 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,733 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans