Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RohiniP-26
Resolver I
Resolver I

View created in ssms is not listed in Power BI Desktop

Hi All,

My ssms instance has two databases say A and B. I have created one view in each database. When i try to connect ssms from Power BI Desktop, it lists the view which i created in database B but not the view created in database A. Both ssms and Power BI Desktop exist in same physical machine and the source(ssms) is connected to Power BI Desktop using windows authentication. The windows user has server roles sysadmin, public and user mapping to both db as dbowner, dbreader, dbwriter, public access in ssms.

 

Why the view created in database A is not listed in Power BI Desktop?

1 ACCEPTED SOLUTION

Hi @lbendlin 

Yes, the datasource is Sql server. I am using Power BI Desktop to connect with sql server. When we connect to Sql server, if we didn't specify database name, then it will list all databases of its instance. During that time, if i expand database B, then i can see the view which i have created in db 'B' but if i expand database A, then i couldn't see the view created in db 'A'. This was my problem statement.

 

Thank you for spending your time and reply but anyhave i found the solution.

 

Solution: When we connect to sql server, Power BI lists only top 10,000 tables(including views) in an alphabetical order of a db. The db 'A' consists of more than 10000 tables & views. The view name which i have created starts with letter 'z', so it didn't get displayed in top 10,000 views & tables. To lists this view, either i shoud rename the view name with prefix increasing alphabetical order or i can select any of the displayed view and go to transform data, there in the source i can edit view name and load the view which i needed. This limitation is not mentioned in Power BI Documentation.

View solution in original post

2 REPLIES 2
lbendlin
Super User
Super User

SSMS is not a data source. You probably mean a SQL Server instance?

 

Usually you connect to a SQL server database from Power BI, not to a SQL server  (the latter is possible in Desktop but unsupported in the service). So you wouldn't be able to see both views at the same time anyway.

Hi @lbendlin 

Yes, the datasource is Sql server. I am using Power BI Desktop to connect with sql server. When we connect to Sql server, if we didn't specify database name, then it will list all databases of its instance. During that time, if i expand database B, then i can see the view which i have created in db 'B' but if i expand database A, then i couldn't see the view created in db 'A'. This was my problem statement.

 

Thank you for spending your time and reply but anyhave i found the solution.

 

Solution: When we connect to sql server, Power BI lists only top 10,000 tables(including views) in an alphabetical order of a db. The db 'A' consists of more than 10000 tables & views. The view name which i have created starts with letter 'z', so it didn't get displayed in top 10,000 views & tables. To lists this view, either i shoud rename the view name with prefix increasing alphabetical order or i can select any of the displayed view and go to transform data, there in the source i can edit view name and load the view which i needed. This limitation is not mentioned in Power BI Documentation.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.