locked
Automate query extract RRS feed

  • Question

  • Hi,

    I need to generate an extract based on a query sourcing data from a tabular SSAS database and storing the extract at a specific location.

    This needs to be scheduled to run every day automatically.

    Can this be setup on sharepoint or any other way ?

    Friday, May 22, 2015 5:38 AM

Answers

  • Hi,

    To connect tabular SSAS database from SharePoint, you need to enable PowerPivot gallery in SharePoint, and from where,  you can create a BI semantic model connection file in SharePoint that provide redirection to a database running on an Analysis Services tabular mode server. Please see Create a BI Semantic Model Connection to a Tabular Model Database(https://msdn.microsoft.com/en-us/hh230972).

    However, you may not be able to extract data from SharePoint. As a workaround, I suggest you use PowerShell to invoke the query language (MDX, DMS, or XMLA) to extract the data, and then save the data to local excel or SharePoint library.

    Analysis Services PowerShell
    https://msdn.microsoft.com/en-us/library/hh213141.aspx

    Thanks,
    Reken Liu


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, May 25, 2015 6:51 AM

All replies

  • Hi,

    To connect tabular SSAS database from SharePoint, you need to enable PowerPivot gallery in SharePoint, and from where,  you can create a BI semantic model connection file in SharePoint that provide redirection to a database running on an Analysis Services tabular mode server. Please see Create a BI Semantic Model Connection to a Tabular Model Database(https://msdn.microsoft.com/en-us/hh230972).

    However, you may not be able to extract data from SharePoint. As a workaround, I suggest you use PowerShell to invoke the query language (MDX, DMS, or XMLA) to extract the data, and then save the data to local excel or SharePoint library.

    Analysis Services PowerShell
    https://msdn.microsoft.com/en-us/library/hh213141.aspx

    Thanks,
    Reken Liu


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, May 25, 2015 6:51 AM
  • Hi Reken,

    We are creating the data extract directly from the relational datawarehouse using SQL.

    I have 2 queries :

    1) Can we save this extract to SharePoint library using SSIS and users can access these through SharePoint ?

    We need to create 29 extracts daily one for each location.

    2) Can we create 29 SSRS Reports for this purpose and configure subscriptions so that the extract is saved daily on the SharePoint Library ?

    Thursday, June 11, 2015 6:24 AM
  • Hi,

    I think the option 2 is possible with the latest SQL Server 2016. Please check the following article:

    https://msdn.microsoft.com/en-us/library/bb283186.aspx

    However, the current workaround is still to use PowerShell script, and you schedule the script to run daily in a Windows task. Regarding option 1 and more information within SQL Server, I would recommend you to ask in our SQL Server support forum.

    Thanks,
    Reken Liu


    TechNet Community Support
    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.

    Monday, July 6, 2015 8:28 AM