{"id":5181,"date":"2018-10-13T21:19:14","date_gmt":"2018-10-13T21:19:14","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=5181"},"modified":"2018-11-07T17:23:32","modified_gmt":"2018-11-07T17:23:32","slug":"call-rest-api-in-microstrategy-json-soap-xml","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/call-rest-api-in-microstrategy-json-soap-xml\/","title":{"rendered":"How to call REST API in MicroStrategy (JSON \/ XML SOAP)"},"content":{"rendered":"

\u00a0Introduction<\/h2>\n

\"MicroStrategy<\/a>In this new article, we will show how to call REST API in MicroStrategy.\u00a0MicroStrategy is a Business Intelligence company that provides powerful software\u00a0to create nice charts, reports to take decisions. It started in 1993 and now it\u00a0is one of the most popular Business Intelligence tools in the world because it\u00a0is simple, intuitive and friendly. Also, It can connect to almost any\u00a0database. We will use ZappySys ODBC PowerPack. Zappysys ODBC is a pretty nice\u00a0software used to connect to REST API.\u00a0 ZappySys ODBC is an excellent plugin\u00a0for MicroStrategy, because, with this plugin, you can connect to almost any\u00a0application that supports REST API.<\/p>\n

Requirements<\/h2>\n
    \n
  1. First of all, we will need\u00a0MicroStrategy Desktop<\/a> installed.<\/li>\n
  2. Secondly, you will require\u00a0ZappySys PowerPack for\u00a0ODBC<\/a> installed.<\/li>\n<\/ol>\n

    Getting started<\/h2>\n

    First of all, we will need to configure the ODBC ZappySys to connect to\u00a0REST API. Once the ODBC connection is created, we will invoke the ODBC\u00a0connection in Microstrategy.<\/p>\n

    Configuring the ODBC Zappysys<\/h3>\n
      \n
    1. Search “odbc” in start menu and select ODBC Data sources (64-bit)<\/strong><\/li>\n
    2. First of all, we will open the ODBC Data Source Administrator in the\u00a0System DSN<\/strong> and press Add:\n
    3. Secondly, add a new ZappySys JSON Driver:\n
    4. Also, we will add the following URL to the Simple View:\n
      https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Invoices?$format=json<\/pre>\n
      \"Configuration<\/a>
      ODBC configuration to connect to REST API<\/p><\/div><\/li>\n<\/ol>\n
      Configuring MicroStrategy<\/h3>\n
        \n
      1. In MicroStrategy Desktop, press the New Dossier<\/strong> button:\n
      2. Secondly, in the Dossier, press the New Data<\/strong> button:\n
      3. Also, in Data Sources select Databases<\/strong>:\n
        <\/div>\n
      4. In addition, select the option to Type a Query<\/strong>:\n
      5. Next, in Data Sources press the +<\/strong> icon:\n
      6. Finally, in Data Source, select DSN Data Sources and select the data\u00a0source created in the previous section using the ZappySys ODBC Driver. In\u00a0version, select Generic DBMS<\/strong> and write any username and password:\n
        \"Select<\/a>
        Select DSN, version<\/p><\/div><\/li>\n<\/ol>\n
        Call REST API in MicroStrategy<\/h3>\n
          \n
        1. You can write different queries using the ZappySys ODBC Driver. The driver supports SQL queries to REST API data including WHERE clauses, GROUP BY, HAVING, ORDER BY, LIMIT WITH:\n
          SELECT ShipName FROM value  \r\nWHERE ShipName=\"Around the Horn\"<\/pre>\n
          \"Call<\/a>
          Query to REST API using MicroStrategy<\/p><\/div><\/li>\n
        2. Secondly, you can connect online (Live) or use an in-memory Dataset:\n
        3. Finally, you can use the data for your MicroStrategy reports:\n
          \"REST<\/a>
          MicroStrategy report created<\/p><\/div><\/li>\n<\/ol>\n
          Invoking a JSON file in MicroStrategy using ODBC<\/h3>\n
          With ZappySys PowerPack, you can not only CALL REST API in Microstrategy, but you can also create queries to plain JSON files stored locally or in a web\u00a0address. We will use the following JSON file to test:<\/p>\n
            \n
          1. First of all, in order to connect, you will need to specify on the ZappySys JSON\u00a0Driver the path of the local JSON file:\n
          2. Secondly, you can create queries in MicroStrategy in the same way that\u00a0we did in the Call REST API in MicroStrategy section:\n
            \"Connection<\/a>
            Query to JSON file in MicroStrategy<\/p><\/div><\/li>\n<\/ol>\n
            Call REST API in MicroStrategy to get Gmail information<\/h3>\n
            It is also possible to invoke and query Gmail data using REST API in\u00a0MicroStrategy using ZappySys ODBC PowerPack. With the ODBC PowerPack, you can\u00a0write SQL queries and call REST API in MicroStrategy using OAuth or HTTP
            \nconnections.<\/p>\n
              \n
            1. First of all, in the ZappySys ODBC JSON driver, write the following URL:\n
              https:\/\/www.googleapis.com\/gmail\/v1\/users\/me\/messages\/<\/pre>\n<\/li>\n
            2. Secondly, in connection type select OAuth Provider<\/strong>.<\/li>\n
            3. Also, in the OAuth Parameter in OAuth Provider, select Google and in scope\u00a0https:\/\/mail.google.com<\/a> and press\u00a0generate token button and specify your credentials:\n
            4. Additionally, in Zappysys you can write queries in MicroStrategy as we did with other REST API URLs:\n
              SELECT * FROM messages<\/pre>\n
              \"Get<\/a>
              Query to Gmail information in MicroStrategy<\/p><\/div><\/li>\n<\/ol>\n
              Problems with the boolean data type in MicroStrategy<\/h3>\n
              In addition, there is known issue related to boolean data in Microstrategy:<\/p>\n
              Error in accessing information from the external data source. Database Instance: (DataImport) Error Fetch Failed Error type: Invalid operation. Data of a string cell was truncated.<\/p>\n
              https:\/\/community.microstrategy.com\/s\/article\/KB20514-How-to-map-a-boolean-datatype-as-integer-with-PostgreSQL<\/a><\/p>\n
              We have a workaround for this situation.<\/p>\n
                \n
              1. First of all, you will need to change the metadata. The following article shows how to change the metadata:https:\/\/zappysys.com\/blog\/metadata-odbc-api-drivers-query\/<\/a>Also, in order to fix, save your\u00a0current metadata:\n
              2. Additionally, modify your data from Boolean to String:\n
                \"change<\/a>
                MicroStrategy boolean to string metadata<\/p><\/div><\/li>\n<\/ol>\n
                 <\/p>\n
                REST API \/ SOAP API Pagination Settings for MicroStrategy<\/h2>\n
                Even we set up ODBC Data Source to get the data, it may not be enough. Usually, if you are getting a huge data set from API provider, it won't give it to you in one HTTP response. Instead, it gives back only a subset of data and provides a mechanism for data pagination. The good news is that\u00a0ZappySys ODBC Driver<\/em> includes many options to cover virtually any pagination method.<\/div>\r\n
                Below you will find a few examples of API pagination. If you need something more sophisticated check the below link (the article was written for SSIS PowerPack but UI options and concepts apply to ODBC Driver too):<\/span><\/div>\r\n
                https:\/\/zappysys.com\/blog\/ssis-rest-api-looping-until-no-more-pages-found\/<\/a><\/div>\r\n
                Paginate by Response Attribute<\/h3>\r\nThis example shows how to paginate API calls where you need to paginate until the last page detected. In this example, next page is indicated by some attribute called nextlink (found in response). If this attribute is missing or null then it stops fetching the next page.\r\n
                SELECT * FROM $\r\nWITH(\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/pagination_nextlink_inarray_1.json'\r\n,NextUrlAttributeOrExpr = '$.nextlink'  --keep reading until this attribute is missing. If attribute name contains dot then use brackets like this $.['my.attr.name']\r\n)<\/pre>\r\n
                Paginate by URL Parameter (Loop until certain StatusCode)<\/h3>\r\nThis example shows how to paginate API calls where you need to pass page number via URL. The driver keeps incrementing page number and calls next URL until the last page detected (401 error). There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).\r\n
                SELECT * FROM $\r\nWITH (\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/page-xml.aspx?page=1&mode=DetectBasedOnResponseStatusCode'\r\n,PagingMode='ByUrlParameter'\r\n,PagingByUrlAttributeName='page'\r\n,PagingByUrlEndStrategy='DetectBasedOnResponseStatusCode'\r\n,PagingByUrlCheckResponseStatusCode=401\r\n,IncrementBy=1\r\n)<\/pre>\r\n
                Paginate by URL Path (Loop until no record)<\/h3>\r\nThis example shows how to paginate API calls where you need to pass page number via URL Path. The driver keeps incrementing page number and calls next URL until the last page is detected. There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).\r\n
                SELECT * FROM $\r\nWITH (\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/cust-<%page%>.xml'\r\n,PagingMode='ByUrlPath'\r\n,PagingByUrlAttributeName='<%page%>'\r\n,PagingByUrlEndStrategy='DetectBasedOnRecordCount'\r\n,IncrementBy=1\r\n)<\/pre>\r\n
                Paginate by Header Link (RFC 5988)<\/h3>\r\nAPI like GitHub \/ Wordpress use Next link in Headers (RFC 5988<\/a>)\r\n
                SELECT * FROM $\r\nLIMIT 25\r\nWITH(\r\n\t Src='https:\/\/wordpress.org\/news\/wp-json\/wp\/v2\/categories?per_page=10'\r\n\t,PagingMode='ByResponseHeaderRfc5988'\r\n\t,WaitTimeMs='200' --\/\/wait 200 ms after each request\r\n)<\/pre>\r\n <\/div>\n
                REST API \/ SOAP Web Service Connection Settings for MicroStrategy<\/h2>\n
                If you need to authenticate or authorize your user to access a web resource, you will need to use one of\u00a0the Connections:<\/em><\/div>\r\n