How to run SSRS or Power BI Reports on SQL Server with NO SQL Databases Data

Goal: Loading Couchbase data to the SQL Server Data Warehouse database as per scheduled frequency. 

 

Process and Key Components:

1) PowerShell: PowerShell invokes the RESTAPI GET method and queries the Couchbase through API and writes each result JSON record into the landing zone table as a valid JSON record.

Example: {

    "address_1":  "123 hotel way",

    "end_time":  "05:00:00",

    "latitude":  36.127028,

    "location_name":  "Embassy Suites",

    "longitude":  -79.8856824,

    "meeting_date":  "2018-05-30",

    "meeting_id":  "21645",

    "phone":  "8594623291",

    "postal_code":  "27519",

    "start_time":  "09:00:00"

}

2)     SQL JSON Function (SQL 2016 upper): Query the raw json records from the landing zone table and load into the stage table with transformations.

Example: Time zone datatype to datetime, etc.

3)     UPSERT: To obtain incremental changes instead of daily full load, use the MERGE function for INSERT AND UPDATE.

4) Schedule the code with SQL Agent.