Hilmar Buchta

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.

image

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.

image

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.

image

Now, if you click on ‚Test connection‘ you will get an error message like the one below:

image

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.

image

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:

image

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.

image

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.