Forum Discussion
Connecting different data sources
Hello Everyone,
We are doing a feasibility study for a project where we would be dealing with connecting different sources (Excel, Access, CSV, SharePoint, HR Database, etc) into one PowerBI Dashboard. These connections can be extended to 30 data sources(Can go beyond that as well).
What are the challenges we could face by connecting and maintaining this dashboard?
Hi Switto ,
For multiple types of connections of data sources, the following restrictions can be considered:
- Dataset size limit - There's a 1-GB limit for datasets stored in Shared capacities in the Power BI service. If you need larger datasets, you can use Power BI Premium.
- Distinct values in a column - When caching data in a Power BI dataset (sometimes called 'Import' mode), there is a 1,999,999,997 limit on the number of distinct values that can be stored in a column.
- Row limit - When using DirectQuery, Power BI imposes a limit on the query results that are sent to your underlying data source. If the query sent to the data source returns more than one million rows, you see an error and the query fails. Your underlying data can still contain more than one million rows. You're unlikely to run into this limit as most reports aggregate the data into smaller sets of results.
- Column limit - The maximum number of columns allowed in a dataset, across all tables in the dataset, is 16,000 columns. This limit applies to the Power BI service and to datasets used in Power BI Desktop. Power BI tracks the number of columns and tables in the dataset in this way, which means the maximum number of columns is 16,000 minus one for each table in the dataset.
For specific content, you can refer to the link:
https://docs.microsoft.com/en-us/power-bi/connect-data/service-get-data
https://docs.microsoft.com/en-us/power-bi/admin/service-premium-what-isBest Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Switto , You can connect to multiple data sources in the import and composite model. You need to take of the design, else you might face performance issue.
refer: https://www.sqlbi.com/blog/marco/2017/10/02/why-data-modeling-is-important-in-powerbi/
https://www.sqlbi.com/articles/the-importance-of-star-schemas-in-power-bi/
Best Practices
https://maqsoftware.com/expertise/powerbi/power-bi-best-practices
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://www.c-sharpcorner.com/article/power-bi-best-practices-part-1/Hi Switto ,
For multiple types of connections of data sources, the following restrictions can be considered:
- Dataset size limit - There's a 1-GB limit for datasets stored in Shared capacities in the Power BI service. If you need larger datasets, you can use Power BI Premium.
- Distinct values in a column - When caching data in a Power BI dataset (sometimes called 'Import' mode), there is a 1,999,999,997 limit on the number of distinct values that can be stored in a column.
- Row limit - When using DirectQuery, Power BI imposes a limit on the query results that are sent to your underlying data source. If the query sent to the data source returns more than one million rows, you see an error and the query fails. Your underlying data can still contain more than one million rows. You're unlikely to run into this limit as most reports aggregate the data into smaller sets of results.
- Column limit - The maximum number of columns allowed in a dataset, across all tables in the dataset, is 16,000 columns. This limit applies to the Power BI service and to datasets used in Power BI Desktop. Power BI tracks the number of columns and tables in the dataset in this way, which means the maximum number of columns is 16,000 minus one for each table in the dataset.
For specific content, you can refer to the link:
https://docs.microsoft.com/en-us/power-bi/connect-data/service-get-data
https://docs.microsoft.com/en-us/power-bi/admin/service-premium-what-isBest Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
