Hello,
This may be a really "silly" question - I am not a "technical person" so please be 'gentle' with me as I try to navigate this new technical world...
We have a SQL database that holds all of the data for our ERP (Sage Evolution). After watching 2.06 I felt much excitement as I felt like I'm on the right track to having all of my answers were in the one video... However, when I went into the "database" to practice, firstly, I was overwhelmed with the sheer number of tables and queries in our database and do not really understand the difference between the two?
Secondly when I selected a few of the tables to have a look at they didn't appear to have the related tables connected? Is this relationship something that needs to be assigned and created within SQL or passed from the ERP?
Thirdly, I have been working with our ERP consultants (at significant cost) to develop a separate "BI Database" as I have had conflicting information on the better way to report... Do you think it is better to go directly to the source (ERP) or have a copy that I report from? I'm in two minds considering the enormous number of tables that I am looking at.
Any advice will be VERY appreciated.
Thank you
Maree
Hi Maree,
Power Query will allow you to expand related tables i.e. if you select one table to import, there will be columns with related tables that you can expand if required.
The challenge you're having is knowing which tables you need, which can be daunting when you're working with big databases. Another option for you is to have your database people write a stored query for you that you connect to, which will bring in all of the tables for your specific needs. You can see in 2.06 there are stored queries at the top of the list in the Navigator window directly above the tables.
I wouldn't advise using a copy of the ERP, it's always best to have one version of the data. The minute you start making copies, you introduce room for error and more work, as you need to reconcile your figures to the original source.
I hope that points you in the right direction.
Mynda
Thank you Mynda, it does.
Does pulling in a stored query work the same as pulling in the tables?
As I have moved along in the course today I have been wondering if I am best to run the "reports" and save the data in work sheets - however I think your "one version of the data" answer above clarifies the "best" option is to use the stored queries.
Thank you again.
Maree
Hi Maree,
Pulling in a stored query simplifies the job of getting the data you need. Other than that it works in the same way.
Mynda
