How to update/refresh a recurrent Dataset that is made of different input coming from different DB

[DanZ]

Dear all,

I would like to build a Power BI dataset which is made of different input coming from different DB ( some from SQL, some from Oracle…etc.) For this initial data extraction, we made that happen via Python as Python is only way in PowerBI, I find for the moment, allowing me connect to different DB and to extract different input in one single script with dynamic parameters( like Acctperiod, LegalEntityCode…); otherwise, with other options of "data sources" in PowerBI, seems that I have to build/refresh the different script/tables one by one by coping the new updated SQL code in each table...etc. ...which is too tedious and definitely not the final solution that i would direct to... 😞 

Once this initial dataset is available, I will have to do some data transformation, new measurements/columns creation, new tech tables creation…etc in PowerBI for final visualization and my final reports will be based on this refined dataset.

My goal is to refresh this initial dataset every quarter, and hope to reuse all changes, transformations, data queries and modelling realized in PowerBI.

but I did not see that possible in PowerBI, with the testing performed so far, my Python script brings me a dataset containing different inputs/tables, but once I refresh the parameters in Python and re-import the same dataset, new table with suffix(1) (2)… will be generated which is not what I am looking for.

Hope I put my ideas clear, anybody has any better practice to share, please.

thanks a lot,

[parry2k]

Maybe easier to jump on a call and look at it. 

[parry2k]

@DanZ you can still filter the data in Power BI (Power Query) for the last two years, you don't have to filter it in python, and with query folding (most RDBMS support query folding), the query will send back to the database and everything should work fine. You can make the solution smart to always use rolling last two years or whatever you want so that there is no need to come back and make any changes.

[DanZ]

I might miss anything here, but I don't see how. for example, when I extract the info from SQL/Oracle, the script is already quite complexe and contains a lot parameters, and some parameters are not even used in PowerBI... 😔

could you advise more, please.

[parry2k]

@DanZ regardless of what way you go, somewhere you have to change the parameter value. you can create these parameters in the power query and use it in your queries for each source, so every time you change the parameter value, it will change all the corresponding queries. Seems like I'm missing something here, it shouldn't be this hard.

[DanZ]

ok, I see where causes us having different understandings on solution.
The reason why I have to build "an initial dataset" with Python, it's because, the raw data/tables in Oracle/SQL are enormous, I can not extract the whole table from Oracle/SQL DB then use the functionality of “parameters” provided in BI. that's why I defined my parameters in Python which will gather all info i need from different places. that's said, every quarter, I need to do PL YtD analysis for last 2 years while the sources data in Oracle contains all PL info (in different tables, of course) since 1980's or earlier... so now, my python solution only takes the data of 2020(Q1-Q4) and 2021(Q1-latestQ) for a given company..etc
With our experiences, even with very limited scope of data info, when you dataset is getting reasonably complexes, the performance of PowerBI will be barely tolerable. That’s why we think it might be better to reduce the size of initial dataset.
Do I make any sense?

[DanZ]

@parry2k, thanks for your comments.

What you mentioned works (inefficiently) definitely. but personally, I will never do that in my work. I don't think I would spend too much time to just update different parameters in different scripts and having fun to copy-paste in PowerBI one by one... if I had only 1 table, maybe I can live with that. However, as I mentioned, I have to extract different tables from different DB ( in total more than 30 tables to be extracted initially, and for 5 different entities each quatre and within very short timeline), and each script has countless parameters to be updated each quarter... unfortunately, I will not consider that as an option. 

[parry2k]

@DanZ I don't think going to the Python route to combine datasets is a good approach. I think you should correct directly to each data source, append/merge/transform whatever needs to be done (or whatever you are doing Python) and that should be your solution. I don't understand why this approach will not work? Seems like you are overcomplicating the solution which should be pretty straightforward.