is possible combine different data source ( Web, SQL and Oracle) in one power BI file (.pbix) ?

[binbing]

Hello ,

I need create a dashboard, data source from Web site, SQL server , Oracle DB and excel file.
I'd like to know is that possible combine several different data source in one power BI file (.pbix) ?
if yes, how to do it?
thank you in advance!

[v-shex-msft]

Hi @binbing,

it is possible to combine them on import model. For live mode, it is impossible to achieve your requirement.

>>if yes, how to do it?

After finish adding one source, you can direct use 'get data' to add other one.

BTW, if you combine lots of different sources in your pbix file, it may caused the refresh issue when you publish to power bi service and setting schedule refresh.

Regards,

Xiaoxin Sheng

[binbing]

Hi Xiaoxin,

thanks for your reply.

One more question.

'After finish adding one source, you can direct use 'get data' to add other one.'

I'd like to confirm if I can use directquery, instead of importing data?

Because I find that when I add a data source （Oracle） and then add a second data source （SQL server) , the following information appears:

'Switching to import mode

The data source you are trying to connect to is different than the existing DirectQuery data source. To continue, all query must be switched to import mode, which may result in a large amount of data being loaded.'

Does this mean that I can't use directquery mode for multiple data sources （Oracle, SQL server, .. ) at the same time?

thank you in advance!

[v-shex-msft]

Hi @binbing,

>>Does this mean that I can't use directquery mode for multiple data sources （Oracle, SQL server, .. ) at the same time?

Yes, combine multiple datasources not available in 'live mode' and 'direct query' mode.

Limitations of DirectQuery

There are currently a few limitations to using DirectQuery:

• All tables must come from a single database

• If the Query Editor query is overly complex, an error will occur. To remedy the error you must either delete the problematic step in Query Editor, or Import the data instead of using DirectQuery. For multi-dimensional sources like SAP Business Warehouse, there is no Query Editor

• Relationship filtering is limited to a single direction, rather than both directions (though it is possible to enable cross filtering in both directions for DirectQuery as a Preview feature). For multi-dimensional sources like SAP Business Warehouse, there are no relationships defined in the model

• Time intelligence capabilities are not available in DirectQuery. For example, special treatment of date columns (year, quarter, month, day, so on) are not supported in DirectQuery mode.

• By default, limitations are placed on DAX expressions allowed in measures; see the following paragraph (after this bulleted list) for more information

• There is a 1 million row limit for returning data when using DirectQuery. This does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.

Reference link:

Use DirectQuery in Power BI Desktop

Regards,

Xiaoxin Sheng

[binbing · Accepted solution]

Hi Xiaoxin,

I got it. 

thank you so much!

[binbing]

Hello , I need create a dashboard, data source from Web site, SQL server , Oracle DB and excel file. I'd like to know is that possible combine several different data source in one power BI file (.pbix) ? if yes, how to do it? thank you in advance!