Planning Analytics

Planning Analytics

Get AI-infused integrated business planning

 View Only
Back to discussions
Expand all | Collapse all

SAAS - Import and Export Data from SQL-Server

  • 1.  SAAS - Import and Export Data from SQL-Server

    Posted Fri April 25, 2025 11:57 AM

    Hello to all!

    We want to exchange data from planning analytics to SQL-Server (import from and export to).

    Any best practice examples available or resources for learning how to do it in a fast way?

    How is this working when using planning analytics SAAS in the cloud (planning analytics and sql-server eg. hosted in azure cloud).

    Thanks for hints,

    Andreas



    ------------------------------
    Andreas Schuster
    ------------------------------


  • 2.  RE: SAAS - Import and Export Data from SQL-Server

    Posted Mon April 28, 2025 07:05 AM

    Hi Andreas,

    A bit more detail might help.  Can you confirm if you are using Planning Analytics on Cloud (IBM Cloud) or Planning Analytics SaaS (AWS or Azure)?  If you are using Planning Analytics on Cloud then you would likely want to create a TI process that uses an ODBC data source.  This requires an ODBC data source to be configured in the Secure Gateway feature in the administration area of Planning Analytics Workspace.  Some cloud based data sources may require you to open a support ticket to have the data source configured.  Our TI processes can also write to SQL databases using the ODBCOpen, ODBCOutput, and ODBCCloud functions.  If you are using Planning Analytics SaaS you might want to determine if you can leverage the new ExecuteHTTPRequest TI function to issue SQL statements (both select and insert) to the REST API for the SQL data source.  ODBC is still possible in Planning Analytics SaaS, but required our ODBC Data Connector.



    ------------------------------
    Stuart King
    Product Manager
    IBM Planning Analytics
    ------------------------------

    Original Message


  • 3.  RE: SAAS - Import and Export Data from SQL-Server

    Posted Tue April 29, 2025 04:05 AM

    Hello Stuart!

    That's already a great help. We are planning the future architecture of our solution and PA SAAS could be part of it. Still investigation and what our options are.

    Thanks a lot.

    Andreas



    ------------------------------
    Andreas Schuster
    ------------------------------

    Original Message


  • 4.  RE: SAAS - Import and Export Data from SQL-Server

    Posted Thu July 10, 2025 12:36 PM

    Hi @Andreas Schuster,

    Curious if you have decided on an approach on exporting data from Planning Analytics to SQL Server?  If yes, how is it working for you?

    Thanks,



    ------------------------------
    Quin Kan
    ------------------------------

    Original Message


  • 5.  RE: SAAS - Import and Export Data from SQL-Server

    Posted Tue July 15, 2025 05:44 AM

    Hello everyone!

    Unfortunately I am not yet as far as I would like to be with this topic.

    We are still on a local onpremis installation of Planning Analytics 2.0.90 and want to connect to a MS-Fabric SQL node. I have installed the SQL driver ODBC 18.0 and wanted to connect to Fabric. The connection to MS-Fabric works (and also the management access to Fabric on the server) but the ODBC connection in PA Architect gives me an error back. The SQL statement cannot be executed.

    Can someone please help me? Should this work? Does the ODBC access work via the locally installed SQL server driver 18. with the new version 2.1.12?

    Thank you very much for your support.

    Andreas

    Translated with DeepL.com (free version)



    ------------------------------
    Andreas Schuster
    ------------------------------

    Original Message


  • 6.  RE: SAAS - Import and Export Data from SQL-Server

    Posted Wed July 16, 2025 01:43 AM
    Any chance it's related to something like this

    The need to put the below statement before your SQL statement

    SET NOCOUNT ON


    Robby

    Details about the personal data CBRE collects and why, as well as your data privacy rights under applicable law, are available at CBRE – Privacy Policy.




    Original Message


  • 7.  RE: SAAS - Import and Export Data from SQL-Server

    Posted Wed July 16, 2025 03:41 AM

    @Robby Meyers, you are my hero. Prefixed with "SET NOCOUNT ON" I can now retrieve the data from MS-Fabric in the TI process via the ODBC driver 18.5.1 (setting EntraID User + PW - no MFA).

    You must have saved me hours of work. If you're ever in Vienna, get in touch, you'll get a free lunch ;-)

    Many thanks for your effort to write back and give me the hint
    Andreas



    ------------------------------
    Andreas Schuster
    ------------------------------

    Original Message


  • 8.  RE: SAAS - Import and Export Data from SQL-Server

    Posted Wed May 07, 2025 03:35 PM
    Edited by Jesper Poulsen Wed May 07, 2025 03:35 PM

    Hi Stuart

    I suppose you mean Satellite Connector and not Secure Gateway



    ------------------------------
    Kind regards,
    Jesper Poulsen
    ------------------------------

    Original Message


  • 9.  RE: SAAS - Import and Export Data from SQL-Server

    Posted Thu May 08, 2025 09:22 AM

    Hi Jesper,

    You are correct, good catch.



    ------------------------------
    Stuart King
    Product Manager
    IBM Planning Analytics
    ------------------------------

    Original Message


  • 10.  RE: SAAS - Import and Export Data from SQL-Server

    Posted Thu July 10, 2025 12:32 PM

    Hi @STUART KING or fellow community members,

    We are also looking into transferring the data from TM1 to SQL Server, and I came across Andreas' post. 

    Wonder in terms of speed, would ODBC have decent speed?  I do not have the amount of data we are transferring for you to assess

    In comparing between ODBC and ExecuteHTTPRequest, is one faster than the other?

    The team who manages the TM1 server is suggesting to export the data into files, and we can use the files for loading into the SQL database.  I read somewhere that using ODBC is slower than using flat files.  Wonder if it is always the case.

    Thanks






    ------------------------------
    Quin Kan
    ------------------------------

    Original Message


  • 11.  RE: SAAS - Import and Export Data from SQL-Server

    Posted Thu July 17, 2025 10:06 AM

    You can speed up ODBC by using an INSERT INTO SQL statement that inserts multiple records in one command. Something like:

    INSERT INTO table_name (column1, column2, column3)
VALUES
    (value1_row1, value2_row1, value3_row1),
    (value1_row2, value2_row2, value3_row2),
    (value1_row3, value2_row3, value3_row3);

    For text files, SQL Server can do the heavy lifting by using bulk upload commands.

    Not sure about the difference in performance of ODBC vs ExecuteHTPRequest, I'll let the experts answer that one.

    Ivan



    ------------------------------
    Ivan C
    ------------------------------

    Original Message


  • 12.  RE: SAAS - Import and Export Data from SQL-Server

    Posted Thu July 17, 2025 03:24 PM

    Thanks @Ivan C.  We changed to using SQL bulk upload.  It saves us lot of time and there is no concern of missing data.

    Best regards,



    ------------------------------
    Quin Kan
    ------------------------------

    Original Message


Related Content