Search This Blog

Sunday, July 11, 2010

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.

image_thumb1

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.

image3_thumb1

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.

image6_thumb2

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

image9_thumb3

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.

image12_thumb2

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:

image15_thumb2

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.

image18_thumb2

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.

7 comments:

  1. Very interesting. I love learning something new. Thanks for the idea.

    ReplyDelete
  2. Hi,

    to use Excel 2007 as Source, you have to use

    Microsoft Office 12.0 Access Database Engine OLE DB Provider

    Unfortunately you are not able to navigate to the proper xlsx-file but you can copy and paste the path.

    Additionalle you have to use the following parameters as extended properties:

    Excel 12.0 Xml;HDR=YES;IMEX=1

    Tom

    P.S.: Yes it's xml and not xlsx even it's a xlsx source file

    ReplyDelete
  3. hi,
    this is an interesting blog for OLAP.
    I am trying to learn Microsoft SQL server BI.
    I need to create a demo project.can you give me any ideas,how i go about it.
    some real word scenarios or so.

    ReplyDelete
  4. Hi,
    Its very good article .i like to thank u for posting this for who desperately needs.
    but small scenario i like to keep it first .
    i'm having Source as a Excel i created the cube on top of this Excel . suppose if any values is updated in Excel how you will refelect this changes in Cube . Vice Versa also i need solution using "Write Back" Concepts .
    can anybody guide me if u came acroos the same scenario .

    Thanks in Advance!
    Sabarinathan

    ReplyDelete
  5. Swathi, you will want to take a look at the product samples at http://sqlserversamples.codeplex.com/. This site offers a lot of samples and information around SQL Server BI. You can also download the sample databases (Adventure Works) as relational database and as OLAP solution.

    ReplyDelete
  6. Sabari, the cube pulls its data from the datasource. Unless you're using the proactive caching feature with a SQL Server or Oracle source, you will have to trigger this update manually. Therefore you can use SQL Server Management Studio but the best idea is to create an SSIS package that is scheduled using a SQL Server agent job.
    Regarding writeback, your cube has to enable writeback for the partitions (which can be configured in BIDS). The write back data is stored in a relational database and you need to have the rights to create tables in this database.

    ReplyDelete
  7. Nice trick, thank you. For success completion this steps in Visual Studio 2010 (SSAS) you should disable 'Retrieve relationships' in Advanced Options, otherwise process failed.

    ReplyDelete