Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
Solved! Go to 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.
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.
User | Count |
---|---|
140 | |
113 | |
102 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |