Forum Discussion
PowerBI Service Dataflow & Datamart & PowerBI Desktop
Hello,
I am new to this community and I have started recently the PowerBI Desktop & Service skills. I am struggling right now because I have to download 3 static archived heavy datasets .csv for the last 13 years. these three should be completed with their respective 3 API calls giving new data each day. so this new data will be appended to each static dataset. I have a problem because I have too many datasets now :
- 3 static datasets 2010-2023(mid-December)
- 3 queries/functions of API calls for getting updated data for the last day which have to be appended to the 3 static datasets 2010-2023(mid-December)
- 3 new appended datasets (appended as new) from 3 static datasets + 3 queries with API cals
So now I have 9 queries, just to get 3 final datasets which should be used for my model.
Besides these 9 queries, I still have 11 datasets which come with API calls each time.
1) Is this a good approach or does it exist another way which is more simple to reduce all those queries in dataflow ?
2) Also each time, seams like there's a download of heavy static documents. I need it to refresh only the new data for the last day but don't find an option to do that. I put on 'enhanced compute engine' and incremental refresh but no difference
3)Also how can we put that into Datamarts ?
thank you a lot for you help.
12 Replies
1) Give Incremental Refresh another try. It is designed to handle your scenario.
2) ditto
3) Datamarts are on the way out. If you really must, use dataflows (but even those don't seem to be needed in your scenario)
Hi Ibendlin,
Happy new year and thank you for your reply. I have consulted it only now. I have to manage this dataset in PowerBI Service (Dataflow or Datamart) in order to share it with others. RLS will be needed later. I just have problems merging huge datasets. I have several tables with 15M one and others around 2M. I need to merge so that we can apply RLS easily and surely. Maybe you can suggest another way ? I just cannot merge because of the heavy datasets. I need to do outer inn but it fails. When you say that neither dataflow nor dtamarts is required in my scenario, what do you mean ? What tool should I use in this case? Also incremental refresh requires a spesific day for saving data. I am afraid that some data will not be refreshed and maybe lost. I have a function which makes an API call for getting data for the day-2 (example: on the 4th of January it will get the data for the 2nd of January. So I don't understand what should I precice in the dataflow for incremental refresh. Thank you for your help.
I would propose you use datasets. Yes, incremental refresh uses date ranges but you can fake these by mapping years to "first day of the year", for example.
Thank you for your feedback. Unfortunately the merge with huge datasets is not working ;-( . I will have to find another solution.
Hi zhanna27 ,
First of all, if you are using Dataset and have all queries built in the PowerBI Desktop, then this is not the optimal solution from the performance point of view as it works much slower than Dataflows. In one of my first projects, when I started working with large data sources, I created the queries in PowerBI Desktop, which then were all published to the Dataset, and the refresh took between 1-2 hours. Then I transferred these queries to Dataflow, and the refresh took 5-10 minutes, so this is the only way I am designing my PowerBI now.
Secondly, I think the incremental refresh may not work for your needs, which you already suggested that it doesn't.
I had to build something similar where I had some historical data in csv files, and this data didn't change, and then I needed to append additional new data, which was updated daily.
The best solution for me was again to use Dataflows in the PowerBI service. You just need to design them in a specific way, so you don't waste time on refresh of something which doesn't need to be refreshed.
So, to start with the archived data, you create a new Dataflow where you load all the data which doesn't change. Depending on where you store these .csv files and how many of them you have you can actually do one query and a function to import all of these. But, if you have just 3 files (based on your initial post) then you can also load them in 3 queries, then potentially merge in another query to have a single output table (unless these 3 you mentioned are different datasets). Any query which is not required for any further processing make sure you disable from data loading (right click on the query in Dataflow and disable option 'Enable load') this will make this query 'hidden', so you can't see it when importing data from this Dataflow to somewhere else but it will also speed up the refresh.
Next, save this new Dataflow and refresh. Now you have all the data loaded and ready for the next step.
Now, create a new Dataflow where you repeat the same process as in the previous step, but this time you load the live data which does change. Then save the Dataflow and setup scheduled refresh e.g. once a day or whatever number of times a day you need it. This way, your archived data is just loaded to the first Dataflow, and you do not set up refresh to it (you can still manually refresh it if the data changes at any time), and for the new data, you have scheduled refresh, which will automatically refresh it for you.
Now, the third step is to merge these two Dataflows. You can either create these queries in Dataflow#2, which is refreshed or create a new Dataflow#3. Anyway, this step involves creating all queries to load the static data from Dataflow#1 and dynamic data from Dataflow#2 and appending these two. If you chose to create Dataflow#3, then you also set up the scheduled refresh; however, this must be at least 1hr after the scheduled refresh of Dataflow#2 to allow Dataflow#2 for a full refresh and load of the latest data. So, it might be easier to just add these queries to Dataflow#2.
Now, you have all the data processed, so you move to the PowerBI desktop, where you create the queries to load the data from the final output query in Dataflow#2 or #3. All the transformations needed for the data should be completed in Dataflow, so here, you just load the data and do not add any steps.
When you publish your report to PowerBI Service, set up the scheduled refresh of the report's Dataset (Semantic model in the latest PowerBI) and make sure that this is again at least 1hr after the refresh of the final queries (Dataflow#2 or #3).
Hi Anonymous, Thank you so much for your detailed feedback. Yes, that's exactly my problem what you've described. It's a good idea to divide those dataflows in order to speed up loading of dynamic data and leaving static one aside. You gave great tips for which I thank you ;-). My problem is now different. My dynamic data which updates the archived dataset every day does show only uploaded data for the current day. The next day this data will be lost. Actually it doesn't register in a dataset. And because we call a dataflow which itself gets a fixed data from my desktop, this dataset is not saving the updated information on my desktop. Datamart doesn't seem to make this registration as well. Do you have ny suggestions ? Also the following step would be to merge all those datasets into one so that I can apply RLS safely and be sure that only necessary information will be seen by antoher user of this datamart. But merging process is not possible with all these big datasets Thank you a lot.
Hi zhanna27
I am glad you find my tips helpful.
Now, to your new issue. Not sure if I understand it correctly - I think what you are saying is that the dynamic data you receive in .csv file contains only data for the current day. But what happens to the previous files from yesterday, the day before, etc? Is the file replaced with a new file every day, so you don't keep the previous days in separate files? If you don't keep the files from the previous days, then you can't really achieve this in PowerBI as whenever you need to refresh the Dataflow, it will only use the files you currently have. e.g. can't get data which is no longer in the datasource. So, in this case, first, you need to sort out the files from previous days to be kept in the folder, and you only add a new file to the folder every day. This way, you keep all the 'dynamic' data files in a folder, so when the Dataflow is refreshed, it can read data from all files within the folder. Depending on how you get the new file every day, there are different ways to sort it out. In my case, I receive the new file via email everyday, then I have a simple PowerAutomate which gets the attachment from the email and saves it in a SharePoint folder for me (if the file name is the same everyday you can add a date at the end of the file name when saving to the folder). This way, the process doesn't involve any manual handling of the data as it is automatically done for me even if I am away. Once the file is uploaded then the scheduled Dataflow is refreshed and the report updated with the latest data. So, the key is that you need to keep all the files for the 'dynamic' data in a single folder, then every month or couple of months, you can move the files to the 'archived' data folder and then remember to refresh the archive data Dataflow to get it updated and the 'dynamic' data folder is empty again so your daily refreshed Dataflow refreshes nice and quick.
One thing you mentioned is that you have the data on your desktop? Although it may work, I would strongly recommend moving the data into SharePoint (I hope you can create your own site within your company or can be given access to any existing SharePoint). You would need to have two folders - one for the archived data files and one for the dynamic data files, so the Dataflows will load all files from these folders. Which brings me to the next point, how do you load the data from files? Is it just load from Excel or do you use load from folder (SharePoint Folder). The best method in your case is to use Load from SharePoint Folder where you need to provide link to the main SharePoint site then filter to the folder you want to load and when you see all files within the folder, the easiest is to create a simple function to extract data from each file and then combine into one single table. Also, I always use formula =Excel.Workbook([content]) in a new column to get a list of all elements within the workbook, then you can filter to the item which you want to extract e.g. Sheet1 or Table_name to only work with what you need and then apply a function to extract/transform the data for me before expanding the final output into a single table.
