Import Data from Multiple Data Source and Servers

[Anonymous]

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

[Greg_Deckler]

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

[Anonymous]

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

[Anonymous]

@Anonymous,

Please review the following example.


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

Regards,
Lydia

[Anonymous]

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.

Hope For respond.

Tks,

Dan

[Anonymous]

@Anonymous,

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-power-bi/
https://community.powerbi.com/t5/Integrations-with-Files-and/parameters-to-SP-or-Sql-query/td-p/57734

Regards,
Lydia

[Anonymous]

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

[Anonymous]

@Anonymous,

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

[Anonymous]

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

[Anonymous]

@Anonymous,

As my previous reply, you would need to create table parameter in Power BI Desktop.

Regards,
Lydia

[Anonymous]

@Anonymous,

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

Regards,
Lydia

[Anonymous]

I have those tables in my Data Base.

It will be great to receive your help or recommendation for it.

Lydia, Tks A lot !

Dan