Have any of you heard of an application that makes it possible for normal business people with no IT knowledge to directly from Excel “publish” a table to SQL Server and Analysis Services?
I’m searching for an Excel add-in that sends the data in a normal Excel table with a few dimensions and some measures, as a data set (preferably via a web service over https) to a server application. The server creates a table in a specific SQL Server database, and automatically creates a cube in a specific analysis service database. The cube on the server is automatically processed and the data is available to all users in the company using their BI GUI.
Any ideas where to find such tool?
/Erik
I don't know any turnkey solution which can solve your entire problem. But there are enough off-the-shelf technologies, such as SQL Server Integration Service, out there from which you can build your own solution.
Alternatively, take a look at the Visual Studio Tools for Office (the .Net interop assemblies for Office essenially) as you can pretty easily write some code behind your XL spreadsheet (or as an XL add-in) yourself that can pull data off the sheet into an ADO.Net recordset which you can then push to SQL Server (or XML or many other formats for that matter).
|||You can also use pivot tables in excel and publish cubes on the web using OWC.|||That is exactly what I would prefer. I use OWC for publishing Aanalysis Services Cube data thru Excel onto Http:// - a basic publication site. It works fine for IT and Biz community with no maintenace. - raj
