none
Change OLAP Cube Datasource Quickly and Easily RRS feed

  • Question

  • The goal is to have a single temple Excel 2013 file, which includes a few highly-customized pivot tables made to look very user-friendly, fed by a single OLAP cube.  The OLAP cube will always come from a Microsoft Project visual report export, so it should always be in the same format.

    Now, I can make the MSP export pretty easy with a simple macro.  The difficulty I'm having is reassigning the OLAP cube on the Excel side.

    I've tried going into the pivot table tools and changing the data source to the new OLAP cube.  The trouble is that this simply throws a bevy of errors and fails. The errors really aren't important, the selection of a new OLAP cube requests a series of network security options and such that my users simply aren't going to want to see.

    My real goal would simply be to have a macro where they could click a button, select a new OLAP cube through a file explorer, and it would be instantly done.  The pivot tables would be refreshed with the new data automatically.

    Is my goal viable, or is selecting a new, updated OLAP cube always going to be a process?

    Thanks!


    Friday, October 24, 2014 7:49 PM

All replies

  • Hi Dustin,

    In excel, we could use macro recorder to generate the VBA code for many requirements.

    To add a new connection, we could use this code:

    Workbooks("MyExcel.xlsm").Connections.AddFromFile _
            "C:\Users\XXX\Documents\My Data Sources\XXX.odc"

    To modify and refresh the connection and data, you could refer to this code:

    With ActiveWorkbook.Connections("connection name"). _
            OLEDBConnection
            .CommandText = Array("Test")
            .CommandType = xlCmdCube
            .Connection = _
            "OLEDB;Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Data Source=XXX;Initial Catalog=XXX"
            .RefreshOnFileOpen = False
            .SavePassword = False
            .SourceConnectionFile = ""
            .MaxDrillthroughRecords = 1000
            .ServerCredentialsMethod = xlCredentialsMethodIntegrated
            .AlwaysUseConnectionFile = False
            .RetrieveInOfficeUILang = True
        End With
        With ActiveWorkbook.Connections("connection name")
            .Name = "XXX"
            .Description = ""
        End With
        ActiveWorkbook.Connections("XXX").Refresh

    So, you could have a command button, then in the click event add the connection and refresh the data.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, October 27, 2014 7:27 AM
    Moderator
  • The trouble is, when I try to change the PivotTable's datasource a generic error is thrown - every time:

    "Internal Error: The operation terminated unsuccessfully."

    This occurs no matter how I try to change, update, or refresh the PivotTable's datasource.  Refreshing the data or selecting a new cube datasource simply does not work.  This seems like something that should be very simple!

    Thank you so much for your help.

    Thursday, October 30, 2014 6:53 PM
  • Hi Dustin,

    Did you use macro recorder to generate the code or you just use my code?

    You need to record macro.

    Best Regards

    Starain


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, October 31, 2014 8:40 AM
    Moderator