cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DAN1980 Regular Visitor
Regular Visitor

Import Data from Multiple Data Source and Servers

Hi All,

I need to make a Dashboard,

which Import Data from only one Server (from 2 servers) and only one Table (from 3 tables)
On Demand (Create by QUERY and Parameters).

 

I need to know :
1. Is there any possibility to make Query which allow User to choose server and Table by Query and Parameters ?

 

More Info:

I Have 2 servers USA & GERMANY

In addition I have 3 Tables Types: Daily, Weekly, Monthly per each server  (All Data Aggregate by Each Period Type)

 

when client open Power BI Report he will see the follows: (UI)

Choose Country: USA \ GERMANY

Choose Report Type: Daily\Weekly\Monthly 

From Date:

To Date:

 

Afterwards, the query will go to the correct server and correct  table and get the data.

 

Hope for your Answers.

 

Tks,

Dan

 

 

 

11 REPLIES 11
Super User
Super User

Re: Import Data from Multiple Data Source and Servers

Yes, you would do this via Query Parameters and then use those query parameters in your Source and/or Navigation line(s).


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

DAN1980 Regular Visitor
Regular Visitor

Re: Import Data from Multiple Data Source and Servers

Hi Greg,

Good to hear 🙂

Can you recommend me on books/blogs/youtube or

any other data source to get practical knowledge to solve this issue ?

I know how to use parameters and SQL Procedure.

However, I don't have all the knowledge how to deal with that.

 

hope for respond.

 

tks a lot !

Dan

Moderator v-yuezhe-msft
Moderator

Re: Import Data from Multiple Data Source and Servers

@DAN1980,

Please review the following example.
1.PNG2.PNG3.PNG4.PNG5.PNG

Reference:
https://powerbi.microsoft.com/en-us/blog/deep-dive-into-query-parameters-and-power-bi-templates/

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DAN1980 Regular Visitor
Regular Visitor

Re: Import Data from Multiple Data Source and Servers

Hi Lydia,

Tks for your respond.

But it's not what I meant.

 

Let me clarify.

When client will open Power BI the next follows will appear:

1.Choose Country: USA \ GERMANY

2.Choose Report Type: Daily\Weekly\Monthly 

3.From Date:

4.To Date:

 

Afterwards,

According to those parameters the query  will act - Go to server and relevant table.

All the process of choosing server and table will be Behind the Scenes
(server name and table name don't show in any stage of the process)

see the next simlar example in picture:
in this example I create SQL Procedure which received User Name, Password and dates and get the data.

(in this case data are imported from only one server and one table.
therefore it's not create trhe flexibility as I expect.
I as sumewe should use Dummy Variables In SQL Statement (see in picture)
and use M Query as you mentioned.

pic1.JPG

pic3.jpg

 

 

Hope For respond.

 

Tks,

Dan

Moderator v-yuezhe-msft
Moderator

Re: Import Data from Multiple Data Source and Servers

@DAN1980,

It seems that you want to use parameter in the SQL stored procedure. If so, please take a look at the following blog and similar thread.

https://www.c-sharpcorner.com/article/execute-sql-server-stored-procedure-with-user-parameter-in-pow...
https://community.powerbi.com/t5/Integrations-with-Files-and/parameters-to-SP-or-Sql-query/td-p/5773...

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DAN1980 Regular Visitor
Regular Visitor

Re: Import Data from Multiple Data Source and Servers

Hi Lydia,

I read the articles you added.

This articles not answer on the next question:

How can I switch between servers and tables Behind the Scenes according Client Details ?

 

For Example:

When Client choose country USA the query should return USA Server address (Behind the Scenes)

When Client choose Country Germany the query should  return Germany Server address (Behind the Scenes)

 

In addition:

When client choose daily report the query should return daily table  (Behind the Scenes)
when client choose weekly report the query should return weekly table (Behind the Scenes)

 

Hope for respond

 

Tks A lot !

Dan

Moderator v-yuezhe-msft
Moderator

Re: Import Data from Multiple Data Source and Servers

@DAN1980,

What you describe can't be achieved in Power BI. You would need to create explicit server parameters for users to choose.

For daily/weekly report requirement, a method is to use bookmark feature in Power BI Desktop.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DAN1980 Regular Visitor
Regular Visitor

Re: Import Data from Multiple Data Source and Servers

Hi Lydia,

Bookmark Allow Users to switch between  Tables Daily /Weekly/Monthly.

But, all tables need to upload to power bi in advance.

is there any possibility to choose the relevant table in query before uploading ?

 

Dan

Highlighted
Moderator v-yuezhe-msft
Moderator

Re: Import Data from Multiple Data Source and Servers

@DAN1980,

It is only possible when you have  these tables in SQL data source and create table parameter in Power BI Desktop.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 52 members 732 guests
Please welcome our newest community members: