Design Question - Is Integration Services the way to go ?

we need to transfer data from a OLTP SQL 2005 database to the backend OLAP Datamart and Datawarehouse databases. This data is a long string of XML (from Infopath), out of which we need to extract the data values and populate the Datamart and datawarehouse. Ideally this will be an automated process - and will also have the flexibility to cater for changes in the xml structur.

we would also like the Analysis services cubes to be "updated" in real-time as the transfer occurs.

is Integration Services the way to go ?

You have a lot of choices here. I would recommend starting out by investigating the XML methods you have in SQL Server in general. Paste this link in Books Online in the "URL" bar:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/63cbe6c9-79d0-4cf3-b451-36dbe4668bf6.htm

Without knowing more, I think SSIS is the way to go for you, but that depends a great deal on whether you are "trickling" the data or loading it in one pass periodically. SSIS gives you a lot of flexibility and functionality, but there is a learning curve with it. That being said, I think it's worth the effort. Spending a little more time getting a package together and documenting it well is a great way to implement your solution in SSIS.

Buck Woody

http://www.buckwoody.com

|||

Buck

thx for this

the destination datamart stores the latest version of the case (i.e. the latest record), the destination datawarehouse stores all of the versions of the case (i.e. all records). Ideally we would be updating both of these records at the same time in real-time "trickling" mode - but if necessary, we could bulk the warehouse updates to (for example) an hourly process.

also need to consider the analysis services part - we are using analysis services cubes for our reporting - and would need these to also be updated in real-time (or bulk upload if required on the datawarehouse database) - is this easily done within SSIS ?

also - alternatives... have considered a couple of alternatives

might SQLCLR code done as a trigger on the required table be an alternative solution ? - how would this perform in comparison to the possible SSIS solution ?

amending the web service that does the update of the OLTP table to also do the update of the datamart / datawharehouse ? (not so keen on this one as we are trying to separate these out ?)

thx

mark

|||

The performance questions you have would have to be tested - there are just too many variables to say one way or another.

The bigger question is how your Analysis system will be used. If you need "real-time" information, then trickling is the way to go, and I'd recommend using an HTTP endpoint or one of the other XML methods native to SQL Server, or perhaps the CLR route you've mentioned if the business logic should be coded in.

If your analysis is truly strategic (based on this data, should we make hubcaps or aircraft parts), then SSIS is my recommendation. Strategic decisions aren't made multiple times a day, so a bulk load process is better tolerated. In either case you will transform "in-flight", meaning you don't have to stage the data, so you save a lot of resources and time.

And in answer to your question about cubes, yes, SSIS is suited for that. In fact, it has many steps and processes built right in specific to AS.