Forum Discussion
PowerBI Service Dataflow & Datamart & PowerBI Desktop
Hi zhanna27 , ok, sorry I missed the part where you said in the original post that the dynamic data is loaded from an API rather than CSV files. So, is it an API call to a database or something else? Can you query more data through the API than just a single day? I can see in your function you are querying the data based on two dates, so can't you just query the data since the static data ends (mid-Dec 23)? If there is too much data for the API to handle, you could probably multiply the queries and load, e.g. a month at a time. Also, if the data is coming from a Database, can't you just use the DB (e.g. SQL connector in Dataflow, so then you can use normal SQL query to import all the data you need)? This would be probably the simplest solution as an SQL query should handle millions of rows of data, so you could probably do it in a single query.
I assume that from time to time data which you currently loading dynamically are dumped to the csv files (e.g. every year?) so then you would need to amend the dynamic data query date range.
As for the storage options, I don't think there is anything like that in PowerBI itself. I am not sure why specifically you are using Datamarts. In my experience, Dataflows work faster when loading data, and I don't find them very useful apart from one single feature, which gives you an SQL endpoint to query the data from Datamart.
You could use a database to store the data, but first you would still need to extract the data from your API. There are a couple of ways how you could automate this depending on what tools you have access to. One example might be to use PowerAutomate, which can run on a schedule every day and export the data from API and either upload it to a Database, Datamart, or SharePoint List or just save it as a .csv or .xlsx file where from you can load the data into your 'dynamic' dataflow. However, there would be some limits on the amount of data you can handle with PowerAutomate, so if you have millions of rows every day, this may not work. Another more advanced solution would be to use Azure DataFactory (if you have access to Azure). In the ADF, you can create a pipeline which will run every day, export the data from API and load it into, for example, the Azure SQL database.
Hi Anonymous,
thank you for your suggestions and your feedback. I don't have any storage/database for my current data (archived/.csv + updated daily/API) except of my desktop which is connected to onedrive and sharepoint with my company account or temporary storage on PowerBi service. One person suggested me to save a static dataset into a semantic model of datamarts and update dailly this dataset with new data which will be appended and will not replace the earlier updated data. I don't know how to do that and I am not sure that datamarts can save a document and just append new information and save this information because each new day it would take the same old source and will add a new data. I was asked for the solution to have storage in a semantic model of powerBi service rather than saving on my computer, etc. I have read about powerautomate and it could work I suppose because I don't have a lot of data on a daily basis (7K rows) but if datamarts/semantic model can be refreshed each day and append the new data to a final dataset, then we don't need powerautomate or ADF pipeline. What do you think?