Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akj2784
Post Partisan
Post Partisan

Connect To Oracle Database in Power BI

Hi All, 

I am new to Power BI Desktop, was playing with the tool by importing few tables from oracle db. Each table has more than 300K records. When I import the table using 'Import' feature it takes a lot of time to import the data. So I waited for 3-4 hrs and finally cancelled it as it doesnt even show the remanining % of the data. Then I thought of using DirectQuery, this brought in the tables but when I create anything new in the Edit Query or Create a new metric/column, everytime it takes a lot of time and for each metric it pops up 'working on it'. To me it looks like for a basic calculation it ends up connecting to db and hence it is slow. So overall the development pace is very slow.  Any help to improve the performance would be appreciated.

 

Regards,

Akash

3 REPLIES 3
mede
Resolver I
Resolver I

Are you comfortable with SQL?

 

My best practice working with data from Oracle DB is connecting via ODBC and use custom SQL, tables or materialized views created in my schema. So I start with an analysis plan, decide what exactly i'd like to analyze and what data I need for it.

Then I write the SQL query for summarizing your data in that format. 

 

And then use the ODBC or Oracle connector to connect to that "smaller subset" and do an "import". 

This approach should work fine for 1-500k rows in your fact table. of course 500k rows will take sometime to load, usually 20-30 mins for me.

 

It is always a good idea to separate your fact and dimension tables and build data model inside Power BI.

Thank you quick response. I am actually using the Get Data > Database > Oracle Database. Then it asks for user and password. 

yes I am very comfortable with SQL. I am actually trying to model a Self Service layer which can be used by end users for analysis.

So its like first creating the model and then they can use this model for multiple analysis.

And I am using 5-6 tables for a small POC. 

 

When I create a new metric /column, it takes a decent time to create the metric. Wanted to know the root cause for the same.

 

I am actually trying to compare it with Oracle BI i.e. OBIEE wherein the development process is very fact. 

So curious to know if there is any such settings which can help me speed the development process.

 

Well that means we work in a very similar universe.

 

If i remember correct, i preferred to use ODBC rather than native Oracle Database connector because of performance issues. But that was maybe a year and a half ago.

 

I have installed Oracle Client and set up an ODBC connection. I create Power BI dasboards as an alternative to OBIEE.

As I said, i define the purpose of my dashboard, then make an analsis plan where i list what measures I need at what grain level for Product, Date and Location dimensions.


Then i go ahead and create Materialized Views directly in Oracle DB, pre aggregated tables for connecting and importing to Power BI.


As long as number of rows don't exceed 400-500k, it loads within acceptable time. Once the data is loaded in, creating the data model and measures inside PBI has never been a problem for me. And the PBIX file sizes are pretty good thanks to super compression.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.