UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Populate Excel Records Using Access, Access 2016    
 
   
pduffer
post Nov 7 2019, 12:55 PM
Post#21



Posts: 444
Joined: 15-April 03
From: Kansas


Yes, I'm familiar with both www.radacad.com and Guy in a Cube. Have used their helps often. I agree they are top notch!

I'm guessing it's the fact that Power BI and PowerPivot in Excel both use the same data engine that is causing the performance improvement I experienced via the tables in Excel instead of Access. Again, these are very small (less than 1,700 records) tables I have used thus far in these particular projects. But, it could also be how our internal network is structured. frown.gif

As I have time I'm going to build a report that will use test data tables. I will have the larger tables in both Access and Excel so I can link the same report to either one easily. Then I can see if the performance difference holds true with larger amounts of data.
Go to the top of the page
 
pduffer
post Nov 7 2019, 01:05 PM
Post#22



Posts: 444
Joined: 15-April 03
From: Kansas


QUOTE
you can create connections in an Excel workbook, and then create queries that grab data from wherever you want. One thing you could do is call a stored procedure that's in your Oracle DB to return a dataset that you want to work with. And then if you need to transform it more before importing, you can transform your data


I can't do the data manipulation required in Excel using an ODBC connection. At least I haven't done enough direct ODBC data retrieval in Excel to know how to handle that yet. My Access procedure uses multiple layers of queries with VBA processes included. As far as a stored procedure goes that gets back to the IT department knowledge and willingness, which are both in short supply right now.
Go to the top of the page
 
MadPiet
post Nov 7 2019, 01:12 PM
Post#23



Posts: 3,364
Joined: 27-February 09



You can bypass Access entirely if you want. You would just create a connection to your data source(s), like your Oracle DB, and then you could call a stored procedure from it to return records. There are tons of options in PowerQuery. The upside is that since Excel will be using essentially an in-memory instance of Tabular, it will compress your data.
Go to the top of the page
 
pduffer
post Nov 7 2019, 01:26 PM
Post#24



Posts: 444
Joined: 15-April 03
From: Kansas


By a stored procedure in Oracle would that be like a view rather than a table? A coworker has built several Power BI reports that pull from a view, but those generally take the longest to refresh. I have always opted to pull directly from tables rather than views as I can get the data I need faster that way.

I've also experimented with using the SQL script from passthrough queries written in Access to pull in data. Maybe I just have more to learn, but my experience has been that I don't have an option to rename the source. For example, our ODBC connection is named "prod" and I can pull three or more different sets of data via a SQL script using the "prod" connection - and everyone will show "prod" as the name of the data source. So you wind up with numerous data source listings that are named the same, but only one will actually allow you to select the table(s) in "prod" you desire while the others will strictly bring in a defined set of records. That can cause a lot of confusion!
Go to the top of the page
 
MadPiet
post Nov 7 2019, 02:40 PM
Post#25



Posts: 3,364
Joined: 27-February 09



I'm not totally sure about the permissions part (because I have SQL Server installed on my local machine), but I can write a query in the window if I want, and pull in just the data that will be returned by the query...

If you go to the Modeling tab, then Get Data, Database, Oracle...
Once you select the server, if you click the Advanced Options triangle, you can basically paste/type a query in there. If it works like the connection to SQL Server does, PowerBI passes the query back to the database to be executed, so you could paste something like

EXEC <schema>.<stored procedure name> param1, param2....

and it would execute that stored procedure/query and return the results to PowerBI.
Go to the top of the page
 
pduffer
post Nov 7 2019, 03:01 PM
Post#26



Posts: 444
Joined: 15-April 03
From: Kansas


I'm sure SQL Server would help but our IT dept said "no way" when we approached them about it. I think their mindset is that if we can produce reports using our current connections - and we can - then there's no need for them to deal with something else even if it makes our job and overall performance simpler/faster.
Go to the top of the page
 
MadPiet
post Nov 7 2019, 03:04 PM
Post#27



Posts: 3,364
Joined: 27-February 09



That's too bad. They could do something super simple like create a schema, add a bunch of objects to it (views, stored procedures, etc), and then grant you access to that. Basically, only let you see and use objects that don't do any data modification. =(

You might have to make a case with them about how much time it would save you...
Go to the top of the page
 
pduffer
post Nov 7 2019, 03:13 PM
Post#28



Posts: 444
Joined: 15-April 03
From: Kansas


Thanks for the info and advice. I think that's going to have to be a battle for later. I know they are already overwhelmed with something that has to be fully implemented by March, so this will probably be pulled back into the forefront after that - if we haven't already come up with something else.
Go to the top of the page
 
2 Pages V < 1 2


Custom Search


RSSSearch   Top   Lo-Fi    8th December 2019 - 01:05 PM