architecture for json webservice to SQL Server DWH? RRS feed

  • Question

  • I have many years of experience in the 'traditional' data warehouse architecture and development. 95% MS SQL Server and currently Power BI

    For my current project I need to build a data warehouse for reporting from a 'data abstraction layer', a central bus system holding the data for ERP, WMS, CRM, PIM, etc. The bus also communicates with all external partners & systems. Specifically, the data layer consists of multiple JSON web services. Little detail, the responses are always paginated.

    The developer of the data abstraction layer confirmed my he can generate delta feeds, if necessary.

    The preferred platform for our (new) DWH is MS SQL Server 2019 as we would like to report in Power BI... Excel is still very often used in the organization, I'd like to use the XMLA endpoint (Hooray)

    What (ETL) architecture would you advise for above setup? I haven't used it before but I see that SQL Server 2017 and 2019 have a lot of convenient JSON functions build in. Is it any good for this situation? Is SSIS 2019 interesting or would you rather advise new technologies such as Azure Databricks?

    Many thanks in advance for your advices

    Thursday, October 10, 2019 9:31 AM

All replies