Dynamics Power BI User Group

Expand all | Collapse all

SQL Server stored procedures as data source

  • 1.  SQL Server stored procedures as data source

    Posted 07-05-2018 04:21 PM
    I've been wrestling unsuccessfully with SQL Server stored procedures, trying to convert them into Views so that Power BI can make immediate use of them in data sources. Since I can use stored procedures as data sources for Excel tables, I've stayed on that side of the aisle for the nonce.

    In Excel I set up parameter cells that are referenced by the stored procedure call. E.g.,

    {call dbo.StoredProcedureName(?,?,?)}

    It appears that a data import in Power BI allows the use of stored procedures; but I'm not clear on how to feed user parameters in.

    Any thoughts?

    Sincerely,

    ------------------------------
    Steve Erbach
    QMS Manager and Business Development Analyst
    GLC Minerals, LLC
    Green Bay WI
    +1 920.965.4201
    ------------------------------


  • 2.  RE: SQL Server stored procedures as data source

    Posted 07-05-2018 05:22 PM
    Might this article provide the answer?

    Sincerely,

    ------------------------------
    Steve Erbach
    QMS Manager and Business Development Analyst
    GLC Minerals, LLC
    Green Bay WI
    +1 920.965.4201
    ------------------------------



  • 3.  RE: SQL Server stored procedures as data source

    Posted 07-06-2018 03:41 PM
    I'll provide my own answer: yes, it does. It doesn't explain that "SQLSource" is a system variable of some sort. That is, you can't simply name it whatever you want; it has to be called SQLSource.

    Regards,

    ------------------------------
    Steve Erbach
    QMS Manager and Business Development Analyst
    GLC Minerals, LLC
    Green Bay WI
    +1 920.965.4201
    ------------------------------



  • 4.  RE: SQL Server stored procedures as data source

    Posted 07-09-2018 01:29 AM
    ​Steve,

    I read the article you referenced and it said you could use a stored procedure for an import as opposed to a Direct Query. I didn't test either one but what was your experience?

    ------------------------------
    Charles Allen
    Senior Managing Consultant
    BKD, LLP
    Houston TX
    713-499-4629
    ------------------------------



  • 5.  RE: SQL Server stored procedures as data source

    Posted 07-09-2018 08:44 AM
    @Charles Allen,

    Like, when do you sleep?

    ​The article pointed the way for me. As I said in my last reply, I took a side road and decided to name the "array" of parameters with my own variable name. No dice. That "variable", SQLSource, has to have that name in order for the syntax checker to have no complaints.

    But the upside is that, as usual with the way I learn things, doing something wrong ten times in a row leads me to understand the correct process much better.

    My main issue now is that I want to understand how to make the form that contains the stored procedure parameters more visible to the user. Easy in Excel; not as clear in Power BI.

    Regards,

    ------------------------------
    Steve Erbach
    QMS Manager and Business Development Analyst
    GLC Minerals, LLC
    Green Bay WI
    +1 920.965.4201
    ------------------------------