Keeping track of funding
-
Wednesday, October 03, 2012 11:16 AM
Hi everyone.
I currently work in the education sector and one of the requirements of upgrading our systems is to track the amount of funding a learner is pulling through throughout his/her academic year. We currently run a funding XML file that is generated by a third party software application and this is then processed by us to get an up to date funding calculation.
What I would like to accomplish is to perform an ETL process on this file (each time we run it) and dump the data into a data warehouse/staging table. We would like to perform analysis on said solution which provides how much funding a learner is generating throughout the academic year (and at different periods of the academic year) and whether he/she are still enrolled on their course(s).
My knowledge and concepts of data warehousing are somewhat limited. I have read a number of forums but I am still unclear on a couple of issues:
- Will data have to be imported in bulk into the warehouse without maintenance to the existing warehouse data (ie clearing the warehouse, record deletion, updating etc)
- What would be the easiest way of accomplishing this?
Any questions, please let me know.
Many thanks in advance.
- Edited by divvyboy Wednesday, October 03, 2012 11:32 AM
All Replies
-
Thursday, October 04, 2012 2:23 PM
What does the XML return to you?
- The funding for a whole academic year. The you can delete all the records for that academic year.
- Only new funding data (new transactions). Then you would have to insert new rows and update existing ones (if necessary).
You can look at this answer on how to insert a XML into a SQL Server table. How to load an XML file into a database using an SSIS package?
- Marked As Answer by Eileen ZhaoMicrosoft Contingent Staff, Moderator Tuesday, October 23, 2012 9:03 AM
.png)
