Problem to import data from SQL Server View

[thiagogouveiar]

Hi,

I've all my data in Azure SQL Server and I created some views to use in Power BI Desktop. However, when I create a view with some calculations (e.g. groupby) in SQL Server Management, Power BI cannot import the data:

Failed to save changes to server. Error returned: "OLE DB or ODBC error: [DataSource.Error] Microsoft SQL: Query timeout exceeded.
".

Some disclaimers:

1) Power BI works for this view when I use Direct Query

2) For simpler views (just selecting some columns from a table), it´s possible to import data

SQL CODE to create a view:

CREATE VIEW marketing.Leads5 AS
        SELECT DISTINCT (l.Email) AS [Email Lead]
        , l.Cargo AS Cargo
        , l.ID_lead as id_lead
        , l.Empresa as Empresa
        , l.Funcionários as [Tamanho da Empresa]
        , l.Segmento as Segmento
        , l.Website as [Site da Empresa]
         , COUNT(c.Identificador) as Conversões
FROM dbo.leads l
LEFT JOIN dbo.conversions c
ON l.Email = c.Email
GROUP BY l.Email, l.Cargo, l.ID_lead, l.Empresa , l.Funcionários , l.Segmento, l.Website

Could anyone help me with this issue?

[v-sihou-msft]

Hi,

There are many factors which can cause the query timeout, data amount, network, etc. In this scenario, I suggest you separate the view into multiple views and only import useful columns. See: https://www.sqlbi.com/articles/data-import-best-practices-in-power-bi/

Also you can set the timeout property in Power Query within Advanced Editor.

For more details, please see: https://blog.crossjoin.co.uk/2014/09/29/advanced-options-for-loading-data-from-sql-server-with-power...

Regards,

Simon Hou