Friday, October 5, 2012

Use Sybase SQL Anywhere as data source for PowerPivot

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.

clip_image001

  • When the Save as ODBC Data Source window open, type in a name for the ODBC and click on the Save button.

clip_image001[5]

  • 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.

clip_image001[7]

  • 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

image

  • You could type in the following connection string in the Table Import Wizard window:
      Provider=SAOLEDB.12;Data Source=DataSourceName; Persist Security Info=False
    clip_image001 

  • Or Click on the Build button to open the Data Link Properties window to select the ODBC just created.

imageclip_image001[1]

Now, you should be able to load the data from SQL Anywhere data source into PowerPivot workbook.

Wednesday, October 3, 2012

SQL Saturday #174

This SQL Saturday training event is focused on Microsoft Business Intelligence topics.

I am honored to have been selected to present “Explore the Integration Services Catalog” out SQL Server on October 27, 2012 in Charlotte North Carolina.

I am very excited as this will be my first time presenting in SQL Saturday.

If you in the area, there is no better opportunity to get free Microsoft BI training.  I am looking forward to meeting you there.

image