0

How to call or execute(take as data-source) an SQL stored procedure in pyramid analytics

hi all,

I was trying to call an SQL stored procedure from custom query into pyramid analytics in below way.

exec dbo.[Get_SalesdetailsPyramid]

The statement is valid, but I have error while dragging the fileds in Discovery.  Error is as below:

Failed run SQL query in model source. model: 3850ba93-dfc6-4926-b394-8f995f8cbe2b|OPTM|06b35dad-20aa-428d-8baa-ba995dcdb61d|, query: WITH [_sp1_] as (exec dbo.[Get_SalesdetailsPyramid])

SELECT TOP 20000001 [_sp1_].[Country] [_sp1___Country_]
FROM [_sp1_] [_sp1_]
GROUP BY [_sp1_].[Country]
ORDER BY [_sp1_].[Country] ASC
Pyramid Version:2020.00.055
message:Incorrect syntax near the keyword 'exec'.

Can any one please help whether i can call stored procedure in pyramid analytics, if yes how can i call it??

 

thanx in advance..

4 replies

null
    • "making the sophisticated simple"
    • AviPerez
    • 5 yrs ago
    • Reported - view

    You cannot currently call a stored procedure as a live data-set from Pyramid. Instead you can use the Model tools to ingest results from the stored procedure into another data model. Alternatively, use views as the source - then it can be queried live.

    • srilakshmi
    • 5 yrs ago
    • Reported - view

    Hi Avi Perez,

        Thanx for the reply. Can you please explain this: "Instead you can use the Model tools to ingest results from the stored procedure into another data model."

      • Daniel_Chitlaru
      • 5 yrs ago
      • Reported - view

      srilakshmi 

      Create a ETL process that runs the stored procedure and saves the results in another table (maybe another database)

      1. Create a new Data Flow ("Model Pro")
      2. Add your SQL Server data source
      3. Add a SELECT QUERY node
      4. Type the exec stored procedure script
      5. Add a data destination node (can be the same database or another database)
      6. Add any other tables you might need
      7. Connect All
      8. Create data model as before.
      9. When this is executed, the results from the stored procedure will be saved in another table that can be queried by discoveries. 
    • Shai_Ben_Ami
    • 5 yrs ago
    • Reported - view

    Hi Srilakshmi,

     

    The data discovery doesn't know how to work with stored procedures (or exec commands).

    Avi suggest a different solution - use the ETL to execute the store procedure, and store the results on a different database on a regular table, and then use the data discovery.

    in the attachment you can find a small example of that process.

     

    Let me know if you need any additional help,

    Shai.

Login to reply

Content aside

  • Status Answered
  • 5 yrs agoLast active
  • 4Replies
  • 177Views
  • 4 Following