Friday, June 2, 2017

Power BI does not show all columns in SQL Server View

I experienced a strange issue with Power BI today, I might have missed something or there is something unknown to me. This is what I experienced; Power BI does not show all columns when trying to import records from a SQL Server view.

Just for simulating the issue, I created a view with AdventureWorks2014, using three tables, Product, ProductSubCategory, and ProductCategory, and named as vProduct.

  1. USE [AdventureWorks2014]  
  2. GO  
  3.   
  4. CREATE OR ALTER VIEW dbo.vProduct  
  5. AS  
  6.  SELECT p.ProductID, p.Name Product, s.Name SubCategory, c.Name Category  
  7.  FROM Production.Product p  
  8.   INNER JOIN Production.ProductSubcategory s  
  9.    ON p.ProductSubcategoryID = s.ProductSubcategoryID  
  10.   INNER JOIN Production.ProductCategory c  
  11.    ON s.ProductCategoryID = c.ProductCategoryID;  
  12. GO  

Then I granted SELECT permission to Jack user.

  1. GRANT SELECT ON dbo.vProduct TO Jack;  
  2. GO  

If Jack executes a SELECT against the view, he sees records with all columns.


Now, if Jack connects to the database with Power BI, this is what he sees.


Anyone has experienced this? I made a search but could not find a solution. I might have made a mistake or missed something but could not figure it out. Appreciate if you can share your thoughts or a solution if you have already seen this and sorted out.

I used the latest Power BI version that is May 2017.

Note that I see the same result for both Import and Direct Query.

No comments: