Power BI Report Server Data and SQL Server
Hi All and d_gosbell, My company purchased SQL Server Enterprise Edition 2019, with which I got the Power BI Report Server (Jan 2022 Version). Since our client had a strict on premises requirement. I develop Power BI Dashboards on the Power BI Desktop (For Report Server Jan 2022 Version). I then save it on the Power BI Report Server Web Portal. I have 2 main questions; 1) I develop a Power BI Dashboard which displays project progress for multiple departments such as Engineering, Construction, Procurement, etc. I get multiple data sources (Manual and Live) for this report. Eg: I get an Excel/ PDF/ Word File sent by Email every week, I have to load Excel manual data from SharePoint, I have already linked to some Oracle/ SQL Databases for live data, etc. Currently, what I do is, for example, My KPI is Project Plan Vs Actual Data. I get data for this table on a weekly basis. I have stored this data in an Excel Table which I linked to the Power BI Dashboard. When I get next week's data, I will overwrite on the current data in the Excel table. The issue with this is every week I'm overwriting the Past Week's data. So I'm not able to retain past data, and will always only have the current week's data. My requirement is to store all weeks data in one place. I cannot only have the recent week's data. I need to have a filter which can show all the week's data. Question, how and where am I supposed to store every week's data for different visuals? Can I store it in Excel (Where every week I add new rows in the table instead of overwriting the past data), or do I store all this data in a database such as SQL? 2) My 2nd Question is this. I'm aware that when I develop Dashboards and save it in the Power BI Report Server. My data gets stored in the SQL Server (SQL Server Management Studio) - the one we installed along with PBIRS. --What kind of data get saved in the SQL Server? How do I access that? --Based on Question 1 - Can I use this SQL Server as a database where I store all my week's data for all my Power BI Visuals? How would that work exactly? Kindly assist on the above. Thank you in advance for your support!
