To use Sybase SQL Anywhere as the Power Pivot data source, you need to use ODBC. It is not intuitive to set it up. After encountered several errors, I am able to load the data into PowerPivot to analyze it now.
Here is the list of steps:
- Install SQL Anywhere Client. You may download it from Sybase we: http://www.sybase.com/detail?id=1087327. If you have 32 bits Office product, you need to install 32 bit client.
- Open the Sybase Central App and create a new connection to the Sybase Host and the database you want to load the data from.
- After you test the connection, click on the Save ODBC Data Source menu on the Tools button.
- When the Save as ODBC Data Source window open, type in a name for the ODBC and click on the Save button.
- You could now open the ODBC data source admin to verify it been created. Please note that if you install 32 bit SQL Anywhere on a 64 bit windows machine, then you will need to use the 32 bit ODBC to view/configure it. You could find it in C:\Windows\System32\odbcad32.exe.
- In Excel, open the PowerPivot, click on the Get External Data from Other Sources on the Home page.
- Select Others(OLEDB/ODBC) from the list to specify the Connection String
- You could type in the following connection string in the Table Import Wizard window:
- Provider=SAOLEDB.12;Data Source=DataSourceName; Persist Security Info=False
- Or Click on the Build button to open the Data Link Properties window to select the ODBC just created.
Now, you should be able to load the data from SQL Anywhere data source into PowerPivot workbook.