By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,034 Members | 608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,034 IT Pros & Developers. It's quick & easy.

How to consolidate data from multiple sources for reporting team?

P: n/a
I am a manager assigned to a new reporting team and am responsible for building out the team and finding a better way to report the information we collect. I'm looking for the appropriate roles that should be defined for the team but understand it will be based on the tools needed for reporting. We currently collect data from various sharepoint sites, testing tool database, excel, etc... and utilize HTML/JS to compile all the data into a spreadsheet so there is logic that color codes or flags things that are not compliant. We also apply VBA for summarized views of the data. I am looking to find out if Access or SQL Server or some other means of centralized data collecting would be better served for our team, yet, do not know if I want to duplicate everyone elses data into my own collection. Just looking for some ideas.
Nov 6 '10 #1
Share this Question
Share on Google+
6 Replies

Expert Mod 10K+
P: 14,534
A database by it's very nature will always handle data better. However, there are a number of issues to consider.
  • Are you extracting data as a flat file?
  • Is there identifiable relationships between the various data sources that you need to exploit?

For example, if you were dealing with something like membership data and the various sources were data input by members that needed to be collated. Now if this ended up as just a flat file of unique records then I imagine Excel with VBA would be sufficient for reporting. If not then I think you need to look at a database.

Whether to use Access or SQL server depends really on the amount of data you are reporting on. For records up to about 0.5 million Access should work fine. Anything over that would require SQL server. However, if you are using Excel then I imagine your record size is quite small as it doesn't handle anything over 65k very well.
Nov 6 '10 #2

P: n/a
Here's the structure:
System of record comes from the PMO SharePoint site. This gives me the full scope of projects in which I need to report on.

I have my own SharePoint site where I track users submitting various forms. Some data is a duplicate of the main source for organizational purposes (SharePoint Views).
Test Metrics come from Quality Center which is extracted and stored in my SharePoint site as well.

HTML/JavaScript uses the PMO SharePoint site for mainsource of data and then compares projects to project in my Sharepoint to pull back matching records that produce a scorecard. There is also logic in the HTML/JavaScript that compares current date to planned dates and if forms have been submitted and to check metrics. Depending on the condition, the project will get marked appropriately with specific color coding to produce a dashboard that tells the health of each project from a testing perspective.

Based on your comments, it sounds like if we went to a database, Access would be the best.

I know that we can integrate SharePoint with Access and are tinkering around with it. I don't know enough about Access to know if the same sort of logic can be applied, what language would be needed for this, or if there is a reporting tool that can do this as well that would basically sit on top of Access better than Access reporting.

This is all new to me. I've always done this reporting, but not in a formal reporting team structure nor with various requests coming in now. We just keep tweaking our HTML/JS for the different view but logic is not always necessary and can be a straight data extract but always need the sources mentioned above. Trying to find ways to make it better.

What type of resources would I need if I went with the Access Database extraction to Excel considering what I mentioned above?
Nov 7 '10 #3

Expert 100+
P: 295
I managed a reporting team not too long ago. If you are planning on using MS SQL then you should plan on expanding your team and getting a Ms SQL DBA.

MS Access will also require an additionally resource with expertise in SQL, VBA, and Basic Visual Basic design skills.

As far as your team the structure I implemented was the following:

1. Coder / Programmer
2. Designer and Content Manager (Used to design reports, design custom graphs and any visual design you will need). This role is not a necessity but it helps.
3. Database Administrator (Strong SQL Skills)
4. Quality Control and Quality Assurance Manager. Designed to ensure that the data being compiled is valid and that the reports meet client requirements.

Now if this is not the structure you were looking for let me know. Probably need more insight
Jan 20 '11 #4

Narender Sagar
P: 189
If you are using SharePoint site, then half of the problem is already solved. What I understand that you want to collect data from multiple source.
You can achieve this objective no. of ways:
1. You must have list on sharepoint site. You can create separate form for data collection and give the link to team (who have to provide the data to you). Result - Data will be automatically compiled.
2. If you have Microsoft Infopath, then this is the best option. You can create an infopath form and distribute it to entire team. This infopath automatically compile information to a single database. Of course you need to do certain setup while creating Infopath form, which is very easy.

Then you can easily link Sharepoint to MS Access and do lots of data analysis there.
Jul 26 '11 #5

P: 27
You must organize some kind of version registering.
I used to be Project administrator at a small Hungarian factory of General Electric. Part of may task was to print out the Autocad drawings and other instructions used in production. These documentations were changing daily even during the mounting the equipment in the shop. I wrote a database in Access
The main goal was that only the latest versions of drawings must be on the tables of the work managers and test engineers and the outdated versions should be withdrawn and destroyed in a paper mill.
Another advice: Force your subordinates to use the same subfolder structure to store the different kinds and versions of your files.

Oct 13 '11 #6

P: 3
esProc may solve it easily, It is very easy to access multiple data sources , and more fit for report.
Check URL for details about it.
The Good Helper of Reporting Tool
Apr 9 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.