none
Swap datasource Access DB for SSAS

    Question

  • I have a powerpivot model that I have been using for about a year now.  One of my dimension tables is sourced from an access database.  That lookup data is now available in an analysis services cube. 

    Are there any tricks that I could use to repoint the table from the Access DB to the Cube?

    Side note: I have been able to repoint from one access db to another with no problem. but this was because it is the same connection type.

    Thanks in advance 

    Z

    Friday, July 12, 2013 1:10 PM

Answers

  • Unfortunately, as of now, there is no easy way to switch from one connection type to another.  As you have pointed out with your Access example, it is easy to switch from different data sources that are of the same type.

    You will have to create a new connection to SSAS, import the data to a new PowerPivot table making sure it has the same columns as your original Access data.  It must then be given the exact same column names as your original data.  You also must set up the exact same relationships that your original data had.  If measures were in your original table, they must be moved to the new table.  The last step is to delete the original table and then rename the new table with the exact same name that the original table had.

    Once you have completely duplicated the original table in every way and removed the old table, go back out to the regular Excel ribbon and do a data refresh from there.

    Not as easy as simply changing the connection but it should allow you to make the switch with minimal impact on the pivots and charts you have already created.

    Good luck!


    Friday, July 12, 2013 1:35 PM

All replies

  • Unfortunately, as of now, there is no easy way to switch from one connection type to another.  As you have pointed out with your Access example, it is easy to switch from different data sources that are of the same type.

    You will have to create a new connection to SSAS, import the data to a new PowerPivot table making sure it has the same columns as your original Access data.  It must then be given the exact same column names as your original data.  You also must set up the exact same relationships that your original data had.  If measures were in your original table, they must be moved to the new table.  The last step is to delete the original table and then rename the new table with the exact same name that the original table had.

    Once you have completely duplicated the original table in every way and removed the old table, go back out to the regular Excel ribbon and do a data refresh from there.

    Not as easy as simply changing the connection but it should allow you to make the switch with minimal impact on the pivots and charts you have already created.

    Good luck!


    Friday, July 12, 2013 1:35 PM
  • OK that's kind of what I thought.   It is good to know that I can repoint stuff to a new duplicated table... I'll have to give that a try.

    Thanks for the help.

    Cheers  :-)

    Z

    Friday, July 12, 2013 1:54 PM