0

We have a scenario where we will be given access to a Power BI dataset (XMLA Endpoint). We will need to import data from there into our on-premise "data warehouse" database (which is a normal MS-SQL database on SQL 2016), and this has to run as a schedule. Our current tech stack is SSIS ETL Packages. I am wondering what we can use to achieve this. Please feel free to ask questions as I am not exactly what I need to aware of to start with.

So far by searching I got the following:

  • I can't access this using VS/SSDT SSIS project.
  • I can access using VS\SSAS project.
  • I can access it using SSMS (certain version 18+) but not sure how this can wrapped into something scheduled to run regularly.
  • Python, which is not installed, can be used to access XMLA Endpoint but usual IT red tape for this.
  • Can Azure Data Factory be used for it?
  • I understand this can be consumed using Power BI directly. Unfortunately we have a design restriction(?) that requires our data transformation to be done on on-premise database.
  • Feel free to suggest.

I understand this does not follow then defined definition of a stackoverflow post to have specific problem/tried-solutions pattern but would really appreciate your replies.

AhmedHuq
  • 459
  • 1
  • 4
  • 13
  • 1
    This looks like you're inviting discussion here; [so] isnt a (discussion) forum it's a Q&A site. What is your specific programming question here? – Thom A Jun 22 '23 at 17:07
  • 1
    Reading REST API's from SSIS is basically having an SSIS package which is all .Net code. So I recommend that instead you write something standalone in .Net or Powershell that is dedicated to doing this. Ironically, Power BI should be getting its data from the datawarehouse, not the other way around. I'm going to guess someone with limited data experience has created a Power BI dataset and although it adds value, it probably isn't really production ready. When all you have is a hammer everything looks like a nail – Nick.Mc Jun 23 '23 at 00:51

1 Answers1

1

https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries Sounds like that is what you're looking for. For an SSIS approach, you're probably looking at Script Tasks and .NET to make the API calls. I'd store the data as is (json) into a landing table and then make use of the json parsing in SQL Server.

The challenge with parsing in SSIS is that you'll want to use nuget to get the Newtonsoft packages and SSIS doesn't do nuget properly---it'll build but as soon as you close the editor, the assemblies go away and when you execute the package, it doesn't have the brains to go and fetch them. So to make it work, you have to install to the Global Assembly Cache, GAC.

billinkc
  • 59,250
  • 9
  • 102
  • 159