As part of requirement we need to connect to Oracle data base. the tables hold data more than 20 million rows.
Using import method was making the desktop unresponsive and all the RAM of desktop being eaten up to eventually crash the power bi.
because of this we thought of going for Direct query mode. but again there is a limitation of 1 million rows on direct query mode.
i can understand that if you have a SSAS Tabular server with good memory, we can increase the limit in the file msmdsrv.ini configuration file.
But what is the best way for ORACLE database. is it not the purpose of direct query to be able to use in case of large amounts of data !?
i cant make aggregate tables as the data needed was of profile responses with question and answers kind of data.
What type of Power BI License do you have? I would assume with that much data you would need a Power BI Premium subscription for data to be able to update in an adequate amount of time.
Here is link where it shows you what is included with each type of license. I’m not saying pro license won’t work for you, but direct query in my experience work slow even with much smaller datasets that what you have.
@swapnil
Connecting to large databases normally require creation of a dynamic management view in the back end data base that is centred on your query requirements. This should reduce the native columns in tables exposed when you query a specific table.
When building your data model you really need to consider what data tables are required for the analysis and what level of detail or granularity is required for end users.
If you have a reasonable use case you can look at Power BI premium which will provide better ways to manage larger datasets via incremental refreshes, better performance SKUs and aggregation table views. I’m not 100% sure of your answer in relation to “profile responses” but it sounds like you need a larger solution then what a Pro license will provide.
