Apologies if this seem too broad or general, but I do have some specific questions.
The place that I work at has a very poorly implemented specialty enterprise system, and the MRP (Material Resource Planning) module is pretty much useless. I with the help of this forum, I was able to design a bare bones "functioning" MRP system work around with Excel 2007 using existing reports from the ERP, and for that I am very grateful. However, I am not the type to to set it and forget it, and I my workbooks have grown unwieldy, sluggish, and well, they have never been pretty.
So, while I can't really expect the company to spring for a $50k re-implementation of the ERP system, I was able to talk them into giving me one of the few computers that has an Excel 2013 key. Of course, this also comes at a time where I am tasked with generating more reports that the ERP system fails at. Frankly, I am just a couple of steps away from redesigning the entire ERP system output. With that in mind, I am turning my eye to the Power X suite of add-ins.
So, per my question, what are some best practices? How do I accomplish this effectively?
Some specific question I have:
- Importing Data:
Ideally, I would like to query the SQL database directly, however our IT dept. consists of 1 person, and that person is actually our accountant. And while I have an AS in Comp Sci, I am a code monkey, not a network guru or DBA. So, my knowledge of SQL is limited, and I do not want to mess up our already shaky foundation. I have heard that even read-only access can cause issues if done incorrectly. Am I over thinking this? Please tell me that SQL queries aren't that scary, lol!
The alternative is to build a "shadow" DB in Excel with exported reports. I don't mind maintaining such a DB, but I would like advise on building it if I have to go down that road. I already know that some data will need to maintain historical data, but generated reports will be snapshots that contain duplicate records. If I add these reports to the data source at a rate of ~600 records a week, 45 columns wide, how long before it becomes a performance problem? Will deleting out of date records cause problems later? What about daisy chaining reports into a "aggregate" table prior to utilization? Is it possible to import reports based on the folder they are in, and will it mess up if I remove files from that folder?
Don't Do's? Mistake to avoid when preparing the data would definitely be helpful!- Data Utilization
So, this is more to do with the Power X suite. Namely, am I barking up the wrong tree by sticking to Excel, or should I make the jump to Power BI? I've read the pro's and con's, and feel like Excel is still the right choice but this is a lot of work, on top of all the work that I've done, and want to be certain that it is the right choice before committing. My understanding is that this is a matter of utility vs. aesthetics. I feel like more useful "work" will be done in the Excel environment rather than the BI environment, and the data will be coming from Excel already, so....
I already know that once I get this ball rolling, the boss is going to want to see the data sliced every which way to Sunday... they already are asking more than I can give based on current utilization. however I like to be able to readily see my data sources, so that I know when something isn't kosher. Plus my impression is that Excel has more customization with regard to data manipulation. What is the compromise here?- Resources
Please, hook me up! Tutorials, blogs, videos... anything that you may feel is relevant or useful. Right now I am kind of aimlessly wandering in the world of data modelling.
Well, I hope that this community can help me out once again. I'd like to apologize once again for the broad topic. Also, feel free to ask any questions about my projected use cases, I will be happy to answer!
Thanks!
dacheeba
Bookmarks