Hello everyone!
My question is a more database related one, but with the goal to improve the architecture of our current Power BI solution. Maybe someone with general IT knowledge can help out 🙂
So the background is that we currently have one big report, where the data is mainly coming from a oracle database. All "KPI´s" are stored in separate tables/entities (The database was originally developed for a web application). Since we needed a star schema for the report we choose import mode, and via power query applied the needed ETL steps to get the required fact and dimension table.
But since we import all the data into the one PBIX. file, we are now starting to experience scalability issues, where some team members can´t even open and edit the report anymore.
My idea now was to use the direct query connection, so we dont run into scalability issues and also have the advantage to see live data, and not just a daily version.
That sayed we are all business users and dont have much experience with databases. So I was not sure what the best approach would be.
Does it e.g. make sense to create the fact table in the database itself? The IT developers surely will need the data in the currently existing separate structure. So therefore does it make sense or is it even possible to create a fact and dimension table with the data from the separate tables within the database? (That would mean that we are storing the same data twice but in different formats...) Or is there a way to create a "virtual table" in a oracle database, which you can then select from the Power BI connection?
Would be greate to get some opinions on that matter.
Thank you in advance!
