How to use an Excel 2003 file as a datasource for an SSAS cube
SQL Server 2005 | SQL Server 2008 | SQL Server 2008R2
Ok, usually you wouldn't want to use an Excel file directly as a datasource for an SSAS cube in real life scenarios. But just in case you'd like to set up a quick demo without bothering to create a new database using Microsoft SQL Server or maybe Microsoft Access, the ability of sourcing your cube from an Excel file could be more than welcome. Just imagine you want to try some design ideas. While working with SQL server databases you would end in a large amount of test databases or you would need to backup/restore your databases all the time to test different scenarios. With Excel as a source for your cubes you could put your test data right into your SSAS solution. In order to modify the datasource you can simple make a copy of your Excel file (for backing up the older version) or of your solution instead of caring about databases. And even if you don't have the databases installed you can use any of your testing solutions by just opening the solution as the data becomes part of the solution.
Sounds good, doesn't it? But how can you do so? First, when trying to set Excel as an OLE-DB source you will notice that it just isn't there.
But what we can do, is to use the Microsoft Jet 4.0 OLE DB Provider. So that's where we start. The next dialog asks us to provide the database file name.
If you click on the 'Browse...' button you will notice that the selection is limited to .mdb-files or .accdb-files as the Jet OLE-DB provider as usually used with Microsoft Access databases. So we just change our file type selection to 'All files' and pick our Excel file.
Now, if you click on 'Test connection' you will get an error message like the one below:
Our Jet OLE-DB provider still believes, that we are connecting to a Microsoft Access database file and therefore it cannot connect. So here comes the really important step. We open our connection again, click on the 'Edit' button to edit the connection string and then we switch to the 'All' tab of the connection properties.
As shown in the screenshot we have to set the extended properties to 'Excel 8.0;HDR=Yes;IMEX=1;'.
Excel 8.0 stands for Excel 2003 (I couldn't get Excel 2007 to connect properly using 'Excel 9.0', so I stayed with the Excel 2003 format here). 'HDR=Yes' means that our Excel tables contain headers.
After that, a click on 'Test Connections' gives the desired result:
Of course, we now need to build up our Excel-file. Each "source table" sits on its own sheet. You can easily build up some time dimension or use Excel functions like RAND() to create random fact data or VLOOKUP(...) to link your tables with testing data to each other.
Although this is not at all useful for real life situations (as we would extract the data from the Excel sheet using ETL tools or simply not storing the source data in Excel at all), this might still be useful in order to set up a quick and dirty example solution and play around by modifying the source data (add columns, use different formats etc.) without the need to work on a 'real' database.