calling a stored procedure from a dimension named query

Question

Hello,
 I am trying to create a cube(SSAS) dimension by calling a stored proc. If I put "exec stored proc" in the "edit named query" dialog box then I get an error below.
I know that I can not call a stored procedure from a view. Is there a way to call a stored procedure to create the cube dimension?
Thanks for the answers



TITLE: Microsoft Visual Studio
------------------------------

Cannot create the named query using the specified query definition.

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=CannotCreateNamedQuery&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Deferred prepare could not be completed.
Statement(s) could not be prepared.
Incorrect syntax near the keyword 'EXEC'.
The following statement executed when creating named query:

SELECT [Dimension Prd - Product].*
FROM
(
EXEC [ProductDimension]
)
 AS [Dimension Prd - Product]  (Microsoft.AnalysisServices.Controls)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%u00ae+Visual+Studio%u00ae+2005&ProdVer=8.0.50727.762&EvtSrc=Microsoft.AnalysisServices.Controls.ControlsSR&EvtID=ShowSQL&LinkId=20476

------------------------------

Deferred prepare could not be completed.
Statement(s) could not be prepared.
Incorrect syntax near the keyword 'EXEC'. (Microsoft SQL Native Client)

------------------------------
BUTTONS:

OK
------------------------------

Answer 1

I always create a ssis package to refresh my cube. In this way, you can create a physical dimension table and refresh this table through a component of ssis package such as Data Flow Task. And then rebuild your cube or update this dimension only.

---

:)

Answer 2

You can't call stored procs from the DSV. You are pretty much restricted to a similar sub-set of SQL that you can use inside a view.

If you can put your logic inside a function that returns a table you could call that from the DSV. If you still need your stored proc for use elsewhere then you could replace the body of your stored proc with a call to select from the function to keep your logic centralized.

Or you could just stage the data as suggested by Mr.QuestionMark 

---

http://geekswithblogs.net/darrengosbell - please mark correct answers