Hi, we are a small ish company who use Business Central as our ERP system, we currently use PBI dataflows to extract and transform our data for use within PBI. This approach worked well in the beginning but as we add more financial years worth of data the refreshes of the dataflows are really poor so we now need to look at alternative solutions.
There’s so much info out there on different solutions data warehouses, data lakes, data lakehouses etc etc. and now Fabric is coming on the scene that it is a bit overwhelming to choose the right solution so i thought i would ask other on this forum what they currently use to extract, transform and store their data so that they can use within PBI?
As you already are within the Microsoft Dynamics Ecosystem, your questions on performance are well-timed, considering the fresh offering available under Microsoft Fabric. Though Business Central is an entry solution, every business has its own nuances over time. It sounds as if you are voicing an issue with Dataflows, though good to understand the current capacity of your ERP system.
Microsoft Fabric (for all the things that it is and will be): In the broad sense, Fabric most likely will be in your future as long as you lean on the Microsoft Ecosystem for different capabilities.
General questions:
: Has a project plan been scoped, and stakeholders identified?
: Is this addressed within the scope of the Governance structure within your environment?
: Is this addressed within the scopes of Business Continuity and Disaster Recovery?
: Is the Tenant Administrator engaged, and what has been identified thus far?
: What are the results from checking existing Dataflows for changes or other issues?
: What is the result from checking the Dynamics Capacity and utilization of your environment?
: Have there been any recent alerts on rapid database growth?
: Have you contacted your Microsoft Account Manager yet?
: Have you a Microsoft Partner that previously assisted in standing up Business Central?
Generally speaking, each Microsoft Dynamics 365 Business Central cloud instance comes with 80 GB of database storage, which is more than ample for most organizations. When the issue is storage, you can leverage the add-on SKU to provide additional storage capacity, by contacting your Account Manager.
If you are unsure of your Account Manager, you can always reach a trusted Microsoft solution provider or contact the sales team via chat, phone, or email to add additional storage to Dynamics 365 Business Central, or for specific guidance to help you solve business-critical challenges.
This is not really a Business Central issue it’s really a question of what solutions should i use to extract and transform the data before i then comsume within PBI.
The problem is that i am having to clean the data when pulling into DataFlows and then i am pulling the dataflows into a PBI dataset to create reports.
The dataflows take time to run as for example i am pulling multuiple entities into dataflows and then merging these into 1 table to provide a complete group table and this happens multiple times across different areas e.g. suppliers, vendors, general ledger transactions etc but I don’t think dataflows really works when transforming a lot of data.
I have previously used SQL and I was able to create views and stored procesures and so that data that is consumed within PBI is already cleaned so everything runs well and fast so i am looking for solutions potentially cloud based SQL or data warehouses, data lakes etc. that will enable me to extract and clean the data before it is consumed within the PBI enviornment.
While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done.
Thank you for being a part of our community, and we hope you find the help you need.