Desktop - Get Data from multiple JSON sources

[Steve0212]

I am new to Power BI.  I have a scenario where I have multiple servers in our environment, but for simplification, let's say server1 and server2.  Each server is running WCF and publishing data via REST.  On both servers, the urls http://<servername>/GetReadings and http://<servername>/GetObjects both return a propertly formatted JSON result that can be cross referenced (each object in the second query has a reading in the first query).  While I am simplifying here with just two servers and two REST operations, there is the potential to be a lot more servers and 20+ REST operations per server.

Now, how can I generate a report to aggregate the data from all the servers, or better yet, let the user pick the servers they wish to use for aggregation.  If we were doing the simple route with just 2 servers and 2 operations, I could just set up 4 web data sources 2 for each server x 2 for each operation.  That works with the simplification.  However, as this grows, it would become very unruly.

I really have two questions:

1. How can I create a single dataset for each REST operation and point it to multiple servers?  I would say then that if there are 20 REST operations, I would have 20 datasets (unless there is a better way)
2. How can I give the user a list of servers to select from for querying the data and then, based on what they selected, query each server for their data

Being new to Power BI, if I am thinking about this in the wrong way or there is a better way, please don't hesitate to give me a better way to do this.

Thanks.

[v-ljerr-msft]

Hi @Steve0212,

According to your description above, using Query Parameters should be an option here in your scenario. You can create different Parameters for your servers and operations, then referencing these parameters on each input field on the Data Source dialog like below.

Within the Report view, users can edit parameter values by using the “Edit Parameters” button in the Home ribbon tab (under “Edit Queries”). This will bring up a dialog to allow users to reconfigure parameter values and will refresh the data and report visuals based on the new parameter values.

For more details about Query Parameters, you can refer to this article. 

Regards

[Steve0212]

Thank you for the reply.  So, using that, could I create one dataset of web (for one of the REST calls), using advanced and parameters and then put it on the report multiple times with different parameters?

[v-ljerr-msft]

Hi @Steve0212,

Based on my understanding, you may need to use the Web connector with advanced and parameters multiple times(maybe 5 of 20 or more in your scenario) to import your data into different queries(tables) in one dataset. Then you can edit the parameters to change and refresh the data tables among all the 20 based on the new parameter values.

I know it's not a perfect solution here, but it should somehow help in your scenario.

Regards

[Steve0212]

Using both of your suggestions, I can create a query with parameters and use the advanced setting to then use them in the URL.  However, it appears to me that I can only set the parameter once per report.  That will not work.  I want a report that accesses all of the sources and puts the data together.  This does not appear to be possible unless I create one dataset per JSON query which is what I am trying to avoid.

Am I missing something?

[v-ljerr-msft]

Hi @Steve0212,

As I have mentioned above, it won't help much if you want to get all of the sources and put the data together at one time. But you can get parts of sources(let's say 5 of 20) each time, then change the parameters to get other parts of sources within a single dataset. 

Regards