Cube Function connection refresh to SQL Data Model Source

[RonBarrett]

Cube Function connection refresh to SQL Data Model Source

Creating a PowerPivot table connected to a SQL data source is relatively simple and straight forward. CUBE functions are the next step in creating data presentations. However, there's an issue I discovered that don't know how to address.

Situation:
Okay, you open up a new Excel workbook, go to PowerPivot, get data from an SQL source. You go back to the Excel worksheet, and write a CUBE formula. For example - CubeMember("PowerPivot Data","[Dim_Practice].[PracticeID].[1000]". Boom, the value 1000 is returned to the worksheet cell. Now, here's the problem.

Go back to the PowerPivot window, get data from a new SQL source. Perhaps the data is another table in a data warehouse. Go back to the Excel worksheet. In another cell, write a CUBE formula addressing the new table. Oops... Excel isn't aware of the table you just added. While constructing the formula Excel's prompts doesn't list the new table. After completing the formula Excel simply returns #N/A.

If this were a PowerPivot table, you'd simply do a data refresh. However, this is a CUBE formula. How do you refresh the connection to an SQL Data Model Source?

 

[MD610]

Are you using Excel 2010?

Have you done a "double refresh"? Once from within the PowerPivot window itself, and then a second time from the data tab in regular Excel?

 

[RonBarrett]

Are you using Excel 2010?

Have you done a "double refresh"? Once from within the PowerPivot window itself, and then a second time from the data tab in regular Excel?

Yes, I have Excel 2010. More importantly, THANK YOU, the 'double refresh' did the trick. Thanks!

 

[MD610]

Anytime! It's a bit of a nuisance, but PP 2010 Workbooks needs the double refresh. Excel basically treats PP data as an external data source, so 1 refresh to update PP and a 2nd to update Excel.